SQL Quotes Insanity !

Trying to do an Update in SQL on an ASP page.
I need to know why these dont work or what to do to make them work:

sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
Line_ID= '" & LINE_ID & "'"

also this one:
' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID & "'",conn

It says Syntax error near WHERE.

can someone help at all?

cheers,

sd

Re: SQL Quotes Insanity !

sdmusicmaker wrote:
> Trying to do an Update in SQL on an ASP page.
> I need to know why these dont work or what to do to make them work:
>
> sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> Line_ID= '" & LINE_ID & "'"
>
> also this one:
> ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> "'",conn
>
> It says Syntax error near WHERE.
>
> can someone help at all?
>
> cheers,
>
> sd
Read these and then let us know if you need further assistance. If you do,
we will need more information about your database type and version, as well
as some details about the datatypes of the fields in your cus_comment table.

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Quotes Insanity !

Bob, thanks for responding. I'm using SQL server, connection is open,
recordset is open, the update bombs out. The Table is EMORDLIN_SQL . THe data
types are cus_comment = CHAR and Line_ID = Int . I think i have my quotes
messed up somewhere because it is returning the syntax error but i dont see
it?

sd

"Bob Barrows [MVP]" wrote:

> sdmusicmaker wrote:
> > Trying to do an Update in SQL on an ASP page.
> > I need to know why these dont work or what to do to make them work:
> >
> > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> > Line_ID= '" & LINE_ID & "'"
> >
> > also this one:
> > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> > "'",conn
> >
> > It says Syntax error near WHERE.
> >
> > can someone help at all?
> >
> > cheers,
> >
> > sd
> Read these and then let us know if you need further assistance. If you do,
> we will need more information about your database type and version, as well
> as some details about the datatypes of the fields in your cus_comment table.
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: SQL Quotes Insanity !

So did you read the links I sent? particularly the first one containing the
rules for delimiting data in dynamic sql? It contains the answer for your
problem. Particularly the advice to response.write the result of your
concatenation and look at the actual sql statement that's being sent to the
server, enabling you to test it in SQL Query Analyzer ...

I forgot to include the links about the main reason for avoiding dynamic
sql:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Since you are using SQL Server, stored procedures can really make you life
easier
http://tinyurl.com/jyy0

Oh, I forgot to include the link that shows how to use parameters without
stored procedures:
Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

I'm sorry for not specifically telling you how to solve your problem but:
1 I hate dynamic sql so much I can't stand looking at it anymore
2 Give a hungry man a fish and he'll have dinner that evening - give him
fishing instructions and he'll never go hungry again.



sdmusicmaker wrote:
> Bob, thanks for responding. I'm using SQL server, connection is open,
> recordset is open, the update bombs out. The Table is EMORDLIN_SQL .
> THe data
> types are cus_comment = CHAR and Line_ID = Int . I think i have my
> quotes
> messed up somewhere because it is returning the syntax error but i
> dont see
> it?
>
> sd
>
> "Bob Barrows [MVP]" wrote:
>
>> sdmusicmaker wrote:
>>> Trying to do an Update in SQL on an ASP page.
>>> I need to know why these dont work or what to do to make them work:
>>>
>>> sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "'
>>> WHERE
>>> Line_ID= '" & LINE_ID & "'"
>>>
>>> also this one:
>>> ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
>>> "'",conn
>>>
>>> It says Syntax error near WHERE.
>>>
>>> can someone help at all?
>>>
>>> cheers,
>>>
>>> sd
>> Read these and then let us know if you need further assistance. If
>> you do,
>> we will need more information about your database type and version,
>> as well
>> as some details about the datatypes of the fields in your
>> cus_comment table.
>>
>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>
>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Quotes Insanity !

well with a deadline looming i'm ready to go hungry for now, so how do i use
this :

UPDATE EMORDLIN_SQL SET cus_comment = [NewComment]
WHERE Line_ID = [Line_ID]

in my situation? just use this as my sql string?


sd

"Bob Barrows [MVP]" wrote:

> So did you read the links I sent? particularly the first one containing the
> rules for delimiting data in dynamic sql? It contains the answer for your
> problem. Particularly the advice to response.write the result of your
> concatenation and look at the actual sql statement that's being sent to the
> server, enabling you to test it in SQL Query Analyzer ...
>
> I forgot to include the links about the main reason for avoiding dynamic
> sql:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> Since you are using SQL Server, stored procedures can really make you life
> easier
> http://tinyurl.com/jyy0
>
> Oh, I forgot to include the link that shows how to use parameters without
> stored procedures:
> Using Command object to parameterize CommandText:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> I'm sorry for not specifically telling you how to solve your problem but:
> 1 I hate dynamic sql so much I can't stand looking at it anymore
> 2 Give a hungry man a fish and he'll have dinner that evening - give him
> fishing instructions and he'll never go hungry again.
>
>
>
> sdmusicmaker wrote:
> > Bob, thanks for responding. I'm using SQL server, connection is open,
> > recordset is open, the update bombs out. The Table is EMORDLIN_SQL .
> > THe data
> > types are cus_comment = CHAR and Line_ID = Int . I think i have my
> > quotes
> > messed up somewhere because it is returning the syntax error but i
> > dont see
> > it?
> >
> > sd
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> sdmusicmaker wrote:
> >>> Trying to do an Update in SQL on an ASP page.
> >>> I need to know why these dont work or what to do to make them work:
> >>>
> >>> sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "'
> >>> WHERE
> >>> Line_ID= '" & LINE_ID & "'"
> >>>
> >>> also this one:
> >>> ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> >>> "'",conn
> >>>
> >>> It says Syntax error near WHERE.
> >>>
> >>> can someone help at all?
> >>>
> >>> cheers,
> >>>
> >>> sd
> >> Read these and then let us know if you need further assistance. If
> >> you do,
> >> we will need more information about your database type and version,
> >> as well
> >> as some details about the datatypes of the fields in your
> >> cus_comment table.
> >>
> >> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
> >>
> >> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I
> >> don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: SQL Quotes Insanity !

