Simple Question I Think !

[Terry Burns]

OK i have the following table in access.

F1 Integer
F2 String
F3 String
F4 ( Primary Key ) Autonumber

I Can get this into a table and display it on a datagrid ok. But when I try
and add a new row and update it I get the invalid INSERT. What should the
INSERT command look like and how do we get the next unique number from
access ?

Here is my code to insert

Dim DR As DataRow = tableEvents.NewRow()

Dim cmd As New OleDbCommand

cmd.CommandText = "INSERT INTO Events(Action, Comments, EventType,
PersonIndex,ID) VALUES (?, ?, ?, ?.?)"

cmd.Connection = con

cmd.Parameters.Add(New OleDbParameter("ID",
System.Data.OleDb.OleDbType.Integer, 0, "ID"))

cmd.Parameters.Add(New OleDbParameter("Action", OleDbType.VarWChar, 50,
"Action"))

cmd.Parameters.Add(New OleDbParameter("Comments", OleDbType.VarWChar, 0,
"Comments"))

cmd.Parameters.Add(New OleDbParameter("EventType", OleDbType.VarWChar, 50,
"EventType"))

cmd.Parameters.Add(New OleDbParameter("PersonIndex", OleDbType.Integer, 0,
"PersonIndex"))

Try

con.Open()

DR("PersonIndex") = dgPeople.Item(dgPeople.CurrentRowIndex, 0)

DR("EventType") = "Birthday"

DR("Comments") = ""

DR("Action") = ""

DR("ID") = 33

tableEvents.Rows.Add(DR)

daEvents.Update(tableEvents)

Catch ex As OleDbException

MessageBox.Show(ex.Message)

Catch ex As InvalidOperationException

MessageBox.Show(ex.Message)

Finally

con.Close()

End Try

 

[William Ryan]

Terry:

There are a lot of answers to your question depending on
methodology.

Have you looked at the autoincrement property of a
datacolumn?
http://www.dotnetextreme.com/articles/GetStartADO2.asp

You can set the seed and the increment step...

If you are on the desktop, you may want to take a
different approach, Requery the DB and find the largest
value and use it...you can even reset the increment value
if you want.

If you have multiple users, and they don't refresh often,
you'll need to check this value or risk exceptions and
all the joys that they bring with them.

Hopefully this helps, but if it doesn't, let me know and
I can give you some more specific guideance (hopefully).


Good Luck,

Bill


Cordially,

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com

 

[Terry Burns]

I must b missing somethinf because this keeps telling me that i have a
systax error in my insert statement, i cant see it , can you ????

regards - terry

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

Dim DR As DataRow

Dim InsertCmd As New OleDbCommand

'Open the connection

con.Open()

'set up the insert command

InsertCmd.CommandType = CommandType.Text

InsertCmd.CommandText = "INSERT INTO Events(Action, Comments, EventType,
PersonIndex) VALUES (?, ?, ?, ?)"

InsertCmd.Connection = con

InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Action",
System.Data.OleDb.OleDbType.VarWChar, 50, "Action"))

InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Comments",
System.Data.OleDb.OleDbType.VarWChar, 50, "Comments"))

InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("EventType",
System.Data.OleDb.OleDbType.VarWChar, 50, "EventType"))

InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("PersonIndex",
System.Data.OleDb.OleDbType.Integer, 0, "PersonIndex"))

daEvents.InsertCommand = InsertCmd





'Genereate New Row

DR = tableEvents.NewRow()

DR("PersonIndex") = 39

DR("EventType") = "Birthday"

DR("Comments") = "-none-"

DR("Action") = "-none-"

DR("ID") = 67

tableEvents.Rows.Add(DR)

Try

daEvents.Update(tableEvents)

Catch ex As OleDbException

MessageBox.Show(ex.ToString())

End Try

tableEvents.Clear()

daEvents.Fill(tableEvents)

'Close the connection

con.Close()

 

[Carl Prothman [MVP]]

I must be missing something because this keeps telling me that I have a
syntax error in my insert statement, I cant see it , can you ????

InsertCmd.CommandText = "INSERT INTO Events(Action, Comments, EventType,
PersonIndex) VALUES (?, ?, ?, ?)"

Terry,
Some of those names may be keywords in OLE DB, so put brackets around them.

InsertCmd.CommandText = "INSERT INTO [Events] ([Action], [Comments],
[EventType], [PersonIndex]) VALUES (?, ?, ?, ?)"

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Terry Burns]

Carl,

EXCELLENT - THANK YOU VERY MUCH.

Regards - Terry

I must be missing something because this keeps telling me that I have a
syntax error in my insert statement, I cant see it , can you ????

InsertCmd.CommandText = "INSERT INTO Events(Action, Comments, EventType,
PersonIndex) VALUES (?, ?, ?, ?)"

Terry,
Some of those names may be keywords in OLE DB, so put brackets around them.

InsertCmd.CommandText = "INSERT INTO [Events] ([Action], [Comments],
[EventType], [PersonIndex]) VALUES (?, ?, ?, ?)"

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com