ERROR [HY000] parameter marker count incorrect (1)

Question

Hi All I am using ODBC connection to connect my SQlite database. its is working fine for insert and delete data but if i am updating any data then i am getting below error. ERROR [HY000] parameter marker count incorrect (1) here is my query which i am using : SQLConnection.Instance.Open(); OdbcCommand mySQLiteCommand = SQLConnection.Instance._dbCon.CreateCommand(); mySQLiteCommand.CommandText = Update SyncTable set LastTime = @LastTimeStatus = @Status Where DisplayName = ' + task.Key + 'and UserName = ' + loginObj.Key + '; mySQLiteCommand.Parameters.Clear(); mySQLiteCommand.Parameters.Add(new OdbcParameter(@LastTime task.Value as object)); mySQLiteCommand.Parameters.Add(new OdbcParameter(@Status status as object)); mySQLiteCommand.ExecuteNonQuery(); SQLConnection.Instance.Close(); i am not getting where i am doing wrong.Please help me. Thanks

Vulpes · Answer

Another thing you could try is to use question marks instead of parameter names in the update statement itself. The question marks should then be replaced by the parameter values in the order they were added to the collection: mySQLiteCommand.CommandText = Update SyncTable set LastTime = ? Status = ? Where DisplayName = ' + task.Key + 'and UserName = ' + loginObj.Key + ';

Robbinson · Answer

Hi Sumit it was not working because OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters. you can get more description about how parameters are working for oledb provider over here. http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx Hope this would help you to understand why it was not working. Don't forget to mark as answer if this post helps you.

Sumit Kumawat · Answer

Hi vulpes Thanks for your reply. Its working fine now. :)

Sumit Kumawat · Answer

hi tejas Thanx for your replay but using this data is not updating in my databse

Robbinson · Answer

Hi Sumit This error is similar to parameter index out of range. Please try with removing '@' from OdbcParameter class parameters. Below is updated statement to get an idea. mySQLiteCommand.Parameters.Add(new OdbcParameter(LastTime task.Value as object)); mySQLiteCommand.Parameters.Add(new OdbcParameter(Status status as object)); Hope this will help you out. Please Don't forget to mark as answer if this post helps you.