Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 7th, 2005, 02:04 PM
Friend of Wrox
 
Join Date: Mar 2005
Location: , , .
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default error trying to update record in asp

Hi everbody. I have an asp script that supposed to edit a player table records. When ever i try to edit a record value i get this strange error. I be happy if some one help me fix this error and be able to update the players records.Thanks

data type for players table:

 playerno ==> number
 name==>text
initials==>text
bird_date==>date/time
************==>text
joined==>number
street==>text
houseno==>text
postcode==>text
town==>text
phoneno==>text
leagueno==>text

---------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''8, POSTCODE = '6392LK'.
/asp/asp/tenissdb_edit.asp, line 236

and the line 236 points to this line :
        cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords

------------------------------------------------------------------
[cod]
edit code:


    Case "edit"
        ' First of a 2 part process... build a form with the
        ' values from the db.
        iRecordId = Request.QueryString("playerno")
        If IsNumeric(iRecordId) Then
            iRecordId = CLng(iRecordId)
        Else
            iRecordId = 0
        End If

        strSQL = "SELECT * FROM players WHERE playerno=" & iRecordId & ";"

        Set rstDBEdit = Server.CreateObject("ADODB.Recordset")
        rstDBEdit.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText

        If Not rstDBEdit.EOF Then
            %>
            <p>
            Note: Watch your input... the text field is small and no error
            handling is done to check for valid integers or dates. If
            an error gets thrown when you submit simply hit back and fix
            the offending entry before resubmitting.
            </p>

            <form action="<%= SCRIPT_NAME %>?action=editsave" method="post">
                <input type="hidden" name="playerno" value="<%= rstDBEdit.Fields("playerno").Value %>"/><br />
                                <input type="text" name="NAME" value="<%= Server.HTMLEncode(rstDBEdit.Fields("NAME").Value) %>" /><br />
                                <input type="text" name="INITIALS" value="<%= Server.HTMLEncode(rstDBEdit.Fields("INITIALS").Val ue) %>" /><br />
                                <input type="text" name="BIRTH_DATE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("BIRTH_DATE").V alue) %>" /><br />
                                <input type="text" name="************" value="<%= Server.HTMLEncode(rstDBEdit.Fields("************").Value) %>" /><br />
                                <input type="text" name="JOINED" value="<%= Server.HTMLEncode(rstDBEdit.Fields("JOINED").Value ) %>" /><br />
                                <input type="text" name="STREET" value="<%= Server.HTMLEncode(rstDBEdit.Fields("STREET").Value ) %>" /><br />
                                <input type="text" name="HOUSENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("HOUSENO").Valu e) %>" /><br />
                                <input type="text" name="POSTCODE" value="<%= Server.HTMLEncode(rstDBEdit.Fields("POSTCODE").Val ue) %>" /><br />
                                <input type="text" name="TOWN" value="<%= Server.HTMLEncode(rstDBEdit.Fields("TOWN").Value) %>" /><br />
                                <input type="text" name="PHONENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("PHONENO").Valu e) %>" /><br />
                <input type="text" name="LEAGUENO" value="<%= Server.HTMLEncode(rstDBEdit.Fields("LEAGUENO").Val ue) %>" /><br />

                                <input type="submit" name="Update Database">
            </form>
            <%
        Else
            Response.Write "Record not found!"
        End If

        rstDBEdit.Close
        Set rstDBEdit = Nothing

        Response.Write(BACK_TO_LIST_TEXT)
-------------------------------------------------------------
[/code]

[cod]
editsave code:

Case "editsave"
        ' Part 2 of 2: Here's where we save the values that the
        ' user entered back to the DB. Again... no error
        ' handling or input checking so ' characters and invalid
        ' values will throw error messages.
        iRecordId = Request.Form("PLAYERNO")
        iRecordId = Replace(iRecordId, "'", "''")

        ' Date delimiter on this should be changed to # for Access
        strSQL = "UPDATE PLAYERS SET " _
            & "NAME = '" & CStr(Replace(Request.Form("NAME"), "'", "''")) & "', " _

            & "INITIALS = " & CStr(Replace(Request.Form("INITIALS"), "'", "''")) & ", " _

            & "BIRTH_DATE = '" & CStr(Replace(Request.Form("BIRTH_DATE"), "'", "''")) & "', " _

                        & "************ = '" & CStr(Replace(Request.Form("************"), "'", "''")) & "', " _

                        & "JOINED = '" & CStr(Replace(Request.Form("JOINED"), "'", "''")) & "', " _

                        & "STREET = '" & CStr(Replace(Request.Form("STREET"), "'", "''")) & "', " _

                        & "HOUSENO = '" & CStr(Replace(Request.Form("HOUSENO"), "'", "''")) & ", " _

                        & "POSTCODE = '" & CStr(Replace(Request.Form("POSTCODE"), "'", "''")) & ", " _

                        & "TOWN = '" & CStr(Replace(Request.Form("TOWN"), "'", "''")) & "', " _

                        & "PHONENO = '" & CStr(Replace(Request.Form("PHONENO"), "'", "''")) & ", " _

                        & "LEAGUENO = '" & CDate(Replace(Request.Form("LEAGUENO"), "'", "''")) & ", " _

                        & "WHERE (PLAYERNO = " & iRecordId & ")"

        ' If something does throw an error, checking this is
        ' actually a valid command often helps debug.
        Response.Write strSQL

        Set cnnDBEdit = Server.CreateObject("ADODB.Connection")
        cnnDBEdit.Open CONN_STRING

    ===> cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords

        cnnDBEdit.Close
        Set cnnDBEdit = Nothing

        Response.Write("<p>Record Id #" & iRecordId & " updated!</p>")
        Response.Write(BACK_TO_LIST_TEXT)


[/code]


Reply With Quote
  #2 (permalink)  
Old May 9th, 2005, 05:42 AM
Authorized User
 
Join Date: Jul 2004
Location: Hyderabad, Andhra Pradesh, India.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to raj_makro
Default

Hi
Check these lines
                       & "HOUSENO = '" & CStr(Replace(Request.Form("HOUSENO"), "'", "''")) & ", " _

                        & "POSTCODE = '" & CStr(Replace(Request.Form("POSTCODE"), "'", "''")) & ", " _

It seems you have forgot to place a single quote (') before comma(,)

Those lines have to be changed to
& "HOUSENO = '" & CStr(Replace(Request.Form("HOUSENO"), "'", "''")) & "', " _

                        & "POSTCODE = '" & CStr(Replace(Request.Form("POSTCODE"), "'", "''")) & "', " _



Regards
Raj
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update record error Dejitan ASP.NET 1.0 and 1.1 Basics 5 December 20th, 2006 10:15 AM
asp-mysql update error chidomen Classic ASP Databases 1 June 15th, 2005 05:29 PM



All times are GMT -4. The time now is 12:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.