Prev: Getting out what's in XMLDom object
Next: Error 6 line of code?
From: fniles on 20 May 2010 18:00
I am using VB6, ADO and SQL Server 2005 as the database.

I have a stored procedure like this:
alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
@ID int output,
@Floor varchar(50) = NULL,
@Order varchar(50) = NULL,
@TradeType varchar(10) = NULL,
@ACCOUNT varchar(10) = NULL
AS
insert into HistTradesOrig
([Floor],[Order],TradeType,ACCOUNT)
values
(@Floor,@Order,@TradeType,@ACCOUNT)

When I call that stored procedure from the program, is there any way I can
insert the parameter values NOT in the same order of the parameters orde in
the SP ?
With the below method, I have to do it in the same order, otherwise the
values are not set to the correct columns.

Set m_cmd = New ADODB.Command
Set m_cmd.ActiveConnection = adoCon
m_cmd.CommandType = adCmdStoredProc
m_cmd.CommandText = sCommandText
m_sCommandText = sCommandText
m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
adParamOutput, 12, 0)
m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
adParamInput, 50, "")
m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
adParamInput, 50, "1")
m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
adParamInput, 10, "BUY")
m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
adParamInput, 10, "123")

If I do the following, it will insert "1" to the Floor column instead of to
the Order column, "123" to the Order column instead of Account column, "" to
the TradeType column instead of Floor, and "BUY" to the Account column
instead of TradeType column.
m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
adParamOutput, 12, 0)
m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
adParamInput, 50, "1")
m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
adParamInput, 10, "123")
m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
adParamInput, 50, "")
m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
adParamInput, 10, "BUY")

Thank you


From: Andy on 20 May 2010 22:57
Try separating out the setting of parameter values. Something like this...

Dim P As ADODB.Parameter
Dim m_cmd As ADODB.Command
Dim m_sCommandText As String

Set m_cmd = New ADODB.Command
With m_cmd
Set .ActiveConnection = adoCon
.CommandType = adCmdStoredProc
.CommandText = sCommandText
m_sCommandText = sCommandText

Set P = .CreateParameter("ID", adInteger, adParamOutput, 12)
.Parameters.Append P
Set P = .CreateParameter("Floor", adVarChar, adParamInput, 50)
.Parameters.Append P
Set P = .CreateParameter("ORDER", adVarChar, adParamInput, 50)
.Parameters.Append P
Set P = .CreateParameter("TradeType", adVarChar, adParamInput, 10)
.Parameters.Append P
Set P = .CreateParameter("Account", adVarChar, adParamInput, 10)
.Parameters.Append P

.Parameters("ID").Value = 0
.Parameters("ORDER").Value = "1"
.Parameters("Account").Value = "123"
.Parameters("Floor").Value = ""
.Parameters("TradeType").Value = "BUY"

.Execute
End With

HTH

"fniles" wrote:

> I am using VB6, ADO and SQL Server 2005 as the database.
>
> I have a stored procedure like this:
> alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
> @ID int output,
> @Floor varchar(50) = NULL,
> @Order varchar(50) = NULL,
> @TradeType varchar(10) = NULL,
> @ACCOUNT varchar(10) = NULL
> AS
> insert into HistTradesOrig
> ([Floor],[Order],TradeType,ACCOUNT)
> values
> (@Floor,@Order,@TradeType,@ACCOUNT)
>
> When I call that stored procedure from the program, is there any way I can
> insert the parameter values NOT in the same order of the parameters orde in
> the SP ?
> With the below method, I have to do it in the same order, otherwise the
> values are not set to the correct columns.
>
> Set m_cmd = New ADODB.Command
> Set m_cmd.ActiveConnection = adoCon
> m_cmd.CommandType = adCmdStoredProc
> m_cmd.CommandText = sCommandText
> m_sCommandText = sCommandText
> m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
> adParamOutput, 12, 0)
> m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
> adParamInput, 50, "")
> m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
> adParamInput, 50, "1")
> m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
> adParamInput, 10, "BUY")
> m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
> adParamInput, 10, "123")
>
> If I do the following, it will insert "1" to the Floor column instead of to
> the Order column, "123" to the Order column instead of Account column, "" to
> the TradeType column instead of Floor, and "BUY" to the Account column
> instead of TradeType column.
> m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
> adParamOutput, 12, 0)
> m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
> adParamInput, 50, "1")
> m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
> adParamInput, 10, "123")
> m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
> adParamInput, 50, "")
> m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
> adParamInput, 10, "BUY")
>
> Thank you
>
>
> .
>
From: MikeD on 21 May 2010 10:13

"fniles" <fniles(a)pfmail.com> wrote in message
news:uQpKgfG#KHA.1888(a)TK2MSFTNGP05.phx.gbl...
> I am using VB6, ADO and SQL Server 2005 as the database.
>
> I have a stored procedure like this:
> alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
> @ID int output,
> @Floor varchar(50) = NULL,
> @Order varchar(50) = NULL,
> @TradeType varchar(10) = NULL,
> @ACCOUNT varchar(10) = NULL
> AS
> insert into HistTradesOrig
> ([Floor],[Order],TradeType,ACCOUNT)
> values
> (@Floor,@Order,@TradeType,@ACCOUNT)
>
> When I call that stored procedure from the program, is there any way I can
> insert the parameter values NOT in the same order of the parameters orde
> in the SP ?

