|
 |
asp_databases thread: Update issue
Message #1 by "Oliver Tomkins" <oliver.tomkins@g...> on Mon, 23 Apr 2001 11:30:40 +0100
|
|
Hi guys I am sure this is something really simple but I'm pulling my hair
out.
<%response.buffer=true%>
<%
owner_id = request.form("owner_id")
strowner_name = request.form("owner_name")
strowner_address = request.form("owner_address")
strowner_postcode = request.form("owner_postcode")
strowner_telephone = request.form("owner_telephone")
stremergency_contact = request.form("emergency_contact")
stremergency_telephone = request.form("emergency_telephone")
sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
strowner_name & "', '" & strowner_address & "', '" & strowner_postcode & "',
'" & strowner_telephone & "', '" & stremergency_contact & "', '" &
stremergency_telephone & "')"
sql = sql & " WHERE owner_id = " & owner_id & ""
response.write(sql)
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "animal"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open sql,DataConn,1,2
%>
<%
response.redirect("owner.asp")
%>
The above code gives this error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/reedhouse/update_owner_details.asp, line 16
Line 16 is the RSlist.Opem line, if any body could help that would be
greatfully appreciated.
thanks
Olly.
************************************************************************
* Tracking #: D6AF7EE1C437D5119EC200A0C98331C2FF253FAB
*
************************************************************************
Message #2 by Gregory_Griffiths@c... on Mon, 23 Apr 2001 12:04:34 +0100
|
|
As the error seems to be with your SQL, could you confirm that your
response.write(sql) is fine and runable ?
> -----Original Message-----
> From: oliver.tomkins@g... [mailto:oliver.tomkins@g...]
> Sent: 23 April 2001 11:51
> To: asp_databases@p...
> Cc: oliver.tomkins@g...
> Subject: [asp_databases] Update issue
>
>
> Hi guys I am sure this is something really simple but I'm
> pulling my hair
> out.
>
> <%response.buffer=true%>
> <%
> owner_id = request.form("owner_id")
> strowner_name = request.form("owner_name")
> strowner_address = request.form("owner_address")
> strowner_postcode = request.form("owner_postcode")
> strowner_telephone = request.form("owner_telephone")
> stremergency_contact = request.form("emergency_contact")
> stremergency_telephone = request.form("emergency_telephone")
> sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
> strowner_name & "', '" & strowner_address & "', '" &
> strowner_postcode & "',
> '" & strowner_telephone & "', '" & stremergency_contact & "', '" &
> stremergency_telephone & "')"
> sql = sql & " WHERE owner_id = " & owner_id & ""
> response.write(sql)
> Set DataConn = Server.CreateObject("ADODB.Connection")
> DataConn.Open "animal"
> Set RSlist = Server.CreateObject("ADODB.recordset")
> RSlist.Open sql,DataConn,1,2
> %>
>
> <%
> response.redirect("owner.asp")
> %>
>
> The above code gives this error.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in
> UPDATE statement.
>
> /reedhouse/update_owner_details.asp, line 16
>
> Line 16 is the RSlist.Opem line, if any body could help that would be
> greatfully appreciated.
>
> thanks
>
> Olly.
>
Message #3 by "Blake, Shane" <Shane.Blake@p...> on Mon, 23 Apr 2001 09:07:50 -0400
|
|
you're trying to open a recordset with an UPDATE sql string...
only use rs.open with you have a SELECT that returns a recordset...
in your case use :
DataConn.Execute sql
instead of
DataConn.Open "animal"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open sql,DataConn,1,2
shane
-----Original Message-----
From: Oliver Tomkins [mailto:oliver.tomkins@g...]
Sent: Monday, April 23, 2001 6:51 AM
To: ASP Databases
Subject: [asp_databases] Update issue
Message #4 by "ASPCoder123" <sahmer@n...> on Mon, 23 Apr 2001 16:11:34
|
|
Check to see if you are passing an "'" in the data you are entering, If
yes then you have to replacer it with "''", in order to make your SQL
statement work correctly, one more tip try using Clng(Trim(orderid)) as
maybe it has some blank spaces issue.
> As the error seems to be with your SQL, could you confirm that your
> response.write(sql) is fine and runable ?
>
> > -----Original Message-----
> > From: oliver.tomkins@g... [mailto:oliver.tomkins@g...]
> > Sent: 23 April 2001 11:51
> > To: asp_databases@p...
> > Cc: oliver.tomkins@g...
> > Subject: [asp_databases] Update issue
> >
> >
> > Hi guys I am sure this is something really simple but I'm
> > pulling my hair
> > out.
> >
> > <%response.buffer=true%>
> > <%
> > owner_id = request.form("owner_id")
> > strowner_name = request.form("owner_name")
> > strowner_address = request.form("owner_address")
> > strowner_postcode = request.form("owner_postcode")
> > strowner_telephone = request.form("owner_telephone")
> > stremergency_contact = request.form("emergency_contact")
> > stremergency_telephone = request.form("emergency_telephone")
> > sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> > owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
> > strowner_name & "', '" & strowner_address & "', '" &
> > strowner_postcode & "',
> > '" & strowner_telephone & "', '" & stremergency_contact & "', '" &
> > stremergency_telephone & "')"
> > sql = sql & " WHERE owner_id = " & owner_id & ""
> > response.write(sql)
> > Set DataConn = Server.CreateObject("ADODB.Connection")
> > DataConn.Open "animal"
> > Set RSlist = Server.CreateObject("ADODB.recordset")
> > RSlist.Open sql,DataConn,1,2
> > %>
> >
> > <%
> > response.redirect("owner.asp")
> > %>
> >
> > The above code gives this error.
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in
> > UPDATE statement.
> >
> > /reedhouse/update_owner_details.asp, line 16
> >
> > Line 16 is the RSlist.Opem line, if any body could help that would be
> > greatfully appreciated.
> >
> > thanks
> >
> > Olly.
> >
Message #5 by "Oliver Tomkins" <oliver.tomkins@g...> on Mon, 23 Apr 2001 16:20:04 +0100
|
|
Guys, thanks for all your help I reordered the SQL and guess what it works
fine :-)
thanks again.
Olly.
> -----Original Message-----
> From: ASPCoder123 [mailto:sahmer@n...]
> Sent: 23 April 2001 16:12
> To: ASP Databases
> Subject: [asp_databases] RE: Update issue
>
>
> Check to see if you are passing an "'" in the data you are entering, If
> yes then you have to replacer it with "''", in order to make your SQL
> statement work correctly, one more tip try using Clng(Trim(orderid)) as
> maybe it has some blank spaces issue.
>
>
> > As the error seems to be with your SQL, could you confirm that your
> > response.write(sql) is fine and runable ?
> >
> > > -----Original Message-----
> > > From: oliver.tomkins@g... [mailto:oliver.tomkins@g...]
> > > Sent: 23 April 2001 11:51
> > > To: asp_databases@p...
> > > Cc: oliver.tomkins@g...
> > > Subject: [asp_databases] Update issue
> > >
> > >
> > > Hi guys I am sure this is something really simple but I'm
> > > pulling my hair
> > > out.
> > >
> > > <%response.buffer=true%>
> > > <%
> > > owner_id = request.form("owner_id")
> > > strowner_name = request.form("owner_name")
> > > strowner_address = request.form("owner_address")
> > > strowner_postcode = request.form("owner_postcode")
> > > strowner_telephone = request.form("owner_telephone")
> > > stremergency_contact = request.form("emergency_contact")
> > > stremergency_telephone = request.form("emergency_telephone")
> > > sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> > > owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
> > > strowner_name & "', '" & strowner_address & "', '" &
> > > strowner_postcode & "',
> > > '" & strowner_telephone & "', '" & stremergency_contact & "', '" &
> > > stremergency_telephone & "')"
> > > sql = sql & " WHERE owner_id = " & owner_id & ""
> > > response.write(sql)
> > > Set DataConn = Server.CreateObject("ADODB.Connection")
> > > DataConn.Open "animal"
> > > Set RSlist = Server.CreateObject("ADODB.recordset")
> > > RSlist.Open sql,DataConn,1,2
> > > %>
> > >
> > > <%
> > > response.redirect("owner.asp")
> > > %>
> > >
> > > The above code gives this error.
> > >
> > > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > >
> > > [Microsoft][ODBC Microsoft Access Driver] Syntax error in
> > > UPDATE statement.
> > >
> > > /reedhouse/update_owner_details.asp, line 16
> > >
> > > Line 16 is the RSlist.Opem line, if any body could help that would be
> > > greatfully appreciated.
> > >
> > > thanks
> > >
> > > Olly.
>
Message #6 by "Charles Feduke" <webmaster@r...> on Mon, 23 Apr 2001 21:06:31 -0400
|
|
UPDATE and INSERT are different syntax:
INSERT table (field, field, ...) VALUES (value, value, ...)
UPDATE table SET field = value, field = value, ... WHERE field = criteria
This is very annoying, I know. I wish there was an UPDATE OR INSERT which
would ignore the criteria if it was INSERTing.
Hope that helps.
- Chuck
----- Original Message -----
From: "Oliver Tomkins" <oliver.tomkins@g...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, April 23, 2001 6:51 AM
Subject: [asp_databases] Update issue
> Hi guys I am sure this is something really simple but I'm pulling my hair
> out.
>
> <%response.buffer=true%>
> <%
> owner_id = request.form("owner_id")
> strowner_name = request.form("owner_name")
> strowner_address = request.form("owner_address")
> strowner_postcode = request.form("owner_postcode")
> strowner_telephone = request.form("owner_telephone")
> stremergency_contact = request.form("emergency_contact")
> stremergency_telephone = request.form("emergency_telephone")
> sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
> strowner_name & "', '" & strowner_address & "', '" & strowner_postcode & "',
> '" & strowner_telephone & "', '" & stremergency_contact & "', '" &
> stremergency_telephone & "')"
> sql = sql & " WHERE owner_id = " & owner_id & ""
> response.write(sql)
> Set DataConn = Server.CreateObject("ADODB.Connection")
> DataConn.Open "animal"
> Set RSlist = Server.CreateObject("ADODB.recordset")
> RSlist.Open sql,DataConn,1,2
> %>
>
> <%
> response.redirect("owner.asp")
> %>
>
> The above code gives this error.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
>
> /reedhouse/update_owner_details.asp, line 16
>
> Line 16 is the RSlist.Opem line, if any body could help that would be
> greatfully appreciated.
>
> thanks
>
> Olly.
Message #7 by "Charles Feduke" <webmaster@r...> on Mon, 23 Apr 2001 21:24:24 -0400
|
|
Question:
> > > sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> > > owner_telephone, emergency_contact, emergency_telephone) VALUES ('" &
> > > strowner_name & "', '" & strowner_address & "', '" &
> > > strowner_postcode & "',
---
> Guys, thanks for all your help I reordered the SQL and guess what it works
> fine :-)
Does this mean your above syntax actually worked? ::confused::
- Chuck
Message #8 by "Oliver Tomkins" <oliver.tomkins@g...> on Wed, 25 Apr 2001 09:24:52 +0100
|
|
No Charles, I had to lose the VALUES and use SET instead.
You were right no need to be confused.
> -----Original Message-----
> From: Charles Feduke [mailto:webmaster@r...]
> Sent: 24 April 2001 02:24
> To: ASP Databases
> Subject: [asp_databases] RE: Update issue
>
>
> Question:
>
> > > > sql = "UPDATE owner (owner_name, owner_address, owner_postcode,
> > > > owner_telephone, emergency_contact, emergency_telephone)
> VALUES ('" &
> > > > strowner_name & "', '" & strowner_address & "', '" &
> > > > strowner_postcode & "',
> ---
> > Guys, thanks for all your help I reordered the SQL and guess
> what it works
> > fine :-)
>
> Does this mean your above syntax actually worked? ::confused::
>
> - Chuck
>
>
|
|
|
View Cart
