my sql query

[omerbutt]

hi i am making an application with the help of html asp and javascript ihave made a New login form and when it submits its values on to another page to create the login and save it into the database i want to make a check that the user name that the person has choosed to create, exists in the database or not i have used the following query but it is giving me error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Missing semicolon (;) at end of SQL statement.
/ASHAR/Ad_us_done.asp, line 16

any body can help me ? ....fanks in advance :)

Ad_us_done.asp

<%
    username=Request("us_name")
    password=Request("pass")
    Dim strSQLus, conus, ADUSD
 
    set ADUSD=Server.CreateObject("ADODB.Recordset")
    set conus=Server.CreateObject("ADODB.Connection")
        conus="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
 
        //strSQLus = "SELECT login.user_n, login.pass_w FROM login;"
        strSQLus = "insert into login(user_n, pass_w) "
        strSQLus = strSQLus & " values('"& username &"','"& password &"') "
        strSQLus = strSQLus & " where user_n <> '"& (Request.Form("us_name")) &"'"
        ADUSD.CursorType = 2
        ADUSD.LockType = 3
        ADUSD.Open strSQLus, conus, 3
        ADUSD.AddNew
 
        ADUSD.fields("user_n")=username
        ADUSD.fields("pass_w")=password
 
        ADUSD.Update
        ADUSD.Close
 
    set ADUSD = Nothing
    set con = Nothing
    Response.Redirect("Ad_us.asp")
%>

[nedu]

Hi,

U r using the where condition in INSERT SQL statement which is not possible. That is the error . . . .

Regards,
Nedu. M

[JamieHowarth0]

Hi omerbutt,

The reason you have an error in your code is due to the syntax in a couple of places. INSERT INTO statements do not accept WHERE clauses. You must perform a SELECT FROM which searches for the username, then check the number of records returned - if 1, then the username exists, and if 0, then the username doesn't exist. If you get any other number then there's an error in your connection or your code somewhere.

<%
    myUsername=Request.QueryString("us_name")
    myPassword=Request.QueryString("pass")
 
    Set myConn =Server.CreateObject("ADODB.Connection")
    strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
    myConn.Open strConn
%>

Previously you had used conus as both your connection object and your connection string, which doesn't help.

<%
    Set rsUser = Server.CreateObject("ADODB.Recordset")
    strSQLus = "SELECT login.user_n, login.pass_w FROM login WHERE login.user_n = '" & myUsername & "';"
    rsUser.Open strSQLus, myConn, 1, 1
    'Now you've performed the search to see if the username already exists
    '(note the specific use of single quotes so that SQL can interpret the
    'string value of the username inputted)
 
    If rsUser.RecordCount = 1
        'Username found, throw error back to user
        Response.Write("Username already exists.<br />Please try again using a different username.")
    Else
        rsUser.Close
       'Closes the recordset object so we can re-use it to insert the
        'new username/password combination
        strSQLus =  "INSERT INTO login VALUES ('"& myUsername &"','"& myPassword &"');"
        rsUser.Open strSQLus, myConn, 2, 3
    Response.Redirect("Ad_us.asp")
End If
%>

Your other problem lies in the fact that you are performing an insert query, which once executed successfully, does not return a recordset - yet you were trying to use the AddNew method to try and add to a recordset that has (in this particular instance) not been returned.
You are supposed to use the AddNew method where you have already SELECTed a number of records from a table/query and want to insert new data into the recordset.

Hope this helps.

medicineworker

[omerbutt]

sir as u told me so i used ur way to use myconn and constr seperately but it produces error on the line where i am trying to open the connection string
myconn.Open connstr.......and the errror is

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/ashar/Ad_us_done.asp, line 10

whereas if i use the same method as i used before using Connus for both object and string it runs fine but still add the names which exist in the data base
for that case i tried to print the query

"select * from login where login.user_n = '"& username &"'"

and yes inbtween 1 thing more that the method that you used Request.Querystring("user_n") to get the values when i try to print the value in the username it is blank whereas using Request("user_n") display the correct value but it isnt comparing it from the database
when i print the strSQL it displays these values in it

1...when i use request.querystring("user_n")

Select * From login Where login.user_n = '';

2.....when i used request("user_n")

Select * From login Where login.user_n = 'Omer Aslam';

can ya help me out i once used the same way for the editing of records where i was passing the primary key and used to get it on the other pagehaving the name id_no and then compared it with the primarykeys in the db to edit the specific record but there i used the clng(request.querystring("id_no")) to make it correctly work do i have to use some funtion of the same kind which should change the requested name into string or wot .......u can tell me better

Hi omerbutt,

The reason you have an error in your code is due to the syntax in a couple of places. INSERT INTO statements do not accept WHERE clauses. You must perform a SELECT FROM which searches for the username, then check the number of records returned - if 1, then the username exists, and if 0, then the username doesn't exist. If you get any other number then there's an error in your connection or your code somewhere.

<%
    myUsername=Request.QueryString("us_name")
    myPassword=Request.QueryString("pass")
 
    Set myConn =Server.CreateObject("ADODB.Connection")
    strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
    myConn.Open strConn
%>

Previously you had used conus as both your connection object and your connection string, which doesn't help.