>
> If I do the following, it will insert "1" to the Floor column instead of
> to the Order column, "123" to the Order column instead of Account column,
> "" to the TradeType column instead of Floor, and "BUY" to the Account
> column instead of TradeType column.
> m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
> adParamOutput, 12, 0)
> m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
> adParamInput, 50, "1")
> m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
> adParamInput, 10, "123")
> m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
> adParamInput, 50, "")
> m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
> adParamInput, 10, "BUY")
>


Specify that you want to use named parameters and make sure you're actually
using the right names (with named params, you'll get an error otherwise).

Set m_cmd = New ADODB.Command
m_cmd.NamedParameters = True
Set m_cmd.ActiveConnection = adoCon
m_cmd.CommandType = adCmdStoredProc
m_cmd.CommandText = "INSERT_INTO_HistTradesOrigtest"
m_cmd.Parameters.Append m_cmd.CreateParameter("@ID", adInteger,
adParamOutput, 12, 0)
m_cmd.Parameters.Append m_cmd.CreateParameter("@ORDER", adVarChar,
adParamInput, 50, "1")
m_cmd.Parameters.Append m_cmd.CreateParameter("@Account", adVarChar,
adParamInput, 10, "123")
m_cmd.Parameters.Append m_cmd.CreateParameter("@Floor", adVarChar,
adParamInput, 50, "")
m_cmd.Parameters.Append m_cmd.CreateParameter("@TradeType", adVarChar,
adParamInput, 10, "BUY")
m_cmd.Execute


You can also do this:

Set m_cmd = New ADODB.Command
m_cmd.NamedParameters = True
Set m_cmd.ActiveConnection = adoCon
m_cmd.CommandType = adCmdStoredProc
m_cmd.CommandText = "INSERT_INTO_HistTradesOrigtest"
m_cmd.Parameters.Refresh

to retrieve the parameters from the SP. If you do this, you don't have to
explicitly create them. This does require an additional hit on the database
though.

--
Mike


From: fniles on 21 May 2010 12:00
Thanks, that works !

"Andy" <andrewjellis(a)hotmailnospam.com> wrote in message
news:26EAD457-B201-48B7-8A3C-F3FF3B947113(a)microsoft.com...
> Try separating out the setting of parameter values. Something like
> this...
>
> Dim P As ADODB.Parameter
> Dim m_cmd As ADODB.Command
> Dim m_sCommandText As String
>
> Set m_cmd = New ADODB.Command
> With m_cmd
> Set .ActiveConnection = adoCon
> .CommandType = adCmdStoredProc
> .CommandText = sCommandText
> m_sCommandText = sCommandText
>
> Set P = .CreateParameter("ID", adInteger, adParamOutput, 12)
> .Parameters.Append P
> Set P = .CreateParameter("Floor", adVarChar, adParamInput, 50)
> .Parameters.Append P
> Set P = .CreateParameter("ORDER", adVarChar, adParamInput, 50)
> .Parameters.Append P
> Set P = .CreateParameter("TradeType", adVarChar, adParamInput, 10)
> .Parameters.Append P
> Set P = .CreateParameter("Account", adVarChar, adParamInput, 10)
> .Parameters.Append P
>
> .Parameters("ID").Value = 0
> .Parameters("ORDER").Value = "1"
> .Parameters("Account").Value = "123"
> .Parameters("Floor").Value = ""
> .Parameters("TradeType").Value = "BUY"
>
> .Execute
> End With
>
> HTH
>
> "fniles" wrote:
>
>> I am using VB6, ADO and SQL Server 2005 as the database.
>>
>> I have a stored procedure like this:
>> alter PROCEDURE [dbo].[INSERT_INTO_HistTradesOrigtest]
>> @ID int output,
>> @Floor varchar(50) = NULL,
>> @Order varchar(50) = NULL,
>> @TradeType varchar(10) = NULL,
>> @ACCOUNT varchar(10) = NULL
>> AS
>> insert into HistTradesOrig
>> ([Floor],[Order],TradeType,ACCOUNT)
>> values
>> (@Floor,@Order,@TradeType,@ACCOUNT)
>>
>> When I call that stored procedure from the program, is there any way I
>> can
>> insert the parameter values NOT in the same order of the parameters orde
>> in
>> the SP ?
>> With the below method, I have to do it in the same order, otherwise the
>> values are not set to the correct columns.
>>
>> Set m_cmd = New ADODB.Command
>> Set m_cmd.ActiveConnection = adoCon
>> m_cmd.CommandType = adCmdStoredProc
>> m_cmd.CommandText = sCommandText
>> m_sCommandText = sCommandText
>> m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
>> adParamOutput, 12, 0)
>> m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
>> adParamInput, 50, "")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
>> adParamInput, 50, "1")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
>> adParamInput, 10, "BUY")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
>> adParamInput, 10, "123")
>>
>> If I do the following, it will insert "1" to the Floor column instead of
>> to
>> the Order column, "123" to the Order column instead of Account column, ""
>> to
>> the TradeType column instead of Floor, and "BUY" to the Account column
>> instead of TradeType column.
>> m_cmd.Parameters.Append m_cmd.CreateParameter("ID", adInteger,
>> adParamOutput, 12, 0)
>> m_cmd.Parameters.Append m_cmd.CreateParameter("ORDER", adVarChar,
>> adParamInput, 50, "1")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("Account", adVarChar,
>> adParamInput, 10, "123")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("Floor", adVarChar,
>> adParamInput, 50, "")
>> m_cmd.Parameters.Append m_cmd.CreateParameter("TradeType", adVarChar,
>> adParamInput, 10, "BUY")
>>
>> Thank you
>>
>>
>> .
>>


 | 
Pages: 1
Prev: Getting out what's in XMLDom object
Next: Error 6 line of code?