CODEWITHSUNDEEP
vb.net
Boneyt
Boneyt

Reputation: 73

No value given for one or more required parameter vb.net

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

Answers (2)

Tim Schmelter
Tim Schmelter

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

fofik
fofik

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

Related Questions