Home > Archive > MS SQL Server ODBC > October 2006 > Incorrect parameters being received by SQL Server Stored Procedure ...









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 Incorrect parameters being received by SQL Server Stored Procedure ...
Vipul Pathak

2006-10-24, 6:40 pm

Hi Everybuddy,

I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).

I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.

[VB Code]
-----------------------------------------------------
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command

cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionStr
ing

'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)

cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)

cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithI
d))

cmdBuilty.Name = " InsertConsignmentRec
ord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = " InsertConsignmentRec
ord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45

Set rsBuilty = cmdBuilty.Execute()

'nRetVal = cmdBuilty.Parameters("@RETURN_VALUE")

Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
-----------------------------------------------------



[Stored Procedure Code]
-----------------------------------------------------
CREATE PROCEDURE dbo. InsertConsignmentRec
ord
@nLR_No As Int,
@dtLR_Date As DateTime,
@nDestinationId As Int,
@nConsignorId As Int,
@nConsigneeId As Int,
@nMBT As Int,
@nMST As Int,
@nLH_FL As Int,
@nCBB As Int,
@nSCBB As Int,
@nMilkCaret As Int,
@nPOP As Int,
@dtInv_Date As DateTime,
@szInvoiceId As VarChar(21),
@szIsCancelled As VarChar(1),
@nInvoiceType As TinyInt,
@nConsignmentType As Int,
@szClubbedId As VarChar(21)
AS

-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=3652
8001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;

Select @nInvoiceType As "InvoiceType", @nMBT As "MBT", @nMST As "MST",
@nLH_FL As "LH", @nCBB As "CBB", @nSCBB As "SCBB", @nConsignmentType As
"ConsignmentType", @szInvoiceId As "InvoiceId", @szClubbedId As "ClubbedId"

-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @nLR_No,@dtLR_Date, @nDestinationId, @nConsignorId, @nConsigneeId,
-- @nMBT, @nMST, @nCBB, @nLH_FL, @nSCBB, @nMilkCaret, @nPOP,
-- @szInvoiceId, @dtInv_Date, @szIsCancelled,
-- @nInvoiceType, @nConsignmentType, @szClubbedId)

Return 0
-----------------------------------------------------

If any one can trap some problem here, that would be great ...

Thanks,

*(Vipul)() ;



Ralph

2006-10-24, 6:40 pm


"Vipul Pathak" <vpathak@impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
>
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
>
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
>
> [VB Code]
> -----------------------------------------------------
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
>
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionStr
ing
>
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
>
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
>
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithI
d))
>
> cmdBuilty.Name = " InsertConsignmentRec
ord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = " InsertConsignmentRec
ord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
>
> Set rsBuilty = cmdBuilty.Execute()
>
> 'nRetVal = cmdBuilty.Parameters("@RETURN_VALUE")
>
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> -----------------------------------------------------
>
>
>
> [Stored Procedure Code]
> -----------------------------------------------------
> CREATE PROCEDURE dbo. InsertConsignmentRec
ord
> @nLR_No As Int,
> @dtLR_Date As DateTime,
> @nDestinationId As Int,
> @nConsignorId As Int,
> @nConsigneeId As Int,
> @nMBT As Int,
> @nMST As Int,
> @nLH_FL As Int,
> @nCBB As Int,
> @nSCBB As Int,
> @nMilkCaret As Int,
> @nPOP As Int,
> @dtInv_Date As DateTime,
> @szInvoiceId As VarChar(21),
> @szIsCancelled As VarChar(1),
> @nInvoiceType As TinyInt,
> @nConsignmentType As Int,
> @szClubbedId As VarChar(21)
> AS
>
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=3652
8001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
>
> Select @nInvoiceType As "InvoiceType", @nMBT As "MBT", @nMST As "MST",
> @nLH_FL As "LH", @nCBB As "CBB", @nSCBB As "SCBB", @nConsignmentType As
> "ConsignmentType", @szInvoiceId As "InvoiceId", @szClubbedId As
"ClubbedId"
>
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @nLR_No,@dtLR_Date, @nDestinationId, @nConsignorId, @nConsigneeId,
> -- @nMBT, @nMST, @nCBB, @nLH_FL, @nSCBB, @nMilkCaret, @nPOP,
> -- @szInvoiceId, @dtInv_Date, @szIsCancelled,
> -- @nInvoiceType, @nConsignmentType, @szClubbedId)
>
> Return 0
> -----------------------------------------------------
>
> If any one can trap some problem here, that would be great ...
>
> Thanks,
>
> *(Vipul)() ;
>

Best guess...

There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.

-ralph


Vipul Pathak

2006-10-24, 6:40 pm

Thanks a lot Ralph,

You are damn correct in one shot. It worked in first time after the
suggested change.

BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?

Thanks for your help ...

*(Vipul)() ;



"Ralph" < nt_consulting64@yaho
o.com> wrote in message
news:7sGdnbeE3_xPXLr
YnZ2dnUVZ_qudnZ2d@ar
kansas.net...
>
> "Vipul Pathak" <vpathak@impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@TK2MSFTNGP03.phx.gbl...
are[color=darkred]
X=12,[color=darkred]

code[color=darkred]
cmdBuilty.CreateParameter("@RETURN_VALUE",[color=darkred]
> cmdBuilty.CreateParameter("@nDestinationId",
cmdBuilty.CreateParameter("@nConsignorId",[color=darkred]
cmdBuilty.CreateParameter("@nConsigneeId",[color=darkred]
4,[color=darkred]
cmdBuilty.CreateParameter("@nInvoiceType",[color=darkred]
> cmdBuilty.CreateParameter("@szIsCancelled",
cmdBuilty.CreateParameter("@szInvoiceId",[color=darkred]
cmdBuilty.CreateParameter("@szClubbedId",[color=darkred]
rsBuilty. Fields(qq)[color=dar
kred]
> "ClubbedId"
>
> Best guess...
>
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
>
> -ralph
>
>


Ralph

2006-10-24, 6:40 pm


"Vipul Pathak" <vpathak@impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
>
> You are damn correct in one shot. It worked in first time after the
> suggested change.
>
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
>
> Thanks for your help ...
>
> *(Vipul)() ;
>
<snipped>

Essentially - yes.

That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>

Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>

-ralph


Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2009 droptable.com