Pass an SQL row using hyperlink

mawoodall · May 25th, 2006, 11:25 PM

I changed the syntax to
IdLink = request.form("hdnId")

Syntax error (missing operator) in query expression 'ID ='.
/asp/Assignment8/dbutils-8.asp, line 37
This error refers to this Sub:

Sub GetRecs (qstr)
    EOF = true

    if isObject(db) then 'Line 36
        set rs = db.execute(qstr) 'Line 37
        EOF = rs.EOF
        if not EOF then
            MyData = rs.getrows
            end if

            rs.Close
            set rs = Nothing
        end if
    End Sub

My main intention is to:
1. click the hyperlinked row number,
2. display that row's firstname, lastname, phone, city
3. edit any of the columns
4. update the record

MaryAnn

woodyz · May 26th, 2006, 01:21 AM

That error is a sql error.
You need to verify that you are getting the value for Id so that the sql string builds correctly.

I strongly suggest you check on the sql statement you are building and verify for yourself that it is correclty formed by printing it out with a response.write and then a response.end.

You need to see that you are getting the value correctly for the Id, and fix whatever it is that is causing you to not get that value.

Try your query in Access without the value for Id and you will get that same error.

I am guessing that you are never putting a value into the hdnId form variable, and therefore nothing is coming back out.

If you have a number of rows and you are selecting one of those rows, then the hidden variable will be useful only if you have a way of populating it with the selected row UPON SELECTING THAT ROW.

So... please post your code so we can see what exaclty you are doing.

Thanks

Woody Z http://www.learntoprogramnow.com

erobb · May 26th, 2006, 07:53 AM

It looks like its not pulling the value which is why its failing. Do you have a hidden field in your form or are you just trying to pull the query String. If your just pulling the query string use the code below.

IdLink = request.form("hdnId") 'This is for requesting form values
response.write IdLink &" =value"
response.end

If you are trying to get the value of the queryString use
IdLink = trim(request.queryString("hdnId"))
'This is for requesting queryStrings

Earl
www.jhdesigninc.com

mawoodall · May 26th, 2006, 09:10 AM

Finally making some progress. I am passing the value of id. Now I need to correct my SQL syntax for UPDATE and I'm home.

This is the page that needs a row based on id. This is working.

<%
'include file dbutils-8.asp contains OpenDB/CloseDB Subs as well as GetRows()
'functions-8.asp contains functions to convert names, phone numbers, city with correct lower/upper case, etc

IdLink = request("Id")
qstr = "SELECT firstname, lastname, city, phone FROM users WHERE ID = " & IdLink

Call OpenDB ("C:\Inetpub\wwwroot\asp\users.mdb", "", "")
    'qstr = "SELECT firstname, lastname, city, phone FROM users WHERE ID = " & IdLink
    'Example: SELECT firsname, lastname, phone, city from users WHERE id = 3"
    qstr = "SELECT ID, firstname, lastname, phone, city from users WHERE ID =" & IdLink
    GetRecs(qstr) 'using GetRows() to return array

for n=0 to Ubound(MyData,2)
    Id = MyData(0,n)
    response.write IdLink & "=IdLink<br>"
    response.write "Id value in For loop = " & Id & "<br>"
    If (Id = (MyData(0,n))) then
        firstname = strName(MyData(1,n)) 'functions to convert lower and upper cases of strings
        lastname = strName(MyData(2,n))
        phone = convertPhone(MyData(3,n))
        city = strCity(MyData(4,n))
    else
        response.write "no match <br>"
    end if

next 'n
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>Edit.ASP</title>
</head>

<body>

<p>Edit Record</p>

<table border=1 cellpadding=2 cellspacing=1 bgcolor=#cccccc>

<form action="editRecord.asp" method="post">
<input type="hidden" name="hdnId" value="<%=Id%>">
<tr><td>First Name</td><td><input type="Text" name="firstname" size="20" value=<%=firstname%>></td></tr>
<tr><td>Last Name</td><td><input type="Text" name="lastname" size="20" value=<%=lastname%>></td></tr>
<tr><td>Phone</td><td><input type="Text" name="phone" size="20" value=<%=phone%> ></td></tr>
<tr><td>City</td><td><input type="Text" name="city" size="20" value=<%=city%>></td></tr>
<tr><td> </td><td><input type="Submit" name="action" value="Update"></td>
</tr>
</form>

</table>
<%CloseDB %>
</body>
</html>

This is editRecord.asp whose purpose is to execute the UPDATE query.

<%
'editRecord.asp
'include file dbutils-8.asp contains OpenDB/CloseDB Subs as well as GetRows()

        Call OpenDB ("C:\Inetpub\wwwroot\asp\users.mdb", "", "")
        IdLink = request("Id")
        firstname=Trim(Request.form("firstname"))
        lastname=Trim(Request.form("lastname"))
        phone=Trim(Request.form("phone"))
        city=Trim(Request.form("city"))

qstr ="UPDATE users SET firstname = " & "'" & firstname & "'" & "," & "'" & lastname & "'" & "," &_
"'" & phone & "'" & "," & "'" & city & "'" & "WHERE ID =" & IdLink

    'UPDATE users SET firstname ='firstname', lastname='lastname', phone = 'phone', city='city' WHERE ID = id
    db.execute(qstr)

    CloseDB

