Exception while excecuting insert query to SQL Server from c#

Question

My application was working well but customer wanted me to add some features to it. Now when I run the app any thing is working but by clicking a button that executes an insert query an exception occurs with this message:

The parameterized query '(@ID int,@Subj nvarchar(50),@Pic varbinary(8000),@LetDate date,@' expects the parameter '@Pic', which was not supplied.

what are those silly parentheses? the code of this part of program:

conn.Open();
string sqlcmd = "Insert into Pictures (ID, Subj, Pic, LetDate, LetTitle) Values (@ID, @Subj, @Pic, @LetDate, @LetTitle)";
insertCommand = new SqlCommand(sqlcmd, conn);
// For image data, we save the bytes into the database. We save the image to the JPG format bytes. 
insertCommand.Parameters.Add("ID", SqlDbType.Int).Value = (++lastID);
insertCommand.Parameters.Add("Subj", SqlDbType.NVarChar, 50).Value = textBox1.Text;
insertCommand.Parameters.Add("Pic", SqlDbType.VarBinary).Value = dynamicDotNetTwain1.SaveImageToBytes(lastIndex, Dynamsoft.DotNet.TWAIN.Enums.DWTImageFileFormat.WEBTW_JPG);
insertCommand.Parameters.Add("LetDate", SqlDbType.Date).Value = dateTimeSelector1.Value.Value.Date;
insertCommand.Parameters.Add("LetTitle", SqlDbType.NText).Value = titleTextBox1.Text;
index++;
int queryResult = insertCommand.ExecuteNonQuery();
if (queryResult == 1)
    MessageBox.Show("تصویر با موفقیت در پایگاه داده ذخیره شد", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();

"silly parentheses"? Just data type size (it's a _favor_ SS does to help you to understand query errors, just ignore them). Is @Pic null and Pic not nullable? (or...is this code that throws exception?) — Adriano Repetti, Aug 31 '15 at 13:27
sorry I thought ")" in "ar(50)" is the end for "(@ID". @pic is nullable but here is not null. the control of it's value is a TWAIN component — Shaho, Aug 31 '15 at 13:31

score 1 · Accepted Answer · answered Aug 31 '15 at 14:07

This is an error occuring elsewhere in your program, and is unrelated to your stored procedure execution code.

The SQL error message "expects the parameter '@VariableName', which was not supplied." usually indicates that you are setting a parameter value to null. If you actually intend to pass NULL to the query, you have to use the DBNull.Value value. However, it looks like you are trying to pass an actual value. Thus, the source of your issue is this line:

insertCommand.Parameters.Add("Pic", SqlDbType.VarBinary).Value =  dynamicDotNetTwain1.SaveImageToBytes(lastIndex, Dynamsoft.DotNet.TWAIN.Enums.DWTImageFileFormat.WEBTW_JPG);

If you can figure out why the dynamicDotNetTwain1.SaveImageToBytes() function is returning null and get that function working as expected, then you're stored procedure will work as expected.

Thanks for your nice guidance. First I thought the component license has expired but it is correct. so I think problem is in lastIndex variable that defines which picture in buffer will save into SQL Server — Shaho, Aug 31 '15 at 14:36

score -1 · Answer 2 · answered Aug 31 '15 at 13:31

I think you have forgot to put @ infront of the variable names...

insertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = (++lastID);
insertCommand.Parameters.Add("@Subj", SqlDbType.NVarChar, 50).Value = textBox1.Text;
insertCommand.Parameters.Add("@Pic", SqlDbType.VarBinary).Value = dynamicDotNetTwain1.SaveImageToBytes(lastIndex, Dynamsoft.DotNet.TWAIN.Enums.DWTImageFileFormat.WEBTW_JPG);
insertCommand.Parameters.Add("@LetDate", SqlDbType.Date).Value = dateTimeSelector1.Value.Value.Date;
insertCommand.Parameters.Add("@LetTitle", SqlDbType.NText).Value = titleTextBox1.Text;

While that is a best practice it's not required http://stackoverflow.com/questions/10245510/is-it-necessary-to-add-a-in-front-of-an-sqlparameter-name — juharr, Aug 31 '15 at 14:29

Exception while excecuting insert query to SQL Server from c#

2 Answers2