INSERT INTO troubles

[valt76]

Hi,

I am desperately trying to make this query to work. Whenever I do run it, returns a syntax error the code lines I wrote are as below:

 

<%

p_name = Request.querystring("p_name")

p_pass1 = Request.querystring("p_pass1")

p_pass2 = Request.querystring("p_pass2")

p_first = Request.querystring("p_first")

p_last = Request.querystring("p_last")

p_email = Request.querystring("p_email")

 

%>

<%

Dim outpostDB

Dim outpostDBPath

stroutpostDBPath = Server.MapPath("outpost.mdb")

%>

<%

set outpostDB = Server.CreateObject("ADODB.Connection")

outpostDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & stroutpostDBPath & ";"

%>

<%

theSQL = "INSERT INTO members(username, password, first_name, last_name, email) VALUES ('"&p_name&"', '"&p_pass1&"', '"&p_first&"', '"&p_last&"', '"&p_email&"')"

%>

<%

outpostDB.Execute(theSQL)

%>

<%

outpostDB.Close

set outpostDB = Nothing

%>

 

If I make a Response.Write(theSQL), and I copy the query against Access2000 it works.

All fields in the DB table are set as text and characters are 50.

I've also tried to make a SELECT query on this page and it works, therefore the connection with the DB is open and running.

 

Which kind of magic or evil spell am I facing here? :confused:

 

Valter

[Heiko]

I was getting syntax errors when I omitted the blanks between the concatenators.

 

Did not work:

theSQL = "SELECT name FROM member where name = '"&p_name&"'"

 

Did work:

theSQL = "SELECT name FROM member where name = '" & p_name & "'"

[Nerseus]

You've come across VB's little "trick" of typecasting a variable. The & after a variable indicates it should be a Long (or Integer, I can't remember). Obviously not what you want on a string variable. Some extra whitespace should fix it up nicely, as Heiko pointed out.

 

-nerseus