Problem with my Insert command  
Author Message
NET PR





PostPosted: Visual C# General, Problem with my Insert command Top

Hello Everyone,

I am trying to write an insert command in my code with in a while loop. The insert should take different values everytime it goes through the loop.

I have this so far:

-------Start Code----

// Connect to a Microsoft Access database using the
// System.Data.OleDb.OleDbConnection class

string strOleDb;

OleDbConnection cn = new OleDbConnection(strOleDb);

OleDbCommand myCom = new OleDbCommand();
myCom.Connection = cn;
i=0;

cn.Open();

while (i < 3)
{
tmpString = "test1";
tmpAnnot = "test2";

myCom.CommandText = "Insert Into test (test, testdata, id)"
+ " values (" + tmpString + ", " + tmpAnnot + ", " + i + ")";

myCom.ExecuteNonQuery();

i++;
}

cn.Close();

-------End Code-----

But at execution, I get this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

What am I doing wrong



Visual C#5  
 
 
Dennis Stone - MSFT





PostPosted: Visual C# General, Problem with my Insert command Top

Don't you need to enclose the string values in the sql statement with single quotes



 
 
boban.s





PostPosted: Visual C# General, Problem with my Insert command Top

You must either enclose your string values with single quotes like this:
myCom.CommandText = "INSERT INTO test (test, testdata, id)"
                    + " VALUES ('" + tmpString + "', '" + tmpAnnot + "', " + i + ")";

Or to use parameters. In that way the command text will be:
myCom.CommandText = "INSERT INTO test (test, testdata, id)"
                    + " VALUES( test,  testdata, id)";
But then you need to add parameters to commadn and set it's values:
myComm.Paramaters.Add(" test", OleDbType.Char, 20).Value = tmpString;
myComm.Paramaters.Add(" testdata", OleDbType.VarChar, 100).Value = tmpAnnot;
myComm.Paramaters.Add(" id", OleDbType.Int).Value = i;



 
 
NET PR





PostPosted: Visual C# General, Problem with my Insert command Top

Thanx boban.s. You were right, I needed to add single quotes, and I changed my command text to:

myCom.CommandText = "Insert Into test (test, testdata, id) values ('tmpString', 'tmpAnnot', " + i + ")";

Works great!