"sdmusicmaker" wrote in message
news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> Trying to do an Update in SQL on an ASP page.
> I need to know why these dont work or what to do to make them work:
>
> sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> Line_ID= '" & LINE_ID & "'"
>

Function SQLStr(rsIn)
SQLStr = Replace(rsIn, "'", "''")
End Function

sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
StrSQL(NewComment) & "' WHERE Line_ID= '" & _
StrSQL(LINE_ID) & "'"


> also this one:
> ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
"'",conn
>

rs.open "SELECT * FROM EMORDLIN_SQL" & _
"WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn


> It says Syntax error near WHERE.
>
> can someone help at all?
>
> cheers,
>
> sd

Re: SQL Quotes Insanity !

See Anthony's reply.
sdmusicmaker wrote:
> well with a deadline looming i'm ready to go hungry for now, so how
> do i use
> this :
>
> UPDATE EMORDLIN_SQL SET cus_comment = [NewComment]
> WHERE Line_ID = [Line_ID]
>
> in my situation? just use this as my sql string?
>
>
> sd
>
> "Bob Barrows [MVP]" wrote:
>
>> So did you read the links I sent? particularly the first one
>> containing the
>> rules for delimiting data in dynamic sql? It contains the answer for
>> your
>> problem. Particularly the advice to response.write the result of your
>> concatenation and look at the actual sql statement that's being sent
>> to the
>> server, enabling you to test it in SQL Query Analyzer ...
>>
>> I forgot to include the links about the main reason for avoiding
>> dynamic
>> sql:
>> http://mvp.unixwiz.net/techtips/sql-injection.html
>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>>
>> Since you are using SQL Server, stored procedures can really make
>> you life
>> easier
>> http://tinyurl.com/jyy0
>>
>> Oh, I forgot to include the link that shows how to use parameters
>> without
>> stored procedures:
>> Using Command object to parameterize CommandText:
>> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>>
>> I'm sorry for not specifically telling you how to solve your problem
>> but: 1 I hate dynamic sql so much I can't stand looking at it anymore
>> 2 Give a hungry man a fish and he'll have dinner that evening - give
>> him
>> fishing instructions and he'll never go hungry again.
>>
>>
>>
>> sdmusicmaker wrote:
>>> Bob, thanks for responding. I'm using SQL server, connection is
>>> open,
>>> recordset is open, the update bombs out. The Table is EMORDLIN_SQL .
>>> THe data
>>> types are cus_comment = CHAR and Line_ID = Int . I think i have my
>>> quotes
>>> messed up somewhere because it is returning the syntax error but i
>>> dont see
>>> it?
>>>
>>> sd
>>>
>>> "Bob Barrows [MVP]" wrote:
>>>
>>>> sdmusicmaker wrote:
>>>>> Trying to do an Update in SQL on an ASP page.
>>>>> I need to know why these dont work or what to do to make them
>>>>> work:
>>>>>
>>>>> sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "'
>>>>> WHERE
>>>>> Line_ID= '" & LINE_ID & "'"
>>>>>
>>>>> also this one:
>>>>> ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
>>>>> "'",conn
>>>>>
>>>>> It says Syntax error near WHERE.
>>>>>
>>>>> can someone help at all?
>>>>>
>>>>> cheers,
>>>>>
>>>>> sd
>>>> Read these and then let us know if you need further assistance. If
>>>> you do,
>>>> we will need more information about your database type and version,
>>>> as well
>>>> as some details about the datatypes of the fields in your
>>>> cus_comment table.
>>>>
>>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>>>
>>>> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>>>>
>>>> --
>>>> Microsoft MVP - ASP/ASP.NET
>>>> Please reply to the newsgroup. This email account is my spam trap
>>>> so
>>>> I
>>>> don't check it very often. If you must reply off-line, then remove
>>>> the "NO SPAM"
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Quotes Insanity !

getting this error :

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'='.

"Anthony Jones" wrote:

>
> "sdmusicmaker" wrote in message
> news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> > Trying to do an Update in SQL on an ASP page.
> > I need to know why these dont work or what to do to make them work:
> >
> > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> > Line_ID= '" & LINE_ID & "'"
> >
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
> sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> StrSQL(LINE_ID) & "'"
>
>
> > also this one:
> > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> "'",conn
> >
>
> rs.open "SELECT * FROM EMORDLIN_SQL" & _
> "WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn
>
>
> > It says Syntax error near WHERE.
> >
> > can someone help at all?
> >
> > cheers,
> >
> > sd
>
>
>

Re: SQL Quotes Insanity !

also this error:

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'StrSQL'
/wo_process_knockout.asp, line 43

Line_ID is an INT in the SQL Server.

sd

"Anthony Jones" wrote:

