type mismatch

[shank]

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

 

[Bob Barrows]

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]

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]

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

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

 

[shank]

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

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

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]

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.

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

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

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