ado properties / parameters

The below code works, but is truncating part of the inserted data.
AffNo is numeric and inserts fine
orderno is alphanumeric and gets truncated at 6 digits
qty is numeric and inserts fine

Not being familiar with the ADO properties, I've tried changing the
following line numbers without success. I've tried 200 and 201 per
http://www.w3schools.com/ado/prop_type.asp#datatypeenum

cmd.Execute ,arParms,129 'adExecuteNoRecords

What should that line be?
thanks

<%
Dim DataConn,SQL,cmd,orderno,qty,arParms,varTextArea

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
cmd.commandtype=1 'adcmdtext
set cmd.activeconnection=DataConn

varTextArea =Split(Request.Form("TextArea"), vbCrLf)

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
s = "SELECT [OrderNo],[Qty] FROM BO WHERE " & _
"[AffNo] = ? AND [OrderNo] = ?"
arParms=array(Session("AffNo"),orderno)
cmd.commandtext=s
Set rs = cmd.Execute(,arParms)

If (rs.EOF) Then
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
"VALUES ('" & Session("AffNo") & "',?,?) "
arParms=array(orderno,qty)
Else
SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _
" WHERE [AffNo] = ? AND [OrderNo] = ?"
arParms=array(Session("AffNo"),orderno)
End If

cmd.commandtext=SQL
cmd.Execute ,arParms,129 'adExecuteNoRecords
rs.close
set rs = nothing
Next
%>

Re: ado properties / parameters

shank wrote:
> The below code works, but is truncating part of the inserted data.
> AffNo is numeric and inserts fine
> orderno is alphanumeric and gets truncated at 6 digits

What is the size of the orderno field in your database?

> qty is numeric and inserts fine
>
> Not being familiar with the ADO properties, I've tried changing the
> following line numbers without success. I've tried 200 and 201 per
> http://www.w3schools.com/ado/prop_type.asp#datatypeenum
>
> cmd.Execute ,arParms,129 'adExecuteNoRecords
>
> What should that line be?

That's exactly what it should be. The article you are reading is irrelevant.
You are not setting data type properties in this line. The 129 is a
combination of two constants: adCmdText and adExecuteNoRecords - you should
familiarize yourself with the ADO documentation at
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadoo verview.asp


What I need to know is:
1. What database are you using?
2. What are the datatypes of the fields in your sql statement? (not the
Format property if you are using Access - just the data types and sizes)
3. How have you verified that the values are not being truncated earlier in
the process (hint - use some response.write statements)?

> varTextArea =Split(Request.Form("TextArea"), vbCrLf)
>
> For i = 0 To UBound(varTextArea)
> arrName = Split(varTextArea(i),",")
> orderno=arrName(0)
> qty=arrName(1)
> 'validate data
> s = "SELECT [OrderNo],[Qty] FROM BO WHERE " & _
> "[AffNo] = ? AND [OrderNo] = ?"

If all you are doing is seeing if this record exists, there is no need to
return more than one field:
s = "SELECT [OrderNo] FROM BO WHERE " & _


> arParms=array(Session("AffNo"),orderno)
> cmd.commandtext=s
> Set rs = cmd.Execute(,arParms)
>
> If (rs.EOF) Then

I would be closing this recordset here:

dim DoInsert
If rs.eof then DoInsert = true
rs.close:set rs = nothing

If DoInsert then

> SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
> "VALUES ('" & Session("AffNo") & "',?,?) "

Why are you concatenating this value in instead of utilizing your arParms
array? Do this:

SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
"VALUES (?,?,?) "
arParms=array(Session("AffNo"), orderno,qty)


> Else
> SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _
> " WHERE [AffNo] = ? AND [OrderNo] = ?"
> arParms=array(Session("AffNo"),orderno)

Why are you concatenating qty instead of utilizing the arParms array? Again,
do this:

SQL="UPDATE BO SET [Qty] = [Qty] + ? "
" WHERE [AffNo] = ? AND [OrderNo] = ?"
arParms=array(qty,Session("AffNo"),orderno)


> End If
>
> cmd.commandtext=SQL
> cmd.Execute ,arParms,129 'adExecuteNoRecords
> rs.close
> set rs = nothing
> Next
> %>

--
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: ado properties / parameters

