5

I am getting an error when execution reaches cmd.ExecuteNonQuery() which says Must declare the scalar variable:

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Connection;
cmd.CommandTimeout = 0;

string commandText = "update groups set subjectline ='" +    txtSubjectLine.Text + "',data= @data where groupid = " + ddlGroup.SelectedItem.Value + " ";
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;

cmd.Parameters.Add("@Data",OleDbType.VarBinary);
cmd.Parameters["@Data"].Value = binarydata;               
cmd.ExecuteNonQuery();
Eitan T
  • 32,660
  • 14
  • 72
  • 109
Priscilla Jobin
  • 609
  • 7
  • 19

2 Answers2

4

Replace 

string commandText = "update groups set subjectline ='" +    txtSubjectLine.Text + "',data= @data where groupid = " + ddlGroup.SelectedItem.Value + " ";

with

string commandText = "update groups set subjectline ='" +    txtSubjectLine.Text + "',data= ? where groupid = " + ddlGroup.SelectedItem.Value + " ";

that is, replace "@data" with "?" in the command text. This is how you specify parameter placeholders with OleDbCommand.

Here's the edited original:

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Connection;
cmd.CommandTimeout = 0;

cmd.CommandText = "update groups set subjectline ='" + txtSubjectLine.Text + "', data = ? where groupid = " + ddlGroupSelectedItem.Value;
cmd.CommandType = CommandType.Text;

cmd.Parameters.Add("p1", OleDbType.VarBinary);
cmd.Parameters["p1"].Value = binarydata;

cmd.ExecuteNonQuery();
m1kael
  • 2,801
  • 1
  • 15
  • 14
  • m getting error as: 'No value given for one or more required parameters.' – Priscilla Jobin May 29 '12 at 10:44
  • this error occurs when you don't specify parameters (for instance, with cmd.Parameters.Add( ). here's the working code (simplified): OleDbCommand cmd = new OleDbCommand(); cmd.Connection = new OleDbConnection("Data Source=.; Initial Catalog=Titles; Integrated Security=SSPI; Provider=SQLOLEDB;"); cmd.Connection.Open(); cmd.CommandType = CommandType.Text; cmd.CommandText = "update titles set name = ? where id = 1"; cmd.Parameters.Add("p1", OleDbType.VarWChar); cmd.Parameters["p1"].Value = "new name"; cmd.ExecuteNonQuery(); – m1kael May 29 '12 at 10:58
  • i just changed the @data to ? in the above code which i mentioned which keeps cmd.Parameters.Add() as it is. Then after execution i got 'No value given for one or more required parameters.' – Priscilla Jobin May 29 '12 at 11:02
  • just replace your part of code with the one in my edit to the answer. – m1kael May 29 '12 at 11:44
0

This link shows accepted answer on how to update binarydata to table using .Write

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/dc1b053d-f0d5-48f8-ad82-fb6d96d27f80

If that not solves your problem go ahead and read below:

You have used variable @data whereas you are declaring variable @Data, both are of different case.

In certain condition variable names can be case sensitive in TSQL, for instance if MS Sql server is installed using case sensitive collation, then table, column, variable names become case sensitive, even if database has case insensitive collation.

see the following links for more details.

Case sensitive variables in SQL Server

Is SQL syntax case sensitive?

Community
  • 1
  • 1
Imran Rizvi
  • 7,331
  • 11
  • 57
  • 101