1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ASP SQL issue

Discussion in 'C#' started by Bartleby, Nov 27, 2008.

0
  1. #1
    i am using asp to update a record and keep getting an error in the update statement.

    
SQL = "UPDATE info SET dept='" & dept & "',strsect='" & sect & "',person='" & person & "',comp='" & comp & "',role='" & role & "' WHERE ID=" & ID
    Code (markup):
    response.write gives:

    
UPDATE info SET dept='this',strsect='is',person='a',comp='test',role='update' WHERE ID=5359
    Code (markup):
    Utilizing an access database, any help would be appreciated. Im going insane!
     
    Bartleby, Nov 27, 2008 IP
  2. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #2
    What is the error msg that you get?
     
    vihutuo, Nov 28, 2008 IP
  3. Bartleby

    Bartleby Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks for your reply. The error i am receiving is:

    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error in UPDATE statement.
    /update_record.asp, line 39
     
    Bartleby, Nov 28, 2008 IP
  4. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #4
    Are all the fields of datatype "text" except for ID?
     
    vihutuo, Nov 28, 2008 IP
  5. Bartleby

    Bartleby Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Yes, ID is autonumber, all the rest are text.

    There is a username and date field that are not being updated as well, but both are text.
     
    Bartleby, Nov 28, 2008 IP
  6. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #6
    There doesn't seem to be any error in the update statement itself.. How are you executing the update statement?
     
    vihutuo, Nov 28, 2008 IP
  7. Bartleby

    Bartleby Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    contents of update_record.asp

    

<%@ language="vbscript" %> 
<% option explicit %>

<html> 
<head><title>update record</title> 
</head> 
<body> 

<%

dim sql, connection, recordset, id 
dim sconnstring, dept, sect, person, comp, role

id = request.form("id")
dept = request.form("dept")
sect = request.form("sect")
person = request.form("person")
comp = request.form("comp")
role = request.form("role")

sql = "update info set dept='" & dept & "',strsect='" & sect & "',person='" & person & "',comp='" & comp & "',role='" & role & "' where id=" & id

set connection=server.createobject("adodb.connection")

sconnstring="provider=microsoft.jet.oledb.4.0;" & "data source=" & server.mappath("db.mdb") 

connection.open(sconnstring) 

connection.execute(sql)

'response.write sql

connection.close
set connection=nothing

response.write "<br><div align='center'>record updated.</div>" 
%>
<a href="table.asp">display table</a>
</body>
</html>
    Code (markup):
     
    Bartleby, Nov 28, 2008 IP
  8. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #8
    You will need to put brackets in role like this [role]

    sql = "update info set [dept]='" & dept & "',[strsect]='" & sect & "',[person]='" & person & "',[comp]='" & comp & "',[role]='" & role & "' where id=" & id
     
    vihutuo, Nov 28, 2008 IP
  9. Bartleby

    Bartleby Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    NM... Worked great! Thanks.

    Which of those are reserved words??? All of them?
     
    Bartleby, Nov 28, 2008 IP
  10. pitagora

    pitagora Peon

    Messages:
    247
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #10
    role is a reserved word.

    By the way you should know that constructing the query the way you do is very unsafe for your application. Should anyone attempt to hack your application by inserting sql statements in those variables you couldn't end up with a lot of mess in the database. Google a bit on parametrized query asp.net
     
    pitagora, Dec 6, 2008 IP
  11. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #11
    Or you may at least want to replace the>> ' if entered in the fields to avoid errors. eg
    dept = Replace(dept, "'", "")
     
    camjohnson95, Dec 7, 2008 IP
  12. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #12
    comp is a reserved word. But it is good practice to enclose all field names in square brackets

     
    vihutuo, Dec 7, 2008 IP