Using the ADO Command Object to send an Execute command
Author |
Message |
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. Thanks Joel
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
|
Fri, 02 May 2003 03:00:00 GMT |
|
|
|