%>

Cut to the chase:
This is the hidden form input:
<input type="hidden" name="hdnId" value="<%=Id%>">

This is how I get the row passed to edit.asp:
IdLink = request("Id")

This is my (incorrect) UPDATE query on editRecord.asp

qstr ="UPDATE users SET firstname = " & "'" & firstname & "'" & "," & "'" & lastname & "'" & "," &_
"'" & phone & "'" & "," & "'" & city & "'" & "WHERE ID =" & IdLink

This is what the query statment should look like, I believe:
'UPDATE users SET firstname ='firstname', lastname='lastname', phone = 'phone', city='city' WHERE ID = id

My error is :Syntax error in UPDATE statement.

Probably something to do with the single quotes, or the comma...too many, not enough...

Thank you so much for your help.
MaryAnn

woodyz · May 26th, 2006, 09:28 AM

Your update statement is leaving out the fieldnames except for first name. It should be something like this (barring any typos I may have introduced) :

Code:

  qstr = "UPDATE users SET firstname = '" & firstname & _
         "', lastname ='" & lastname & _
         "', phone = '" & phone & _
         "',  city ='" & city & _
         "' WHERE ID = " & idLink

Woody Z http://www.learntoprogramnow.com

woodyz · May 26th, 2006, 09:46 AM

Okay - I think you are getting close to being done with this. I hope it all goes well.

Here is a tip:
If you just want to see what your form is submitting, you can add a litte code at the top of the page to response.write your request variables.

To do this, you would add this to the top of the page, and "uncomment" it out when you need to see the values being posted:

' response.write request.form
' response.end

This way you can quickly see what is getting posted to your page... of course, you can do the same thing for request.querystring.

Once you know you are posting the correct values, you comment those lines out and you're on your way.

Woody Z http://www.learntoprogramnow.com

mawoodall · May 26th, 2006, 04:57 PM

With everybody's good advise, I completed everything. Only one little glitch left but I turned in the assignment without fixing it.

This is the code I use to UPDATE. I use GetRows() to return an array and then compare each array value to the request.form("value"). If they don't match, it means the field has been altered. I assign the request.form("value") to that field. For some reason this doesn't work for the 'phone' field.

Call OpenDB ("C:\Inetpub\wwwroot\asp\users.mdb", "", "")
    qstr = "select ID, firstname, lastname, phone, city from users"
    GetRecs(qstr) 'get an array to compare each record to request.form value
for n= 0 to UBound(MyData,2)

        if MyData(1,n) <> request.form("firstname") then
            qstr = "UPDATE users set firstname = '" & request.form("firstname") & "' WHERE ID = " & IdLink
            db.execute(qstr)
            end if 'compare firstname

        if MyData(2,n) <> request.form("lastname") then
            qstr = "UPDATE users set lastname = '" & request.form("lastname") & "' WHERE ID = " & IdLink
            db.execute(qstr)
            end if 'compare lastname

        if MyData(3,n) <> request.form("phone") then
            qstr = "UPDATE users set phone = '" & request.form("phone") & "' WHERE ID = " & IdLink
            db.execute(qstr)
            end if 'compare phone

        if MyData(4,n) <> request.form("city") then
            qstr = "UPDATE users set city = '" & request.form("city") & "' WHERE ID = " & IdLink
            db.execute(qstr)
            end if 'compare city

    next 'n

        Response.Redirect "search.asp"

    Call CloseDB

%>

The 'phone' field is overwritten instead of being replaced. I can edit all other fields and UPDATE the new values. All fields are text except the ID which is autonumber.

erobb · May 26th, 2006, 07:05 PM

Sorry to hear it wasnt "perfect" before you had to turn it in but nothing ever is. I dropped out for a while so I didnt follow the database correction that was needed.

Have you debugged the values with response.write response.end to try and determine cause of failover? Im looking at it and since Im not running it its not obvious to me.

The only, nit picky, thing I see with the code is you call
Response.Redirect "search.asp"
Call CloseDB

You redirect before the DB gets closed so the connection is never closed. Lines should be reversed.

Earl
www.jhdesigninc.com

mawoodall · May 31st, 2006, 04:29 PM

I did try response.write's and I am passing the edited values but for some reason my sql statement is not only not replacing the phone number but is deleting any value from the phone field. Well, alas, I did pass the class. Thank you to EVERYONE for valuable input. Now I have one more question. I do enjoy writing code. I know nothing about .NET. I thought when using .NET developers are using visual studio. When checking that package there's a lot of wysiwyg. I don't like working in that kind of environment. Is there a coding environment for .net? Is the next step from ASP 3.0 to .net or do many developers use Active Server Pages 3.0?

Thanks again for all your help!
MaryAnn

woodyz · May 31st, 2006, 07:27 PM

You can develop for asp.net without the Visual Studio environment. In my experience with it most developers using .NET are using Visual Studio, but that doesn't mean you have to.

ASP is still used a great deal, but it will probably slowly fade away, and there are certainly a lot more jobs for ASP.NET programmers than ASP programmers in most markets, as far as I can tell. ASP.NET is a great deal more powerful, and the new 2.0 is even more so than the 1.1.

I would advice that you spend some time with it and see how it works for you. You can do a lot of work with it without using the drag and drop and wysiwyg.

Woody Z http://www.learntoprogramnow.com