«

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,q ty,(rsWSclient.Fields.Item("AccessLevel").Value)
'Next
'DataConn.Close: Set DataConn=Nothing
%>

thanks

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,q ty,(rsWSclient.Fields.
Item("AccessLevel").Value)
> 'Next
> 'DataConn.Close: Set DataConn=Nothing
> %>
>
> thanks

--
HTH,
Bob Barrows

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,q ty,(rsWSclient.Fields.
> Item("AccessLevel").Value)
>> 'Next
>> 'DataConn.Close: Set DataConn=Nothing
>> %>
>>
>> thanks
>
> --
> HTH,
> Bob Barrows
>
>

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

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(tex t)
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
>
>

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(tex t)
> 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