sql insert into MS Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
...

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.
Click to expand...

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#?
Click to expand...

Almost as simple.
I'm finding example codes creating these DataAdapters and setting these
long
verbose Paramters and I'm getting really confused...
Click to expand...

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
 
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") );
Click to expand...

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);
 
...
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);
Click to expand...

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
 
Back
Top