ole db query

[InDistress]

Howdy all,

I'm having some difficulties in how to write an oledb query that drags information from 2 tables in my database. I know that there are differences between an SQL query and an Access Query, but I can't seem to figure out what I need to do. The code I've added below is how I believe you would write it in say SQL query analyzer.

Private Sub UF002_CmdB_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UF002_CmdB_Search.Click
Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb;Persist Security Info=True"
Dim MyConnection As New System.Data.OleDb.OleDbConnection
Dim oleCommand As New System.Data.OleDb.OleDbCommand
Dim reader As System.Data.OleDb.OleDbDataReader
Dim oledbStr As String
UF002_EnteredPC = Trim(Me.UF002_TB_PCentry.Text)
UF002_EnteredSuburb = Trim(Me.UF002_TB_SUBURBentered.Text)
Me.UF002_LV_SLA.Clear()
Me.UF002_LV_LGA.Clear()
Me.UF002_TB_SSD.Text = ""
Me.UF002_TB_SD.Text = ""
Me.UF002_TB_State.Text = ""

MyConnection = New System.Data.OleDb.OleDbConnection(conString)
oledbStr = "SELECT a.ASGC_LGA, b.SLA_NAME_2006, b.SSD_NAME_2006, b.SD_NAME_2006, b.STATE_NAME_2006 FROM " & _
"LOCS_ASGC2006_JUL06 a," & _
"ASGC_2006_Structures b" & _
"WHERE a.ASGC_Postcode = ? AND a.ASGC_Suburb = ? AND" & _
"a.ASGC_MainCode = b.SLA_MAINCODE_2006"

oleCommand.Connection = (MyConnection)
oleCommand.Parameters.Add("@?", OleDb.OleDbType.VarChar, 200).Value = UF002_EnteredPC
oleCommand.Parameters.Add("@?", OleDb.OleDbType.VarChar, 200).Value = UF002_EnteredSuburb
oleCommand.CommandText = oledbStr
MyConnection.Open()
reader = oleCommand.ExecuteReader()

Can anyone give me some pointers in what I doing wrong?

Thanks in advance.

 

[InDistress]

It's all good - managed to figure it out.

 

[cjard]

dont put @? for both your parameters names

the driver doesnt care what name you put, it relies on the otrder you put them

so do this:

oleCommand.Parameters.Add("thePostcode", OleDb.OleDbType.VarChar, 200).Value = UF002_EnteredPC
oleCommand.Parameters.Add("theSuburb", OleDb.OleDbType.VarChar, 200).Value = UF002_EnteredSuburb



the idea being that you should set this command up once, and then re-use it by simply changing the valeus and re-running it:

cmd.Parameters("thePostcode").Value = newPostcode
cmd.Parameters("theSuburb").Value = newSuburb




-

However, the other thing is, what you have there is a very bad way of coding.. Shoving all the db access code into a button click handler is NOT good for a variety of reasons, including encapsulation, maintainability, and readability of code, use of resources and database efficiency.

Take a read of the DW2 link in my dignature.. it will help you build a Data Access Layer using the GUI, rather than typing this stuff manually.. the code gets done quicker, is 100% working, more robust (it does better error checking) and better encapsulated.. in turn it makes your code (that you write in your button click handlers) more readable and gives you more time to do proper logic without wasting time on the menial aspects such as the DAL..