p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Re: ADODB.Command Problem


Message #1 by "Brian O'Neill" <snowmanco@y...> on Wed, 5 Jun 2002 16:07:20
I was using the ADODB.Command object because it allows you insert 
apostrophes into the Database, when you use the connection.execute method, 
you get an error.

Thanks anyway...


Hi

Why do you do you want to complicate it so much.

Try this
<%


Sqltemp = "Update TableName set '
Sqltemp = Sqltemp & "name = '" & strName & "','"
Sqltemp = Sqltemp & "address = '" & strAddress & "',"
Sqltemp = Sqltemp & "numeric = '" & strNumeric & ")"

 Conn.Execute Sqltemp

 %>


Hope this helps 
Message #2 by "Brian O'Neill" <snowmanco@y...> on Wed, 5 Jun 2002 16:50:58
Thanks a lot Pete, it worked like a charm!



> Brian,

You can add single quotes, but you need to escape them (replace a single
quote with 2 single quotes).  For example:


<%
Function sql_quote( strInput )
	strInput = Replace(strInput,"'","''")
	sql_quote = "'"& strInput & "'"
End Function

strName = "Bob's Name"
strAddress = "100 Nowhere St."
strNumeric = "256"

Sqltemp = "UPDATE TableName SET"
Sqltemp = Sqltemp & " name=" & sql_quote(strName)
Sqltemp = Sqltemp & ", address=" & sql_quote(strAddress)
Sqltemp = Sqltemp & ", numeric=" & sql_quote(strNumeric)


' This will generate a SQL string like this:
' UPDATE TableName SET name='Bob''s Name', address='100 Nowhere St.',
numeric='256'
Conn.Execute Sqltemp

' And it would update the values in the table to look like this:
Bob's Name
100 Nowhere St.
256


Regards,
Pete



> -----Original Message-----
> From: Brian O'Neill [mailto:snowmanco@y...]
> Sent: Wednesday, June 05, 2002 4:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: ADODB.Command Problem
> 
> 
> I was using the ADODB.Command object because it allows you insert 
> apostrophes into the Database, when you use the 
> connection.execute method, 
> you get an error.
> 
> Thanks anyway...
> 
> 
> Hi
> 
> Why do you do you want to complicate it so much.
> 
> Try this
> <%
> 
> 
> Sqltemp = "Update TableName set '
> Sqltemp = Sqltemp & "name = '" & strName & "','"
> Sqltemp = Sqltemp & "address = '" & strAddress & "',"
> Sqltemp = Sqltemp & "numeric = '" & strNumeric & ")"
> 
>  Conn.Execute Sqltemp
> 
>  %>
> 
> 
> Hope this helps 
> 
Message #3 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 5 Jun 2002 11:17:48 -0400
Brian,

You can add single quotes, but you need to escape them (replace a single
quote with 2 single quotes).  For example:


<%
Function sql_quote( strInput )
	strInput = Replace(strInput,"'","''")
	sql_quote = "'"& strInput & "'"
End Function

strName = "Bob's Name"
strAddress = "100 Nowhere St."
strNumeric = "256"

Sqltemp = "UPDATE TableName SET"
Sqltemp = Sqltemp & " name=" & sql_quote(strName)
Sqltemp = Sqltemp & ", address=" & sql_quote(strAddress)
Sqltemp = Sqltemp & ", numeric=" & sql_quote(strNumeric)


' This will generate a SQL string like this:
' UPDATE TableName SET name='Bob''s Name', address='100 Nowhere St.',
numeric='256'
Conn.Execute Sqltemp

' And it would update the values in the table to look like this:
Bob's Name
100 Nowhere St.
256


Regards,
Pete



