Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ado properties / parameters

Reply
Thread Tools

ado properties / parameters

 
 
shank
Guest
Posts: n/a
 
      03-18-2006
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
%>


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-18-2006
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...dooverview.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"


 
Reply With Quote
 
 
 
 
shank
Guest
Posts: n/a
 
      03-18-2006

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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...dooverview.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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-18-2006
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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-18-2006
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"


 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      03-18-2006
THANKS!!! Works great!

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>



 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      03-19-2006
>
> 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
%>


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-19-2006
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
synonym for StProc prevents ADO.Parameters to Refresh() keyser soze ASP General 7 06-15-2007 06:53 PM
Translating Oracle Package Call from ADO to ADO.Net dmiratsky@yahoo.com ASP .Net 0 02-14-2005 10:39 PM
Transfer ADO Code to ADO.NET ronaldlee ASP .Net 1 12-17-2004 04:08 PM
Accessing an ADO Recordset or Record from ADO.NET nita ASP .Net 1 11-20-2004 07:06 AM
Ado sort error-Ado Sort -Relate, Compute By, or Sort operations cannot be done on column(s) whose key length is unknown or exceeds 10 KB. Navin ASP General 1 09-09-2003 07:16 AM



Advertisments