"Bob Barrows [MVP]" wrote in message
news:%23tPOTgrSGHA.4300@TK2MSFTNGP14.phx.gbl...
> shank wrote:
>> The below code works, but is truncating part of the inserted data.
>> AffNo is numeric and inserts fine
>> orderno is alphanumeric and gets truncated at 6 digits
>
> What is the size of the orderno field in your database?
>
>> qty is numeric and inserts fine
>>
>> Not being familiar with the ADO properties, I've tried changing the
>> following line numbers without success. I've tried 200 and 201 per
>> http://www.w3schools.com/ado/prop_type.asp#datatypeenum
>>
>> cmd.Execute ,arParms,129 'adExecuteNoRecords
>>
>> What should that line be?
>
> That's exactly what it should be. The article you are reading is
> irrelevant. You are not setting data type properties in this line. The 129
> is a combination of two constants: adCmdText and adExecuteNoRecords - you
> should familiarize yourself with the ADO documentation at
> http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadoo verview.asp
>
>
> What I need to know is:
> 1. What database are you using?
> 2. What are the datatypes of the fields in your sql statement? (not the
> Format property if you are using Access - just the data types and sizes)
> 3. How have you verified that the values are not being truncated earlier
> in the process (hint - use some response.write statements)?
>
>> varTextArea =Split(Request.Form("TextArea"), vbCrLf)
>>
>> For i = 0 To UBound(varTextArea)
>> arrName = Split(varTextArea(i),",")
>> orderno=arrName(0)
>> qty=arrName(1)
>> 'validate data
>> s = "SELECT [OrderNo],[Qty] FROM BO WHERE " & _
>> "[AffNo] = ? AND [OrderNo] = ?"
>
> If all you are doing is seeing if this record exists, there is no need to
> return more than one field:
> s = "SELECT [OrderNo] FROM BO WHERE " & _
>
>
>> arParms=array(Session("AffNo"),orderno)
>> cmd.commandtext=s
>> Set rs = cmd.Execute(,arParms)
>>
>> If (rs.EOF) Then
>
> I would be closing this recordset here:
>
> dim DoInsert
> If rs.eof then DoInsert = true
> rs.close:set rs = nothing
>
> If DoInsert then
>
>> SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
>> "VALUES ('" & Session("AffNo") & "',?,?) "
>
> Why are you concatenating this value in instead of utilizing your arParms
> array? Do this:
>
> SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
> "VALUES (?,?,?) "
> arParms=array(Session("AffNo"), orderno,qty)
>
>
>> Else
>> SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _
>> " WHERE [AffNo] = ? AND [OrderNo] = ?"
>> arParms=array(Session("AffNo"),orderno)
>
> Why are you concatenating qty instead of utilizing the arParms array?
> Again, do this:
>
> SQL="UPDATE BO SET [Qty] = [Qty] + ? "
> " WHERE [AffNo] = ? AND [OrderNo] = ?"
> arParms=array(qty,Session("AffNo"),orderno)
>
>
>> End If
>>
>> cmd.commandtext=SQL
>> cmd.Execute ,arParms,129 'adExecuteNoRecords
>> rs.close
>> set rs = nothing
>> Next
>> %>
>
> --
> 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"
--------------------------------
What I need to know is:
1. What database are you using?
SQL

2. What are the datatypes of the fields in your sql statement? (not the
Format property if you are using Access - just the data types and sizes)
AffNo numeric
OrderNo varChar(20)
Qty numeric

3. How have you verified that the values are not being truncated earlier in
the process (hint - use some response.write statements)?
Yes

thanks

Re: ado properties / parameters

shank wrote:
> What I need to know is:
> 1. What database are you using?
> SQL
>

Really? Then you should be using a stored procedure for this.

Also, you should be providing better DDL:
http://www.aspfaq.com/5006

> 2. What are the datatypes of the fields in your sql statement? (not
> the Format property if you are using Access - just the data types and
> sizes) AffNo numeric

What are the precision and scale of this column? You haven't just set the
column to numeric without setting the precision and scale have you?
Look up data types in Books Online.

I am going to assume for the sake of example that they are (8,2).

> OrderNo varChar(20)
> Qty numeric
>
> 3. How have you verified that the values are not being truncated
> earlier in the process (hint - use some response.write statements)?
> Yes
>
In QA, run this script to create the procedure in your database:

CREATE PROCEDURE UpdateBO (
@AffNo numeric(8,2),
@orderno varchar(20),
@Qty numeric(8,20)) AS
BEGIN
SET NOCOUNT ON
UPDATE BO SET Qty = Qty + @Qty
WHERE [AffNo] = @AffNo AND [OrderNo] = @orderno)
IF @@ROWCOUNT = 0
INSERT INTO BO (AffNo,OrderNo,Qty)
VALUES (@AffNo,@OrderNo,@Qty)
END
go

In ASP:
<%
Dim DataConn,orderno,qty,varTextArea

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

varTextArea =Split(Request.Form("TextArea"), vbCrLf)

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
DataConn.UpdateBO Session("AffNo"),orderno,qty
Next
DataConn.Close: Set DataConn=Nothing
%>


