Reputation: 73
I have the following code in a vb form
Dim dt As New DataTable
Dim query As String = " select [incident id] as incidentid, ([incident ID] &' '&[incident date]) as incisearch from incident where [stock supplier] =hengwei"
Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kevin\Desktop\Non Conformance\NonConformance.accdb")
Using command As New OleDbCommand(query, connection)
Using adapter As New OleDbDataAdapter(command)
connection.Open()
adapter.Fill(dt)
connection.Close()
End Using
End Using
End Using
If dt.Rows.Count > 0 Then
lb_search.DataSource = dt
lb_search.DisplayMember = "incisearch"
lb_search.ValueMember = "incidentid"
End If
I have taken the query string
select [incident id] as incidentid, ([incident ID] &' '&[incident date]) as incisearch
from incident
where [stock supplier] = hengwei
And ran this directly as a query in Access which returns the results as expected so I know the SQL is correct
However I am getting the following error message
No value given for one or more required parameters.
And the following line is highlighted
adapter.Fill(dt)
Any ideas what's causing this?
Upvotes: 1
Views: 822
Reputation: 460380
I guess hengwey
should be 'hengwey'
:
SELECT [incident id] AS incidentid,
[incident id] + ' ' + [incident date] AS incisearch
FROM incident
WHERE [stock supplier] = 'hengwei'
I have also changed
([incident ID] &' '&[incident date])
to
[incident id] + ' ' + [incident date]
since SQL is not VB.NET (but maybe that works in Access).
If hengwey
is actually dynamic you should use sql-parameters to prevent sql injection:
Dim query As String = "SELECT [incident id] AS incidentid, [incident id] + ' ' + [incident date] AS incisearch FROM incident WHERE [stock supplier] = @hengwei "
' ... '
Dim p As New OleDbParameter("@hengwei", OleDbType.VarChar, 100)
p.Value = txt_supplier.text
command.Parameters.Add(p)
' ... '
Upvotes: 1
Reputation: 1008
You should write your string between double quotes, otherwise access thinks it is a parameterized query. And in your case you should change
where [stock supplier] =hengwei
to
where [stock supplier] = "hengwei"
If you want to use hengwei as a parameter not a constant you should add parameters to your data adapter, like this
adapter.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
For more information you can use this link
Upvotes: 0