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
Showing posts with label vb6. Show all posts
Showing posts with label vb6. Show all posts
Wednesday, March 21, 2012
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@.ar
kansas.net...
> "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)[vbcol=seagreen]
> "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>
-ralphsql
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@.ar
kansas.net...
> "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)[vbcol=seagreen]
> "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>
-ralphsql
Monday, March 12, 2012
Inconsistent stored procedure syntax error
We have a stored procedure that is being called from a vb6 application. The exact line of code is below.
mats_Reports_CSR_OB_Hourly_Percent('11/1/2006 4:51:27 PM','12/1/2006 4:51:27 PM')when executing this in query analyzer it will always fail due to the parenthesis. However when this same line of code is passed to sql server in the vb6 application it usually works. There are times it fails with a syntax error message similar to:
Incorrect syntax near '{
I fixed the problem last time by removing the parenthesis in the vb6 code. However I can now put the parenthesis back into the code and it works again now.
Prior to my recent intervention the code was not being changed as it will work for days and then randomly stop working. Then without any change start working by itself again.
What is causing this behavior? How do I make it always work?
Calling a sql stored procedure is not like calling a vb function. Since you're calling a stored procedure, consider using Parameters collection to pass into desired inputs.http://windowssdk.msdn.microsoft.com/en-gb/library/ms675869.aspx|||
Thank you for your response.
Your response seems to imply there is a bug in the way ODBC handles stored procedure calls?
This application was built over 3 years ago. It is not a realistic solution to switch connections from ODBC to ADO, as it would require the entire application to be recoded.
I have spent the better part of the day yesterday doing research on this problem and it seems it has something to do with ODBC escape sequences. What conditions would change how sql server parses a statement? There has to be something the server is automatically deciding on using that changes, because the same code works and then doesn't work (running against the same data).
|||No. What I hinted at is that you shouldn't call a proc like so:myproc(para1,para2)
Instead you should do:
cmd.CommandText="myproc para1, para2"
or create a parameters collection for your parameters.|||I already know not using the parenthesis will work. I was looking for why it's happening.|||There is a simple answer: Because its the syntax of calling stored procedures that way. Did you ever ask yourself why a vb function is called like SomeFunction('Something') and not like SomeFunction*'Something'* :-) Its simply the syntax that is specified for calling the procedure.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
OK, your missing what I am getting at. I will make an analogy:
Say your using your keyboard, everytime you press the the 'k' key you'd expect the letter k every time you pressed it.
Now if sometimes when you pressed the 'k' key you got the letter q you'd pull the keyboard and replace it with one that works, or you'd send the keyboard in for repair.
Now what I am describing is the same way. If you call a function using the syntax I already described above you'd expect it to work every time you used it. What I am getting though is sometimes the same syntax is sucessfully running, other times it is giving a syntax error. I am asking why is this occuring? What can you think of that would change to cause this behavior?
|||Where is the error thrown ? Could it be that this is some SQL Server error (not actually in the frontend) which is caused by some malformed composed dynamic SQL in the backend, producing error messages like this ? Are you able to start profiler on the server to see what happends behind the scenes and what comamdns are fired against the database.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)