Hi Chris
It might be better if I show you the code I have so far, and you may
be able to spot what has gone wrong:
<code>
m_OLEDBCon.Open()
m_OLEDBDataAdapter = New OleDbDataAdapter
m_OLEDBDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM
MyTable", m_OLEDBCon)
tbl = New DataTable
m_OLEDBDataAdapter.Fill(tbl)
dr = tbl.NewRow
dr.BeginEdit()
dr("Column1") = "Stuff"
dr.EndEdit()
tbl.Rows.Add(dr)
bldr = New OleDbCommandBuilder(m_OLEDBDataAdapter)
m_OLEDBDataAdapter.InsertCommand = bldr.GetInsertCommand()
m_OLEDBDataAdapter.Update(tbl) ' THIS LINE GIVES EXCEPTION "Syntax
error in INSERT INTO"
tbl.AcceptChanges()
</code>
[assume all variables are declared as their usage implies]
The insert command generated contains values of ?, ?, ? ... which I
think must be the problem, but I don't know why.
As a general point, my solution seems very long winded for what I am
trying to do. Is there an easier way? I see there is a thing called
OleDbCommand. Would this help?
Also, it seems unneccessary to have to fill the data table before I
can add rows. Can this step be skipped? And why do I need to do
tbl.Rows.Add(dr)
when I have already done
dr = tbl.NewRow
It seems like I am doing the same thing twice.
Thanks again for any help.
Charles
tbl.NewRow just creates a DataRow object with the correct schema. It
does not actually add it to the table. There are uses for this that
aren't worth going into right now.
The commandBuilder may be your problem. The problem is that the
commandbuilder is creating a command with text that has a syntax error.
I recommend you manually create the InsertCommand as you did with the
SelectCommand.
Also as Chris stated you can use a command object directly from the
connection without first filling a DataSet/DataTable.
=====================================================================
OleDbConnection m_OLEDBCon = ...
OleDbCommand cmdInsert = new OleDbCommand(m_OLEDBCon);
cmdInsert.CommandText = "Insert Into myTable "
+ "(Col1, Col2, Col3) Values (?, ?, ?)";
// add 3 OleDbParameter to cmdInsert for Col1, Col2, and Col3 in the
// same order as named in the commandtext,
// and set Value of each parameter
m_OLEDBCon.Open(); //open at last moment possible
cmdInsert.ExecuteNonQuery();
m_OLEDBCon.Close();
=====================================================================
To get the auto-incrementing value you must execute another command. The
commandText for the command depends on which database engine you are
using. I know how to do this for SQL and access.
SQL:
Just append an identity query to the command text of the insert command.
Although, for SQL you should probably be using the Sql provider.
=====================================================================
SqlCommand cmdInsert = new SqlCommand(m_SQLCon);
cmdInsert.CommandText = "Insert Into myTable "
+ "(Col2, Col3) Values (@Col2, @Col3); "
+ "set @Col1 = SCOPE_IDENTITY()";
// assumes Col1 is the PK
// add 3 SqlParameter objects, order does not matter since
// they are named. Direction of the PK col should be
// set to Output instead of default Input.
m_SQLCon.Open();
cmdInsert.ExecuteNonQuery();
m_SQLCon.Close();
//database assigned pk is now in the pk parameter
object newPk = sqlparPK.Value; //convert to int or whatever type is.
=====================================================================
Access:
Access does not support multi step sql statements, and also does not
support the SCOPE_IDENTITY() method. The OleDb provider does support
@@Identity.
=====================================================================
OleDbConnection m_OLEDBCon = ...
OleDbCommand cmdInsert = new OleDbCommand(m_OLEDBCon);
cmdInsert.CommandText = "Insert Into myTable "
+ "(Col2, Col3) Values (?, ?)";
// add 2 OleDbParameter to cmdInsert for Col2, and Col3 in the
// same order as named in the commandtext,
// and set Value of each parameter
OleDbCommand cmdIdentity = new OleDbCommand(m_OLEDBCon);
cmdIdentity.CommandText = "Select @@Identity"
m_OLEDBCon.Open(); //open at last moment possible
cmdInsert.ExecuteNonQuery();
int newIden = (int)cmdIdentity.ExecuteScalar();
m_OLEDBCon.Close();
=====================================================================
I hope this helps. When you get a chance take a look at "generic
ADO.NET" in my signature, and the latest code template on the generator
site. You may find this helpful as well?
--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/dbobjecter (code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
