problems adding record to access database

[Sérgio Almeida]

Greetings

I'm having problems adding records to an access database.
What I want to do is very simple. Here is my code.

__CODE_START__

dim sqlStatement
sqlStatement="insert into Records(artist,album,format,label,tracks,year)
values ('Pink Floyd','THE WALL','2CD','EMI',99,1979)"

dim dataConn
dim strDatabase, affecetd
strDatabase="database/musicstore.mdb"
Set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Provider="Microsoft.Jet.OLEDB.4.0"
dataConn.ConnectionTimeout = 15
dataConn.CommandTimeout = 30
dataConn.Open Server.MapPath(strDatabase)

dataconn.Execute sqlStatement,affected',adCmdText ' <- ERROR OCCURS HERE

if affecetd = 1 then
Response.Write("<h1> success </h1>")
end if

__CODE_END__

when I try run the cript I get the error

__ERROR_START__

Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/MusicStore/addrecord.asp, line 53


__ERROR_END__

If I run the sabe sql statement on "query analyser" of access, it works
ok (the record is inserted).

What can be the problem? any ideas?

Than you very much

Sérgio Almeida

[Sérgio Almeida]

Greetings

I'm having problems adding records to an access database.
What I want to do is very simple. Here is my code.

__CODE_START__

dim sqlStatement
sqlStatement="insert into Records(artist,album,format,label,tracks,year)
values ('Pink Floyd','THE WALL','2CD','EMI',99,1979)"

dim dataConn
dim strDatabase, affecetd
strDatabase="database/musicstore.mdb"
Set dataConn = Server.CreateObject("ADODB.Connection")
dataConn.Provider="Microsoft.Jet.OLEDB.4.0"
dataConn.ConnectionTimeout = 15
dataConn.CommandTimeout = 30
dataConn.Open Server.MapPath(strDatabase)

dataconn.Execute sqlStatement,affected',adCmdText ' <- ERROR OCCURS HERE

if affecetd = 1 then
Response.Write("<h1> success </h1>")
end if

__CODE_END__

when I try run the cript I get the error

__ERROR_START__

Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/MusicStore/addrecord.asp, line 53


__ERROR_END__

If I run the sabe sql statement on "query analyser" of access, it works
ok (the record is inserted).

What can be the problem? any ideas?

Than you very much

Sérgio Almeida

[Ray at]

[url]www.aspfaq.com/5003[/url]

Ray at work

"Sérgio Almeida" <sgalmeidaNOSPAM@bragatelNOSPAM.pt> wrote in message
news:ul$HuHlmDHA.2160@TK2MSFTNGP10.phx.gbl...

> Greetings
>
> I'm having problems adding records to an access database.
> What I want to do is very simple. Here is my code.
>

[Ray at]

[url]www.aspfaq.com/5003[/url]

Ray at work

"Sérgio Almeida" <sgalmeidaNOSPAM@bragatelNOSPAM.pt> wrote in message
news:u1T8sHlmDHA.2432@TK2MSFTNGP10.phx.gbl...

> Greetings
>
> I'm having problems adding records to an access database.
> What I want to do is very simple. Here is my code.
>

[Bob Barrows]

Sérgio Almeida wrote:

> Greetings
>
> I'm having problems adding records to an access database.
> What I want to do is very simple. Here is my code.
>
> __CODE_START__
>
> dim sqlStatement
> sqlStatement="insert into
> Records(artist,album,format,label,tracks,year) values ('Pink
> Floyd','THE WALL','2CD','EMI',99,1979)"
>
> dataconn.Execute sqlStatement,affected',adCmdText ' <- ERROR OCCURS
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /MusicStore/addrecord.asp, line 53

We seem to be having a rash of reserved words problems in the past couple
days :-)
"year" (and maybe "format") is a reserved word and should not be used for a
column name in your database. If you cannot change the name of the field in
your table, then you will need to surround it with brackets [] when running
queries containing those names from ADO.

Here is a list of reserved words which you should avoid when naming objects
in your database: [url]http://www.aspfaq.com/show.asp?id=2080[/url]

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Sérgio Almeida]

Than you Bob.
That worked, but now I get the error

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/MusicStore/addrecord.asp, line 53

What mus I do?

Thanks once again.

Bob Barrows wrote:

> Sérgio Almeida wrote:
>

>>Greetings
>>
>>I'm having problems adding records to an access database.
>>What I want to do is very simple. Here is my code.
>>
>>__CODE_START__
>>
>>dim sqlStatement
>>sqlStatement="insert into
>>Records(artist,album,format,label,tracks,year) values ('Pink
>>Floyd','THE WALL','2CD','EMI',99,1979)"
>>
>>dataconn.Execute sqlStatement,affected',adCmdText ' <- ERROR OCCURS
>>Error Type:
>>Microsoft JET Database Engine (0x80040E14)
>>Syntax error in INSERT INTO statement.
>>/MusicStore/addrecord.asp, line 53

>
>
> We seem to be having a rash of reserved words problems in the past couple
> days :-)
> "year" (and maybe "format") is a reserved word and should not be used for a
> column name in your database. If you cannot change the name of the field in
> your table, then you will need to surround it with brackets [] when running
> queries containing those names from ADO.
>
> Here is a list of reserved words which you should avoid when naming objects
> in your database: [url]http://www.aspfaq.com/show.asp?id=2080[/url]
>
> HTH,
> Bob Barrows

[Bob Barrows]

Sérgio Almeida wrote:

> Than you Bob.
> That worked, but now I get the error
>
> Error Type:
> Microsoft JET Database Engine (0x80004005)
> Operation must use an updateable query.
> /MusicStore/addrecord.asp, line 53
>

[url]http://www.aspfaq.com/show.asp?id=2062[/url]

HTH,
Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"