get values from ado/dataset

[jozino01]

hi,
just trying to put together my first vb express 2008 application (after using vb6 for several years)

i want to get values from access table (based on selection from two combo boxes) and insert them to text boxes:
here is my code:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
On Error Resume Next
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim mystate As String
Dim mycounty As String

If Len(ComboBox1.Text) < 2 Then
MsgBox("Select state first.")
Exit Sub
End If

If Len(ComboBox2.Text) < 3 Then
MsgBox("Select county first.")
Exit Sub
End If

mystate = "'" & ComboBox1.Text & "'"
mycounty = "'" & ComboBox2.Text & "'"

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\zcs\zcs\ldata.mdb"

con.Open()

sql = "select , [W] from snowload where [st]=" & mystate & " and [county]=" & mycounty & ""

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "s")

con.Close()

TextBox1.Text = ds.Tables("s").Columns(0).ToString
da.Fill(ds, "w")
TextBox2.Text = ds.Tables("w").Columns(1).ToString

End Sub

i am getting an error "No value given for one or more required parameters" for da.Fill(ds, "s")

please advice

 

[earthandfire]

From memory, (its a long time since I've used Access), but I think square brackets [] in a where clause indicate parameters. Try rewriting your query without the square brackets.


Hope this helps.

 

[RiverGuy]

Try removing the brackets [ ]

 

[jozino01]

well, i don't think the brackets are causing error since the following code (executed before Button2_Click) works ok:

Private Sub ComboBox2_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.GotFocus
On Error Resume Next
Dim con As New OleDb.OleDbConnection ' database connect variable
Dim ds As New DataSet ' dataset variable
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim mystate As String

If Len(ComboBox1.Text) < 2 Then
MsgBox("Select state first.")
Exit Sub
End If

mystate = "'" & ComboBox1.Text & "'"

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\zcs\zcs\ldata.mdb"

con.Open()

sql = "select [county] from ziplist where [state]=" & mystate & " group by [county]"

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "county")

'MsgBox("Database Connected")

con.Close()

ComboBox2.DataSource = ds.Tables("county")
ComboBox2.ValueMember = ds.Tables("county").Columns(0).ToString
ComboBox2.DisplayMember = ds.Tables("county").Columns(1).ToString
Exit Sub

End Sub

 

[Sorwen]

vb.net and access can be picky sometimes. The brackets are only need on irregular words. Say for example the field was county# it would then need the brackets. I would get rid of them. Second you have to single quote on a string. So:

sql = "SELECT county FROM ziplist WHERE state='" & mystate & "' GROUP BY county"

Would be the corrected query. If that doesn't work you have a problem else were.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

One other thing I do and I'll admit I don't know why since I've been doing it so long is:

Con.CursorLocation = CursorLocationEnum.adUseClient

So you might add that as well if the other change isn't enough.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

Oops. Really need an edit button. Like I said some times it is picky so you might need to add a semicolon at the end of the sql to signify the end of the statement. It doesn't always need it, but sometimes I've had problems if I didn't.

sql = "SELECT county FROM ziplist WHERE state='" & mystate & "' GROUP BY county;"

-I hate Microsoft!
-Forever and always forward.