sql insert into MS Access

[MrNobody]

What's the easiest way to make an insert using Microsoft.Jet.OLEDB.4.0
OleDbConnection connection to a MS Access data base file?

I'm finding examples out on the net that have like 1,000 line of code to
perform what I would imagine should be a very simple task.

I've mostly done DB code in Java and it would be simply making a
PreparedStatement with the wild cards inside, like:

"insert into MyTable (my_id, my_text) values (?, ?)"

and then using the various setObject(int position, object value) methods to
set the wildcards.... simple!

is there something as simple in C#?

I'm finding example codes creating these DataAdapters and setting these long
verbose Paramters and I'm getting really confused...

[Bjorn Abelli]

"MrNobody" wrote...

I haven't seen anyone answering this, so I'll give it a shot...

What's the easiest way to make an insert using
Microsoft.Jet.OLEDB.4.0 OleDbConnection connection
to a MS Access data base file?

I'm finding examples out on the net that have like 1,000
line of code to perform what I would imagine should be a
very simple task.

It *is* a very simple task, but I guess most examples you've found are
focusing on the benefits of using DataSets.

I've mostly done DB code in Java and it would be simply making a
PreparedStatement with the wild cards inside, like:

"insert into MyTable (my_id, my_text) values (?, ?)"

and then using the various setObject(int position, object value) methods
to set the wildcards.... simple!

is there something as simple in C#?

Almost as simple.

I'm finding example codes creating these DataAdapters and setting these
long
verbose Paramters and I'm getting really confused...

The verbosity is greater in C#/ADO.NET than in Java, but not by much.

/// Just as in Java, you'll need to instantiate
/// and open a Connection

OleDbConnection connection =
new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0" +
";Data Source=myFile.mdb");

connection.Open();

/// Then create a Command (almost equivalent to a
/// a Java Statement), providing the SQL-string and
/// the connection.

OleDbCommand cmd =
new OleDbCommand
("insert into MyTable (my_id, my_text) values (:p_id, :p_text)",
connection);

/// Instead of the Java "setThings", you Add parameters with the
/// values you want.

cmd.Parameters.Add(new OleDbParameter("p_id", 7) );
cmd.Parameters.Add(new OleDbParameter("p_text", "the_text") );

/// And execute...

cmd.ExecuteNonQuery();

connection.Close();

=======================================

Of course there are many more variants of this, but maybe this can be a
start.

/// Bjorn A

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

OleDbCommand cmd =
new OleDbCommand
("insert into MyTable (my_id, my_text) values (:p_id, :p_text)",
connection);

/// Instead of the Java "setThings", you Add parameters with the
/// values you want.

cmd.Parameters.Add(new OleDbParameter("p_id", 7) );
cmd.Parameters.Add(new OleDbParameter("p_text", "the_text") );

I had never seen this way to express the name of the parameters, have u
tested it?

I know of two: , anonymous, like

new OleDbCommand
("insert into MyTable (my_id, my_text) values ( ?, ?)",
connection);

and then the order of the parameters decide what goes where

and using @
new OleDbCommand
("insert into MyTable (my_id, my_text) values ( @id, @text)",
connection);

[Bjorn Abelli]

"Ignacio Machin ( .NET/ C# MVP )" wrote...

>OleDbCommand cmd =
new OleDbCommand
("insert into MyTable (my_id, my_text) values (:p_id, :p_text)",
connection);

/// Instead of the Java "setThings", you Add parameters with the
/// values you want.

cmd.Parameters.Add(new OleDbParameter("p_id", 7) );
cmd.Parameters.Add(new OleDbParameter("p_text", "the_text") );

I had never seen this way to express the name of the parameters,
have u tested it?

Yep!

I know of two: , anonymous, like

new OleDbCommand
("insert into MyTable (my_id, my_text) values ( ?, ?)",
connection);
and then the order of the parameters decide what goes where

and using @
new OleDbCommand
("insert into MyTable (my_id, my_text) values ( @id, @text)",
connection);

That makes three... ;-)

I'm accustomed to the Oracle way of using parameters, so when I made some
stubs a couple of years ago, I wanted them to work against both Oracle and
Access...

And yes, it works! :-)

I guess it's a question of built in compatibility with different paradigms
where it doesn't collide with other syntactical elements, so I guess they
allow both ?, @ and :
/// Bjorn A