> -----Original Message-----
> From: Brian O'Neill [mailto:snowmanco@y...]
> Sent: Wednesday, June 05, 2002 4:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: ADODB.Command Problem
> 
> 
> I was using the ADODB.Command object because it allows you insert 
> apostrophes into the Database, when you use the 
> connection.execute method, 
> you get an error.
> 
> Thanks anyway...
> 
> 
> Hi
> 
> Why do you do you want to complicate it so much.
> 
> Try this
> <%
> 
> 
> Sqltemp = "Update TableName set '
> Sqltemp = Sqltemp & "name = '" & strName & "','"
> Sqltemp = Sqltemp & "address = '" & strAddress & "',"
> Sqltemp = Sqltemp & "numeric = '" & strNumeric & ")"
> 
>  Conn.Execute Sqltemp
> 
>  %>
> 
> 
> Hope this helps 
> 
Message #4 by junkmail@i... on Wed, 5 Jun 2002 15:17:17
Hi

Why do you do you want to complicate it so much.

Try this
<%


Sqltemp = "Update TableName set '
Sqltemp = Sqltemp & "name = '" & strName & "','"
Sqltemp = Sqltemp & "address = '" & strAddress & "',"
Sqltemp = Sqltemp & "numeric = '" & strNumeric & ")"

Conn.Execute Sqltemp

%>


Hope this helps 

H
 



> Hello,
I>  am having some trouble getting an UPDATE statment to work with an 
A> DODB.Command object.  A INSERT statment works fine, but when I try and 
u> se an UPDATE it gives me this error: Data type mismatch in criteria 
e> xpression.
H> ere is the code:
-> ---------------------------------------------------------------
D> im DBCom
S> et DBCom = Server.createobject("ADODB.Command")
D> BCom.ActiveConnection = DBCon
D> BCom.CommandText = "UPDATE tblCars SET tblcars.Name = ?, tblCars.Email 
=>  ?, tblCars.Priority = ?, tblCars.Title = ?, tblCars.Project = ?, 
t> blCars.WBS = ?, tblCars.Comment = ? WHERE tblCars.ID = '" & strrec_id 
&>  "' ;"
D> BCom.Prepared = True
D> BCom.Parameters.Append DBCom.CreateParameter("name",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter("email",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter("priority",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter("title",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter("project",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter("wbs",adVarChar, ,255)
D> BCom.Parameters.Append DBCom.CreateParameter ("comment", 
adVarChar, ,255)
D> BCom("name") = strname
D> BCom("email") = stremail
D> BCom("priority") = intpriority
D> BCom("title") = strtitle
D> BCom("project") = strproject
D> BCom("wbs") = strwbs
D> BCom("comment") = strcomment
D> BCom.Execute ,,adCmdText + adExecuteNoRecords
-> ------------------------------------------------------------------------
--
T> hanks in advance for any help.
B> rian
Message #5 by "Brian O'Neill" <snowmanco@y...> on Tue, 4 Jun 2002 19:52:40
Hello,
I am having some trouble getting an UPDATE statment to work with an 
ADODB.Command object.  A INSERT statment works fine, but when I try and 
use an UPDATE it gives me this error: Data type mismatch in criteria 
expression.
Here is the code:
----------------------------------------------------------------
Dim DBCom
Set DBCom = Server.createobject("ADODB.Command")
DBCom.ActiveConnection = DBCon
DBCom.CommandText = "UPDATE tblCars SET tblcars.Name = ?, tblCars.Email 
= ?, tblCars.Priority = ?, tblCars.Title = ?, tblCars.Project = ?, 
tblCars.WBS = ?, tblCars.Comment = ? WHERE tblCars.ID = '" & strrec_id 
& "' ;"
DBCom.Prepared = True
DBCom.Parameters.Append DBCom.CreateParameter("name",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter("email",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter("priority",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter("title",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter("project",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter("wbs",adVarChar, ,255)
DBCom.Parameters.Append DBCom.CreateParameter ("comment", adVarChar, ,255)
DBCom("name") = strname
DBCom("email") = stremail
DBCom("priority") = intpriority
DBCom("title") = strtitle
DBCom("project") = strproject
DBCom("wbs") = strwbs
DBCom("comment") = strcomment
DBCom.Execute ,,adCmdText + adExecuteNoRecords
---------------------------------------------------------------------------
Thanks in advance for any help.
Brian

  Return to Index