Error in executing parameterised query  
Author Message
TA123





PostPosted: .NET Framework Data Access and Storage, Error in executing parameterised query Top

Hi,

I have a table Role in sql database wih following fields

RoleID pk int

Description nvarchar(50)

I am trying to run a parameterised query using sqlcommand and exceutenonquery using following code but it is giving error

"Incorrect syntax near ' '.\r\nIncorrect syntax near ' ":

------------------------------------------------------------------------------------------------------------------------------

SqlConnection objConn = new SqlConnection("Data Source=HDDLNTD6011180;Initial Catalog=TestDal;Integrated Security=True");

objConn.Open();

System.Data.IDbCommand cmd = new SqlCommand("UPDATE [Role] SET [Description] = WHERE [RoleID] = ",objConn);

IDataParameterCollection cmdParams = cmd.Parameters;

System.Data.IDbDataParameter parDescription = cmd.CreateParameter();

parDescription.ParameterName = " ";

parDescription.Value = "testing";

cmdParams.Add(parDescription);

System.Data.IDbDataParameter checkRoleID = cmd.CreateParameter();

checkRoleID.ParameterName = " ";

checkRoleID.Value = 15; //any test value which is present in database

cmdParams.Add(checkRoleID);

cmd.ExecuteNonQuery();

-----------------------------------------------------------------------------------------

I don't see any fault at my end.Please let me know how to resolve this issue.

regards

 

 

 



.NET Development11  
 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Error in executing parameterised query Top


You don't need to specify the ParameterName in this instance since you're not using named parameters. I would remove the ParameterName references since they could be causing the problem.

 
 
Ed Mundorf





PostPosted: .NET Framework Data Access and Storage, Error in executing parameterised query Top

Don't use the " " approach. Use something like this:

System.Data.IDbCommand cmd = new SqlCommand( ,objConn);

cmd.Parameters.Add(DbFactory.CreateDbParameter( , DbType.String, 50, ParameterDirection.Input, "some description"));

cmd.Parameters.Add(DbFactory.CreateDbParameter( , DbType.Int32, 4, ParameterDirection.Input, 23));

cmd.ExecuteNonQuery();


 
 
Sarah Parra - MSFT





PostPosted: .NET Framework Data Access and Storage, Error in executing parameterised query Top

Here is a reference that shows the parameter syntax that is supported with each Microsoft .NET data provider:

Working with Command Parameters
http://msdn2.microsoft.com/en-us/library/yy6y35y8.aspx

As the previous posters have indicated, you need to use named parameters with SqlClient. The syntax only works with ODBC and OleDb.

Thanks,
Sarah