Retreiving Data from a Database using VB6 2

[jedel]

I am creating a VB Interface that is linked to a MS Access Database using a MS Datalink and using ADO object on the forms. I can add and delete data fine, I have managed to add data to a "child" table also. The one thing that I can't seem to figure out is how to select a recortset from a list. The Database is MS Access 97 (Jet3.51)
For example, one of my forms has the activity, from and To dates, the coordinator and his/her contact details. I place a datacombo onto this form that pulls its list from the "Activities" Column. When I select this activity, I want all of the other fields to update to that Activity.

Any Suggestions?

JEDEL

 

[zemp]

You are going to need a parameter SQL statement or an Access query with a parameter. The parameter will come from the combo box's selected item. Then you will need to refresh the data on your form.

I don't know exactly what you need to do because you didn't post any code. Try the abve logic and post some code if you are still stuck. Thanks and Good Luck!

zemp

 

[jedel]

zemp
I would love to post some code, but at this stage I don't know what or weher the code from the combo box comes from, or where to place it into the properties box. If you go to my first string, and based on your reply, you should understand what I am trying to do. Do you havesome example code that I can massage to suite my project? Possible where to find some code that will do the jod?

Thanks for your reply

JEDEL

 

[zemp]

It still comes down to your code. It can tell me exactly what controls and type of connection you are using. For example are you using ADODB through code or an ADODC (ADO data control) placed on your form.

Sounds like you are loading a list of activities from the database into a combo box. Then you want to select the activity and see the activity details. (dates, coordinator, etc.). Since the details are based on the activity the activity will become the parameter. The basic SQL is as follows.

strSQL = &quot;Select * From <table> Where Activity='&quot; & combo1.text & '&quot;

Use it to open a recordset,

rs.open strSQL, CONN, adopenstatic

Then assign the recordset fields to the textboxes.

text1=rs![field1]
etc.

Again that is generalized and basic. If I know what your code is doing then I can get more specific.

Thanks and Good Luck!

zemp

 

[johnwm]

JEDEL,
Have a look at faq222-2244 see why ZEMP is asking the questions
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jedel]

johnwm,
Thanks for your input, but the FAQ you pointed me to does not hekp in this problem. I thgought I provided enough information in my first string. The reason I haven't yet posted any code is because I needed some direction. So far zemp has been very helpful and I will persist.

zemp
Thanks again for your reply. Your general help is great.
Here is what I can give you so far.
I have set up the form using the form data wizard and made it a &quot;parent / child&quot; form. The 'Parent' data being the tbl_main (activity details). I have added an ADO Data Control to the form along with a DataCombo box that uses the data from the ADO data control. The SQL in the ADO is:

Select * From tbl_main

In the DataCombo box I have placed the Code:

Private Sub DataCombo1_Change()
Dim strSQL As String
Set rs = Recordset

strSQL = &quot;Select*From tbl_main WHERE tbl_main.Activity = & DataCombo1.Text& &quot;

rs.Open strSQL, CONN, adOpenStatic

tb_activity = rs![Activity]
tb_cookloc = rs![cook_request_loc]
tb_coord = rs![Coordinator]
tb_cook = rs![ctr_cook_request]
tb_from = rs![Date_From]
tb_to = rs![Date_To]
tb_email = rs!
tb_ext = rs![Ext]
tb_mob = rs![Mobile]
End Sub

The problem from here is when it gets to the setting of the text boxes with the fields, the error &quot;invalid use of property&quot; appears.
I really don't know what else I can tell that I haven't already said earlier about how I have set this up

Hope you can solve this

Cheers

JEDEL

 

[jedel]

Zemp,
I've been reading through some of your older responses to other members to see if any of their problems related to mine. There was one that had the same issue, but with a data grid rather than using text boxes.

In my last thread, I mentioned that I was continually getting and &quot;invald use of property&quot; error using the code that you suggested, What if I were to place &quot;Set&quot; before each of the text box lines? I'm at work at the moment so I'll try it when I get home unless you stop me beforehand. I'll check back later.

Cheers

JEDEL

 

[zemp]

I don't believe that the Set will accomplish anything. Set is for objects not controls.

Which line is the error occuring on? You mentioned that you are using an ado datacontrol. The sample code I posted (rs.open strSQL, CONN, adopenstatic) is for ADODB code NOT ADODC (ado data control). You will be loking for code similar to the following,

Private Sub Combo1_Click()
Adodc1.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\Test.mdb;Persist Security Info=False&quot;
Adodc1.RecordSource = &quot;Select * From [tblCustomers] Where [Type]='&quot; & Combo1.Text & &quot;';&quot;
Adodc1.Refresh
End Sub

You may not need to add the connection string information. The connection string information can be set at design time.

If you are using the ADODC then you can bind you text boxes directly to it ( no code needed). Follow these steps.
1. Set the text boxes .datasource property to the ADODC.
2. Select which field to display by setting .dataField property to a field from the drop down list.


If you are really stuck I can email you a test project that demonstrates this, just post your email address.

Thanks and Good Luck!

zemp

 

[jedel]

Zemp,
Thanks for your reply. I would love to get the example. Please send it to deanellis@primus.com.au . I will try this code when I get home.

Cheers

JEDEL

 

[jedel]

Zemp,
Thankyou for your efforts. The form works! (Eureka!). For your info, the form panned out as such (I'll post this onto a news group area after this

Firstly I placed the seven text boxes required to see the data, 1 DataCombo box, 1 Datagrid to see the sub form info and 3 adodc controls onto the form.

ADODC1 was used to control the DataCombo box with the SQL reads as

Select Activity from tbl_main

ADODC2 was used to set the information ffor the text boxes. The SQL read as:

Adodc2.RecordSource = &quot;Select * from tbl_main where Activity = '&quot; & DataCombo1.Text & &quot;';&quot;
Adodc2.Refresh

This was placed into the on Change code of the DataCombo box.

ADODC3 was used to retrieve the relevant info for the Datagrid, your example that you emailed me clued me in on this one. the code, also placed into the on change in the DataCombo reads:

Adodc3.RecordSource = &quot;Select*from stbl_cateringA where Activity = '&quot; & DataCombo1.Text & &quot;';&quot;
Adodc3.Refresh
Set DataGrid1.DataSource = Adodc3
DataGrid1.Refresh

Now all of the areas are working a treat. There is one little bug though, and i'm not sure if it is with my PC or the program. The fault is intermittent (the worst of all problems).

THE PROBLEM
When I run the form, I will somethings get an error that reads:

Runtime error '-2147417848 (80010108)':
Method 'Refresh' of Object 'IAdodc' failed

When I debug this, it highlights the &quot;Adodc2.Refresh&quot; line. I'll click run again and it will work.

As I said earlier this is intermittent so I know the code works. I searched for IAdodc but there was nothing in the code.Any thoughts?

 

[johnwm]

We now see why this forum's guideline says 'Dont do email answers'

We now see a 2 way discussion about code the rest of us can't see, can't use, and can't help with!

However, you've probably got ADODC2 command type set to 'Table', and then try to post a SQL statement to it's command text. Have a look here for a detailed answer:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q238279

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jedel]

johnwm
The code that I posted was the email solution to my problem. I would never keep anything that would be of value to this forum. I know of a number of people in this forum that have exactly the same problem as I have. so far as a two way conversation? I don't think so. All of the code shared is on this string.

Thankyou however for the link you sent me to. It is exactly the problem I am having. But the problem did not go away. I tried the following

1. Stipulating the Commandtype as Unknown and text on both the adodc2 and 3. unsuccessful.

2. placing the SQL in the adodc instead of the code for the dataCombo box. This stopped the form from working altogether.

3. The SQL is placed in the parenthes anyway as it won't work without them.

The database I am using is an Access97 database. would this have any affect?

The UDL I'm using is set for a Jet 3.51. and it's set to read, write and share, deny none at this stage.

One other thing, I'm finding that VB6 is getting a lot of General protection faults on my PC. Which is why I wasn't sure if the fault was code or my PC. The error I said was intermittent. Would you know of any other software that doesn't like vb6? I installed the update VB runtime files from Microsoft. I'm not sure if this is the cause, I would have thought it would solve any issues.

Your help is appreciated

Cheers

JEDEL

 

[johnwm]

JEDEL,
Thanks for your code post and clarification - I'm always a grump in the mornings!

Have you got the VB6 SP5 on your dev machine? it's available from:

http://msdn.microsoft.com/vstudio/downloads/updates/sp/vs6/sp5/dnldoverview.asp

In an ADODC I use 1-adCommandText as the command type. I then stick a 'vanilla' SQL string in the text so the ADODC doesn't complain at startup (Select * from tblWhatever Where ID = 1) then use the first combo contents to generate the real query (much as you already have).

The only difference I can see is making sure that you have a valid text command in the ADODC at startup
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[gprima]

JEDEL

I pulled this code out of an application of mine that is currently in production. It includes a class called clsDataBase and a sub routine that will pull data from a client database through the class.

I hope the following code helps.

'This a class I call clsDataBase

Option Explicit


Public rs As New ADODB.Recordset
Public cnn1 As New ADODB.Connection
Public cmd As New Command
Public objVariable As New clsVariables
Public Sub Connect()
Public strError as String

'Check if a connection is already open
'This will occure if you have left it open for a previous query
If cnn1.state <> 0 Then 'if the object is open then close it
cnn1.Close
End If

'Open a connection using the Microsoft ODBC provider for jet.
Set cnn1 = New ADODB.Connection
Set cmd = New Command
cnn1.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
cnn1.ConnectionString = App.Path & &quot;LivingClients.mdb&quot;
cnn1.Open


End Sub


Public Sub selData(Query As String)
'This Sub Selects The Data
Dim cmd As New Command
Dim SQL As String
Set rs = New ADODB.Recordset

'First open a connection
Connect

Declare a new recordset
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set cmd.ActiveConnection = cnn1
cmd.CommandText = Query

rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

End Sub



Public Sub upDate(Query As String)
'This Sub Updates Data
Dim objDB As New clsDataBase

Connect
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn1
cmd.CommandText = Query

cnn1.Execute Query

End Sub



Public Sub insData(Query As String)
'This Sub Inserts Data
Dim objDB As New clsDataBase

Dim errLoop
Dim strError As String
Connect
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn1
cmd.CommandText = Query

On Error Resume Next
cnn1.Execute Query

'check for errors
' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In cnn1.Errors
strError = &quot;Error #&quot; & errLoop.Number & vbCr & _
&quot; &quot; & errLoop.Description & vbCr & _
&quot; (Source: &quot; & errLoop.Source & &quot&quot; & vbCr & _
&quot; (SQL State: &quot; & errLoop.SQLState & &quot&quot; & vbCr & _
&quot; (NativeError: &quot; & errLoop.NativeError & &quot&quot; & vbCr

Next

Resume Next
cnn1.close

End Sub



Public Sub delData(Query As String)

'This Sub Deletes Data
Dim objDB As New clsDataBase
Dim objLaw As New clsLawformsMain

Connect
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn1

cmd.CommandText = Query
cnn1.Execute Query

'check for errors
' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In cnn1.Errors
strError = &quot;Error #&quot; & errLoop.Number & vbCr & _
&quot; &quot; & errLoop.Description & vbCr & _
&quot; (Source: &quot; & errLoop.Source & &quot&quot; & vbCr & _
&quot; (SQL State: &quot; & errLoop.SQLState & &quot&quot; & vbCr & _
&quot; (NativeError: &quot; & errLoop.NativeError & &quot&quot; & vbCr

Next

Resume Next
cnn1.close

End Sub




'I use the following code to iterate through a retrieved recordset
'The table name in the database is tblClient and i am inserting the data into a
'listbox control

sub GetClientList()
dim objDB as New clsDataBase
dim SQL as string

SQL = &quot;Select clientid, firstname, lastname from tblClient
objDB.selData SQL

Do While Not objDB.rs.EOF
list1.additem objDB.rs(&quot;clientid&quot & &quot;, &quot; & objDB.rs(&quot;firstname&quot & &quot;, &quot; objDB.rs(&quot;lastname&quot
objDB.rs.MoveNext

Loop

End Sub

 

[jedel]

gprima,
Thanks fo ryour input, but I have my connections as ADODC, not ADODB. The code is different. I will play around with ADODB and use your code at a later stage. Thanks

JEDEL

Johnwm
Thanks agian for your suggestion. Because I am on the slow network (ie phoneline), I have a friend downloading the 129M SP5 pack for me and will update as soon as I get it. At the moment, My connection to the database doesn't seem to work at all. Tested the UDL connection and the rror says that the test connection failed because of an error initializing the provider. The number following is: 0x80040e21 what ever that means. Hopefully this update will solve a lot of this.

Thanks again, will get back to you as soon as I have updated and tested. Will also keep an eye on any other replies you may have.

Cheers

JEDEL

 

[jedel]

Gents
I have now installed SP5 and all seems well with the Form. No crashes or hangs to date. Thanks for all of your help. I now have a new issue I would like to solve. On the form there is a datagrid that relates to the recordset on the form. I would like to add to that &quot;Child&quot; Recordset without needing to enter the linked field, or the field with the relating data. ie if the Activity was the primary key, I want that to automatically appear when I choose to enter a new record in the Data grid.
Any Suggestions?

Cheers

JEDEL