>
> "sdmusicmaker" wrote in message
> news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> > Trying to do an Update in SQL on an ASP page.
> > I need to know why these dont work or what to do to make them work:
> >
> > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> > Line_ID= '" & LINE_ID & "'"
> >
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
> sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> StrSQL(LINE_ID) & "'"
>
>
> > also this one:
> > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> "'",conn
> >
>
> rs.open "SELECT * FROM EMORDLIN_SQL" & _
> "WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn
>
>
> > It says Syntax error near WHERE.
> >
> > can someone help at all?
> >
> > cheers,
> >
> > sd
>
>
>

Re: SQL Quotes Insanity !

here is the block:

Function SQLStr(rsIn)
SQLStr = Replace(rsIn, "'", "''")
End Function


set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID & "'",conn


sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
StrSQL(NewComment) & "' WHERE Line_ID= '" & _
StrSQL(LINE_ID) & "'"

that is how it stands now but there is an error because Line_ID is an INT
in the SQL DB.

sd

"Anthony Jones" wrote:

>
> "sdmusicmaker" wrote in message
> news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> > Trying to do an Update in SQL on an ASP page.
> > I need to know why these dont work or what to do to make them work:
> >
> > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "' WHERE
> > Line_ID= '" & LINE_ID & "'"
> >
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
> sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> StrSQL(LINE_ID) & "'"
>
>
> > also this one:
> > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> "'",conn
> >
>
> rs.open "SELECT * FROM EMORDLIN_SQL" & _
> "WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn
>
>
> > It says Syntax error near WHERE.
> >
> > can someone help at all?
> >
> > cheers,
> >
> > sd
>
>
>

Re: SQL Quotes Insanity !

sdmusicmaker wrote:
> here is the block:
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
>
> set rs=Server.CreateObject("ADODB.Recordset")
> rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> "'",conn
>
>
> sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> StrSQL(LINE_ID) & "'"
>
> that is how it stands now but there is an error because Line_ID is
> an INT in the SQL DB.
>
Why are you both opening a recordset and running the update statement?

The obvious solution is to get rid of the quotes (apostrophes) around
the Line_ID data. Data intended for numeric columns should not be
delimited.

Also. you HAVE to response.write the sql variable whenever you get a
syntax error so you (we) can see the actual resulting sql statement. Do
not post a sql syntax problem without showing us the sql statement. The
vbscript concatenation code that is supposed to result in the sql
statement will not always reveal the problem.

Response.write sql

Bob Barrows

Re: SQL Quotes Insanity !

"sdmusicmaker" wrote in message
news:474ADE48-783A-4519-B066-B4402AB22C78@microsoft.com...
> here is the block:
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
>
> set rs=Server.CreateObject("ADODB.Recordset")
> rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID & "'",conn
>
>
> sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> StrSQL(LINE_ID) & "'"
>
> that is how it stands now but there is an error because Line_ID is an INT
> in the SQL DB.
>
Ok. I appeared to me from you OP that Line_ID was a string. If the Line_ID
variable is an number type then you don't need quotes od the SQLStr
function:-

Function SQLStr(rsIn)
SQLStr = Replace(rsIn, "'", "''")
End Function


set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID=" & Line_ID, conn


sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
StrSQL(NewComment) & "' WHERE Line_ID= " & _
LINE_ID



> sd
>
> "Anthony Jones" wrote:
>
> >
> > "sdmusicmaker" wrote in message
> > news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> > > Trying to do an Update in SQL on an ASP page.
> > > I need to know why these dont work or what to do to make them work:
> > >
> > > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "'
WHERE
> > > Line_ID= '" & LINE_ID & "'"
> > >
> >
> > Function SQLStr(rsIn)
> > SQLStr = Replace(rsIn, "'", "''")
> > End Function
> >
> > sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> > StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> > StrSQL(LINE_ID) & "'"
> >
> >
> > > also this one:
> > > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> > "'",conn
> > >
> >
> > rs.open "SELECT * FROM EMORDLIN_SQL" & _
> > "WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn
> >
> >
> > > It says Syntax error near WHERE.
> > >
> > > can someone help at all?
> > >
> > > cheers,
> > >
> > > sd
> >
> >
> >

Re: SQL Quotes Insanity !

i have decided to heed your advice, forgoe the error of my ways, convert, and
just try to do a SP. This is what I have on my page, the error that now
appears, and my SP syntax which test ok BTW.

ERROR:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/WebViews/wo_process_knockout.asp, line 34
....that would be the the Line_ID.

My code:
<%

A = Request.Form("KO_A")
B = Request.Form("KO_B")
C = Request.Form("KO_C")
D = Request.Form("KO_D")

NewComment = Trim(A & B & C & D)
Response.write (NewComment)
Line_ID= Request.QueryString("Line_ID")

%>
<%
Dim Command1__Line_ID
Command1__Line_ID = ""
if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)

Dim Command1__cus_comment
Command1__cus_comment = ""
if(Request("cuscomment") <> "") then Command1__cus_comment = NewComment

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = Con_STRING
Command1.CommandText = "dbo.UpdateKnockout"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
'Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@Line_ID", 3,
1,4,Command1__Line_ID)
Command1.Parameters.Append Command1.CreateParameter("@cus_comment", 129,
1,40,Command1__cus_comment)
Command1.Execute()

set Command1 = Nothing

%>

My SP:

CREATE PROCEDURE dbo.UpdateKnockout
@Line_ID INT,
@cus_comment CHAR(40)

AS
BEGIN

