DATA Selection Help

[SQUASHJUNKIE]

Hi,

I'm a bit of a beginer to VB.net and have struggled throught the last couple of days to produce a simple form.

However I want the form to show data based on a selection from a combo box, but I keep getting an error. I beleive the problem to be in my sql statement, but at the moment I cant see the wood for the trees! Can anyone see any problems with the code below?

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


' Load the Combo Box
DataSet11.Clear()
OleDbDataAdapter1.Fill(DataSet11, "qry01_artran_data")


Me.Btnupdate.Enabled = False




End Sub



Private Sub cbodocno_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbodocno.SelectedIndexChanged
'load the dataconnection for the form
Dim conn As OleDb.OleDbConnection = OleDbConnection1
Dim selectstring As String
Dim cmd As OleDb.OleDbCommand
Dim reader As OleDb.OleDbDataReader

Try

conn.Open()

selectstring = "select DOCNO, customer_name, DOCDATE, TERMSDESC, no_months, date1, date2 from QRY01_ARTRAN_DATA WHERE sysdocid = '" & cbodocno.SelectedValue & "'"
cmd = New OleDb.OleDbCommand(selectstring, conn)


reader = cmd.ExecuteReader()

While (reader.Read())

LBLCUSTNAME.Text = reader(1)
LBLDOCDATE.Text = reader(2)
LBLTERMSDESC.Text = reader(3)
lblmonths.Text = reader(4)
LBLDAY1.Text = reader(5)
LBLDAY2.Text = reader(6)

Me.Btnupdate.Enabled = True

End While

reader.Close()
conn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message, "access database", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub
End Class

 

[jebenson]

What error do you get, and where do you get it (what line causes the error)?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Bruce1965]

OK, 2 things,

firstly,in your connection string, you use - cbodocno.SelectedValue - to refer to the selection. Try using the text property i.e cbodocno.text

secondly, put a break point on this line and run through debug to see exactly what the connection statement is picking up

 

[SQUASHJUNKIE]

Hi bruce2402

Thx for the input, I changed the property to cbodocno.text and placed the breakpoint in.

Fixed It!!!

just as a bonus question though , if the combo box is looking up an invno which is not unique in the query that I am accessing, and I want the combo box to return a value which is unique so that the correct record is returned...for example
invoice = 0002 Sysdocid = 1234
invoice = 0002 Sysdocid = 1235
So the user would see the invoice number in the combo box, but the data returned would be based on the value from the sysdocid (a numerical unique value)..
thx for your help so far!

 

[Bruce1965]

i was going to suggest setting your intitial qry (qry01_artran_data) to Select Distinct - this'll work for loading your combo box,BUT you use the same query in your connection string which doesn't want to select distinct records. (if i understand it correctly!!)

What you could try is manually configure an additional connection string to load you combobox -

i.e.

Dim conn As OleDb.OleDbConnection = OleDbConnection1
Dim selectstring As String
Dim cmd As OleDb.OleDbCommand
Dim reader As OleDb.OleDbDataReader

Try

conn.Open()

selectstring = "select DISTINCT form ... where ..."
cmd = New OleDb.OleDbCommand(selectstring, conn)


reader = cmd.ExecuteReader()

While (reader.Read())
cbodocno.items.add(your field name)

End While

reader.Close()
conn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message, "access database", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try


or something similar.



Hope this helps