VB & Database, Using the ADO Command Object to send an Execute command

Joel Isaacso
#1 / 5

Using the ADO Command Object to send an Execute command

I have set up a stored procedure to add new items to a table. In my vb
program, I set up an ADO command and a command string, filling in the
variables, which were set up in the stored procedure as parameters.

When I run the execute command, I get the message
Syntax error or access violation
It seems that the cause of this is that the execute command is adding braces
to the text I send it, as follows:
{ call Exec sp_addnewadv 402563,89,1,1, 'test adv 11',402563, 'isaacson' }

I send it a text field containing everything except the braces.
If I copy the text data to Sql Server Query Analyzer, and remove the braces,
the command runs perfect.

The code in my vb program for this action is:
'Add the new advertiser to the advertiser table
sExecText = "Exec sp_addnewadv "
sExecText = sExecText & lTempAdvNo & "," & mskMkt2(0) & "," &
mskInd2(0) & ","
sExecText = sExecText & mskMedia2(0) & ", '" & Trim(txtName2(0)) &
"'," & lTempAdvNo & ", '"
sExecText = sExecText & gsWhoIsIt & "'"

Set adocmdXray = New ADODB.Command
With adocmdXray
.ActiveConnection = adoXraycnn
.CommandText = sExecText
.CommandType = adCmdStoredProc
End With

If adorsAdv.State = 1 Then adorsAdv.Close
adocmdXray.Execute glRecordsAffected

Does anyone know where the braces are coming from, and how to get rid of
them?

Thanks

Joel

Mon, 28 Apr 2003 03:00:00 GMT

Buddy Robbin
#2 / 5

Using the ADO Command Object to send an Execute command

One thing you might want to change is .CommandType = adCmdText since you are
supplying sql instead of stored procedure name.

If you want to use adCmdStoredProc, you would do the following
With adocmdXray
.ActiveConnection = adoXraycnn
.CommandText = "sp_addnewadv"
.Parameters.Append

.Parameters.Append

' or you could cheat with the .Parameters.Refresh and then add
the parameter values
.CommandType = adCmdStoredProc
End With

--
Buddy Robbins, MCSD, MCSE, MCDBA
Software Engineer
AUTOVIA Corp.

Quote:

>I have set up a stored procedure to add new items to a table. In my vb
>program, I set up an ADO command and a command string, filling in the
>variables, which were set up in the stored procedure as parameters.

>When I run the execute command, I get the message
>Syntax error or access violation
>It seems that the cause of this is that the execute command is adding
braces
>to the text I send it, as follows:
>{ call Exec sp_addnewadv 402563,89,1,1, 'test adv 11',402563, 'isaacson' }

>I send it a text field containing everything except the braces.
>If I copy the text data to Sql Server Query Analyzer, and remove the
braces,
>the command runs perfect.

>The code in my vb program for this action is:
> 'Add the new advertiser to the advertiser table
> sExecText = "Exec sp_addnewadv "
> sExecText = sExecText & lTempAdvNo & "," & mskMkt2(0) & "," &
>mskInd2(0) & ","
> sExecText = sExecText & mskMedia2(0) & ", '" & Trim(txtName2(0)) &
>"'," & lTempAdvNo & ", '"
> sExecText = sExecText & gsWhoIsIt & "'"

> Set adocmdXray = New ADODB.Command
> With adocmdXray
> .ActiveConnection = adoXraycnn
> .CommandText = sExecText
> .CommandType = adCmdStoredProc
> End With

> If adorsAdv.State = 1 Then adorsAdv.Close
> adocmdXray.Execute glRecordsAffected

>Does anyone know where the braces are coming from, and how to get rid of
>them?

>Thanks

>Joel

Mon, 28 Apr 2003 03:00:00 GMT

Scott Shel
#3 / 5

Using the ADO Command Object to send an Execute command

1. One thing you should do is SET adocmdXray.ActiveConnection = adoXraycnn.
If you omit the Set keyword, VB uses the object's default properties. In
this case, you are assigning a connection string to the ActiveConnection
property, thereby implicitly creating a new Connection object rather than
using the existing one. Probably doesn't matter, but might not be what you
intended.