UPDATE EMORDLIN_SQL SET
cus_comment=@cus_comment

WHERE
Line_ID = @Line_ID
END
GO

Seems that I have the Datatypes right,...your thoughts

sd




"Bob Barrows [MVP]" wrote:

> sdmusicmaker wrote:
> > here is the block:
> >
> > Function SQLStr(rsIn)
> > SQLStr = Replace(rsIn, "'", "''")
> > End Function
> >
> >
> > set rs=Server.CreateObject("ADODB.Recordset")
> > rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> > "'",conn
> >
> >
> > sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> > StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> > StrSQL(LINE_ID) & "'"
> >
> > that is how it stands now but there is an error because Line_ID is
> > an INT in the SQL DB.
> >
> Why are you both opening a recordset and running the update statement?
>
> The obvious solution is to get rid of the quotes (apostrophes) around
> the Line_ID data. Data intended for numeric columns should not be
> delimited.
>
> Also. you HAVE to response.write the sql variable whenever you get a
> syntax error so you (we) can see the actual resulting sql statement. Do
> not post a sql syntax problem without showing us the sql statement. The
> vbscript concatenation code that is supposed to result in the sql
> statement will not always reveal the problem.
>
> Response.write sql
>
> Bob Barrows
>
>
>

Re: SQL Quotes Insanity !

sdmusicmaker wrote:
> i have decided to heed your advice, forgoe the error of my ways,
> convert, and just try to do a SP.

Excellent!

> This is what I have on my page, the
> error that now appears, and my SP syntax which test ok BTW.

You now see the beauty of using a SP.

>
> ERROR:
> ADODB.Command (0x800A0D5D)
> Application uses a value of the wrong type for the current operation..

This usually (not in this case) means that you failed to define the ADO
constants that you used in your CreateParameter statements (see
http://www.aspfaq.com/show.asp?id=2112). However, you didn't use any
constants - you used the actual values. So I'm a little puzzled ...

> /WebViews/wo_process_knockout.asp, line 34
> ....that would be the the Line_ID.
>
It would have been helpful if you had pinpointed the line (34) that
generated that error for us.There are several lines that contain the
word "Line_ID".

I could go through the trouble of setting up a test page and debugging
your code for myself, but I would like to make an alterative suggestion
(which you can read about in one of the links I supplied): since you
have no output parameters and you aren't interested in reading the value
of the Return parameter, you don't really need to be using an explicit
Command object. See below for my suggestion:

> My code:
> <%
>
> A = Request.Form("KO_A")
> B = Request.Form("KO_B")
> C = Request.Form("KO_C")
> D = Request.Form("KO_D")
>
> NewComment = Trim(A & B & C & D)
> Response.write (NewComment)
> Line_ID= Request.QueryString("Line_ID")
>
> %>
> <%
> Dim Command1__Line_ID
> Command1__Line_ID = ""
> if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)
>
> Dim Command1__cus_comment
> Command1__cus_comment = ""
> if(Request("cuscomment") <> "") then Command1__cus_comment =
> NewComment
>
> %>
> <%
>
get rid of this stuff
************************************************************ ***
> set Command1 = Server.CreateObject("ADODB.Command")
> Command1.ActiveConnection = Con_STRING

Bad, bad, bad! :-)
Always use an explicit Connection object. See below.
Also, just in case, see this: http://www.aspfaq.com/show.asp?id=2126


> Command1.CommandText = "dbo.UpdateKnockout"
> Command1.CommandType = 4
> Command1.CommandTimeout = 0
> Command1.Prepared = true
> 'Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE",
> 3, 4) Command1.Parameters.Append Command1.CreateParameter("@Line_ID",
> 3, 1,4,Command1__Line_ID)
> Command1.Parameters.Append Command1.CreateParameter("@cus_comment",
> 129, 1,40,Command1__cus_comment)
> Command1.Execute()
>
> set Command1 = Nothing
************************************************************ ****

and do this instead:

on error resume next
Command1__Line_ID=clng(Command1__Line_ID)
if err <> 0 then
response.write "Could not convert " & Command1__Line_ID & " to Long"
Response.end
end if
dim cn
Set cn=CreateObject("ADODB.Connection")
cn.open Con_STRING
cn.UpdateKnockout Command1__Line_ID,Command1__cus_comment
if err<> 0 then
'handle the error
end if

>
> %>
>
> My SP:
>
> CREATE PROCEDURE dbo.UpdateKnockout
> @Line_ID INT,
> @cus_comment CHAR(40)
>
> AS
> BEGIN
>
> UPDATE EMORDLIN_SQL SET
> cus_comment = @cus_comment
>
> WHERE
> Line_ID = @Line_ID
> END
> GO
>
> Seems that I have the Datatypes right,...your thoughts
>
If you really have a need to use an explicit Command object, then you
should try out my SP Code Generator available for free download at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Quotes Insanity !

using your code, now it says could convert to long...running out of time.
why do you have Clng for the Line_Id that is supposed to be an Int?

sd

"Bob Barrows [MVP]" wrote:

> sdmusicmaker wrote:
> > i have decided to heed your advice, forgoe the error of my ways,
> > convert, and just try to do a SP.
>
> Excellent!
>
> > This is what I have on my page, the
> > error that now appears, and my SP syntax which test ok BTW.
>
> You now see the beauty of using a SP.
>
> >
> > ERROR:
> > ADODB.Command (0x800A0D5D)
> > Application uses a value of the wrong type for the current operation..
>
> This usually (not in this case) means that you failed to define the ADO
> constants that you used in your CreateParameter statements (see
> http://www.aspfaq.com/show.asp?id=2112). However, you didn't use any
> constants - you used the actual values. So I'm a little puzzled ...
>
> > /WebViews/wo_process_knockout.asp, line 34
> > ....that would be the the Line_ID.
> >
> It would have been helpful if you had pinpointed the line (34) that
> generated that error for us.There are several lines that contain the
> word "Line_ID".
>
> I could go through the trouble of setting up a test page and debugging
> your code for myself, but I would like to make an alterative suggestion
> (which you can read about in one of the links I supplied): since you
> have no output parameters and you aren't interested in reading the value
> of the Return parameter, you don't really need to be using an explicit
> Command object. See below for my suggestion:
>
> > My code:
> > <%
> >
> > A = Request.Form("KO_A")
> > B = Request.Form("KO_B")
> > C = Request.Form("KO_C")
> > D = Request.Form("KO_D")
> >
> > NewComment = Trim(A & B & C & D)
> > Response.write (NewComment)
> > Line_ID= Request.QueryString("Line_ID")
> >
> > %>
> > <%
> > Dim Command1__Line_ID
> > Command1__Line_ID = ""
> > if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)
> >
> > Dim Command1__cus_comment
> > Command1__cus_comment = ""
> > if(Request("cuscomment") <> "") then Command1__cus_comment =
> > NewComment
> >
> > %>
> > <%
> >
> get rid of this stuff
> ************************************************************ ***
> > set Command1 = Server.CreateObject("ADODB.Command")
> > Command1.ActiveConnection = Con_STRING
>
> Bad, bad, bad! :-)
> Always use an explicit Connection object. See below.
> Also, just in case, see this: http://www.aspfaq.com/show.asp?id=2126
>
>
> > Command1.CommandText = "dbo.UpdateKnockout"
> > Command1.CommandType = 4
> > Command1.CommandTimeout = 0
> > Command1.Prepared = true
> > 'Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE",
> > 3, 4) Command1.Parameters.Append Command1.CreateParameter("@Line_ID",
> > 3, 1,4,Command1__Line_ID)
> > Command1.Parameters.Append Command1.CreateParameter("@cus_comment",
> > 129, 1,40,Command1__cus_comment)
> > Command1.Execute()
> >
> > set Command1 = Nothing
> ************************************************************ ****
>
> and do this instead:
>
> on error resume next
> Command1__Line_ID=clng(Command1__Line_ID)
> if err <> 0 then
> response.write "Could not convert " & Command1__Line_ID & " to Long"
> Response.end
> end if
> dim cn
> Set cn=CreateObject("ADODB.Connection")
> cn.open Con_STRING
> cn.UpdateKnockout Command1__Line_ID,Command1__cus_comment
> if err<> 0 then
> 'handle the error
> end if
>
> >
> > %>
> >
> > My SP:
> >
> > CREATE PROCEDURE dbo.UpdateKnockout
> > @Line_ID INT,
> > @cus_comment CHAR(40)
> >
> > AS
> > BEGIN
> >
> > UPDATE EMORDLIN_SQL SET
> > cus_comment = @cus_comment
> >
> > WHERE
> > Line_ID = @Line_ID
> > END
> > GO
> >
> > Seems that I have the Datatypes right,...your thoughts
> >
> If you really have a need to use an explicit Command object, then you
> should try out my SP Code Generator available for free download at
> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: SQL Quotes Insanity !

i meant it could not convert to Long...;-)

"Bob Barrows [MVP]" wrote:

