How to insert a row into a table

I have a database with a single table. This table has many rows already and
I want to add a new row.

I open a connection, and then what? There seems to be so many ways to access
the data in ADO.NET that I am unsure which to choose.

I wish to insert a new row into the table, without first having to read data
from the table, in a way much like this:

<pdl>
Open database
Get handle to table
Create new row in memory
Put data into column 1 in new row (dr("Column1") = "Something")
Put data into column 2 in new row (dr("Column2") = "Something else")
Put data into column N in new row
Insert row into table
Close database
</pdl>

Could someone please give me some pointers?

What happens if the table has a primary key, which is an auto-incrementing
ID? How do I retrieve the ID of the new row?

TIA

Charles

William
Wed Sep 03 08:33:41 CDT 2003

There are a 'lot' of ways to do this. If you are using a Databound grid for
instance, and you hit the down arrow, it'll add a new row for you.

If you are using databound text boxes, <google for
BindingManager/BindingContext> you can use .Addnew.

If you want to create a datatable from scratch and add rows...here's an
example of creating a DataSet programatically...

You can define a PrimaryKey on your DataTable and DataColumns allow you to
indicate Autoincrement and Autoincrement Seed and Value.

There's a lot to it, so it's kind of hard giving a general answer...it
really depends on how you want to add the column, via a control or
programatically. If you check out MSDN though, there are tons of examples.
Also, if you are getting into ADO.NET, I highly recommend ADO.NET core
Reference by David Sceppa or Bill Vaughn's Best practices book
(http://www.betav.com/Files/Books/current_titles.htm)

They will pay for themselves in the first day of having either of them.



public static DataSet CreateDataSet()

{

DataSet ds;

System.Data.DataTable dt;


ds = new DataSet();

dt = new DataTable();

DataColumn LastName;

DataColumn FirstName;

DataColumn WT;

DataColumn Desc;

DataColumn DictationDate;

DataColumn DictationTime;

DataColumn JobStatus;

DataColumn NewPatient;

DataColumn SSN;

DataColumn TitleLocation;

DataColumn PhysicianType;

DataColumn ConsultRequest;

DataColumn Addendum;

LastName = new DataColumn("LastName");

FirstName = new DataColumn("FirstName");

WT =new DataColumn("WT");

Desc = new DataColumn("Desc");

DictationDate = new DataColumn("DictationDate");

DictationTime = new DataColumn("DictationTime");

JobStatus = new DataColumn("JobStatus");

NewPatient = new DataColumn("NewPatient");

SSN = new DataColumn("SSN");

TitleLocation = new DataColumn("TitleLocation");

PhysicianType = new DataColumn("PhysicianType");

ConsultRequest = new DataColumn("ConsultRequest");

Addendum = new DataColumn("Addendum");

dt.Columns.Add(LastName);

dt.Columns.Add(FirstName);

dt.Columns.Add(WT);

dt.Columns.Add(Desc);

dt.Columns.Add(DictationDate);

dt.Columns.Add(DictationTime);

dt.Columns.Add(JobStatus);

dt.Columns.Add(NewPatient);

dt.Columns.Add(SSN);

dt.Columns.Add(TitleLocation);

dt.Columns.Add(PhysicianType);

dt.Columns.Add(ConsultRequest);

dt.Columns.Add(Addendum);

ds.Tables.Add(dt);


return ds;

}

}

}

"Charles Law" <blah@thingummy.com> wrote in message
news:%23Oc006hcDHA.300@TK2MSFTNGP12.phx.gbl...
> I have a database with a single table. This table has many rows already
and
> I want to add a new row.
>
> I open a connection, and then what? There seems to be so many ways to
access
> the data in ADO.NET that I am unsure which to choose.
>
> I wish to insert a new row into the table, without first having to read
data
> from the table, in a way much like this:
>
> <pdl>
> Open database
> Get handle to table
> Create new row in memory
> Put data into column 1 in new row (dr("Column1") = "Something")
> Put data into column 2 in new row (dr("Column2") = "Something else")
> Put data into column N in new row
> Insert row into table
> Close database
> </pdl>
>
> Could someone please give me some pointers?
>
> What happens if the table has a primary key, which is an auto-incrementing
> ID? How do I retrieve the ID of the new row?
>
> TIA
>
> Charles
>
>

Charles
Wed Sep 03 08:39:18 CDT 2003

Hi William

Thanks for the very quick response.

I want to do the whole thing in code; no data grid or binding. The table
already exists. I am not looking to create new columns, only populate
existing columns. I am just looking for the simplest way to do all this, a
bit like the old ADO way, but in ADO.NET.

Do I have to use a dataset, or can I do it all through a DataTable object?

Charles


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:Onmrt%23hcDHA.3748@TK2MSFTNGP11.phx.gbl...
> There are a 'lot' of ways to do this. If you are using a Databound grid
for
> instance, and you hit the down arrow, it'll add a new row for you.
>
> If you are using databound text boxes, <google for
> BindingManager/BindingContext> you can use .Addnew.
>
> If you want to create a datatable from scratch and add rows...here's an
> example of creating a DataSet programatically...
>
> You can define a PrimaryKey on your DataTable and DataColumns allow you to
> indicate Autoincrement and Autoincrement Seed and Value.
>
> There's a lot to it, so it's kind of hard giving a general answer...it
> really depends on how you want to add the column, via a control or
> programatically. If you check out MSDN though, there are tons of
examples.
> Also, if you are getting into ADO.NET, I highly recommend ADO.NET core
> Reference by David Sceppa or Bill Vaughn's Best practices book
> (http://www.betav.com/Files/Books/current_titles.htm)
>
> They will pay for themselves in the first day of having either of them.
>
>
>
> public static DataSet CreateDataSet()
>
> {
>
> DataSet ds;
>
> System.Data.DataTable dt;
>
>
> ds = new DataSet();
>
> dt = new DataTable();
>
> DataColumn LastName;
>
> DataColumn FirstName;
>
> DataColumn WT;
>
> DataColumn Desc;
>
> DataColumn DictationDate;
>
> DataColumn DictationTime;
>
> DataColumn JobStatus;
>
> DataColumn NewPatient;
>
> DataColumn SSN;
>
> DataColumn TitleLocation;
>
> DataColumn PhysicianType;
>
> DataColumn ConsultRequest;
>
> DataColumn Addendum;
>
> LastName = new DataColumn("LastName");
>
> FirstName = new DataColumn("FirstName");
>
> WT =new DataColumn("WT");
>
> Desc = new DataColumn("Desc");
>
> DictationDate = new DataColumn("DictationDate");
>
> DictationTime = new DataColumn("DictationTime");
>
> JobStatus = new DataColumn("JobStatus");
>
> NewPatient = new DataColumn("NewPatient");
>
> SSN = new DataColumn("SSN");
>
> TitleLocation = new DataColumn("TitleLocation");
>
> PhysicianType = new DataColumn("PhysicianType");
>
> ConsultRequest = new DataColumn("ConsultRequest");
>
> Addendum = new DataColumn("Addendum");
>
> dt.Columns.Add(LastName);
>
> dt.Columns.Add(FirstName);
>
> dt.Columns.Add(WT);
>
> dt.Columns.Add(Desc);
>
> dt.Columns.Add(DictationDate);
>
> dt.Columns.Add(DictationTime);
>
> dt.Columns.Add(JobStatus);
>
> dt.Columns.Add(NewPatient);
>
> dt.Columns.Add(SSN);
>
> dt.Columns.Add(TitleLocation);
>
> dt.Columns.Add(PhysicianType);
>
> dt.Columns.Add(ConsultRequest);
>
> dt.Columns.Add(Addendum);
>
> ds.Tables.Add(dt);
>
>
> return ds;
>
> }
>
> }
>
> }
>
> "Charles Law" <blah@thingummy.com> wrote in message
> news:%23Oc006hcDHA.300@TK2MSFTNGP12.phx.gbl...
> > I have a database with a single table. This table has many rows already
> and
> > I want to add a new row.
> >
> > I open a connection, and then what? There seems to be so many ways to
> access
> > the data in ADO.NET that I am unsure which to choose.
> >
> > I wish to insert a new row into the table, without first having to read
> data
> > from the table, in a way much like this:
> >
> > <pdl>
> > Open database
> > Get handle to table
> > Create new row in memory
> > Put data into column 1 in new row (dr("Column1") = "Something")
> > Put data into column 2 in new row (dr("Column2") = "Something else")
> > Put data into column N in new row
> > Insert row into table
> > Close database
> > </pdl>
> >
> > Could someone please give me some pointers?
> >
> > What happens if the table has a primary key, which is an
auto-incrementing
> > ID? How do I retrieve the ID of the new row?
> >
> > TIA
> >
> > Charles
> >
> >
>
>

Chris
Wed Sep 03 09:06:26 CDT 2003

Hi,

As stated by William there are many ways to skin this cat. If all you want
is to insert a record into the server table, you could feasably do it with a
SQL insert statement through the command object.

Hope this helps

Chris Taylor
"Charles Law" <blah@thingummy.com> wrote in message
news:%23Oc006hcDHA.300@TK2MSFTNGP12.phx.gbl...
> I have a database with a single table. This table has many rows already
and
> I want to add a new row.
>
> I open a connection, and then what? There seems to be so many ways to
access
> the data in ADO.NET that I am unsure which to choose.
>
> I wish to insert a new row into the table, without first having to read
data
> from the table, in a way much like this:
>
> <pdl>
> Open database
> Get handle to table
> Create new row in memory
> Put data into column 1 in new row (dr("Column1") = "Something")
> Put data into column 2 in new row (dr("Column2") = "Something else")
> Put data into column N in new row
> Insert row into table
> Close database
> </pdl>
>
> Could someone please give me some pointers?
>
> What happens if the table has a primary key, which is an auto-incrementing
> ID? How do I retrieve the ID of the new row?
>
> TIA
>
> Charles
>
>

Charles
Wed Sep 03 09:29:01 CDT 2003

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


"Chris Taylor" <chris_taylor_za@hotmail.com> wrote in message
news:OXQQgQicDHA.2432@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> As stated by William there are many ways to skin this cat. If all you want
> is to insert a record into the server table, you could feasably do it with
a
> SQL insert statement through the command object.
>
> Hope this helps
>
> Chris Taylor
> "Charles Law" <blah@thingummy.com> wrote in message
> news:%23Oc006hcDHA.300@TK2MSFTNGP12.phx.gbl...
> > I have a database with a single table. This table has many rows already
> and
> > I want to add a new row.
> >
> > I open a connection, and then what? There seems to be so many ways to
> access
> > the data in ADO.NET that I am unsure which to choose.
> >
> > I wish to insert a new row into the table, without first having to read
> data
> > from the table, in a way much like this:
> >
> > <pdl>
> > Open database
> > Get handle to table
> > Create new row in memory
> > Put data into column 1 in new row (dr("Column1") = "Something")
> > Put data into column 2 in new row (dr("Column2") = "Something else")
> > Put data into column N in new row
> > Insert row into table
> > Close database
> > </pdl>
> >
> > Could someone please give me some pointers?
> >
> > What happens if the table has a primary key, which is an
auto-incrementing
> > ID? How do I retrieve the ID of the new row?
> >
> > TIA
> >
> > Charles
> >
> >
>
>

Charles
Wed Sep 03 11:00:19 CDT 2003

Hi Michael

Thanks for that.

> cmdInsert.CommandText = "Insert Into myTable "
> + "(Col1, Col2, Col3) Values (?, ?, ?)";

This is actually what I have been trying to avoid, hence using the
CommandBuilder. I have never been keen on constructing the INSERT statement
by hand, since the ADO days and before. This is partly because I end up
having to effectively do it twice: once for the INSERT and once for the
UPDATE commands (sql syntax issue), but also because it seems more
long-winded, and more difficult to maintain.

I am a bit concerned though that the command builder is creating an insert
command with a syntax error. I had assumed that it was a coding error on my
part. Assuming, for the moment, that it is an error on my part, can you see
anything that could be wrong with the code that I am using? I think I would
like to understand why this approach does not work.

I like the idea of using a command object directly from the connection, but
I would prefer to use the command builder if I can.

Thanks again.

Charles


"Michael Lang" <ml@nospam.com> wrote in message
news:Xns93EB6BE5E9B2Clang1474icqmailcom@207.46.248.16...
> "Charles Law" <blah@thingummy.com> wrote in
> news:eOmlyeicDHA.1128@tk2msftngp13.phx.gbl:
>
> > 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)
>

Michael
Wed Sep 03 16:59:00 CDT 2003

"Charles Law" <blah@thingummy.com> wrote in
news:u#wgnEkcDHA.620@TK2MSFTNGP11.phx.gbl:

> This is what I get, immediately before the update:
>
> "INSERT INTO MyTable( MyColumn1 , MyColumn2 , MyColumn3 ) VALUES ( ? ,
> ? , ? )"
>
> Any thoughts?
>
> Charles

