Calling insertion stored procedure using ADODB.Command

[fniles]

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

[Andy]

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

Post by fniles
I am using VB6, ADO and SQL Server 2005 as the database.
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
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
.

[fniles]

Thanks, that works !

Post by Andy
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

Post by fniles
I am using VB6, ADO and SQL Server 2005 as the database.
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
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
.

[MikeD]

Post by fniles
I am using VB6, ADO and SQL Server 2005 as the database.
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
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.