.execute vs .open

[ashly]

Dear all,

May I know what is the different using MainDB.execute SQLStr and rs.Open SQLStr? Any pro & con using each other?


Thanks.

[RunsWithScissors]

You might tend to use Execute to, well, EXECUTE and action query that does not return any record, for example, an INSERT.

You would tend to use rs.Open to open a recordset and iterate through the records.

Depending upon the technolgy you are asking about (ADODB or DAO?), there is some overlap.

With ADODB, you could use either method for either purpose. However, I believe INSERTS and UPDATES are performed more efficiently using Execute. You can do them using rs.Open "INSERT INTO . . . Etc" but it is often easier and more efficient to use a command. You can look up the proper sytax in the product documentation (I am rusty on the use of Command in ADO, and I tend to AVOID DAO altogether . . .).

That said, it would be very helpful if you could tell us:

A. What database are you using (Access? SQL Server?)

B. What technology are you using? (ADODB, DAO?)

C. If you ARE using DAO, STOP it , and move to ADODB.

;-)

I wil try to check this thread tomorrow. I am actually pretty good with ADODB, but haven't used it in a while, since migrating to .NET.

[ashly]

You might tend to use Execute to, well, EXECUTE and action query that does not return any record, for example, an INSERT.

You would tend to use rs.Open to open a recordset and iterate through the records.

Depending upon the technolgy you are asking about (ADODB or DAO?), there is some overlap.

With ADODB, you could use either method for either purpose. However, I believe INSERTS and UPDATES are performed more efficiently using Execute. You can do them using rs.Open "INSERT INTO . . . Etc" but it is often easier and more efficient to use a command. You can look up the proper sytax in the product documentation (I am rusty on the use of Command in ADO, and I tend to AVOID DAO altogether . . .).

That said, it would be very helpful if you could tell us:

A. What database are you using (Access? SQL Server?)

B. What technology are you using? (ADODB, DAO?)

C. If you ARE using DAO, STOP it , and move to ADODB.

;-)

I wil try to check this thread tomorrow. I am actually pretty good with ADODB, but haven't used it in a while, since migrating to .NET.

So means if I only want insert or update a record, prefer use EXECUTE. If want retrieve records, use OPEN, am I right?

A. Mainly SQL Server now.
B. ADODB

[si_the_geek]

So means if I only want insert or update a record, prefer use EXECUTE. If want retrieve records, use OPEN, am I right?

Correct.

[RunsWithScissors]

Since you indicate that you are using primarilty SQLServer, the other area where ADODB Commands come into play is in the execution of Stored Procedures which require Parameters.

You can use Parameters with In-line SQL text as well (at least, in Access you can-haven't had to do that with SQLServer because SPROCS arfe just SOOO much better!). I suspect that SQLServer will recognize parameters passed in.
It would look SOMETHING like this (Check your documetation for sytax on this-I am hurrying off to work, haven't done this much w/ADODB, and can't test it right now):

Using Command, parameter, and SQL Text:

Private Sub GetMyClientDataUsingSQLString(ByVal TheClientID As Long)
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim strSQL As String
    
    strSQL = "" & _
            "SELECT LastName, FirstNAme " & _
            "FROM tblCLient " & _
            "WHERE CLientID = @ClientID"
            
            'NOTE: I THINK SQLServer Will recognize @ClientID as a Parameter in this context . . .
            
    'Set up your connection object:
    cn.ConnectionString = "YourConnectionString"
    
    'Define your Command:
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL
    
    'Define your PARAMETER (And Check my syntax on this too . . .!)
    Dim prm As New ADODB.Parameter
    prm.Name = "@ClientID"
    prm.Direction = adParamInput
    prm.Value = TheClientID
    cmd.Parameters.Append prm
    
    'Open the Connection:
    cn.Open
    
    Set cmd.ActiveConnection = cn
    Set rs = cn.Execute 'There are Optional arguments you can pass in here, but they override
                        'anything you specified while defining the Command, above.
    
    'Now you should have a recordset (in THIS case, of ONE record):
    If Not rs.EOF Then
        'Do something with your data
    End If
    
    
    'Clean up:
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cmd = Nothing
    Set cn = Nothing

End Sub

You can (and SHOULD, in my book) always use parameters in SQL when possible, instead of string concatenations. Whether you are calling an SPROC or executing a SQL String, because it prevents SQL injection attacks, and in general limits the value of the parameter passed in AND restricts it's datatype (not certain to what degree ADODB itself does this, but your SQLServer backend WILL):

Using Command, Parameters, and Stored Procedures:

Private Sub GetMyClientDataUsingSPROC(ByVal TheClientID As Long)
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    
    'Define your connection:
    cn.ConnectionString = "YourConnectionString"
    
    'Define your Command, and tell it the name of
    'the SQL SPROC you want Executed:
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "MySPROC"
    
    'Define parameters corresponding to those defined
    'on the Server with the SPROC:
    Dim prm As New ADODB.Parameter
    prm.Name = "@ClientID"
    
    'SPROCS CAN send parameters back. In THIS case we are passing one IN:
    prm.Direction = adParamInput 'Another enumeration from intellisense:
    
    'Assign a value to the new parameter:
    prm.Value = TheClientID
    'Add the parameter to the Parameters Array of the Command Object:
    cmd.Parameters.Append prm
    
    'Open the connection:
    cn.Open
    
    'Tell the COmmand to use the new connection:
    Set cmd.ActiveConnection = cn
    
    'Execute the command and return a recordset:
    Set rs = cn.Execute
    
    If Not rs.EOF Then
        'Do something with your data
    End If
    
    
    'Clean Up:
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cmd = Nothing
    Set cn = Nothing

End Sub

I got vb.NET at the same time as I began using SQL Server, so I don't have a lot of experience working with Stored Procedures + ADODB. However, I strongly recommend investigating Stored procedures, ADODB, and Parameters if you haven't already . . .