locked
Anyone know how to fix this error?? **Newbie** RRS feed

  • Question

  • User701507221 posted

    I get this when I fire up the application.   I migrated the tables and storted procedures to SQL.   I changed the connection string, which I know works.  The albums.aspx display the albums along with the photos I moved using DTS.  If I click on one of the images I get the same error as below. 

    What am I missing??? 

     

    Dean

     

    Procedure or function 'GetPhotos' expects parameter '@PhotoID', which was not supplied.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'GetPhotos' expects parameter '@PhotoID', which was not supplied.

    Source Error:

    Line 82: 				connection.Open();
    Line 83: 				List<Photo> list = new List<Photo>();
    Line 84: 				using (SqlDataReader reader = command.ExecuteReader()) {
    Line 85: 					while (reader.Read()) { 
    Line 86: 						Photo temp = new Photo(

    Source File: c:\Documents and Settings\Dean\Desktop\OrangeTrak\App_Code\PhotoManager.cs    Line: 84

    Stack Trace:

    [SqlException (0x80131904): Procedure or function 'GetPhotos' expects parameter '@PhotoID', which was not supplied.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857306
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734918
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
       System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
       System.Data.SqlClient.SqlDataReader.get_MetaData() +62
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
       System.Data.SqlClient.SqlCommand.ExecuteReader() +84
       PhotoManager.GetPhotos(Int32 AlbumID) in c:\Documents and Settings\Dean\Desktop\OrangeTrak\App_Code\PhotoManager.cs:84
       PhotoManager.GetPhotos() in c:\Documents and Settings\Dean\Desktop\OrangeTrak\App_Code\PhotoManager.cs:101
    
    [TargetInvocationException: Exception has been thrown by the target of an invocation.]
       System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
       System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
       System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +296
       System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
       System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482
       System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
       System.Web.UI.WebControls.FormView.DataBind() +4
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
       System.Web.UI.WebControls.FormView.EnsureDataBound() +163
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
       System.Web.UI.Control.EnsureChildControls() +87
       System.Web.UI.Control.PreRenderRecursiveInternal() +41
       System.Web.UI.Control.PreRenderRecursiveInternal() +161
       System.Web.UI.Control.PreRenderRecursiveInternal() +161
       System.Web.UI.Control.PreRenderRecursiveInternal() +161
       System.Web.UI.Control.PreRenderRecursiveInternal() +161
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
    

    Wednesday, May 9, 2007 1:26 PM

All replies

  • User-319574463 posted
    Although the error was reported at line 84, the problem lies some lines back where there is a call to GetPhotos. Please post that block of code together with the stored procedure.
    Wednesday, May 9, 2007 2:07 PM
  • User701507221 posted

    The stored procedure and the block of code have not been changed from what comes in the personal stater kit.  But  here it is

    Stored Proc:

    ALTER PROCEDURE GetPhotos

    @PhotoID int,

    @Size int,

    @IsPublic bit

    AS

    IF @Size = 1

    SELECT TOP 1 [BytesThumb] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 2

    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 3

    SELECT TOP 1 [BytesFull] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 4

    SELECT TOP 1 [BytesOriginal] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE

    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    RETURN

     

    Code:

     

    using System;

    using System.Collections;

    using System.Collections.Generic;

    using System.Configuration;

    using System.Data;

    using System.Data.SqlClient;

    using System.Drawing;

    using System.Drawing.Drawing2D;

    using System.Drawing.Imaging;

    using System.IO;

    using System.Web;

    using System.Web.Security;

    public class PhotoManager {

    // Photo-Related Methods

    public static Stream GetPhoto(int photoid, PhotoSize size) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@PhotoID", photoid));

    command.Parameters.Add(new SqlParameter("@Size", (int)size));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    object result = command.ExecuteScalar();

    try {

    return new MemoryStream((byte[])result);

    } catch {

    return null;

    }

    }

    }

    }

    public static Stream GetPhoto(PhotoSize size) {

    string path = HttpContext.Current.Server.MapPath("~/Images/");

    switch (size) {

    case PhotoSize.Small:

    path += "placeholder-100.jpg";

    break;

    case PhotoSize.Medium:

    path += "placeholder-200.jpg";

    break;

    case PhotoSize.Large:

    path += "placeholder-600.jpg";

    break;

    default:

    path += "placeholder-600.jpg";

    break;

    }

    return new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);

    }

    public static Stream GetFirstPhoto(int albumid, PhotoSize size) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetFirstPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", albumid));

    command.Parameters.Add(new SqlParameter("@Size", (int)size));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    object result = command.ExecuteScalar();

    try {

    return new MemoryStream((byte[])result);

    } catch {

    return null;

    }

    }

    }

    }

    public static List<Photo> GetPhotos(int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetPhotos", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    List<Photo> list = new List<Photo>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Photo temp = new Photo(

    (int)reader["PhotoID"],

    (int)reader["AlbumID"],

    (string)reader["Caption"]);

    list.Add(temp);

    }

    }

    return list;

    }

    }

     

    }

    public static List<Photo> GetPhotos() {

    return GetPhotos(GetRandomAlbumID());

    }

    public static void AddPhoto(int AlbumID, string Caption, byte[] BytesOriginal) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("AddPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@BytesOriginal", BytesOriginal));

    command.Parameters.Add(new SqlParameter("@BytesFull", ResizeImageFile(BytesOriginal, 600)));

    command.Parameters.Add(new SqlParameter("@BytesPoster", ResizeImageFile(BytesOriginal, 198)));

    command.Parameters.Add(new SqlParameter("@BytesThumb", ResizeImageFile(BytesOriginal, 100)));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void RemovePhoto(int PhotoID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("RemovePhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@PhotoID", PhotoID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void EditPhoto(string Caption, int PhotoID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("EditPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@PhotoID", PhotoID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    // Album-Related Methods

    public static List<Album> GetAlbums() {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetAlbums", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    //bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    bool filter = true;

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    List<Album> list = new List<Album>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Album temp = new Album(

    (int)reader["AlbumID"],

    (int)reader["NumberOfPhotos"],

    (string)reader["Caption"],

    (bool)reader["IsPublic"]);

    list.Add(temp);

    }

    }

    return list;

    }

    }

    }

    public static void AddAlbum(string Caption, bool IsPublic) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("AddAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@IsPublic", IsPublic));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void RemoveAlbum(int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("RemoveAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void EditAlbum(string Caption, bool IsPublic, int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("EditAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@IsPublic", IsPublic));

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static int GetRandomAlbumID() {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetNonEmptyAlbums", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    connection.Open();

    List<Album> list = new List<Album>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Album temp = new Album((int)reader["AlbumID"], 0, "", false);

    list.Add(temp);

    }

    }

    try {

    Random r = new Random();

    return list[r.Next(list.Count)].AlbumID;

    } catch {

    return -1;

    }

    }

    }

    }

    // Helper Functions

    private static byte[] ResizeImageFile(byte[] imageFile, int targetSize) {

    using (System.Drawing.Image oldImage = System.Drawing.Image.FromStream(new MemoryStream(imageFile))) {

    Size newSize = CalculateDimensions(oldImage.Size, targetSize);

    using (Bitmap newImage = new Bitmap(newSize.Width, newSize.Height, PixelFormat.Format24bppRgb)) {

    using (Graphics canvas = Graphics.FromImage(newImage)) {

    canvas.SmoothingMode = SmoothingMode.AntiAlias;

    canvas.InterpolationMode = InterpolationMode.HighQualityBicubic;

    canvas.PixelOffsetMode = PixelOffsetMode.HighQuality;

    canvas.DrawImage(oldImage, new Rectangle(new Point(0, 0), newSize));

    MemoryStream m = new MemoryStream();

    newImage.Save(m, ImageFormat.Jpeg);

    return m.GetBuffer();

    }

    }

    }

    }

    private static Size CalculateDimensions(Size oldSize, int targetSize) {

    Size newSize = new Size();

    if (oldSize.Height > oldSize.Width) {

    newSize.Width = (int)(oldSize.Width * ((float)targetSize / (float)oldSize.Height));

    newSize.Height = targetSize;

    } else {

    newSize.Width = targetSize;

    newSize.Height = (int)(oldSize.Height * ((float)targetSize / (float)oldSize.Width));

    }

    return newSize;

    }

    public static ICollection ListUploadDirectory() {

    DirectoryInfo d = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("~/Upload"));

    return d.GetFileSystemInfos("*.jpg");

    }

    }

    Wednesday, May 9, 2007 3:03 PM
  • User701507221 posted

    The stored procedure and the block of code have not been changed from what comes in the personal stater kit.  But  here it is

    Stored Proc:

    ALTER PROCEDURE GetPhotos

    @PhotoID int,

    @Size int,

    @IsPublic bit

    AS

    IF @Size = 1

    SELECT TOP 1 [BytesThumb] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 2

    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 3

    SELECT TOP 1 [BytesFull] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE IF @Size = 4

    SELECT TOP 1 [BytesOriginal] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    ELSE

    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)

    RETURN

     

    Code:

     

    using System;

    using System.Collections;

    using System.Collections.Generic;

    using System.Configuration;

    using System.Data;

    using System.Data.SqlClient;

    using System.Drawing;

    using System.Drawing.Drawing2D;

    using System.Drawing.Imaging;

    using System.IO;

    using System.Web;

    using System.Web.Security;

    public class PhotoManager {

    // Photo-Related Methods

    public static Stream GetPhoto(int photoid, PhotoSize size) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@PhotoID", photoid));

    command.Parameters.Add(new SqlParameter("@Size", (int)size));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    object result = command.ExecuteScalar();

    try {

    return new MemoryStream((byte[])result);

    } catch {

    return null;

    }

    }

    }

    }

    public static Stream GetPhoto(PhotoSize size) {

    string path = HttpContext.Current.Server.MapPath("~/Images/");

    switch (size) {

    case PhotoSize.Small:

    path += "placeholder-100.jpg";

    break;

    case PhotoSize.Medium:

    path += "placeholder-200.jpg";

    break;

    case PhotoSize.Large:

    path += "placeholder-600.jpg";

    break;

    default:

    path += "placeholder-600.jpg";

    break;

    }

    return new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);

    }

    public static Stream GetFirstPhoto(int albumid, PhotoSize size) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetFirstPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", albumid));

    command.Parameters.Add(new SqlParameter("@Size", (int)size));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    object result = command.ExecuteScalar();

    try {

    return new MemoryStream((byte[])result);

    } catch {

    return null;

    }

    }

    }

    }

    public static List<Photo> GetPhotos(int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetPhotos", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    List<Photo> list = new List<Photo>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Photo temp = new Photo(

    (int)reader["PhotoID"],

    (int)reader["AlbumID"],

    (string)reader["Caption"]);

    list.Add(temp);

    }

    }

    return list;

    }

    }

     

    }

    public static List<Photo> GetPhotos() {

    return GetPhotos(GetRandomAlbumID());

    }

    public static void AddPhoto(int AlbumID, string Caption, byte[] BytesOriginal) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("AddPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@BytesOriginal", BytesOriginal));

    command.Parameters.Add(new SqlParameter("@BytesFull", ResizeImageFile(BytesOriginal, 600)));

    command.Parameters.Add(new SqlParameter("@BytesPoster", ResizeImageFile(BytesOriginal, 198)));

    command.Parameters.Add(new SqlParameter("@BytesThumb", ResizeImageFile(BytesOriginal, 100)));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void RemovePhoto(int PhotoID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("RemovePhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@PhotoID", PhotoID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void EditPhoto(string Caption, int PhotoID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("EditPhoto", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@PhotoID", PhotoID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    // Album-Related Methods

    public static List<Album> GetAlbums() {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetAlbums", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    //bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));

    bool filter = true;

    command.Parameters.Add(new SqlParameter("@IsPublic", filter));

    connection.Open();

    List<Album> list = new List<Album>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Album temp = new Album(

    (int)reader["AlbumID"],

    (int)reader["NumberOfPhotos"],

    (string)reader["Caption"],

    (bool)reader["IsPublic"]);

    list.Add(temp);

    }

    }

    return list;

    }

    }

    }

    public static void AddAlbum(string Caption, bool IsPublic) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("AddAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@IsPublic", IsPublic));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void RemoveAlbum(int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("RemoveAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static void EditAlbum(string Caption, bool IsPublic, int AlbumID) {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("EditAlbum", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@Caption", Caption));

    command.Parameters.Add(new SqlParameter("@IsPublic", IsPublic));

    command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));

    connection.Open();

    command.ExecuteNonQuery();

    }

    }

    }

    public static int GetRandomAlbumID() {

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {

    using (SqlCommand command = new SqlCommand("GetNonEmptyAlbums", connection)) {

    command.CommandType = CommandType.StoredProcedure;

    connection.Open();

    List<Album> list = new List<Album>();

    using (SqlDataReader reader = command.ExecuteReader()) {

    while (reader.Read()) {

    Album temp = new Album((int)reader["AlbumID"], 0, "", false);

    list.Add(temp);

    }

    }

    try {

    Random r = new Random();

    return list[r.Next(list.Count)].AlbumID;

    } catch {

    return -1;

    }

    }

    }

    }

    // Helper Functions

    private static byte[] ResizeImageFile(byte[] imageFile, int targetSize) {

    using (System.Drawing.Image oldImage = System.Drawing.Image.FromStream(new MemoryStream(imageFile))) {

    Size newSize = CalculateDimensions(oldImage.Size, targetSize);

    using (Bitmap newImage = new Bitmap(newSize.Width, newSize.Height, PixelFormat.Format24bppRgb)) {

    using (Graphics canvas = Graphics.FromImage(newImage)) {

    canvas.SmoothingMode = SmoothingMode.AntiAlias;

    canvas.InterpolationMode = InterpolationMode.HighQualityBicubic;

    canvas.PixelOffsetMode = PixelOffsetMode.HighQuality;

    canvas.DrawImage(oldImage, new Rectangle(new Point(0, 0), newSize));

    MemoryStream m = new MemoryStream();

    newImage.Save(m, ImageFormat.Jpeg);

    return m.GetBuffer();

    }

    }

    }

    }

    private static Size CalculateDimensions(Size oldSize, int targetSize) {

    Size newSize = new Size();

    if (oldSize.Height > oldSize.Width) {

    newSize.Width = (int)(oldSize.Width * ((float)targetSize / (float)oldSize.Height));

    newSize.Height = targetSize;

    } else {

    newSize.Width = targetSize;

    newSize.Height = (int)(oldSize.Height * ((float)targetSize / (float)oldSize.Width));

    }

    return newSize;

    }

    public static ICollection ListUploadDirectory() {

    DirectoryInfo d = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("~/Upload"));

    return d.GetFileSystemInfos("*.jpg");

    }

    }

    Wednesday, May 9, 2007 3:03 PM
  • User-319574463 posted

    The argument list on the stored procedure bares no relationship to the calling code!  See the parts as bold!

    ALTER PROCEDURE GetPhotos
    @PhotoID int,
    @Size    int,
    @IsPublic bit
    AS
    IF @Size = 1
    SELECT TOP 1 [BytesThumb] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
    ELSE IF @Size = 2
    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
    ELSE IF @Size = 3
    SELECT TOP 1 [BytesFull] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
    ELSE IF @Size = 4
    SELECT TOP 1 [BytesOriginal] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
    ELSE
    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
    RETURN


    Code:

    public static List<Photo> GetPhotos(int AlbumID)
    {
      using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OTDB"].ConnectionString)) {
      using (SqlCommand command = new SqlCommand("GetPhotos", connection))
      {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));
        bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));
        command.Parameters.Add(new SqlParameter("@IsPublic", filter));
        connection.Open();
        List<Photo> list = new List<Photo>();
        using (SqlDataReader reader = command.ExecuteReader()) {
        while (reader.Read()) {
        Photo temp = new Photo( ( int)reader["PhotoID"], (int)reader["AlbumID"], (string)reader["Caption"]);
        list.Add(temp);
      }
    }
    return list;
    }}

    Wednesday, May 9, 2007 3:19 PM
  • User701507221 posted
    I understand that.  I made no changed to the code other than the connection string.  This all wodks fine in SQL Express.  I copied the tables and the stored procedures verbatim over to SQL Server and I get the error above.  The code is straight out of the box for the Personal Web Satrter Kit.
    Wednesday, May 9, 2007 4:54 PM
  • User-319574463 posted
    Now I am puzzled as what works in SQL Express, should work perfectly well i SQL2005.
    Wednesday, May 9, 2007 5:13 PM
  • User701507221 posted
    I blew away the tables and the stored procs and rebuilt them.  Everything works great!!!!!
    Wednesday, May 9, 2007 6:23 PM