Can someone help me?

[lavarock09]

I need to use a database with my application I'm making.

I have searched many websites and the all keep giving me something like this,

Dim TutVB4DB As Database
Dim TutRS As Recordset

but recordset and database dont work there

Does anyone know any good sites for learning about Databases with VB 6.0

[RobDog888]

Check out the Database FAQ on our fourm - http://www.vbforums.com/showthread.php?t=337051

There is a link to a good ADO tutorial.

[Hack]

What version of VB are you using?

[Harsh Gupta]

probably u missed adding Reference for the ADO connection!!!

[lavarock09]

What would the code be for searching a ADO Database?

[Hack]

What would the code be for searching a ADO Database?

There are MS Access datbases; MS SQL Server databases; Oracle databases; MySQL databases, but there is no such thing as a ADO database.

ADO is a method used to connect to an existing database.

Lets get connected, and then we can talk about searching.

Set a reference the Microsoft ActiveX Object Library, and then run this code.

Dim ADOCn As ADODB.Connection
Dim ConnString As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\yourdatabasename.mdb;" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString

[lavarock09]

sorry, my mistake,

I mean MS Access Database

I've connected and all, I just need to search it

[Hack]

Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
Set adoRS = New ADODB.Recordset
sSQL = "SELECT * FROM table WHERE " 'dont know what you want to search for so
'i have no idea what to use as an example in a WHERE clause, but here is
'a start that will give you a recordset with all records matching your SQL
'statement
adoRS.Open sSQL, ADOCn

Now that you have a bunch of records, what do you want to do to/with them?

[lavarock09]

Display them in a List box and when you choose one it displays it in the controls

[Hack]

Display them in a List box

What are the "them"? What are you searching for?

and when you choose one it displays it in the controls

What controls? How does "it" (whatever "it" is) display?

[lavarock09]

THEM = a string, e.g. Chris, Bill, g7463, what the search has returned

IT = The option you have chosen from the listbox

[Hack]

THEM = a string, e.g. Chris, Bill, g7463, what the search has returned

IT = The option you have chosen from the listbox

Ok, and what "controls" would it be displayed in once it got selected?

Answer that one last question and I can give you sample code to do all this stuff which you should be able to easily modifiy to suit your specific needs.

[lavarock09]

the controls are text boxes

[Hack]

Ok. From the top, starting with connecting to the database all the way down to display a piece of information in a textbox.

Private Sub Command1_Click()
Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\yourdatabase.mdb;" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
'it doesnt look like you need a specific WHERE clause for this situation
'however, if you did it would look something like
'WHERE fieldname = 'Chris'
sSQL = "SELECT fieldname FROM tablename "
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    List1.AddItem adoRS.Fields.Item("fieldname").Value
    adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing
End Sub
 
Private Sub List1_Click()
Text1.Text = List1.List(List1.ListIndex)
End Sub

You will need to change:
yourdatabase.mdb to your datbase name
fieldname to the name of the field holding the data you are going after
tablename to the name of the table that holds the field that holds the data you are going after
I'm using generic control names (List1, Text1, etc). You will need to change those to match the control names you have.

This, however, should get you on the road to where you want to go.

[lavarock09]

Question 1: would I have to change the SQL statement to include the table name (tbl_master)

Question 2: would I have to change the SQL statement to include the fieldname (Callsign/Station)

Quesion 3: I keep getting the error: 'No value given for one or more required parameters' and when I click debug it highlights this: 'adoRS.Open sSQL, ADOCn'

[Hack]

Question 1: would I have to change the SQL statement to include the table name (tbl_master)

You will need to change:
tablename to the name of the table that holds the field that holds the data you are going after

Question 2: would I have to change the SQL statement to include the fieldname (Callsign/Station)

You will need to change:
fieldname to the name of the field holding the data you are going after

Quesion 3: I keep getting the error: 'No value given for one or more required parameters' and when I click debug it highlights this: 'adoRS.Open sSQL, ADOCn'

Have you made those changes?

[lavarock09]

Yes.

This is what my code is now

