index > Windows Forms Data Controls and Databinding > Syntax error on INSERT INTO single column auto increment table

Syntax error on INSERT INTO single column auto increment table

I am working on an access db I inherited so I can not change the layout. I am using Jet 2.6 via oledb and C#.
I am trying to insert a new row into a table that only has a single column (ID) and isconfigured as auto increment - so basically I need to just produce a new key and not provide any additional field data.
These SQL statements are rejected with syntax errors:
INSERT INTO |tableName] () VALUES()
INSERT INTO |tableName] VALUES()
I can usean @@IDENTITY + 1 - but that defeats the purpose of an auto increment done by the db - and possibly can fail.
I tried to google a solution but seems a rare problem.
Any hint is appreciated...
Thanks ahead!
tb

tb2000 Tuesday, July 21, 2009 6:18 PM

Martin, thanks for following up and sorry for my late response - I was out travelling.
I found a separate solution when I found this gread article http://blogs.msdn.com/mattwar/pages/linq-links.aspx , however to finalize our subject as well:
I was trying to get the ORM I was using (WILSON ORM) to create a new record on a table that only has one column and where that column is the system's primary 1:1 key. The code has some flaws, it seems 1:1 relationships are not supported at all thus such a table structure is not part of the ORM scope. However I have to live with the table I have, as I down't own that part of the project - and having the ORM in source I could fix the problem with your recommendation.
Now the ORM is using INSERT INTO ..VALUES sequence and I was trying to expand the code and still retain the insert from a single command (it works if you have more fields than just the index).
Also - to make life more fun - at the code location where that happens I do not have the acutal column/field name for the ID because it is an autogen'd key the mapper excludes the fieldId from the column name sequence. Anyhow, I started using - based on your recommendation as above
"SELECT TOP 1 [" + entityType.Name + "].* FROM [" + entityType.Name + "] ORDER BY 1 DESC;"; which resturns the new Id. The column in that statement is accessed
then the insert based on incremented index.
Here the field (column) access is by index '1' rather than field name. Of cause this has the disadvantage of a sort operation, probably MAX() woudl perform better, but again to use MAc I would need the field name - there seems to be no Max(COLUMN 1). Besides also using MAX() would mean two commands to the database.
Preference would be to have a a single command "instert new record and return new index value" it seems I would need to address the Id / key column numerically
INSERT INTO [table].[columndescriptor] VALUES [columnValue];
columndescriptor however always represents the field name and there seems to be no alternative numerical index - at least I could not find it so far (I admit not being an SQL expert, please advise if there is another option). It seems only for the sorting OREDER BY clause a numeric column index is available as alternative address method to column name.

Anyways, my problem is solved with moving to a different and linq supporting ORM per the link I pasted.
I appreciate your support - thanks again!
tb

Edited bytb2000 Friday, July 31, 2009 8:30 AM
Proposed As Answer byShariqDON Thursday, August 06, 2009 5:37 PM
Marked As Answer bytb2000 Thursday, August 06, 2009 5:48 PM

tb2000 Friday, July 31, 2009 8:23 AM

Hi tb2000,

1. To insert a new record into MS Access table which has only one Filed(e.g. ID) of AutoNumber data type, you can this idea: Firstly select the maximal value from the AutoNumberField, then insert new record with the the maximal value plus 1.

Code sample:

Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")

con.Open()

Dim cmd As OleDbCommand = New OleDbCommand("Select Max(ID) From Table1", con)

Dim max As Int32 = cmd.ExecuteScalar

cmd = New OleDbCommand("Insert Into Table1(ID) Values('" & max + 1 & "')", con)

cmd.ExecuteNonQuery()

con.Close()

End Sub

End Class

2. If you can add a redundant Field (e.g. Name) of non-AutoNumber data type to the Table, then you can easily insert a new record via Insert T-SQL command.

Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")

con.Open()

Dim cmd As OleDbCommand = New OleDbCommand("Insert Into Table1(Name) Values('MartinXie')", con) ' Don't need to specify AutoNumber Field explicitly.
cmd.ExecuteNonQuery()

con.Close()

End Sub

End Class

3. If you use SQL Server database instead of MS Access database, you can easily insert a new record by specifying theDefault value for auto increment Field.
Code sample:

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=True;AttachDbFilename=D:\MyDatabase.mdf")

con.Open()

Dim cmd As SqlCommand = New SqlCommand("Insert Into Table1(ID) Values(Default)", con)

cmd.ExecuteNonQuery()

con.Close()

End Sub

End Class

Tutorial: Insert (T-SQL) Syntax
http://msdn.microsoft.com/en-us/library/ms174335.aspx

Best regards,
Martin Xie

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Proposed As Answer byMartin Xie - MSFTMSFTThursday, July 30, 2009 10:43 AM

Martin Xie - MSFT Wednesday, July 22, 2009 11:15 AM

hello Sir Martin! i m specialy come for You here ...
i need to know about solution of some Problem . give us Hints and Definations...
i m making a "Student forum" for teach new users and Students
i need some help . Please see this forum last Post.
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/054bef3c-b602-409c-9985-86026ef4082f
Please come to teach us

Bookmark this forum, Please Daily Check this forum. for teach us Thanx

Proposed As Answer byShariqDON Thursday, August 06, 2009 5:36 PM

ShariqDON Wednesday, July 22, 2009 11:36 AM

To add to my previous post:

Here's a workaround that seems to do it - not being a db / access expert though I am unsure this will work in regards to locking the db for other clients updates. Also seems a bit of a hack to handle the issue - so any improval suggested is surely welcome as there must be a way to do this more seamless (I guess)! - Thanx again - tb -

if (sqlStatement.ToUpper().StartsWith("INSERT INTO") && parameters.Length == 0) //means we are inserting into a 1 column autokey table
{
//This provides the highest field id - generic on column 1
command.CommandText = "SELECT TOP 1 [" + entityType.Name + "].* FROM [" + entityType.Name + "] ORDER BY 1 DESC;";
int nextIdx = (int)command.ExecuteScalar() + 1;
command.CommandText = "INSERT INTO [" + entityType.Name + "] VALUES (" + nextIdx + ");";
command.ExecuteNonQuery();
command.CommandText = SELECT @@IDENTITY AS KeyField"
return command.ExecuteScalar(); //returns the new Id
}

Proposed As Answer byMartin Xie - MSFTMSFTThursday, July 30, 2009 10:46 AM

tb2000 Wednesday, July 22, 2009 3:06 PM

Martin, thanks! I have to use access - so unfortunately T-SQL is no option.
The Max() idea is great - probable much better performance, however as
I do not have the Column name (the code is generic to the specific column names of the table):
is there a syntax to address that specific column? (like in "ORDER BY 1")
tx again
tb

Proposed As Answer byShariqDON Thursday, August 06, 2009 5:36 PM

tb2000 Wednesday, July 22, 2009 4:47 PM

is there a syntax to address that specific column? (like in "ORDER BY 1")

Could you please elaborate this question?
Generally we address specific column by Column Name in T-SQL statement.
Do you mean addressing specific column by Column Index? As far as I know, such feature is not supported.

This article may be helful to you.
Using SQL statements to manage databases (create database and alter database schemas etc.)
http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx
SQL not only let you select, add, and delete data from databases table, it also provides commands to manage databases. Using SQL statements you can create database objects programmatically such as a table, view, stored procedure, rule, index and so on. It also provides commands to alter adatabase and database schemas for example adding and deleting a column from a database table, adding some constraints to a column and so on.

Hi ShariqDON,
Thank you for contacting me here and active participation in MSDN community.
It looks that you have gotten many good responses in your thread. Here are many enthusiastic community members to offer their help and suggestions.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Martin Xie - MSFT Thursday, July 23, 2009 12:13 PM

Hello tb2000,

Does this help? If you have any future questions or concerns, please feel free to let me know.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback on our support, please contact msdnmg@microsoft.com

Martin Xie - MSFT Thursday, July 30, 2009 10:54 AM