If this is EXACTLY the text the command builder generated, then I don't see
where the problem is?

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/dbobjecter (code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

Charles
Wed Sep 03 18:16:34 CDT 2003

Hi Michael

The database/engine/whatever certainly seems to have a problem.

I kind of thought that the question marks should have been replaced with
real data by this stage. Are you saying that this is not the case?

Charles


"Michael Lang" <ml@nospam.com> wrote in message
news:Xns93EBACC501B80lang1474icqmailcom@207.46.248.16...
> "Charles Law" <blah@thingummy.com> wrote in
> news:u#wgnEkcDHA.620@TK2MSFTNGP11.phx.gbl:
>
> > This is what I get, immediately before the update:
> >
> > "INSERT INTO MyTable( MyColumn1 , MyColumn2 , MyColumn3 ) VALUES ( ? ,
> > ? , ? )"
> >
> > Any thoughts?
> >
> > Charles
>
> If this is EXACTLY the text the command builder generated, then I don't
see
> where the problem is?
>
> --
> Michael Lang, MCSD
> See my .NET open source projects
> http://sourceforge.net/projects/dbobjecter (code generator)
> http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
>

Michael
Thu Sep 04 11:34:45 CDT 2003

"Charles Law" <blah@thingummy.com> wrote in news:uxq1kFncDHA.3748
@TK2MSFTNGP11.phx.gbl:

> Hi Michael
>
> The database/engine/whatever certainly seems to have a problem.
>
> I kind of thought that the question marks should have been replaced with
> real data by this stage. Are you saying that this is not the case?
>
> Charles

The Command.CommandText property will never change it's string value to
contain the values. That is what the parameter objects on the command are
for. However, The Command.ExecuteNonQuery() method probably fills in the
'?'s with the values before passing the request to the data engine. But I
have no way of knowing that since I don't have the source code.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen/ (simple generic code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

Charles
Thu Sep 04 12:18:33 CDT 2003

Hi Michael

I have cut my test prog down to an absolute minimum - empty table with just
one [text] column - and have got it working. As you say, the question marks
remain, even up to the last minute, so that was a red herring.

I am now going to build it back up to where I was before and see if still
works.

Thanks for your help.

Charles


"Michael Lang" <ml@nospam.com> wrote in message
news:Xns93EC75D4316FAlang1474icqmailcom@207.46.248.16...
> "Charles Law" <blah@thingummy.com> wrote in news:uxq1kFncDHA.3748
> @TK2MSFTNGP11.phx.gbl:
>
> > Hi Michael
> >
> > The database/engine/whatever certainly seems to have a problem.
> >
> > I kind of thought that the question marks should have been replaced with
> > real data by this stage. Are you saying that this is not the case?
> >
> > Charles
>
> The Command.CommandText property will never change it's string value to
> contain the values. That is what the parameter objects on the command are
> for. However, The Command.ExecuteNonQuery() method probably fills in the
> '?'s with the values before passing the request to the data engine. But I
> have no way of knowing that since I don't have the source code.
>
> --
> Michael Lang, MCSD
> See my .NET open source projects
> http://sourceforge.net/projects/colcodegen/ (simple generic code
generator)
> http://sourceforge.net/projects/dbobjecter (database app code generator)
> http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
>

Charles
Thu Sep 04 13:02:07 CDT 2003

I can now report my findings:

One of the columns in my database was called "Password".

Since when did Password become a banned word? Was I away that day? The
database happily lets me use this name for a column. It would have been nice
to have got a better error message than "Syntax error in INSERT INTO". I
can't even get at the real SQL that is used so that I can paste it into TOAD
and get a better error message. AARGH!

I have wasted days on this and I am not best pleased. I think I need a lie
down.

Charles


"Charles Law" <blah@thingummy.com> wrote in message
news:ett%23JiwcDHA.2368@TK2MSFTNGP09.phx.gbl...
> Hi Michael
>
> I have cut my test prog down to an absolute minimum - empty table with
just
> one [text] column - and have got it working. As you say, the question
marks
> remain, even up to the last minute, so that was a red herring.
>
> I am now going to build it back up to where I was before and see if still
> works.
>
> Thanks for your help.
>
> Charles
>
>
> "Michael Lang" <ml@nospam.com> wrote in message
> news:Xns93EC75D4316FAlang1474icqmailcom@207.46.248.16...
> > "Charles Law" <blah@thingummy.com> wrote in news:uxq1kFncDHA.3748
> > @TK2MSFTNGP11.phx.gbl:
> >
> > > Hi Michael
> > >
> > > The database/engine/whatever certainly seems to have a problem.
> > >
> > > I kind of thought that the question marks should have been replaced
with
> > > real data by this stage. Are you saying that this is not the case?
> > >
> > > Charles
> >
> > The Command.CommandText property will never change it's string value to
> > contain the values. That is what the parameter objects on the command
are
> > for. However, The Command.ExecuteNonQuery() method probably fills in
the
> > '?'s with the values before passing the request to the data engine. But
I
> > have no way of knowing that since I don't have the source code.
> >
> > --
> > Michael Lang, MCSD
> > See my .NET open source projects
> > http://sourceforge.net/projects/colcodegen/ (simple generic code
> generator)
> > http://sourceforge.net/projects/dbobjecter (database app code generator)
> > http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
> >
>
>

