May 17, 2011 at 12:13 am
When I update an image column through a program, it fails when the file being uploaded is more than 400 K!
It was working smooth before with no problems
May 17, 2011 at 5:34 am
Assuming you have the image datatype and that you have enough space in the db / drives, it should be as simple as insert into or update... like any other columns.
Please post the code you are using and the error message you are getting.
May 17, 2011 at 10:34 pm
I get this error when the file is more than 400K:
A severe error occurred on the current command. The results, if any, should be discarded. A severe error occurred on the current command. The results, if any, should be discarded. - at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at FuncName(String strType, Byte[]& Buffer, Int32 FileLen, String DocumentID1, String FileContentType1)
And the source is:
try
{
conn = new SqlConnection();
conn.ConnectionString=ConfigurationSettings.AppSettings.Get("SomeConnectionString");
cmd = new SqlCommand("storedProcName goes here",conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
DocumentIDParameter = new SqlParameter("@DocumentID_1",SqlDbType.Decimal, 0);
DocumentIDParameter .Precision = 18;
DocumentIDParameter.Value = DocumentID1;
FileDataParameter = new SqlParameter("@FileData_10", SqlDbType.Image,FileLen);
FileDataParameter.Value = Buffer;
FileTypeParameter = new SqlParameter("@FileType_11", SqlDbType.Char,5);
FileTypeParameter.Value= strType;
FileSizeParameter = new SqlParameter("@FileSize_12", SqlDbType.BigInt);
FileSizeParameter.Value=FileLen;
FileContentTypeParameter = new SqlParameter("@FileContentType_13", SqlDbType.Char, 20);
FileContentTypeParameter.Value=FileContentType1;
cmd.Parameters.Add(DocumentIDParameter );
cmd.Parameters.Add(FileDataParameter );
cmd.Parameters.Add(FileTypeParameter );
cmd.Parameters.Add(FileSizeParameter );
cmd.Parameters.Add(FileContentTypeParameter );
cmd.CommandTimeout=1000;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
cmd.Dispose();
}
catch(Exception e)
{
Console.WriteLine(e.Message + " - " + e.StackTrace);
Response.Write(e.Message + " - " + e.StackTrace);
}
Error happens on cmd.ExecuteNonQuery();
May 18, 2011 at 5:48 am
That's weird. So the code works for 300K but fails at 400K?
That makes me think the code works, that the server is setup correctly and that maybe you have something else wrong maybe in the web server.
Just for the heck of it, I found a recent code I wrote to upload / save images in the db. The sql server was 2008 but we could save images of 18 MB with that code.
So assuming you can use my code and that it still fails you know you have to look outside your code, and possibly outside sql server.
I would start looking at the logs of all servers to see if I can get more info.
public void cmdUploadGarantieMarque_Click(object sender, EventArgs e)
{
if (this.FileUploadGarantieMarque.HasFile)
{
if (this.FileUploadGarantieMarque.FileName.ToLower().Contains(".pdf"))
{
HttpPostedFile objHttpPostedFile = this.FileUploadGarantieMarque.PostedFile;
int intContentlength = objHttpPostedFile.ContentLength;
Byte[] bytImage = new Byte[intContentlength];
objHttpPostedFile.InputStream.Read(bytImage, 0, intContentlength);
this.FileUploadGarantieMarque.PostedFile.InputStream.Dispose();
PP_Admin_SaveMakerWarranty(bytImage, this.ddlMarquesGaranties.SelectedValue, "application/pdf");
this.ImgBtnPDFMarque.Visible = true;
this.LinkPdfMarque.HRef = "../FetchImageFromId.aspx?CodeWarranty=" + HttpUtility.UrlEncode(this.ddlMarquesGaranties.SelectedValue);
}
}
}
public void PP_Admin_SaveMakerWarranty(byte[] pdf, string CodeMarque, string MimeType)
{
SqlConnection conn = new SqlConnection("Data Source=SERVEURNAV;Initial Catalog=GBB;Persist Security Info=True;User ID=Commandesweb;Password=WebOrders1234;Application Name=FS");
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = @"
INSERT INTO GBB.dbo.[Pneus Supérieurs Inc_$Item Category Warranty] ( Code , image_data, MimeType )
SELECT @Code
, @Img
, @MimeType
WHERE NOT EXISTS ( SELECT 'exists'
FROM dbo.[Pneus Supérieurs Inc_$Item Category Warranty] IC
WHERE IC.Code = @Code )
IF @@ROWCOUNT = 0
BEGIN
UPDATE GBB.dbo.[Pneus Supérieurs Inc_$Item Category Warranty] SET image_data = @Img, MimeType = @MimeType WHERE Code = @Code
END
";
SqlParameter param = new SqlParameter("@Code", CodeMarque);
comm.Parameters.Add(param);
param = new SqlParameter("@img", pdf);
param.SqlDbType = SqlDbType.Image;
comm.Parameters.Add(param);
param = new SqlParameter("@MimeType", GetAdjustedMimeType(MimeType));
comm.Parameters.Add(param);
comm.ExecuteNonQuery();
comm.Parameters.Clear();
conn.Close();
}
May 20, 2011 at 10:35 pm
Thanks for the reply.
I checked the SQL log, there was a message saying "insufficient memory"
And the spid it referes to is for a database which is not the one which my program is working with it.
ANy idea?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply