Help in Syntax Error in Insert Statement :-( 1

kazai · Oct 19, 2001

Can Anyone help me find a syntax error in this statement?

strSQL = "Insert into SIGNIN (FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID) values ( "& txt_fName &","& txt_lName &","& txt_UserName &","& txt_Password &","& txt_Email &&quot

"

KodoSan · Oct 19, 2001

you need to surround your values like so '"&value&"'

ToddWW · Oct 19, 2001

Here it is re-written.

Code:

strSQL = "Insert into SIGNIN (FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID) values ('"& txt_fName &"','"& txt_lName &"','"& txt_UserName &"','"& txt_Password &"','"& txt_Email &"')"

Basically, when you send a character value in SQL, you need to surround it with single quotes. So your final SQL string would look like this.

Code:

Insert into SIGNIN (FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID) values ('John','Doe','doej','letmein','johndoe@hotmail.com')

When ever I am baffled on an SQL error, I place the following line just before I execute the statement.

Code:

Response.Write strSQL & "<br>"

That way, my SQL statement will be rendered in the browser so I can examine it for errors.

Hope this helps.

ToddWW

kazai · Oct 19, 2001

Hello Todd,
I tried replacing it, but it still gives me the same error. I have placed Response.Write's after every string like this, but the strings never seem to appear when I execute the file. A page saying"page cannot be displayed...." comes, with the error description:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/Malavika/PFEfiles/SIGN_IN_ASP_files/Insert.asp, line 62, column 15

This is the line of the Insert statement above.
I can't seem to figure this out!!!!
Do let me know if you have any suggestions.
Thankyou very much.

ToddWW · Oct 19, 2001

OK. Just to confirm. Are you getting the error on the line that assigns the string to strSQL or are you getting the error on the line that executes the SQL statement ??

ToddWW

kazai · Oct 19, 2001

I'm geeting it on the line that assigns the string.

ToddWW · Oct 20, 2001

OK, I just ran the following lines on a page in my application and I did not get an error.

Code:

<%@ Language=VBScript %> <% strSQL = "Insert into SIGNIN (FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID) values ('"& txt_fName &"','"& txt_lName &"','"& txt_UserName &"','"& txt_Password &"','"& txt_Email &"')" %>

Can you post the entire page for /Malavika/PFEfiles/SIGN_IN_ASP_files/Insert.asp here so I can take a look.

ToddWW

kazai · Oct 20, 2001

Here you Go Todd,
I appreciate the help..thank you very much. Line 1 starts from <HTML>.

<HTML>
<BODY>
<FORM>
<%@ Language=VBScript %>
<%
Dim Str1
Dim Str2
Dim txt_UserName
Dim txt_Password
Dim txt_fName
Dim txt_lName
Dim txt_Email
Dim hdn_InsCheck
Response.Write("here&quot

hdn_InsCheck = Request.Form("hdn_InsCheck&quot

txt_UserName = Request.Form("txt_UserName&quot

txt_Password = Request.Form("txt_Password&quot

if hdn_InsCheck = "Insert" then
txt_fName = Request.Form("txt_fName&quot

txt_lName = Request.Form("txt_lName&quot

txt_Email = Request.Form("txt_Email&quot

end if

set cnn = server.CreateObject("ADODB.connection&quot

set adoRs1 = server.CreateObject("ADODB.recordset&quot

set adoRs2 = server.CreateObject("ADODB.recordset&quot

cnn.Open "driver={SQLServer}; server=...........; database=.........; uid=......; password=......"

adoRs1.ActiveConnection = cnn
adoRs2.ActiveConnection = cnn
if hdn_InsCheck = "Check" or hdn_InsCheck = "ViewCheck" then
Str1 = "select * from SIGNIN where USERNAME = '"&txt_UserName&"' AND USERPASSWORD = '"& txt_Password &"'"
Response.Write Str1 & "<br>"
adoRs1.open(Str1)

if (adoRs1.EOF) then
Session("InvalidID&quot

= "InvalidId"
if hdn_InsCheck = "Check" then
Response.Redirect("signin.asp&quot

elseif hdn_InsCheck = "ViewCheck" then
Response.Redirect("ViewCheck.asp&quot

end if
else
if hdn_InsCheck = "Check" then
Response.Redirect("SubscribePage.asp&quot

elseif hdn_InsCheck = "ViewCheck" then
Response.Redirect("xyz.asp&quot

end if
end if

elseif hdn_InsCheck = "Insert" or hdn_InsCheck = "InsertViewer" then

Str2 = "select USERNAME from SIGNIN where USERNAME = '"& txt_UserName &"'"

adoRs1.open(Str2)
if adoRs1.EOF then
Str1 = "Insert into SIGNIN(FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID)values('"&txt_fName&"','"&txt_lName&"','"&txt_UserName&"','"&txt_Password&"','"&txt_Email&"')"
Response.Write Str1
adoRs2.open(Str1)
Response.write('here')
if hdn_InsCheck = "Insert" then
Response.redirect("subscribe.asp&quot

elseif hdn_InsCheck = "InsertViewer" then
Response.redirect("xyz.asp&quot

end if
else
Session("InvalidId&quot

= "DuplicateUserName"
if hdn_InsCheck = "Insert" then
Response.redirect("register.asp&quot

elseif hdn_InsCheck = "InsertViewer" then
Response.redirect("NewViewer.asp&quot

end if
end if
end if
cnn.close
%>
</FORM>
</BODY>
</HTML>

Thanks again!!!
Radhika.

ToddWW · Oct 20, 2001

OK. This is the code that I see. I'm not at my test environment so I can't test the syntax of that string variable assignment. But I do see something that looks strange.

Code:

   if adoRs1.EOF then
Str1 = &quot;Insert into SIGNIN(FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID)values('&quot;&txt_fName&&quot;','&quot;&txt_lName&&quot;','&quot;&txt_UserName&&quot;','&quot;&txt_Password&&quot;','&quot;&txt_Email&&quot;')&quot;
         Response.Write Str1
     adoRs2.open(Str1)

adoRs2.open(Str1) Now, everyone has their unique way of creating recordsets and updating database so I'm not saying that yours is wrong. But I do think you might want to try cnn.Execute Str1 to get that insert statement to work correctly. I don't think you can open a recordset with an SQL INSERT statement. Also, I put white space on each side of my values parameter in my insert statement. Again, I can't test that from where I'm at, but it's another thing to think about. It probably doesn't matter.

ToddWW

kazai · Oct 20, 2001

Hello Todd,
Well, Itried cnn.Execute as well, but to no avail. Could tere be something wrong with the database itself??

ToddWW · Oct 20, 2001

Well, let's get back to the basics to see if that is the problem.

Create a new ASP page and copy this into it.

Code:

<%@ Language=VBScript %> <% Option Explicit %> <% dim cnn dim strSQL set cnn = server.CreateObject("ADODB.connection") cnn.Open "driver={SQLServer}; server=......." strSQL = "Insert into SIGNIN (FIRSTNAME, LASTNAME, USERNAME, USERPASSWORD, EMAILID) values ('John','Doe','doej','letmein','johndoe@hotmail.com')" cnn.Execute strSQL %>

Copy that exactly into an ASP page and run it. See if it adds the record to the signin table and / or let me know what errors you receive.

ToddWW

kazai · Oct 20, 2001

Hi Todd,
This works just fine...But the same thing with variables does'nt work. And it's not like the variables have blank values,I checked that out too...they all have valid values.
Thanks,
Radhika.

ToddWW · Oct 20, 2001

OK my friend. You're going to have to trouble shoot that page. You're using conventions on your recordsets that I'm not familiar with so it's not easy for me to debug. I do know, however, that you do need to issue the cnn.Execute strSQL statement to get that INSERT command to work correctly. So at least we identified that. Line 62 was that line in your page.

So, now that we've taken care of that, what error are you getting now, if any..

ToddWW

kazai · Oct 21, 2001

Hello Todd,
Well , actually the problem was that Request.Form was not working, so i used Request.Querystring instead, and Now its working just fine!!!
Is'nt that Strange??
Anyway, Thankyou so much for all your help!!
Radhika.

ToddWW · Oct 21, 2001

Well, I would suggest that you stay away from the Request.QueryString method when posting forms. Reason being is that this method has it's limitations the most convincing one is the limit of data you can send to the page through the QueryString (256 characters including the actual URL).

Therefore, you can go back to using the Request.Form method by making this simple change.

Back on the actual form that you are submitting, you have a <form action="posttopage.asp" method=..> tag. Currently, that tag either does not have a method parameter in it or the method parameter is set to GET. All you have to do is add or change the method parameter to POST and you can use the Request.Form method on the page that is processing the form. I would highly recommend that you use this method unless you have specific reasons not to.

Does that make sense ??

ToddWW

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help in Syntax Error in Insert Statement :-( 1

kazai

Programmer

KodoSan

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

kazai

Programmer

ToddWW

Programmer

Similar threads

Part and Inventory Search

Sponsor