ado properties / parameters

Discussion in 'ASP General' started by shank, Mar 18, 2006.

  1. shank

    shank Guest

    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
    %>
     
    shank, Mar 18, 2006
    #1
    1. Advertising

  2. 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/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"
     
    Bob Barrows [MVP], Mar 18, 2006
    #2
    1. Advertising

  3. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > 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/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
     
    shank, Mar 18, 2006
    #3
  4. 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], Mar 18, 2006
    #4
  5. 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"
     
    Bob Barrows [MVP], Mar 18, 2006
    #5
  6. shank

    shank Guest

    THANKS!!! Works great!

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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"
    >
     
    shank, Mar 18, 2006
    #6
  7. shank

    shank Guest

    >
    > 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
    %>
     
    shank, Mar 19, 2006
    #7
  8. 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"
     
    Bob Barrows [MVP], Mar 19, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    879
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    466
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,320
  4. Navin
    Replies:
    1
    Views:
    705
    Ken Schaefer
    Sep 9, 2003
  5. keyser soze
    Replies:
    7
    Views:
    217
    keyser soze
    Jun 15, 2007
Loading...

Share This Page