2. I suspect the syntax error you are getting is because you have the word
EXEC in front of the stored procedure's name. Remove that and try running
it.

3. The braces are put there automatically by ADO. You should leave them
there. The provider expects to see the command in that format, but you
should continue setting up command strings the usual way and let ADO do its
thing.

4. Your CommandType property should remain adCmdStoredProc. You can set
the CommandText to something of the form "sp_whatever(value1, value2)" and
it works fine. In other words, you don't need to set up parameters by using
CreateParameter in order to use adCmdStoredProc. Does that make sense?

5. Your statement:

"If adorsAdv.State = 1 Then adorsAdv.Close"

should ideally be changed to:

"If adorsAdv.State AND adStateOpen Then adorsAdv.Close" 'or just AND 1

because the State property is a bitmap, and can be both open and executing,
for example, and the bitmap would be 0101 (adStateExecuting = 4, adStateOpen
= 1). Though in your case it most likely didn't cause any harm. It might
cause trouble if you execute commands asynchronously however.

--
Scott Shell
Please post replies both to the newsgroup and to my email address,

Quote:

> One thing you might want to change is .CommandType = adCmdText since you
are
> supplying sql instead of stored procedure name.

> If you want to use adCmdStoredProc, you would do the following
> With adocmdXray
> .ActiveConnection = adoXraycnn
> .CommandText = "sp_addnewadv"
> .Parameters.Append

> .Parameters.Append

> ' or you could cheat with the .Parameters.Refresh and then add
> the parameter values
> .CommandType = adCmdStoredProc
> End With

> --
> Buddy Robbins, MCSD, MCSE, MCDBA
> Software Engineer
> AUTOVIA Corp.

> >I have set up a stored procedure to add new items to a table. In my vb
> >program, I set up an ADO command and a command string, filling in the
> >variables, which were set up in the stored procedure as parameters.

> >When I run the execute command, I get the message
> >Syntax error or access violation
> >It seems that the cause of this is that the execute command is adding
> braces
> >to the text I send it, as follows:
> >{ call Exec sp_addnewadv 402563,89,1,1, 'test adv 11',402563,
'isaacson' }

> >I send it a text field containing everything except the braces.
> >If I copy the text data to Sql Server Query Analyzer, and remove the
> braces,
> >the command runs perfect.

> >The code in my vb program for this action is:
> > 'Add the new advertiser to the advertiser table
> > sExecText = "Exec sp_addnewadv "
> > sExecText = sExecText & lTempAdvNo & "," & mskMkt2(0) & "," &
> >mskInd2(0) & ","
> > sExecText = sExecText & mskMedia2(0) & ", '" & Trim(txtName2(0))
&
> >"'," & lTempAdvNo & ", '"
> > sExecText = sExecText & gsWhoIsIt & "'"

> > Set adocmdXray = New ADODB.Command
> > With adocmdXray
> > .ActiveConnection = adoXraycnn
> > .CommandText = sExecText
> > .CommandType = adCmdStoredProc
> > End With

> > If adorsAdv.State = 1 Then adorsAdv.Close
> > adocmdXray.Execute glRecordsAffected

> >Does anyone know where the braces are coming from, and how to get rid of
> >them?

> >Thanks

> >Joel

Mon, 28 Apr 2003 03:00:00 GMT

Vorte
#4 / 5

Using the ADO Command Object to send an Execute command

Scott regarding your advice on the SET command I think you are mistaken!
Checking Sql servers active connections without using the SET it still
remains
1 which is the original startup connection.

Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
'.......................................' Set the connection
Cmd.CommandText = "MaterialList"
'...................................' Set the stored procedure
Cmd.CommandType = adCmdStoredProc
'...............................' Set the type of command

Set the parameter

'.......................' Set the parameter

I can run the above code unlimited through my app and Active connection is
still
only one!!

Bear in mind that Conn is Public which I believe is correct!

Are you confusing the setting of the first connection with
setting the command.activeconnection?

Option Explicit
Public Conn As ADODB.Connection
Dim Cmd As New ADODB.Command

Sub Connect()

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=ZodiacSQL;Data Source=Mike"
Conn.Open