> sdmusicmaker wrote:
> > i have decided to heed your advice, forgoe the error of my ways,
> > convert, and just try to do a SP.
>
> Excellent!
>
> > This is what I have on my page, the
> > error that now appears, and my SP syntax which test ok BTW.
>
> You now see the beauty of using a SP.
>
> >
> > ERROR:
> > ADODB.Command (0x800A0D5D)
> > Application uses a value of the wrong type for the current operation..
>
> This usually (not in this case) means that you failed to define the ADO
> constants that you used in your CreateParameter statements (see
> http://www.aspfaq.com/show.asp?id=2112). However, you didn't use any
> constants - you used the actual values. So I'm a little puzzled ...
>
> > /WebViews/wo_process_knockout.asp, line 34
> > ....that would be the the Line_ID.
> >
> It would have been helpful if you had pinpointed the line (34) that
> generated that error for us.There are several lines that contain the
> word "Line_ID".
>
> I could go through the trouble of setting up a test page and debugging
> your code for myself, but I would like to make an alterative suggestion
> (which you can read about in one of the links I supplied): since you
> have no output parameters and you aren't interested in reading the value
> of the Return parameter, you don't really need to be using an explicit
> Command object. See below for my suggestion:
>
> > My code:
> > <%
> >
> > A = Request.Form("KO_A")
> > B = Request.Form("KO_B")
> > C = Request.Form("KO_C")
> > D = Request.Form("KO_D")
> >
> > NewComment = Trim(A & B & C & D)
> > Response.write (NewComment)
> > Line_ID= Request.QueryString("Line_ID")
> >
> > %>
> > <%
> > Dim Command1__Line_ID
> > Command1__Line_ID = ""
> > if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)
> >
> > Dim Command1__cus_comment
> > Command1__cus_comment = ""
> > if(Request("cuscomment") <> "") then Command1__cus_comment =
> > NewComment
> >
> > %>
> > <%
> >
> get rid of this stuff
> ************************************************************ ***
> > set Command1 = Server.CreateObject("ADODB.Command")
> > Command1.ActiveConnection = Con_STRING
>
> Bad, bad, bad! :-)
> Always use an explicit Connection object. See below.
> Also, just in case, see this: http://www.aspfaq.com/show.asp?id=2126
>
>
> > Command1.CommandText = "dbo.UpdateKnockout"
> > Command1.CommandType = 4
> > Command1.CommandTimeout = 0
> > Command1.Prepared = true
> > 'Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE",
> > 3, 4) Command1.Parameters.Append Command1.CreateParameter("@Line_ID",
> > 3, 1,4,Command1__Line_ID)
> > Command1.Parameters.Append Command1.CreateParameter("@cus_comment",
> > 129, 1,40,Command1__cus_comment)
> > Command1.Execute()
> >
> > set Command1 = Nothing
> ************************************************************ ****
>
> and do this instead:
>
> on error resume next
> Command1__Line_ID=clng(Command1__Line_ID)
> if err <> 0 then
> response.write "Could not convert " & Command1__Line_ID & " to Long"
> Response.end
> end if
> dim cn
> Set cn=CreateObject("ADODB.Connection")
> cn.open Con_STRING
> cn.UpdateKnockout Command1__Line_ID,Command1__cus_comment
> if err<> 0 then
> 'handle the error
> end if
>
> >
> > %>
> >
> > My SP:
> >
> > CREATE PROCEDURE dbo.UpdateKnockout
> > @Line_ID INT,
> > @cus_comment CHAR(40)
> >
> > AS
> > BEGIN
> >
> > UPDATE EMORDLIN_SQL SET
> > cus_comment = @cus_comment
> >
> > WHERE
> > Line_ID = @Line_ID
> > END
> > GO
> >
> > Seems that I have the Datatypes right,...your thoughts
> >
> If you really have a need to use an explicit Command object, then you
> should try out my SP Code Generator available for free download at
> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: SQL Quotes Insanity !

Anthony,

