VB6 ADO

jlawren7

I'm trying to figure out why I keep getting a error searching my database with ado. Here is my search button Private Sub CmdSearch_Click() Dim SQlnameSearch As String SQlnameSearch = txtSearch.Text Adodc1.RecordSource = " Select * FROM Customers WHERE (ContactLastName)= '" & SQlnameSearch & "' " Adodc1.Recordset.Requery **<-- errors here** Adodc1.Refresh **<-- if the above line is commented out it errors here** ' want to fill the datagrid with the results of the search from above. DBGrid1.Visible = True End Sub Now if i build mt own connection like so Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & App.Path & "\databases\PoolApp.mdb" strSearchCustomer = FrmSearch.Searchtxt.Text rst.Open _ "SELECT ContactLastName FROM Customers WHERE ContactLastName = '" & strSearchCustomer & "' ", _ cnn ' Print the values for the fields in ' the first record in the debug window For Each fld In rst.Fields MsgBox rst.Fields.Count <-- I get 1 record but unable to fill the datagrid. Next ' Close the recordset rst.Close Any help would begreatly appreciated Help is great only if you ask correctly :)

Dave Kreskowiak

jlawren7 wrote:

Help is great only if you ask correctly

You said it yourself! You say this code generates errors, but you never say what those errors are. It's kind of impossible to help you without knowing what the errors are. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

jlawren7

This error comes when using the adodc1.recordsource = Runtime error '91' object varible or With block varible not set This line is highlighted in Yellow Adodc1.Recordset.Requery the other section ( built my own connection results in nothing ) Help is great only if you ask correctly :) -- modified at 14:44 Sunday 5th February, 2006

Dave Kreskowiak

You're calling Requery on a RecordSet object that is null, or Nothing in VB. You cal only call this method on a RecordSet object that has been returned by a previous query, if that previous query returned anything at all. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

jlawren7

Well since i have tried to go a different route Now I'm dtuck with filling a dtagrid witrh the table contents. Then once that is working I would like to use a sql search to get the records i want here is what I have now Option Explicit ' Create a Recordset Dim rst As ADODB.Recordset Private Sub Command1_Click() Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient ' Add columns to the Recordset rst.Fields.Append "Customer ID", adInteger rst.Fields.Append "First Name", adVarChar, 40, adFldIsNullable rst.Fields.Append "Last Name", adVarChar, 40, adFldIsNullable rst.Fields.Append "Address", adVarChar, 60, adFldIsNullable rst.Fields.Append "Phone Number", adInteger ' Open the Recordset rst.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Testing\v003\databases\db1.mdb; Persist Security Info=False" ' Populate the Data in the DataGrid Set DataGrid1.DataSource = rst End Sub A simple datagrid, command button on a form

Dave Kreskowiak

Man, you are really confused about these object works. You didn't perform a query against that database at all. All you did was create a blank recordset, add some columns to it, then bound the empty recordset to a datagrid. You need to actually execute a query against the database. Something like this will return all the records in the specified table:

Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & \_
    "C:\\Program Files\\Testing\\v003\\databases\\db1.mdb;Persist Security Info=False"

rs.CursorLocation = adUseClient

rs.Open "_tableName", conn, adOpenStatic, adLockReadOnly, adCmdTable

Set DataGrid1.DataSource = rs_

This probably won't run as listed. I haven't used VB6 in over 5 years now... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome