|
Home > Archive > IIS ASP > March 2006 > ado properties / parameters
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ado properties / parameters
|
|
| shank 2006-03-19, 12:02 pm |
| 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,var
TextArea
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] 2006-03-19, 12:02 pm |
| 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/e...adooverview.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"
| |
| shank 2006-03-19, 12:02 pm |
|
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23tPOTgrSGHA.4300@TK2MSFTNGP14.phx.gbl...
> shank wrote:
>
> What is the size of the orderno field in your database?
>
>
> 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/e...adooverview.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)?
>
>
> 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 " & _
>
>
>
> I would be closing this recordset here:
>
> dim DoInsert
> If rs.eof then DoInsert = true
> rs.close:set rs = nothing
>
> If DoInsert then
>
>
> 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)
>
>
>
> 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)
>
>
>
> --
> 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] 2006-03-19, 12:02 pm |
| 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"
| |
| Bob Barrows [MVP] 2006-03-19, 12:02 pm |
| 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"
| |
| shank 2006-03-19, 12:02 pm |
| THANKS!!! Works great!
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uz9XVFsSGHA.6084@TK2MSFTNGP14.phx.gbl...
> shank wrote:
>
> Really? Then you should be using a stored procedure for this.
>
> Also, you should be providing better DDL:
> http://www.aspfaq.com/5006
>
>
> 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).
>
> 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 2006-03-19, 12:02 pm |
| >
> 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] 2006-03-19, 12:02 pm |
| 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"
|
|
|
|
|