Incorrect parameters being received by SQL Server Stored Procedure ...

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_StdConnectionString
'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_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
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.InsertConsignmentRecord
@.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=36528001;
-- 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)() ;
"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_StdConnectionString
> '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_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> 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.InsertConsignmentRecord
> @.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=36528001;
> -- 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
|||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@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.arkansas.net ...[vbcol=seagreen]
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)
> "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
>
|||"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