it throws a type mismatch on the update line with the code you provided
below. (error say's Type mismatch: 'StrSQL'). Line_Id is an Int and Comments
are Char in DB.

Function SQLStr(rsIn)
SQLStr = Replace(rsIn, "'", "''")
End Function


set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID & "'",conn

sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & StrSQL(NewComment) & "'
WHERE Line_ID= " & LINE_ID

any thoughts?

sd

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

"Anthony Jones" wrote:

>
> "sdmusicmaker" wrote in message
> news:474ADE48-783A-4519-B066-B4402AB22C78@microsoft.com...
> > here is the block:
> >
> > Function SQLStr(rsIn)
> > SQLStr = Replace(rsIn, "'", "''")
> > End Function
> >
> >
> > set rs=Server.CreateObject("ADODB.Recordset")
> > rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID & "'",conn
> >
> >
> > sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> > StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> > StrSQL(LINE_ID) & "'"
> >
> > that is how it stands now but there is an error because Line_ID is an INT
> > in the SQL DB.
> >
> Ok. I appeared to me from you OP that Line_ID was a string. If the Line_ID
> variable is an number type then you don't need quotes od the SQLStr
> function:-
>
> Function SQLStr(rsIn)
> SQLStr = Replace(rsIn, "'", "''")
> End Function
>
>
> set rs=Server.CreateObject("ADODB.Recordset")
> rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID=" & Line_ID, conn
>
>
> sql="UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> StrSQL(NewComment) & "' WHERE Line_ID= " & _
> LINE_ID
>
>
>
> > sd
> >
> > "Anthony Jones" wrote:
> >
> > >
> > > "sdmusicmaker" wrote in message
> > > news:4ACEAD0A-3626-4B09-983F-262853CBBF7E@microsoft.com...
> > > > Trying to do an Update in SQL on an ASP page.
> > > > I need to know why these dont work or what to do to make them work:
> > > >
> > > > sql= "UPDATE EMORDLIN_SQL SET cus_comment = " & NewComment & "'
> WHERE
> > > > Line_ID= '" & LINE_ID & "'"
> > > >
> > >
> > > Function SQLStr(rsIn)
> > > SQLStr = Replace(rsIn, "'", "''")
> > > End Function
> > >
> > > sql= "UPDATE EMORDLIN_SQL SET cus_comment = '" & _
> > > StrSQL(NewComment) & "' WHERE Line_ID= '" & _
> > > StrSQL(LINE_ID) & "'"
> > >
> > >
> > > > also this one:
> > > > ' rs.open "SELECT * FROM EMORDLIN_SQL WHERE Line_ID='" & Line_ID &
> > > "'",conn
> > > >
> > >
> > > rs.open "SELECT * FROM EMORDLIN_SQL" & _
> > > "WHERE Line_ID='" & SQLStr(Line_ID) & "'", conn
> > >
> > >
> > > > It says Syntax error near WHERE.
> > > >
> > > > can someone help at all?
> > > >
> > > > cheers,
> > > >
> > > > sd
> > >
> > >
> > >
>
>
>

Re: SQL Quotes Insanity !

Because a vb/vba/vbscript Long is equivalent to a SQLServer Int
(http://www.carlprothman.net/Technology/DataTypeMapping/tabi d/97/Default
..aspx).

Why don't you show me the value of Line_ID that is raising this error?

sdmusicmaker wrote:
> using your code, now it says could convert to long...running out of
> time. why do you have Clng for the Line_Id that is supposed to be an
> Int?
>
> sd
>
> "Bob Barrows [MVP]" wrote:
>
>> sdmusicmaker wrote:
>>> i have decided to heed your advice, forgoe the error of my ways,
>>> convert, and just try to do a SP.
>>
>> Excellent!
>>
>>> This is what I have on my page, the
>>> error that now appears, and my SP syntax which test ok BTW.
>>
>> You now see the beauty of using a SP.
>>
>>>
>>> ERROR:
>>> ADODB.Command (0x800A0D5D)
>>> Application uses a value of the wrong type for the current
>>> operation..
>>
>> This usually (not in this case) means that you failed to define the
>> ADO constants that you used in your CreateParameter statements (see
>> http://www.aspfaq.com/show.asp?id=2112). However, you didn't use any
>> constants - you used the actual values. So I'm a little puzzled ...
>>
>>> /WebViews/wo_process_knockout.asp, line 34
>>> ....that would be the the Line_ID.
>>>
>> It would have been helpful if you had pinpointed the line (34) that
>> generated that error for us.There are several lines that contain the
>> word "Line_ID".
>>
>> I could go through the trouble of setting up a test page and
>> debugging your code for myself, but I would like to make an
>> alterative suggestion (which you can read about in one of the links
>> I supplied): since you have no output parameters and you aren't
>> interested in reading the value of the Return parameter, you don't
>> really need to be using an explicit Command object. See below for my
>> suggestion:
>>
>>> My code:
>>> <%
>>>
>>> A = Request.Form("KO_A")
>>> B = Request.Form("KO_B")
>>> C = Request.Form("KO_C")
>>> D = Request.Form("KO_D")
>>>
>>> NewComment = Trim(A & B & C & D)
>>> Response.write (NewComment)
>>> Line_ID= Request.QueryString("Line_ID")
>>>
>>> %>
>>> <%
>>> Dim Command1__Line_ID
>>> Command1__Line_ID = ""
>>> if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)
>>>
>>> Dim Command1__cus_comment
>>> Command1__cus_comment = ""
>>> if(Request("cuscomment") <> "") then Command1__cus_comment =
>>> NewComment
>>>
>>> %>
>>> <%
>>>
>> get rid of this stuff
>> ************************************************************ ***
>>> set Command1 = Server.CreateObject("ADODB.Command")
>>> Command1.ActiveConnection = Con_STRING
>>
>> Bad, bad, bad! :-)
>> Always use an explicit Connection object. See below.
>> Also, just in case, see this: http://www.aspfaq.com/show.asp?id=2126
>>
>>
>>> Command1.CommandText = "dbo.UpdateKnockout"
>>> Command1.CommandType = 4
>>> Command1.CommandTimeout = 0
>>> Command1.Prepared = true
>>> 'Command1.Parameters.Append
>>> Command1.CreateParameter("@RETURN_VALUE", 3, 4)
>>> Command1.Parameters.Append Command1.CreateParameter("@Line_ID", 3,
>>> 1,4,Command1__Line_ID)
>>> Command1.Parameters.Append Command1.CreateParameter("@cus_comment",
>>> 129, 1,40,Command1__cus_comment)
>>> Command1.Execute()
>>>
>>> set Command1 = Nothing
>> ************************************************************ ****
>>
>> and do this instead:
>>
>> on error resume next
>> Command1__Line_ID=clng(Command1__Line_ID)
>> if err <> 0 then
>> response.write "Could not convert " & Command1__Line_ID & " to
>> Long" Response.end
>> end if
>> dim cn
>> Set cn=CreateObject("ADODB.Connection")
>> cn.open Con_STRING
>> cn.UpdateKnockout Command1__Line_ID,Command1__cus_comment
>> if err<> 0 then
>> 'handle the error
>> end if
>>
>>>
>>> %>
>>>
>>> My SP:
>>>
>>> CREATE PROCEDURE dbo.UpdateKnockout
>>> @Line_ID INT,
>>> @cus_comment CHAR(40)
>>>
>>> AS
>>> BEGIN
>>>
>>> UPDATE EMORDLIN_SQL SET
>>> cus_comment = @cus_comment
>>>
>>> WHERE
>>> Line_ID = @Line_ID
>>> END
>>> GO
>>>
>>> Seems that I have the Datatypes right,...your thoughts
>>>
>> If you really have a need to use an explicit Command object, then you
>> should try out my SP Code Generator available for free download at
>> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SQL Quotes Insanity !

the value of the Line_ID is 63.

"Bob Barrows [MVP]" wrote:

> Because a vb/vba/vbscript Long is equivalent to a SQLServer Int
> (http://www.carlprothman.net/Technology/DataTypeMapping/tabi d/97/Default
> ..aspx).
>
> Why don't you show me the value of Line_ID that is raising this error?
>
> sdmusicmaker wrote:
> > using your code, now it says could convert to long...running out of
> > time. why do you have Clng for the Line_Id that is supposed to be an
> > Int?
> >
> > sd
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> sdmusicmaker wrote:
> >>> i have decided to heed your advice, forgoe the error of my ways,
> >>> convert, and just try to do a SP.
> >>
> >> Excellent!
> >>
> >>> This is what I have on my page, the
> >>> error that now appears, and my SP syntax which test ok BTW.
> >>
> >> You now see the beauty of using a SP.
> >>
> >>>
> >>> ERROR:
> >>> ADODB.Command (0x800A0D5D)
> >>> Application uses a value of the wrong type for the current
> >>> operation..
> >>
> >> This usually (not in this case) means that you failed to define the
> >> ADO constants that you used in your CreateParameter statements (see
> >> http://www.aspfaq.com/show.asp?id=2112). However, you didn't use any
> >> constants - you used the actual values. So I'm a little puzzled ...
> >>
> >>> /WebViews/wo_process_knockout.asp, line 34
> >>> ....that would be the the Line_ID.
> >>>
> >> It would have been helpful if you had pinpointed the line (34) that
> >> generated that error for us.There are several lines that contain the
> >> word "Line_ID".
> >>
> >> I could go through the trouble of setting up a test page and
> >> debugging your code for myself, but I would like to make an
> >> alterative suggestion (which you can read about in one of the links
> >> I supplied): since you have no output parameters and you aren't
> >> interested in reading the value of the Return parameter, you don't
> >> really need to be using an explicit Command object. See below for my
> >> suggestion:
> >>
> >>> My code:
> >>> <%
> >>>
> >>> A = Request.Form("KO_A")
> >>> B = Request.Form("KO_B")
> >>> C = Request.Form("KO_C")
> >>> D = Request.Form("KO_D")
> >>>
> >>> NewComment = Trim(A & B & C & D)
> >>> Response.write (NewComment)
> >>> Line_ID= Request.QueryString("Line_ID")
> >>>
> >>> %>
> >>> <%
> >>> Dim Command1__Line_ID
> >>> Command1__Line_ID = ""
> >>> if(Request("LineID") <> "") then Command1__Line_ID = Cint(Line_ID)
> >>>
> >>> Dim Command1__cus_comment
> >>> Command1__cus_comment = ""
> >>> if(Request("cuscomment") <> "") then Command1__cus_comment =
> >>> NewComment
> >>>
> >>> %>
> >>> <%
> >>>
> >> get rid of this stuff
> >> ************************************************************ ***
> >>> set Command1 = Server.CreateObject("ADODB.Command")
> >>> Command1.ActiveConnection = Con_STRING
> >>
> >> Bad, bad, bad! :-)
> >> Always use an explicit Connection object. See below.
> >> Also, just in case, see this: http://www.aspfaq.com/show.asp?id=2126
> >>
> >>
> >>> Command1.CommandText = "dbo.UpdateKnockout"
> >>> Command1.CommandType = 4
> >>> Command1.CommandTimeout = 0
> >>> Command1.Prepared = true
> >>> 'Command1.Parameters.Append
> >>> Command1.CreateParameter("@RETURN_VALUE", 3, 4)
> >>> Command1.Parameters.Append Command1.CreateParameter("@Line_ID", 3,
> >>> 1,4,Command1__Line_ID)
> >>> Command1.Parameters.Append Command1.CreateParameter("@cus_comment",
> >>> 129, 1,40,Command1__cus_comment)
> >>> Command1.Execute()
> >>>
> >>> set Command1 = Nothing
> >> ************************************************************ ****
> >>
> >> and do this instead:
> >>
> >> on error resume next
> >> Command1__Line_ID=clng(Command1__Line_ID)
> >> if err <> 0 then
> >> response.write "Could not convert " & Command1__Line_ID & " to
> >> Long" Response.end
> >> end if
> >> dim cn
> >> Set cn=CreateObject("ADODB.Connection")
> >> cn.open Con_STRING
> >> cn.UpdateKnockout Command1__Line_ID,Command1__cus_comment
> >> if err<> 0 then
> >> 'handle the error
> >> end if
> >>
> >>>
> >>> %>
> >>>
> >>> My SP:
> >>>
> >>> CREATE PROCEDURE dbo.UpdateKnockout
> >>> @Line_ID INT,
> >>> @cus_comment CHAR(40)
> >>>
> >>> AS
> >>> BEGIN
> >>>
> >>> UPDATE EMORDLIN_SQL SET
> >>> cus_comment = @cus_comment
> >>>
> >>> WHERE
> >>> Line_ID = @Line_ID
> >>> END
> >>> GO
> >>>
> >>> Seems that I have the Datatypes right,...your thoughts
> >>>
> >> If you really have a need to use an explicit Command object, then you
> >> should try out my SP Code Generator available for free download at
> >> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp
> >>
> >> Bob Barrows
> >> --
> >> Microsoft MVP -- ASP/ASP.NET
> >> Please reply to the newsgroup. The email account listed in my From
> >> header is my spam trap, so I don't check it very often. You will get
> >> a quicker response by posting to the newsgroup.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: SQL Quotes Insanity !

So, you are telling me that if you run the following page, that you get
the error message?

<%
dim id
id="63"
on error resume next
id=clng(63)
if err <> 0 then
response.write "Error: Could not convert " & id & " to Long"
else
response.write "Success"
end if

Wait a minute. Are you simply assuming that Command1__Line_ID contains
"63"? Have you verified it via a Response.Write?

sdmusicmaker wrote:
> the value of the Line_ID is 63.
>
> "Bob Barrows [MVP]" wrote:
>
>> Because a vb/vba/vbscript Long is equivalent to a SQLServer Int
>>
(http://www.carlprothman.net/Technology/DataTypeMapping/tabi d/97/Default
>> ..aspx).
>>
>> Why don't you show me the value of Line_ID that is raising this
>> error?



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.