ado properties / parameters

[shank]

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

 

[Bob Barrows [MVP]]

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/dasdkadooverview.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)

 

[shank]

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/dasdkadooverview.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

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

CREATE PROCEDURE UpdateBO (
@AffNo numeric(8,2),
@orderno varchar(20),
@Qty numeric(8,20)) AS

Oops - make that
@Qty numeric(8,2)) AS

 

[shank]

THANKS!!! Works great!

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"

 

[shank]

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

 

[Bob Barrows [MVP]]

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