SQL and VB Question

[jason12776]

I'm new at working with VB and SQL so please bear with me. I know how to add/delete/edit records using VB, but now I want to have a search field. When a user clicks on the search button, a new form will open, alowing the user to type in an ID number. When they click on the Find Now button on the form, the search form will close, then the information will be displayed in the appropirate text boxes on the main form associated with the ID number. I'm using Access 2000 with VB 6.0. Any ideas will be greatly appreciated.

cheers!

 

[vb5prgrmr]

the SQL part of creating your query would be something like

select * where id = user input

or

select * where id like *user input*


what you will need to do is either set a public variable with the information entered on the second form or a public function that returns the value to search for from the second form. Once you have this information then you would run your query.

 

[Monkeygumbo]

Start by adding a Active Data Objects library to your project references.

You will then need to establish a connection with your database.

Dim connDB as Connection 'global
Dim rsDB As Recordset 'global

'Set the objects
Set connDB = New Connection
Set rsDB = New Recordset

'Established connection
connDB.Open "Driver={SQL Server};" & _
"Server=" & SERVER & ";" & _
"Database=" & DATABASE & ";" & _
"Uid=" & USER & ";" & _
"Pwd=" & PASSWORD & ";"

'now open a recordset (a sort of copy of the tables you want to use)

'Open the recordset from the database
rsDB.Open "Select * From Customer_Table", connDB, adOpenStatic, adLockOptimistic


'Now for your search button
Public Sub Command1_Click()

text1.Text = rsDB.execute "Select CustomerFirstName From Customer_Table Where Customer ID = " & chr(34) & txtSearch.Text & chr(34)

text1.Text = rsDB.execute "Select CustomerLastName From Customer_Table Where Customer ID = " & chr(34) & txtSearch.Text & chr(34)

'Etc. etc.
End Sub

Let me know if that helps

 

[jason12776]

Ok, I do have that part, but now how do I populate the text boxes with all the appropirate fields from the database that are associated with that particular ID?

 

[Monkeygumbo]

Whoops...

Recordsets don't have an Execute method

Ignore opening the recordset and just run the connDB.Execute method with those SQL statements.

 

[Schroeder]

Are you familiar with opening recordsets within your program? I ask because the query types you mentioned can be accomplished using the .Execute method of your database (DAO) or connection (ADO) object.
AFAIK, if you want to get info out of your database, you'll need to open a recordset. First build your SQL statement the way vb5prgrmr said, then get a Recordset object ready and, in DAO:

Set rsRecordset = dbDatabase.OpenRecordset(strSQL)

in ADO:

Set rsRecordset.Open strSQL, cnConnection

After that, you can populate your TextBoxes with the .Fields collection of your recordset.

TextBox1 = rsRecordset.Fields("Field1")
TextBox2 = rsRecordset.Fields("Field2")
and so forth...

 

[Schroeder]

So, it would appear that you don't necessarily need to open a recordset to retrieve info from your database. I've wondered if the .Execute method would return values like that. What happens if you request more than one field? Does .Execute return an array or collection or something like that?

I'd still open a recordset though. Running an Execute for every field means repeatedly searching the database for the same record. If you open a recordset, you can search once and populate every field with your results.

 

[Monkeygumbo]

I'm sorry, I'm new to ADO too.....As far as how to retreive the information look at this code. connDB.Execute doesn't return values

Public Sub Command1_Click()

rs.Open "Select * from Master where ID=" & txtSearch.Text, CON, adOpenKeyset, adLockOptimistic
If rs.RecordCount <> 0 Then
txtID.Text = rs![ID]
txtName.Text = rs![Name]
txtAge.Text = rs![Age]
txtSalary.Text = rs![Salary]
rs.Close
Else
MsgBox &quot;Invalid Employee ID&quot;, vbCritical, &quot;Invalid ID&quot;
End If


end sub

MG

 

[jason12776]

What do you mean by new connection, Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DVD\DVD.mdb;Persist Security Info=False? Would the code be slightly different if I was working with Access 2000?

 

[Schroeder]

That looks like a good connection string for an Access 2000 db.

 

[Monkeygumbo]

connDB.Open &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; & _
&quot;Dbq=c:\somepath\mydb.mdb;&quot; & _
&quot;Uid=admin;&quot; & _
&quot;Pwd=&quot;

Try this for opening an Access DB with standard security.

 

[jason12776]

Hey everybody. I sort of finished up on thisissue, except I keep getting the error I put in. Why?

Private Sub cmdSearch_Click()
Dim sql As String, conn As String
Dim rs As New ADODB.Recordset
Dim dpath As String

dpath = &quot;C:\dvd\dvd.mdb&quot;
conn = &quot;provider=microsoft.jet.oledb.4.0;data source = &quot; & dpath
sql = &quot;SELECT * from movie&quot;
rs.Open sql, conn

With rs
.MoveFirst
Do Until .EOF
If .Fields(&quot;ID&quot = frmMain.txtID.Text Then
frmMain.txtCatagory.Text = .Fields(&quot;Category&quot
If .Fields(&quot;Date&quot <> &quot;&quot; Then ' Not required
frmMain.txtDate.Text = .Fields(&quot;Date&quot
End If
frmMain.txtDirector.Text = .Fields(&quot;Director&quot
frmMain.txtID.Text = .Fields(&quot;ID&quot
If .Fields(&quot;LentOut&quot <> &quot;&quot; Then ' Not required
frmMain.txtLentOut.Text = .Fields(&quot;LentOut&quot
End If
frmMain.txtMovie.Text = .Fields(&quot;Movie&quot
frmMain.txtRating.Text = .Fields(&quot;Rating&quot
Else
MsgBox &quot;Invalid Movie ID&quot;, vbCritical, &quot;Error&quot;
End If
.MoveNext
Loop
End With
rs.Close
End Sub

It will step through as if it accepts the fact that the txtID.text and the ID field match, but it will flash the message box about 5 times before it will quit flashing it. Any ideas?

Cheers

 

[Schroeder]

Your SQL statement retrieves every record in the movie table. The ID fields of some of the records being retrieved probably don't match the value in your &quot;search field&quot; (txtID). Every time you hit one of those records, your error box is displayed.

Try doing what Monkeygumbo suggested earlier. Build your SQL statment thusly:

sql = "SELECT * FROM movie WHERE ID = '" & txtID & "'"

or, if ID is a numeric field:

sql = "SELECT * FROM movie WHERE ID = " & txtID

This will only retrieve records for the appropriate ID. This also will make your If statement (

If .Fields("ID") = frmMain.txtID.Text Then

) irrelevant since it will always evaluate to True.
To check for a valid movie ID, add something like this before your

Do Until .EOF

loop:

If .BOF And .EOF Then MsgBox "Invalid Movie ID", vbCritical, "Error"

If the ID is invalid, the loop won't execute because .EOF will be true right off the bat.

 

[jason12776]

Thank you very much everybody, it works great!!! My only question now is when the user types in a value, i.e. g0034, to have the value either automatically be uppercase or lowercase, to match whatever the database lists. I actually forget how to use this feature. I believe it's ucase() and lcase().

 

[vb5prgrmr]

Yes or you can use &quot;Like&quot; instead of &quot;=&quot; in your query and not have to worry about case sensitivity. This would also allow your users to enter a partial ID and be able to get more results if you use a wildcard character at the end of the string i.e. where id like '&quot; & textid.text & &quot;%'&quot;

Good Luck