C# Parametized OLEDB Query using MS Access

Question

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

I change the OleDBType for nums to Numeric as in the Access file its Numeric

MSDN says that DBDateTime must be in the format yyyymmddhhmmss. It doesn't look like you are formatting it. Maybe that's the issue? — cadrell0
– cadrell0, Commented Oct 30, 2012 at 13:30
@cadrell0 thank you, the thing works with setting the date with the right format. I discovered it by directly putting the numbers in the query. But I want this numbers to be parametize.. — Netorica
– Netorica, Commented Oct 30, 2012 at 13:57

Community · Accepted Answer · 2017-05-23 10:32:20Z

2

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

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Dec 28, 2013 at 7:09

Ben McIntyre

1,98817 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Niklas · Accepted Answer · 2012-10-30 13:27:36Z

1

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 "?" ?

answered Oct 30, 2012 at 13:27

Niklas

13.2k24 gold badges82 silver badges124 bronze badges

4 Comments

Netorica Over a year ago

what about the datedraw parameter? its datetime

Netorica Over a year ago

ok i tried putting directly the numbers in the query it works.. but why? is there any mistake with the oledbtype?

Niklas Over a year ago

That's because you're setting the value to be an integer but passing in a question mark, which isn't an integer.

Netorica Over a year ago

sorry I don't got the point, I want my numbers to be passed as parameters in the query. I already tried removing the quotations from the first place.. still not working :(

Collectives™ on Stack Overflow

C# Parametized OLEDB Query using MS Access

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related