October 20, 2005 at 7:43 am
Hi all,
I ran the attached code and I got the "Run-time error'91'-Object variable not set" on the following statement: cmd.CommandText= "SELECT * FROM CHIL0708A1".
I don't know how to correct this error. Please help and advise.
Thanks in advance,
Scott Chang
///////////////////////////////////////////////////////////////////////////
Sub DisplaySQLDB()
Dim cnn As Connection
Dim rst As Recordset
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim Msg As String
Dim CHIL0708A1 As Object
'Listing 22-7 (P.473 of Fronckowiak & Helda)
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
'Open recordset, and print some test records.
rst.Open "CHIL0708A1", cnn
'Specify the Query
cmd.CommandText = "SELECT * FROM CHIL0708A1"
cmd.CommandType = adCmdText
Set rs = cmd.Execute(NumRecs)
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rs.Fields.Count - 1
Msg = Msg & "|" & rs.Fields(i).Name
Next
MsgBox Msg
'Loop Through and Display The Field Values for Each Record
Msg = " "
rs.MoveFirst
Do While (Not rst.EOF)
For i = 0 To rs.Fields.Count - 1
Msg = Msg & "|" & rs.Fields(i)
Next
MsgBox Msg
rs.MoveNext
Loop
MsgBox ("Connection was successful.")
'Clean up objects.
rs.Close
rst.Close
cnn.Close
Set rs = Nothing
Set rst = Nothing
Set cnn = Nothing
End Sub
October 20, 2005 at 8:11 am
Not really an sql server problem but...
SET cmd = new adodb.command
October 20, 2005 at 9:43 am
Hi RGR'us, Thanks for your help.
Your "Set cmd=New ADODB.Command" worked nicely.
I ran the program with "Set cmd=New ADODB.Command" placed before cmd.CommandText = "SELECT * FROM CHIL0708A1". I got a new error "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context. This error occurred on the statement "Set rs = cmd.Execute(NumRecs)". If I did not use that statement (I put it as a Remark/Comment), it gave me a new Run-time error '91' on "For i=0 To rs.Fields.Count-1". Please help and advise me how to solve this new problem.
Thanks again,
Scott Chang
October 20, 2005 at 9:50 am
set cmd.activeconnection = ConnnectionObject.
The timing is important too... I think that the connection must be opened when you do that or it throws another error.
October 20, 2005 at 11:52 am
Thanks, RGR'us.
I put it in like:
Dim CHIL0708A1 As Object
'Specify the Query
cmd.CommandText = "SELECT * FROM CHIL0708A1"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = ConnectionObject
Set rs = cmd.Execute(NumRecs)
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rs.Fields.Count - 1
Msg = Msg & "|" & rs.Fields(i).Name
Then, I ran the program and I got a new error "Run-time error '424' Object required". I do not know where to put that statement in my program to meet the "timing" you cautioned me. Please help and advise me again.
Scott Chang
October 20, 2005 at 11:53 am
ConnectionObject is the connection you are using to connect to the db... I gues this should work :
Set cmd.ActiveConnection = CurrentProject.Connection
October 21, 2005 at 5:33 am
<Then, I ran the program and I got a new error "Run-time error '424' Object required". I do not know where to put that statement in my program to meet the "timing" you cautioned me. Please help and advise me again. >
Have you tried to debug the code?
Set a breakpoint on one of the first commands, and execute one line at a time. Which line throws the error?
Leif
October 21, 2005 at 8:00 am
Here is a version of your code that will give you the output specified in your original code, providing the connection string is valid and CHIL0708A1 is a table or view in your database (I have deleted the unnecessary lines):
Sub DisplaySQLDB()
Dim cnn As ADODB.Connection
Dim rst As new ADODB.Recordset
Dim str As String
Dim Msg As String
'Listing 22-7 (P.473 of Fronckowiak & Helda)
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
'I assume the following connection string is valid
cnn.Open "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=adp1SQL;Integrated Security=SSPI;"
'Create recordset reference, and set its properties.
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
'Open recordset, and print some test records.
rst.Open "SELECT * FROM CHIL0708A1", cnn 'I am assuming CHIL0708A1 is a table or view
'Loop Through and Display The Field Names
Msg = " "
For i = 0 To rst.Fields.Count - 1
Msg = Msg & "|" & rst.Fields(i).Name
Next
MsgBox Msg
'Loop Through and Display The Field Values for Each Record
If rst.recordcount>0 then
Msg = " "
rst.MoveFirst
Do While (Not rst.EOF)
For i = 0 To rst.Fields.Count - 1
Msg = Msg & "|" & rst.Fields(i)
Next
MsgBox Msg
rst.MoveNext
Loop
EndIf
MsgBox ("Connection was successful.")
'Clean up objects.
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply