Wednesday, March 21, 2012

save upload image to db using stored procedure problem

I am trying to save an uploaded image and its associated info to sql server database using a stored procedure but keep getting trouble. When trying to save, the RowAffected always return -1. but when i debug it, I dont' see problem both from stored procedure server
explore and codebehind. it looks to me every input param contains correct value(such as the uploaded image file name, contentType and etc). well, for the imgbin its input param value returns something like "byte[] imgbin={Length=516}". Below is my code, could anyone help to point out what did I do wrong?
Thank you.

================================================
CREATE PROCEDURE [dbo].[sp_SaveInfo]
(
@.UserID varchar(12),
@.Image_FileName nvarchar(50),
@.Image_ContentType nvarchar(50),
@.Image_ImageData image,
@.Create_DateTime datetime)

AS
set nocount on

insert ExpertImage(UserID, Image_FileName, Image_ContentType, Image_ImageData, Image_ReceiveDateTime)
values(@.UserID, @.Image_FileName, @.Image_ContentType, @.Image_ImageData, @.Create_DateTime)
GO

private void Submit1_ServerClick(object sender, System.EventArgs e)
{
if(Page.IsValid)
{
Stream imgStream = File1.PostedFile.InputStream;
int imgLen=File1.PostedFile.ContentLength;
string imgContentType = File1.PostedFile.ContentType;
string imgName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\\") + 1);
byte[] imgBinaryData = new byte[imgLen];
int n=imgStream.Read(imgBinaryData, 0, imgLen);
int RowsAffected = SaveInfo(imgName,imgBinaryData, imgContentType);
if(RowsAffected > 0)
{
..
}
else
{
..
}
}
}

public int SaveInfo(string imgName, byte[] imgbin, string imgcontenttype)
{

SqlConnection objConn = new DSConnection().DbConn;
SqlCommand objCMD = new SqlCommand("sp_SaveInfo", objConn);
objCMD.CommandType = CommandType.StoredProcedure;

objCMD.Parameters.Add("@.UserID", SqlDbType.VarChar, 12);
objCMD.Parameters["@.UserID"].Value = txtMemberID.Text.ToString();
objCMD.Parameters["@.UserID"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Create_DateTime", SqlDbType.DateTime);
objCMD.Parameters["@.Create_DateTime"].Value = DateTime.Now.ToLongTimeString();
objCMD.Parameters["@.Create_DateTime"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Image_FileName", SqlDbType.NVarChar, 50);
objCMD.Parameters["@.Image_FileName"].Value = imgName;
objCMD.Parameters["@.Image_FileName"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@.Image_ContentType", SqlDbType.NVarChar, 50);
objCMD.Parameters["@.Image_ContentType"].Value = imgcontenttype;
objCMD.Parameters["@.Image_ContentType"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Image_ImageData", SqlDbType.Image);
objCMD.Parameters["@.Image_ImageData"].Value = imgbin;
objCMD.Parameters["@.Image_ImageData"].Direction = ParameterDirection.Input;

int numRowsAffected = objCMD.ExecuteNonQuery();
return numRowsAffected;

}Are you saying you don't get an exception and there row isn't inserted? BTW Don't prefix your procs with "sp_" its bad for perf' they're really reserved for "system procedure".

No comments:

Post a Comment