insert syntax

Dear all,

I just got a problem in executing a insert query. I am using Access 2003 database. I am using it inside VB.NET.

When I first type in these command, it works

**************************************
Dim sql3 As String = "insert into todolist(Event) values('i am updating!!')"

Dim mycommand1 As OleDbCommand = New OleDbCommand(sql3, cn)

Dim i As Integer

i = mycommand1.ExecuteNonQuery()
**************************************

It works. But when I insert more than one field, I failed, let' say


**************************************
Dim sql3 As String = "insert into todolist(Event, Note) values('i am updating!!', 'asdfasd')"

Dim mycommand1 As OleDbCommand = New OleDbCommand(sql3, cn)

Dim i As Integer

i = mycommand1.ExecuteNonQuery()

**************************************




May I ask whether there is anything wrong with the insert syntax?

By the way, how could I insert a field which I set into Data/Time?

Thanks a lot.

Look forward hearing from you soon.

Thanks.


chat

your syntax looks fine

how did it fail? what was the error message?

i'm not sure if access 2003 requires hash marks, i know previous versions did...

insert into sometable (foo, bad, added) values ('a','b',#2004-03-10 14:56#)

Oh. The error message is

*******************
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at PIM.to_do_list.Button1_Click(Object sender, EventArgs e) in D:\To_do_list.vb:line 394

*****************************

I think the statement is very generic. Therefore not very helpful when we debug.

At the same time, I also checked MS SQL statement, learnt that the insert syntax is


INSERT [INTO]
{
table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
| view_name [ [AS] table_alias]
| rowset_function_limited
}

{ [(column_list)]
{ VALUES ( { DEFAULT
| NULL
| expression
}[,…n]
)
| derived_table
| execute_statement
}
}
| DEFAULT VALUES



Sample is

INSERT INTO GRADE (SSN, CCODE, GRADE)
VALUES ('111111111', '1111',NULL)

Reference @ URL


So it seems my syntax is correct, then what is the problem? Errr.......... Very blur.





chat

may i offer some advice: do not use SQL Server documentation to help develop queries for Access

that said, your insert syntax looks okay for Access