0

I have some wierd exception.
I have Access database with 2 tables, one named MYSB_DB and the other Employee.

I'm using the code below when I want to filter on something like that:

Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String = String.Empty ' 
Dim pName As String = String.Empty
Dim fName As String = String.Empty
Dim ColpName As String = String.Empty
Dim ColfName As String = String.Empty
Dim ch As Integer = InStr(1, cmbEmployees.Text, " ", CompareMethod.Text)

pName = cmbEmployees.Text.Substring(0, ch)
fName = cmbEmployees.Text.Substring(ch, cmbEmployees.Text.Length - ch)
ColpName = "שדה1"
ColfName = "שדה2"
sql = "SELECT * FROM Employee WHERE [" & ColpName & "]=" & pName & ";"
da = New OleDb.OleDbDataAdapter(sql, Me.EmployeeTableAdapter.Connection.ConnectionString)
da.Fill(ds, Me.MYSB_DataBaseDataSet1.Employee.TableName)

I only change between the tables name in the code.
When I'm using this code for MYSB_DB table, the code is running well, but when I'm using the code for Employee table, I have an exception.

Any ideas why it's happening?

GSerg
  • 76,472
  • 17
  • 159
  • 346
I Lavi
  • 61
  • 4
  • 4
    well what is the exception and what debugging says,which line? – Navoneel Talukdar Apr 03 '16 at 09:04
  • 2
    Please [do not](http://stackoverflow.com/q/542510/11683) do [that](http://stackoverflow.com/q/332365/11683). This will also solve your problem. – GSerg Apr 03 '16 at 09:42
  • Hey Neel. the exception occured on da.Fill(ds, Me.MYSB_DataBaseDataSet1.Employee.TableName). and the the exception is (I have VS hebrew version, so I'll try to translate: "No value given for one or more parameters" THanks! – I Lavi Apr 03 '16 at 10:43
  • Hey Gserg and thank you! for now I chnaged the sql command to " sql = "SELECT * FROM Employee WHERE " & ColpName & " = '" & pName & "';" and it works fine. thanks – I Lavi Apr 03 '16 at 10:53

1 Answers1

0

pName is a string, so it must be quoted:

sql = "SELECT * FROM Employee WHERE [" & ColpName & "]='" & pName & "';"
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Hey Gustav. in the end I did so and it worked. thanks! – I Lavi Apr 03 '16 at 11:06
  • While this solves the immediate problem, I'm tempted to downvote for promoting [bad things](http://stackoverflow.com/q/332365/11683). – GSerg Apr 03 '16 at 14:29
  • @GSerg: I know. However, here `pName` is derived from a combobox we may assume the questioneer is in control of. – Gustav Apr 03 '16 at 14:40