Bob Barrows
--
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: ado properties / parameters

Bob Barrows [MVP] wrote:
> CREATE PROCEDURE UpdateBO (
> @AffNo numeric(8,2),
> @orderno varchar(20),
> @Qty numeric(8,20)) AS

Oops - make that
@Qty numeric(8,2)) AS
--
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: ado properties / parameters

THANKS!!! Works great!

"Bob Barrows [MVP]" wrote in message
news:uz9XVFsSGHA.6084@TK2MSFTNGP14.phx.gbl...
> shank wrote:
>> What I need to know is:
>> 1. What database are you using?
>> SQL
>>
>
> Really? Then you should be using a stored procedure for this.
>
> Also, you should be providing better DDL:
> http://www.aspfaq.com/5006
>
>> 2. What are the datatypes of the fields in your sql statement? (not
>> the Format property if you are using Access - just the data types and
>> sizes) AffNo numeric
>
> What are the precision and scale of this column? You haven't just set the
> column to numeric without setting the precision and scale have you?
> Look up data types in Books Online.
>
> I am going to assume for the sake of example that they are (8,2).
>
>> OrderNo varChar(20)
>> Qty numeric
>>
>> 3. How have you verified that the values are not being truncated
>> earlier in the process (hint - use some response.write statements)?
>> Yes
>>
> In QA, run this script to create the procedure in your database:
>
> CREATE PROCEDURE UpdateBO (
> @AffNo numeric(8,2),
> @orderno varchar(20),
> @Qty numeric(8,20)) AS
> BEGIN
> SET NOCOUNT ON
> UPDATE BO SET Qty = Qty + @Qty
> WHERE [AffNo] = @AffNo AND [OrderNo] = @orderno)
> IF @@ROWCOUNT = 0
> INSERT INTO BO (AffNo,OrderNo,Qty)
> VALUES (@AffNo,@OrderNo,@Qty)
> END
> go
>
> In ASP:
> <%
> Dim DataConn,orderno,qty,varTextArea
>
> Set DataConn = Server.CreateObject("ADODB.Connection")
> DataConn.Open MM_JSK_STRING
>
> varTextArea =Split(Request.Form("TextArea"), vbCrLf)
>
> For i = 0 To UBound(varTextArea)
> arrName = Split(varTextArea(i),",")
> orderno=arrName(0)
> qty=arrName(1)
> 'validate data
> DataConn.UpdateBO Session("AffNo"),orderno,qty
> Next
> DataConn.Close: Set DataConn=Nothing
> %>
>
>
> Bob Barrows
> --
> 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: ado properties / parameters

>
> In ASP:
> <%
> Dim DataConn,orderno,qty,varTextArea
>
> Set DataConn = Server.CreateObject("ADODB.Connection")
> DataConn.Open MM_JSK_STRING
>
> varTextArea =Split(Request.Form("TextArea"), vbCrLf)
>
> For i = 0 To UBound(varTextArea)
> arrName = Split(varTextArea(i),",")
> orderno=arrName(0)
> qty=arrName(1)
> 'validate data
> DataConn.UpdateBO Session("AffNo"),orderno,qty
> Next
> DataConn.Close: Set DataConn=Nothing
> %>
>
>
> Bob Barrows
> --
> 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"
=======================================
I'm having an issue with the below line. If the user allows his cursor to
add a blank line at the end of list he submits, the qty of the very last
item is doubled. How do I remove blank lines? thanks!

A1,1
B2,2
C3,3
A4,4
A5,5
A6,6
A7,7
A8,8
A9,9<-- if the cursor stops here - no problem
<-- if the cursor stops here - qty of A9 becomes 18

varTextArea =Split(Request.Form("TextArea"), vbCrLf)

<%
Dim DataConn,orderno,qty,varTextArea

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

varTextArea =Split(Request.Form("TextArea"), vbCrLf)

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
DataConn.stp_RES_InsertBO Session("AffNo"),orderno,qty
Next
DataConn.Close: Set DataConn=Nothing
%>

Re: ado properties / parameters

shank wrote:
> I'm having an issue with the below line. If the user allows his
> cursor to add a blank line at the end of list he submits, the qty of
> the very last item is doubled. How do I remove blank lines? thanks!
>
> A1,1
> B2,2
> C3,3
> A4,4
> A5,5
> A6,6
> A7,7
> A8,8
> A9,9<-- if the cursor stops here - no problem
> <-- if the cursor stops here - qty of A9 becomes 18
>

Use an If statement to check the values of the variables, only running the
stored procedure if the values are valid. (That's what I meant by "
'validate data").
Am I missing something? This seems very obvious ...

--
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"