<%
    Set rsUser = Server.CreateObject("ADODB.Recordset")
    strSQLus = "SELECT login.user_n, login.pass_w FROM login WHERE login.user_n = '" & myUsername & "';"
    rsUser.Open strSQLus, myConn, 1, 1
    'Now you've performed the search to see if the username already exists
    '(note the specific use of single quotes so that SQL can interpret the
    'string value of the username inputted)
 
    If rsUser.RecordCount = 1
        'Username found, throw error back to user
        Response.Write("Username already exists.<br />Please try again using a different username.")
    Else
        rsUser.Close
       'Closes the recordset object so we can re-use it to insert the
        'new username/password combination
        strSQLus =  "INSERT INTO login VALUES ('"& myUsername &"','"& myPassword &"');"
        rsUser.Open strSQLus, myConn, 2, 3
    Response.Redirect("Ad_us.asp")
End If
%>

Your other problem lies in the fact that you are performing an insert query, which once executed successfully, does not return a recordset - yet you were trying to use the AddNew method to try and add to a recordset that has (in this particular instance) not been returned.
You are supposed to use the AddNew method where you have already SELECTed a number of records from a table/query and want to insert new data into the recordset.

Hope this helps.

medicineworker

[JamieHowarth0]

Hi omerbutt,

My apologies - if you are using a form (which I failed to spot) then use Request.Form

<%
    myUsername=Request.Form("us_name")
    myPassword=Request.Form("pass")
 
    Set myConn =Server.CreateObject("ADODB.Connection")
    strConn="Provider={Microsoft.Jet.OLEDB.4.0};Data Source=" & Server.MapPath("db/db1.mdb")
    myConn.Open strConn
'Note the addition of curly brackets and removal of all spaces from your connection string
 
    Set rsUser = Server.CreateObject("ADODB.Recordset")
    strSQLus = "SELECT login.user_n, login.pass_w FROM login WHERE login.user_n = '" & myUsername & "';"
    rsUser.Open strSQLus, myConn, 1, 1
    'Now you've performed the search to see if the username already exists
    '(note the specific use of single quotes so that SQL can interpret the
    'string value of the username inputted)
 
    If rsUser.RecordCount = 1
        'Username found, throw error back to user
        Response.Write("Username already exists.<br />Please try again using a different username.")
    Else
        rsUser.Close
       'Closes the recordset object so we can re-use it to insert the
        'new username/password combination
        strSQLus =  "INSERT INTO login VALUES ('"& myUsername &"','"& myPassword &"');"
        rsUser.Open strSQLus, myConn, 2, 3
    Response.Redirect("Ad_us.asp")
End If
%>

This should now work.

medicineworker

[omerbutt]

hi medicineworker,
looks like i found a solution for my code check this out how i did it and its working perfectly fine ;)....but any way really appereciate ur help an advises they they were really percious and useful for me thanks aloot looking forward in future
regards,
Omer

<%
Dim username, passw, RSANCR, connectionstr
 
username=request("us_name")
passw=request("pass")
 
Set RSANCR=Server.CreateObject("ADODB.Recordset")
Set connectionstr=Server.CreateObject("ADODB.Connection")
connectionstr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
 
strSQL = "SELECT * FROM login;"
RSANCR.OPEN strSQL, connectionstr, 3
 
foundIt=False
 
Do Until RSANCR.EOF OR foundIt    
if (StrComp(RSANCR.Fields("user_n"), username, vbTextCompare) = 0) then
foundIt=True 
else
RSANCR.MoveNext 
End if 
Loop
 
  if  foundIt = "False" Then
    Dim r, constr, strSQL1
    set r=Server.CreateObject("ADODB.Recordset")
    set constr=Server.CreateObject("ADODB.Connection")
    constr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/db1.mdb")
    strSQL1="Insert Into login(user_n,pass_w) Values('"& username &"','"& passw &"');"
     r.Open strSQL1, constr, 3        
     set r = Nothing
     set constr = Nothing
     Response.Redirect("Ad_us.asp")
else
RSANCR.Close
Set RSANCR = Nothing     
Set connectionstr= Nothing 
Response.Redirect("Ad_us.asp?error=1") 
End If 
%>
 

Hi omerbutt,

My apologies - if you are using a form (which I failed to spot) then use Request.Form

<%
    myUsername=Request.Form("us_name")
    myPassword=Request.Form("pass")
 
    Set myConn =Server.CreateObject("ADODB.Connection")
    strConn="Provider={Microsoft.Jet.OLEDB.4.0};Data Source=" & Server.MapPath("db/db1.mdb")
    myConn.Open strConn
'Note the addition of curly brackets and removal of all spaces from your connection string
 
    Set rsUser = Server.CreateObject("ADODB.Recordset")
    strSQLus = "SELECT login.user_n, login.pass_w FROM login WHERE login.user_n = '" & myUsername & "';"
    rsUser.Open strSQLus, myConn, 1, 1
    'Now you've performed the search to see if the username already exists
    '(note the specific use of single quotes so that SQL can interpret the
    'string value of the username inputted)
 
    If rsUser.RecordCount = 1
        'Username found, throw error back to user
        Response.Write("Username already exists.<br />Please try again using a different username.")
    Else
        rsUser.Close
       'Closes the recordset object so we can re-use it to insert the
        'new username/password combination
        strSQLus =  "INSERT INTO login VALUES ('"& myUsername &"','"& myPassword &"');"
        rsUser.Open strSQLus, myConn, 2, 3
    Response.Redirect("Ad_us.asp")
End If
%>

This should now work.

medicineworker