Charles
Thu Sep 04 13:49:34 CDT 2003


"Michael Lang" <ml@nospam.com> wrote in message
news:Xns93EC8A5AA7DE6lang1474icqmailcom@207.46.248.16...
> "Charles Law" <blah@thingummy.com> wrote in
> news:usvjg6wcDHA.2412@TK2MSFTNGP09.phx.gbl:
>
> > I can now report my findings:
> >
> > One of the columns in my database was called "Password".
> >
> > Since when did Password become a banned word? Was I away that day? The
> > database happily lets me use this name for a column. It would have
> > been nice to have got a better error message than "Syntax error in
> > INSERT INTO". I can't even get at the real SQL that is used so that I
> > can paste it into TOAD and get a better error message. AARGH!
> >
> > I have wasted days on this and I am not best pleased. I think I need a
> > lie down.
> >
> > Charles
>
> There are many reserved words you shouldn't use as a field name. If you
> need to use a reserved word as a field name then just wrap that field name
> with brackets in your sql statement.
>
> "INSERT INTO MyTable([ReservedWord], MyColumn2, MyColumn3) "
> + "VALUES (?,?,?)"
>
> --
> Michael Lang, MCSD
> See my .NET open source projects
> http://sourceforge.net/projects/colcodegen (simple code generator)
> http://sourceforge.net/projects/dbobjecter (database app code generator)
> http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
>

Charles
Thu Sep 04 13:55:01 CDT 2003

Darn. Sorry about that last blank post. My mouse has got it into its head to
double-click all on its own, and Send is in the same place as Reply Group,
if you see what I mean.

Anyway, I suppose my frustration is that I didn't realise that Password had
become a reserved word. It's not in Access 2000, and it all worked
swimmingly in ADO. It's only now that things seem to be going wrong. Is
there a place that I can turn to for ADO.NET reserved words? I usually make
a point of _not_ using them, so long as I know which ones to avoid.

Thanks.

Charles


"Michael Lang" <ml@nospam.com> wrote in message
news:Xns93EC8A5AA7DE6lang1474icqmailcom@207.46.248.16...
> "Charles Law" <blah@thingummy.com> wrote in
> news:usvjg6wcDHA.2412@TK2MSFTNGP09.phx.gbl:
>
> > I can now report my findings:
> >
> > One of the columns in my database was called "Password".
> >
> > Since when did Password become a banned word? Was I away that day? The
> > database happily lets me use this name for a column. It would have
> > been nice to have got a better error message than "Syntax error in
> > INSERT INTO". I can't even get at the real SQL that is used so that I
> > can paste it into TOAD and get a better error message. AARGH!
> >
> > I have wasted days on this and I am not best pleased. I think I need a
> > lie down.
> >
> > Charles
>
> There are many reserved words you shouldn't use as a field name. If you
> need to use a reserved word as a field name then just wrap that field name
> with brackets in your sql statement.
>
> "INSERT INTO MyTable([ReservedWord], MyColumn2, MyColumn3) "
> + "VALUES (?,?,?)"
>
> --
> Michael Lang, MCSD
> See my .NET open source projects
> http://sourceforge.net/projects/colcodegen (simple code generator)
> http://sourceforge.net/projects/dbobjecter (database app code generator)
> http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
>

Michael
Thu Sep 04 14:23:40 CDT 2003

"Charles Law" <blah@thingummy.com> wrote in
news:OBjQEYxcDHA.2436@TK2MSFTNGP12.phx.gbl:
>
> Anyway, I suppose my frustration is that I didn't realise that
> Password had become a reserved word. It's not in Access 2000, and it
> all worked swimmingly in ADO. It's only now that things seem to be
> going wrong. Is there a place that I can turn to for ADO.NET reserved
> words? I usually make a point of _not_ using them, so long as I know
> which ones to avoid.
>
> Thanks.
>
> Charles

google? I found the following. However, none of these show "password" as
reserved. Let me know if you can find a better comprehensive list.

http://www.jsoftware.com/books/help/user/sql_reserved_words.htm
http://www-3.ibm.com/software/data/db2/os390/cpsqlref/ibmsqlr205.htm
http://www.bairdgroup.com/reservedwords.cfm

--
Michael Lang, MCSD