type mismatch

Discussion in 'ASP General' started by shank, Apr 23, 2009.

  1. shank

    shank Guest

    The below worked fine for long time. I upgraded from SQL2000 to SQL2005 and
    changed servers. Now having problems. Trying to troubleshoot, I commented
    out the lines and I wrote at variables to screen. They are there as
    expected.

    Error: Response object error 'ASP 0106 : 80020005'
    Type Mismatch
    /bulkorderinsert.asp, line 0
    An unhandled data type was encountered.

    Upgrading to SQL2005 I guess the schema must be referenced or objects can't
    be found.
    I had: DataConn.stp_WSD_InsertBackOrder
    And changed it to: DataConn.admin.stp_WSD_InsertBackOrder

    Is that incorrect syntax?


    <%
    Dim DataConn,arrName,orderno,qty,varTextArea

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

    varCleanText = Replace(Replace(Request.Form("TextArea")," ",""),Chr(13) &
    Chr(10) & Chr(13) & Chr(10),vbCrLf)
    varTextArea =Split(varCleanText & ",", vbCrLf)

    'For i = 0 To UBound(varTextArea)
    ' arrName = Split(varTextArea(i),",")
    ' orderno=arrName(0)
    ' qty=arrName(1)
    ' 'validate data
    ' DataConn.admin.stp_WSD_InsertBackOrder
    (rsWSclient.Fields.Item("AcctNo").Value),orderno,qty,(rsWSclient.Fields.Item("AccessLevel").Value)
    'Next
    'DataConn.Close: Set DataConn=Nothing
    %>

    thanks
     
    shank, Apr 23, 2009
    #1
    1. Advertising

  2. shank

    Bob Barrows Guest

    No, you cannot reference the schema using the
    procedure-as-connection-method technique. What you can do is assign the
    admin schema as the default schema for the user account being used in
    the connection string.

    You may also be able to use synonyms in your database to get around
    this. Look up "synonym" in BOL for more information.

    shank wrote:
    > The below worked fine for long time. I upgraded from SQL2000 to
    > SQL2005 and changed servers. Now having problems. Trying to
    > troubleshoot, I commented out the lines and I wrote at variables to
    > screen. They are there as expected.
    >
    > Error: Response object error 'ASP 0106 : 80020005'
    > Type Mismatch
    > /bulkorderinsert.asp, line 0
    > An unhandled data type was encountered.
    >
    > Upgrading to SQL2005 I guess the schema must be referenced or objects
    > can't be found.
    > I had: DataConn.stp_WSD_InsertBackOrder
    > And changed it to: DataConn.admin.stp_WSD_InsertBackOrder
    >
    > Is that incorrect syntax?
    >
    >
    > <%
    > Dim DataConn,arrName,orderno,qty,varTextArea
    >
    > Set DataConn = Server.CreateObject("ADODB.Connection")
    > DataConn.Open MM_SQL_STRING
    >
    > varCleanText = Replace(Replace(Request.Form("TextArea"),"
    > ",""),Chr(13) & Chr(10) & Chr(13) & Chr(10),vbCrLf)
    > varTextArea =Split(varCleanText & ",", vbCrLf)
    >
    > 'For i = 0 To UBound(varTextArea)
    > ' arrName = Split(varTextArea(i),",")
    > ' orderno=arrName(0)
    > ' qty=arrName(1)
    > ' 'validate data
    > ' DataConn.admin.stp_WSD_InsertBackOrder
    >

    (rsWSclient.Fields.Item("AcctNo").Value),orderno,qty,(rsWSclient.Fields.
    Item("AccessLevel").Value)
    > 'Next
    > 'DataConn.Close: Set DataConn=Nothing
    > %>
    >
    > thanks


    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Apr 23, 2009
    #2
    1. Advertising

  3. shank

    shank Guest

    Thanks! That got me to the next error: The precision is invalid
    I never got his before either. I have [AcctNo] numeric(18,0) and [Qty]
    numeric(18,0).
    All other fields are varchar except for [ID] numeric(18,0) which auto seed.
    I can run the Store Procedure in a query with the objects I'm importing
    without a problem.

    What could be generating error: The precision is invalid?

    thanks
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    - - - - - - - - - -

    "Bob Barrows" <> wrote in message
    news:...
    > No, you cannot reference the schema using the
    > procedure-as-connection-method technique. What you can do is assign the
    > admin schema as the default schema for the user account being used in
    > the connection string.
    >
    > You may also be able to use synonyms in your database to get around
    > this. Look up "synonym" in BOL for more information.
    >
    > shank wrote:
    >> The below worked fine for long time. I upgraded from SQL2000 to
    >> SQL2005 and changed servers. Now having problems. Trying to
    >> troubleshoot, I commented out the lines and I wrote at variables to
    >> screen. They are there as expected.
    >>
    >> Error: Response object error 'ASP 0106 : 80020005'
    >> Type Mismatch
    >> /bulkorderinsert.asp, line 0
    >> An unhandled data type was encountered.
    >>
    >> Upgrading to SQL2005 I guess the schema must be referenced or objects
    >> can't be found.
    >> I had: DataConn.stp_WSD_InsertBackOrder
    >> And changed it to: DataConn.admin.stp_WSD_InsertBackOrder
    >>
    >> Is that incorrect syntax?
    >>
    >>
    >> <%
    >> Dim DataConn,arrName,orderno,qty,varTextArea
    >>
    >> Set DataConn = Server.CreateObject("ADODB.Connection")
    >> DataConn.Open MM_SQL_STRING
    >>
    >> varCleanText = Replace(Replace(Request.Form("TextArea"),"
    >> ",""),Chr(13) & Chr(10) & Chr(13) & Chr(10),vbCrLf)
    >> varTextArea =Split(varCleanText & ",", vbCrLf)
    >>
    >> 'For i = 0 To UBound(varTextArea)
    >> ' arrName = Split(varTextArea(i),",")
    >> ' orderno=arrName(0)
    >> ' qty=arrName(1)
    >> ' 'validate data
    >> ' DataConn.admin.stp_WSD_InsertBackOrder
    >>

    > (rsWSclient.Fields.Item("AcctNo").Value),orderno,qty,(rsWSclient.Fields.
    > Item("AccessLevel").Value)
    >> 'Next
    >> 'DataConn.Close: Set DataConn=Nothing
    >> %>
    >>
    >> thanks

    >
    > --
    > HTH,
    > Bob Barrows
    >
    >
     
    shank, Apr 23, 2009
    #3
  4. shank

    Bob Barrows Guest

    I need more information. Are you performing any type casts on the data
    you are passing to the stored procedure?

    shank wrote:
    > Thanks! That got me to the next error: The precision is invalid
    > I never got his before either. I have [AcctNo] numeric(18,0) and [Qty]
    > numeric(18,0).
    > All other fields are varchar except for [ID] numeric(18,0) which auto
    > seed. I can run the Store Procedure in a query with the objects I'm
    > importing without a problem.
    >
    > What could be generating error: The precision is invalid?
    >
    > thanks
    > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    > - - -
    > - - - - - - - - - -
    >


    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Apr 23, 2009
    #4
  5. shank

    shank Guest

    Nope. This is the SP...

    CREATE PROCEDURE [admin].[stp_WSD_InsertBackOrder]
    @AffNo varchar(20),
    @OrderNo varchar(20),
    @Qty numeric(8,2),
    @AccessLevel varchar(10)
    AS
    BEGIN
    SET NOCOUNT ON
    UPDATE admin.WSDBulkOrder SET Qty = Qty + @Qty
    WHERE [AffNo] = @AffNo AND [OrderNo] = @OrderNo
    IF @@ROWCOUNT = 0
    INSERT INTO admin.WSDBulkOrder (AffNo,OrderNo,Qty,AccessLevel)
    VALUES (@AffNo,@OrderNo,@Qty,@AccessLevel)
    END

    Data sample being passed...
    AcctNo (number),orderno(text),qty(number),AccessLevel(text)
    AcctNo: 100385
    OrderNo, Qty are...
    C101,4
    C102,3
    C103,5
    C104,7
    C105,1
    C106,2
    C107,3
    C108,5
    C109,1
    AccessLevel: AB

    thanks

    "Bob Barrows" <> wrote in message
    news:...
    >I need more information. Are you performing any type casts on the data
    > you are passing to the stored procedure?
    >
    > shank wrote:
    >> Thanks! That got me to the next error: The precision is invalid
    >> I never got his before either. I have [AcctNo] numeric(18,0) and [Qty]
    >> numeric(18,0).
    >> All other fields are varchar except for [ID] numeric(18,0) which auto
    >> seed. I can run the Store Procedure in a query with the objects I'm
    >> importing without a problem.
    >>
    >> What could be generating error: The precision is invalid?
    >>
    >> thanks
    >> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    >> - - -
    >> - - - - - - - - - -
    >>

    >
    > --
    > HTH,
    > Bob Barrows
    >
    >
     
    shank, Apr 23, 2009
    #5
  6. shank

    Bob Barrows Guest

    And the code calling the stored procedure ...?

    I would suggest being explicit when passing the qty values - use CSng or
    CDbl to make sure they are treated as numbers.

    shank wrote:
    > Nope. This is the SP...
    >
    > CREATE PROCEDURE [admin].[stp_WSD_InsertBackOrder]
    > @AffNo varchar(20),
    > @OrderNo varchar(20),
    > @Qty numeric(8,2),
    > @AccessLevel varchar(10)
    > AS
    > BEGIN
    > SET NOCOUNT ON
    > UPDATE admin.WSDBulkOrder SET Qty = Qty + @Qty
    > WHERE [AffNo] = @AffNo AND [OrderNo] = @OrderNo
    > IF @@ROWCOUNT = 0
    > INSERT INTO admin.WSDBulkOrder (AffNo,OrderNo,Qty,AccessLevel)
    > VALUES (@AffNo,@OrderNo,@Qty,@AccessLevel)
    > END
    >
    > Data sample being passed...
    > AcctNo (number),orderno(text),qty(number),AccessLevel(text)
    > AcctNo: 100385
    > OrderNo, Qty are...
    > C101,4
    > C102,3
    > C103,5
    > C104,7
    > C105,1
    > C106,2
    > C107,3
    > C108,5
    > C109,1
    > AccessLevel: AB
    >
    > thanks
    >
    > "Bob Barrows" <> wrote in message
    > news:...
    >> I need more information. Are you performing any type casts on the
    >> data you are passing to the stored procedure?
    >>
    >> shank wrote:
    >>> Thanks! That got me to the next error: The precision is invalid
    >>> I never got his before either. I have [AcctNo] numeric(18,0) and
    >>> [Qty] numeric(18,0).
    >>> All other fields are varchar except for [ID] numeric(18,0) which
    >>> auto seed. I can run the Store Procedure in a query with the
    >>> objects I'm importing without a problem.
    >>>
    >>> What could be generating error: The precision is invalid?
    >>>
    >>> thanks
    >>> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    >>> -
    >>> - - -
    >>> - - - - - - - - - -
    >>>

    >>
    >> --
    >> HTH,
    >> Bob Barrows


    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Apr 23, 2009
    #6
    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. data type mismatch error

    , Aug 10, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    654
    Karl Seguin
    Aug 10, 2005
  2. type mismatch error

    , Aug 12, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    3,410
    Kevin Spencer
    Aug 12, 2005
  3. Artie Gold

    Re: type mismatch

    Artie Gold, Feb 28, 2004, in forum: C Programming
    Replies:
    0
    Views:
    413
    Artie Gold
    Feb 28, 2004
  4. Harry Pehkonen

    win32com, string array --> Type mismatch?

    Harry Pehkonen, Aug 8, 2003, in forum: Python
    Replies:
    2
    Views:
    1,924
    Harry Pehkonen
    Aug 11, 2003
  5. programmingzeal
    Replies:
    0
    Views:
    1,170
    programmingzeal
    May 6, 2012
Loading...

Share This Page