End Sub

Are you talking about another instance perhaps? : )

Regards

VOrtex

Quote:

> 1. One thing you should do is SET adocmdXray.ActiveConnection =
adoXraycnn.
> If you omit the Set keyword, VB uses the object's default properties. In
> this case, you are assigning a connection string to the ActiveConnection
> property, thereby implicitly creating a new Connection object rather than
> using the existing one. Probably doesn't matter, but might not be what
you
> intended.

> 2. I suspect the syntax error you are getting is because you have the
word
> EXEC in front of the stored procedure's name. Remove that and try running
> it.

> 3. The braces are put there automatically by ADO. You should leave them
> there. The provider expects to see the command in that format, but you
> should continue setting up command strings the usual way and let ADO do
its
> thing.

> 4. Your CommandType property should remain adCmdStoredProc. You can set
> the CommandText to something of the form "sp_whatever(value1, value2)" and
> it works fine. In other words, you don't need to set up parameters by
using
> CreateParameter in order to use adCmdStoredProc. Does that make sense?

> 5. Your statement:

> "If adorsAdv.State = 1 Then adorsAdv.Close"

> should ideally be changed to:

> "If adorsAdv.State AND adStateOpen Then adorsAdv.Close" 'or just AND 1

> because the State property is a bitmap, and can be both open and
executing,
> for example, and the bitmap would be 0101 (adStateExecuting = 4,
adStateOpen
> = 1). Though in your case it most likely didn't cause any harm. It might
> cause trouble if you execute commands asynchronously however.

> --
> Scott Shell
> Please post replies both to the newsgroup and to my email address,

> > One thing you might want to change is .CommandType = adCmdText since you
> are
> > supplying sql instead of stored procedure name.

> > If you want to use adCmdStoredProc, you would do the following
> > With adocmdXray
> > .ActiveConnection = adoXraycnn
> > .CommandText = "sp_addnewadv"
> > .Parameters.Append

> > .Parameters.Append

> > ' or you could cheat with the .Parameters.Refresh and then
add
> > the parameter values
> > .CommandType = adCmdStoredProc
> > End With

> > --
> > Buddy Robbins, MCSD, MCSE, MCDBA
> > Software Engineer
> > AUTOVIA Corp.

> > >I have set up a stored procedure to add new items to a table. In my vb
> > >program, I set up an ADO command and a command string, filling in the
> > >variables, which were set up in the stored procedure as parameters.

> > >When I run the execute command, I get the message
> > >Syntax error or access violation
> > >It seems that the cause of this is that the execute command is adding
> > braces
> > >to the text I send it, as follows:
> > >{ call Exec sp_addnewadv 402563,89,1,1, 'test adv 11',402563,
> 'isaacson' }

> > >I send it a text field containing everything except the braces.
> > >If I copy the text data to Sql Server Query Analyzer, and remove the
> > braces,
> > >the command runs perfect.

> > >The code in my vb program for this action is:
> > > 'Add the new advertiser to the advertiser table
> > > sExecText = "Exec sp_addnewadv "
> > > sExecText = sExecText & lTempAdvNo & "," & mskMkt2(0) & "," &
> > >mskInd2(0) & ","
> > > sExecText = sExecText & mskMedia2(0) & ", '" &
Trim(txtName2(0))
> &
> > >"'," & lTempAdvNo & ", '"
> > > sExecText = sExecText & gsWhoIsIt & "'"

> > > Set adocmdXray = New ADODB.Command
> > > With adocmdXray
> > > .ActiveConnection = adoXraycnn
> > > .CommandText = sExecText
> > > .CommandType = adCmdStoredProc
> > > End With

> > > If adorsAdv.State = 1 Then adorsAdv.Close
> > > adocmdXray.Execute glRecordsAffected

> > >Does anyone know where the braces are coming from, and how to get rid
of
> > >them?

> > >Thanks

> > >Joel

Fri, 02 May 2003 03:00:00 GMT

Joel Isaacso
#5 / 5

Using the ADO Command Object to send an Execute command

Buddy,

I didn't see my response to you so let me repeat it.
Setting the .commandtype to adcmdtext worked.