CODEWITHSUNDEEP
c#sqltextboxoledb
Noam650
Noam650

Reputation: 113

update database via SQL

i'm trying to update my code with values that already exist there(in the same row) and I get the error below.

here is the code:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
OleDbConnection myConnection = new OleDbConnection(connectionString);

string myInsertQuery = (String.Format("UPDATE tblUsers SET usersID='{0}', usersFirstName='{1}', " +
" usersLastName='{2}',usersPassword='{3}', usersAdress='{4}', usersMail  ='{5}' " +
" WHERE usersID='{6}'", idn, firstName.Text, lastName.Text, password.Text, adress.Text
, mail.Text,  idn));

OleDbCommand myCommand = new OleDbCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();

here is the error:

System.Data.OleDb.OleDbException crossed a native / managed boundary
Message = mismatch in criteria expression
Source = Microsoft Office Access Database Engine
ErrorCode = -2147217913

the parameters in my database are defined like this :"usersID=number" and the rest as text.

Upvotes: 0

Views: 162

Answers (1)

Habib
Habib

Reputation: 223207

First start using parameterized query. See OleDbParameter. Also read about SQL Injection. The error you are getting usually happens when the database is expecting value of one type and you are providing other. In you case it looks like your userID is of type integer and since you are enclosing it in single quote you are getting this error. Try the following query(the changed part is userID={0}). EDIT: Same should be changed in the where clause. 

string myInsertQuery = (String.Format("UPDATE tblUsers SET usersID={0}, usersFirstName='{1}', " +
                " usersLastName='{2}',usersPassword='{3}', usersAdress='{4}', usersMail  ='{5}' " +
                " WHERE usersID={6}", idn, firstName.Text, lastName.Text, password.Text, adress.Text
                , mail.Text,  idn));

Upvotes: 1

Related Questions