Incorporate SQL in VB Code (1 Viewer)

AinsleyW

New member
Local time
Today, 07:32
Joined
May 16, 2003
Messages
4
I am Using VB 2008. I created a project in VB 2008 with a MS Access 2003 db. The DB is accessed from outside the VB Project. The DataSet, Record Source are all set up in the Project and I can write data, delete data from the DB when I run the VB project.

Here is where I am confused.

I am attempting to incorporate a SQL query in my VB code. Shouldn't I be able to incorporate my SQL code without setting up ADO, Recordsets and that sort of stuff due to the fact that I have my DataSet, Record source already set up in the VB 2008 Project?

Here is what I am tring to do:

There are 2 Forms in my Project, Form1 and Form2, their DataSources are Table A and Table B respectively from my DB. I have a Combo Box on my Form2 which have as its recordsource data from Table A. I have three Text Boxes on my Form2 which I want to be filled in with data from Table A when I select a item from the combo box. My Code is as follows:


Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

Dim cbContents As String = Me.ComboBox1.Text

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String

strSQL = "SELECT FirstName FROM Managers "
strSQL = strSQL & "WHERE FirstName = " & "'" & cbContents & "';"

strSQL2 = "SELECT LastName FROM Managers "
strSQL2 = strSQL2 & "WHERE FirstName = " & "'" & cbContents & "';"

strSQL3 = "SELECT FileName FROM Managers "
strSQL3 = strSQL3 & "WHERE FirstName = " & "'" & cbContents & "';"

Me.TextBox1.Text = strSQL
Me.TextBox2.Text = strSQL2
Me.TextBox3.Text = strSQL3

End Sub



I am fairly new to VB, Help!!!
 

Michael_OBrien

New member
Local time
Today, 06:32
Joined
Jan 15, 2008
Messages
4
I haven't used the dataset feature in VB 2008 yet but in 2005 I would use the OLEDB data access methods and they are fairly simple. Especially if you are using a VB front end and an Access back end as you are.

There are 5 steps to gathering the data from the database

First, Create and open a connection to the database, create a variable to hold the data and a command to be executed on the database:
dim dbConn as new OleDBConnection
dim ds as new DataSet
dim SELECTcommand as new OleDbCommand
dbConn = OpenDBConnection() ' OpenDBConnection is a function I define in a module that will be defined lower

Second, Instantiate and initialize the SQL String, set it to the command text and set the connection of the OleDbCommand:
dim SQLString as string = "SELECT FirstName FROM Managers WHERE FirstName = " & "'" & cbContents & "';"
SELECTcommand.commandtext = SQLString
SELECTcommand.connection = dbConn

Third, Set up the adapter that will tell the program how to talk to the database:
dim oda as new OleDbDataAdapter
oda.SelectCommand = SELECTcommand

Forth, Run the command through the adapter on the database:
oda.fill(ds)

Fifth, Close the database connection
CloseDatabase(dbConn)

Aside from the error checking and handling then this outline should allow you to get a dataset full of records from the database.

the OpenDBConnection and CloseDatabase functions look like this:
Public Function OpenDBConnection() as OleDbConnection
Try
Dim dbConn As New OleDbConnection
dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = YOURDATABASEPATH.mdb"
dbConn.Open()
return dbConn
Catch ex as Exception
MessageBox.Show("Error in mdlDBUtilities: Open Connection" & vbcrlf & "Error: " & ex.Message
Return Nothing
End Try
End Function

Public Sub CloseConnection(byref dbConn as OleDbConnection)
dbConn.Close()
End Sub

Hope this helps
Mike O'Brien
 
Last edited:

Users who are viewing this thread

Top Bottom