Insert into access DB

Hi all,

Wonder if you can help me with a problem. I've got a database containing 2
tables (one called 'Main' and one called 'View) that are unrelated. When
trying to insert values it doesn't like it and produces an Error 438 -
Illegal Assignment. Though the table is read/write access and not locked or
anything.

The code I am using is as follows:

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Mode=adModeReadWrite
conn.Open "htdocs\ADO\database.mdb"

sql="INSERT INTO Main Values"
sql=sql & "('" & Request.Form("Username") & "',"
sql=sql & "'" & Request.Form("Password") & "',"
sql=sql & "'Basic',"
sql=sql & "'" & Request.Form("Firstname") & "',"
sql=sql & "'" & Request.Form("Lastname") & "',"
sql=sql & "'" & Request.Form("Country") & "',"
sql=sql & "'" & Request.Form("Email") & "')"

on error resume next
conn.Execute sql

sql="INSERT INTO View Values "
sql=sql & "('" & Request.Form("Username") & "',"
sql=sql & "'#c0d9e9',"
sql=sql & "'#e2f2fb',"
sql=sql & "'#a0d4f2',"
sql=sql & "'#e2f2fb',"
sql=sql & "'#a0d4f2',"
sql=sql & "'#ffffff')"

on error resume next
conn.Execute sql

I have tried closing the connection and reopening before the second sql
statement is executed but no luck. It is happy to execute the first sql
statement into 'Main' so the error arises with the second.

Any help would be much appreciated because no other forums seem to offer any
help.

Jon
warpedpixel@gmail.com
Look at that dead pixel on your screen! *SLAP* Gotcha!

Re: Insert into access DB

Jon wrote:
> Hi all,
>
> Wonder if you can help me with a problem. I've got a database
> containing 2 tables (one called 'Main' and one called 'View) that are
> unrelated. When trying to insert values it doesn't like it and
> produces an Error 438 - Illegal Assignment. Though the table is
> read/write access and not locked or anything.

Why is that relevant? The error says nothing about read-only databases ...

>
> The code I am using is as follows:
>
> set conn=Server.CreateObject("ADODB.Connection")
> conn.Provider="Microsoft.Jet.OLEDB.4.0"
> conn.Mode=adModeReadWrite
> conn.Open "htdocs\ADO\database.mdb"
>
> sql="INSERT INTO Main Values"
> sql=sql & "('" & Request.Form("Username") & "',"
> sql=sql & "'" & Request.Form("Password") & "',"
> sql=sql & "'Basic',"
> sql=sql & "'" & Request.Form("Firstname") & "',"
> sql=sql & "'" & Request.Form("Lastname") & "',"
> sql=sql & "'" & Request.Form("Country") & "',"
> sql=sql & "'" & Request.Form("Email") & "')"
>
> on error resume next
> conn.Execute sql
>
> sql="INSERT INTO View Values "
> sql=sql & "('" & Request.Form("Username") & "',"
> sql=sql & "'#c0d9e9',"
> sql=sql & "'#e2f2fb',"
> sql=sql & "'#a0d4f2',"
> sql=sql & "'#e2f2fb',"
> sql=sql & "'#a0d4f2',"
> sql=sql & "'#ffffff')"
>
> on error resume next
> conn.Execute sql
>
> I have tried closing the connection and reopening before the second

You should have tried running the second query without running the first to
see if that made any difference.

> sql statement is executed but no luck. It is happy to execute the
> first sql statement into 'Main' so the error arises with the second.
>

"View" may be a reserved keyword. Let me see .. yes, according to this,
http://www.aspfaq.com/show.asp?id=2080#v, View is reserved. That means you
need to

a) Change the name of the table to a non-reserved word (recommended)
b) Remember to surround the word with brackets [] whenever using it in a
query run via ADO:
sql="INSERT INTO [View] Values "

You should give these posts a read:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Your use of dynamic sql is leaving your site open to attack via sql
injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

Bob Barrows




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Insert into access DB

Thanks for the help. Though for others information to use the constants
"adExecuteNoRecords" and "adcmdText" they need to be defined and the library
needs to be referenced.

Also, I mentioned the infomration regarding the access rights (read/write)
because a quick microsoft search (though mine was extremely) long uncovered
this error refers to something being read only. Though it also turns out
that this error does have numerous other meanings.

One last thing, correction to your code in both posts you gave me the link
to. It reads

if err<>0 then
Response.Write("Your record has been added to the database")
end if

It should read

if err = 0 then

This did cause me a few confusing days. LOL. anyway sorted now thanks!

--
Jon
warpedpixel@gmail.com
Look at that dead pixel on your screen! *SLAP* Gotcha!
"Bob Barrows [MVP]" wrote in message
news:eBJ3s07XFHA.712@TK2MSFTNGP14.phx.gbl...
> Jon wrote:
>> Hi all,
>>
>> Wonder if you can help me with a problem. I've got a database
>> containing 2 tables (one called 'Main' and one called 'View) that are
>> unrelated. When trying to insert values it doesn't like it and
>> produces an Error 438 - Illegal Assignment. Though the table is
>> read/write access and not locked or anything.
>
> Why is that relevant? The error says nothing about read-only databases ...
>
>>
>> The code I am using is as follows:
>>
>> set conn=Server.CreateObject("ADODB.Connection")
>> conn.Provider="Microsoft.Jet.OLEDB.4.0"
>> conn.Mode=adModeReadWrite
>> conn.Open "htdocs\ADO\database.mdb"
>>
>> sql="INSERT INTO Main Values"
>> sql=sql & "('" & Request.Form("Username") & "',"
>> sql=sql & "'" & Request.Form("Password") & "',"
>> sql=sql & "'Basic',"
>> sql=sql & "'" & Request.Form("Firstname") & "',"
>> sql=sql & "'" & Request.Form("Lastname") & "',"
>> sql=sql & "'" & Request.Form("Country") & "',"
>> sql=sql & "'" & Request.Form("Email") & "')"
>>
>> on error resume next
>> conn.Execute sql
>>
>> sql="INSERT INTO View Values "
>> sql=sql & "('" & Request.Form("Username") & "',"
>> sql=sql & "'#c0d9e9',"
>> sql=sql & "'#e2f2fb',"
>> sql=sql & "'#a0d4f2',"
>> sql=sql & "'#e2f2fb',"
>> sql=sql & "'#a0d4f2',"
>> sql=sql & "'#ffffff')"
>>
>> on error resume next
>> conn.Execute sql
>>
>> I have tried closing the connection and reopening before the second
>
> You should have tried running the second query without running the first
> to
> see if that made any difference.
>
>> sql statement is executed but no luck. It is happy to execute the
>> first sql statement into 'Main' so the error arises with the second.
>>
>
> "View" may be a reserved keyword. Let me see .. yes, according to this,
> http://www.aspfaq.com/show.asp?id=2080#v, View is reserved. That means you
> need to
>
> a) Change the name of the table to a non-reserved word (recommended)
> b) Remember to surround the word with brackets [] whenever using it in a
> query run via ADO:
> sql="INSERT INTO [View] Values "
>
> You should give these posts a read:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Your use of dynamic sql is leaving your site open to attack via sql
> injection:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
> http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
> http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
>
> Bob Barrows
>
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Insert into access DB

Jon wrote:

>
> if err<>0 then
> Response.Write("Your record has been added to the database")
> end if
>
> It should read
>
> if err = 0 then
>
> This did cause me a few confusing days. LOL. anyway sorted now thanks!
>

Oops - sorry
--
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"