CODEWITHSUNDEEP
c#databaseparametersoledb
Netorica
Netorica

Reputation: 19327

C# Parametized OLEDB Query using MS Access

ok i do have a OLEDB function that inserts data in the database. well i know the usual way on how to do SQL with OLEDB but what I want to do here is to parametize each data in the query string.

Now I been in Google for almost 3 hours just know how to make parametized query in OLEDB but all of them are not working with me.

Now at last my query seems to work fine in the code below but still an error occurs

my function code :

private bool dbInsert(int [] inputNumbers, DateTime datetime){
                try {
                    String sql = "INSERT INTO 655(1stNum, 2ndNum, 3rdNum, 4thNum, 5thNum, 6thNum, datedraw) VALUES(?, ?, ?, ?, ?, ? ,?)";
                    OleDbCommand dbcmd = new OleDbCommand(sql, app.oleDbConn());
                    dbcmd.Connection.Open();
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[0];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[1];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[2];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[3];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[4];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[5];
                    dbcmd.Parameters.Add("?",OleDbType.DBTimeStamp).Value = datetime;
                    dbcmd.ExecuteNonQuery();
                    dbcmd.Connection.Close();

                }catch(OleDbException ex){
                    showPrompt("Error reading the database",Color.Red);
                    Console.WriteLine(ex.ToString());
                }
                return true;
            }

this is the error log show in the console:

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

the datatypes of my columns in my access db files are Numeric from 1stNum to 6thNum and Date/Time in datedraw, of course ID is AutoNumber

is there any mistake in the parametize query implementation? please help me.

Update

Upvotes: 1

Views: 836

Answers (2)

Ben McIntyre
Ben McIntyre

Reputation: 1968

One thing I would check is the type: OleDbType.DBTimeStamp

If I recall, TimeStamp is a special type that's not easily compatible with the DateTime type.

Note that OleDb CAN use named parameters: see MS Access, Named parameters and Column Names

Upvotes: 2

Niklas
Niklas

Reputation: 13135

You need single quotes around the values, like this: 

String sql = 
    "INSERT INTO 655(1stNum, 2ndNum, 3rdNum, 4thNum, 5thNum, 6thNum, datedraw)
    VALUES('?', '?', '?', '?', '?', '?' ,'?')";

Also, have you tried inserting a real integer instead of "?" ?

Upvotes: 1

Related Questions