Private Sub Command2_Click()
Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Documents and Settings\Chris\My Documents\VB Logbook\ADO\logbook.mdb;" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
'it doesnt look like you need a specific WHERE clause for this situation
'however, if you did it would look something like
'WHERE fieldname = 'Chris'
sSQL = "SELECT 'Callsign/Station' FROM 'tbl_master'"
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    List1.AddItem adoRS.Fields.Item("Callsign/Station").Value
    adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing
End Sub

[lavarock09]

but the error has changed.

'Syntax Error in query. incomplete query clause'

Sorry for the double post

[Hack]

You don't need the single quotes, but because of the / in the field name, you might need brackets. Try this first:

sSQL = "SELECT Callsign/Station FROM tbl_master

Single quotes are needed around string data elements, not field or table names.

If that still gives you heartburn, but brackets around Callsign/Station (i.e., [Callsign/Station] )

[lavarock09]

Thanks you,

But now it needs a bit more added to it.

I need it to search for what is in the text box, (Text10) so I did this

sSQL = "SELECT [Callsign/Station] FROM tbl_master WHERE [Callsign/Station] = 'Text10.Text'"

But that didn't work

So, can you help me.

I also need it so that when you click on the result in the listbox it updates all the other boxes (text1 to text9)

[Hack]

Thanks you,

But now it needs a bit more added to it.

I need it to search for what is in the text box, (Text10) so I did this

sSQL = "SELECT [Callsign/Station] FROM tbl_master WHERE [Callsign/Station] = 'Text10.Text'"

But that didn't work

So, can you help me.

I also need it so that when you click on the result in the listbox it updates all the other boxes (text1 to text9)

Close, very close. Now you need the single quotes.

sSQL = "SELECT [Callsign/Station] FROM tbl_master WHERE [Callsign/Station] = '" & Text10.Text & "' "

Update all the textboxes how? With the exact same thing?

[lavarock09]

Well, I'll do an example

My Database

Callsign/Station : Date (Text1) : Frequency (Text2) : Start Time (Text3) : End Time (Text4) : Mode (Text5) : Power (Text6) : I forget (Text7) : I forget again (Text8) : Comments (Text9)

They all have bits of data, I need these updating with everything

[Hack]

You are going to have issues because of how you built your table. Date is a reserved word in every programming language I've ever seen. Never used reserved words for field names. That MUST be put in brackets. You have spaces in your field names. Never put spaces in your field names because they are a pain to deal with. Those fields also must be put in brackets.

Based on the query that you ran, you should get one recordback in your recordset, so to update your textboxes you can do something like this:

Text1.Text = adoRs.Fields.Item("[Date]").Value
Text2.Text = adoRs.Fields.Item("[Start Time]").Value
'Im not going to write out 10 statements but you get what I mean.

[lavarock09]

Scrap that Idea for a moment

How would I make it so that when I select the result from the search It makes a text box display the ID of the row that the result is on

[Hack]

Is ID a field in your table that can be brought back with your SELECT statement?

[lavarock09]

yes, It is the number of the row/entry in the database

I have managed to get that but...

I want some code that will-

1: Give me all the data on the row with a certain ID,

so this means,

1: I do a search for whatever string in the Callsign/Station column in the database (Done!)
2: It displays all rows with that data in that column, that meaning Callsign/Station (Done!)
3: I click on the result and it gives me the Id of the row it is on in a textbox. (Not Done!)
4: I then click on a button and it updates all textboxes with the information on the row (Not Done!)

Steps 3 and 4 need coding, but I wouldn't have a clue how to do it, can you help?

[Hack]

When you say "click on the result", where is the result? Is it in your listbox?

If so, then you would need to requery, but that shouldn't be a problem. But, before I post an example I want to make sure I understand what is going on.

[lavarock09]

I search for something in the Callsign clolumn of the table

It comes up with the results in the list box,

I click on one of the results in the listbox

In one text box It gives the Callsign
in another text box it gives the ID of the row that the Callsign Has come from

keep in mind though that I may end up searching for one thing e.g. Y6FGH and getting back 10 different results because Y6FGH may be in the table column, Callsign, more than once

Then I press a but button and It then updates all the textboxes on the form, Date (text1.text), time etc. with the data from the row with the row Id in the previous textbox

Do you understand?

[lavarock09]

*bump*

[Hack]

I search for something in the Callsign clolumn of the table

It comes up with the results in the list box,

I click on one of the results in the listbox

In one text box It gives the Callsign
in another text box it gives the ID of the row that the Callsign Has come from

Assuming your listbox is populated with call signs, code for this would be something along the lines of

SELECT Id, CallSign FROM tbl_master WHERE CallSign = '" & List1.List(List1.ListIndex) & "' "
adoRS.Open sSQL, ADOCn
Text1.Text = adoRS.Fields.Item("ID").Value
Text2.Text = adoRS.Fields.Item("CallSign").Value

Do you understand?

I hope so but I'm not really sure. Did what I post make sense?

[lavarock09]

Let me just show you this,

this is the code I have got,

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Documents and Settings\Chris\My Documents\VB Logbook\ADO\logbook.mdb;" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
'it doesnt look like you need a specific WHERE clause for this situation
'however, if you did it would look something like
'WHERE fieldname = 'Chris'
sSQL = "SELECT ID, [Callsign/Station] FROM tbl_master WHERE [Callsign/Station] = '" & List1.List(List1.ListIndex) & "' "
adoRS.Open sSQL, ADOCn
Combo2.Text = adoRS.Fields.Item("ID").Value
combo1.Text = adoRS.Fields.Item("Callsign/Station").Value
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    List1.AddItem adoRS.Fields.Item("Callsign/Station").Value
    List2.AddItem adoRS.Fields.Item("ID").Value
 adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing

Is there anything wrong with it?

[Hack]

Let me just show you this,

this is the code I have got,

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Documents and Settings\Chris\My Documents\VB Logbook\ADO\logbook.mdb;" & _
        "Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
'it doesnt look like you need a specific WHERE clause for this situation
'however, if you did it would look something like
'WHERE fieldname = 'Chris'
sSQL = "SELECT ID, [Callsign/Station] FROM tbl_master WHERE [Callsign/Station] = '" & List1.List(List1.ListIndex) & "' "
adoRS.Open sSQL, ADOCn
Combo2.Text = adoRS.Fields.Item("ID").Value
combo1.Text = adoRS.Fields.Item("Callsign/Station").Value
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    List1.AddItem adoRS.Fields.Item("Callsign/Station").Value
    List2.AddItem adoRS.Fields.Item("ID").Value
 adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing

Is there anything wrong with it?

Well, you can remove the comments I made about the WHERE clause, but other than that it looks good.

Does it do what you need it to do?

[lavarock09]

Well...this is the code for the button that I press that displays the Callsign In one list box and the ID of the row that the callsign is on in another lisybox,

but Keep getting this error as ot doesn't like this line of code

combo1.Text = adoRS.Fields.Item("Callsign/Station").Value

I get this error and it highlights it

Item cannot be found in the collection corresponding to the requested name or ordinal

[Hack]

Well...this is the code for the button that I press that displays the Callsign In one list box and the ID of the row that the callsign is on in another lisybox,

but Keep getting this error as ot doesn't like this line of code

combo1.Text = adoRS.Fields.Item("Callsign/Station").Value

I get this error and it highlights it

I just knew that / was going to cause trouble. Wierd characters like that tend to freak out querys. Since you already have the field created, encapsulate it in brackets. That should help.

[lavarock09]

you mean like this?

combo1.Text = adoRS.Fields.Item("[Callsign/Station]").Value

If so...

It didn't work

[Hack]

Can you do

Msgbox adoRS.Fields.Item("[Callsign/Station]").Value

[lavarock09]

can you just tell me where to put this code.

I've put it in List1_Click

but I get the same error

[lavarock09]

I can't thank you enough Hack.

But I think I have done everything I needed, but It wouldn't of been possible without you so,

Thank You! (this is a rare occasion, I'm using a smiley)