C# Help: OleDbCommand, Parameters for INSERT

[Buccaneer]

I struggled today on exactly how to INSERT multiple variables into a OleDbConnection (Access table) using OleDbCommand. Thought I would ask for help here since most of the examples in MSDN and online show using a value from a datatable. In my GIS application, I'm picking up the several values from multiple GIS features and want to populate a previously-created table each record at a time. Here's what I've got for a single variable:

string sql = "INSERT INTO TABLE1 (ITEM1) VALUES (?)";
OleDbParameter oleParam = new OleDbParameter():
oleParam.OleDbType = OleDbType.Integer;
oleParam.Value = intVariable1;
OleDbCommand cmdInsert = new OleDbCommand(sql, conn);
cmdInsert.Parameters.Add(oleParam);
cmdInsert.ExecuteNonQuery();

That works fine as I loop through each feature and picking up the intVariable value. But in the next table, I want to add three variables. I am unclear as to creating the objects and getting the value into the parameter:

string sql = "INSERT INTO TABLE2 (ITEM1, ITEM2, ITEM3) VALUES (?,?,?)";
//do I repeat the oleParam object 3 times, each with a different .Value?
//how many OleDbCommand objects do I need and when do I add the Parameter to the collection?

[PackerFanatic]

You would declare three seperate parameter variables with the values you need. You only need one command, then you add the parameters in the order you need them in the SQL statement :)

[PackerFanatic]

And if you ever need any more help man, feel free to IM me on AIM at keksemeister or shoot me an e-mail. I love C# and use it ALL the time...yes, I am a geek :)

[Buccaneer]

Thanks for your response. Something like this?

string sql = "INSERT INTO TABLE2 (ITEM1, ITEM2, ITEM3) VALUES (?,?,?)";
OleDbCommand cmdInsert = new OleDbCommand(sql, conn);
OleDbParameter oleParam = new OleDbParameter():
oleParam.OleDbType = OleDbType.Integer;
oleParam.Value = intVariable1;
cmdInsert.Parameters.Add(oleParam);
OleDbParameter oleParam = new OleDbParameter():
oleParam.OleDbType = OleDbType.Integer;
oleParam.Value = intVariable2;
cmdInsert.Parameters.Add(oleParam);
OleDbParameter oleParam = new OleDbParameter():
oleParam.OleDbType = OleDbType.Integer;
oleParam.Value = intVariable3;
cmdInsert.Parameters.Add(oleParam);
cmdInsert.ExecuteNonQuery();

[PackerFanatic]

That will work just fine! (although make sure you use three different names for the parameters)

Although, to cut down on the lines of code, you can actualy put the OleDbType and Value all on the same line as the parameter declaration...like so...

OleDbParameter oleParam = new OleDbParameter("@int1", OleDbType.Integer).Value = intVariable1;

The "@int1" is simply a name you give the param, you can name it anything. When you type in the .Value, nothing will come up for Intellisense, but it will work just the same.

[Buccaneer]

Thank you for your help. I looked at all of the overloads with the Add functions and just didn't know how to include the .Value part, which is the only important part of the whole function.

[PackerFanatic]

Yeah, when I first starting using parameters, I was confused by that too. I didn't think the .Value would actually work, but it does and it is a very nice thing to use!

And no worries :)