2

I am trying to search the data in a simple access database. The code is this

Call connect()
con.Open()
cmd = New OleDbCommand("Select * from PO_Record_Table where Customer=asd", con)
dr = cmd.ExecuteReader
While dr.Read
    MsgBox(dr(1))
End While

con.Close()

If I press search button, the error: No value given for one or more required parameters comes on this line

dr = cmd.ExecuteReader

The record "asd" is in the database customer field as a text type. Why does this error come and how to finish this search without error?

Moxy
  • 4,162
  • 2
  • 30
  • 49
Ahmed Faizan
  • 446
  • 5
  • 12
  • 22

2 Answers2

5

I suppose that the error is in the text asd passed as value for the customer name

cmd = New OleDbCommand("Select * from PO_Record_Table where Customer='asd'", con)

Putting it in single quotes allows the db engine to recognize it as a string value to check against the Customer column name. Without quotes it is interpreted as the name of a parameter that you haven't passed to the command.

EDIT In your comment below you try to pass, as value for the Customer column, the content of a textbox but you forget to add the quotes around your textbox text value. 

cmd = New OleDbCommand("Select * from PO_Record_Table where Customer='" & textbox.text & "'", con)

However, this should never be done using the string concatenation method, but always with the parameterized approach

cmd = New OleDbCommand("Select * from PO_Record_Table where Customer=@custName", con)
cmd.Parameters.AddWithValue("@custName", Txt_Find.Text)
dr = cmd.ExecuteReader
.....

This is the only good method to query a database passing a command text with values obtained by user input. This method allows your code to be safe from SQL Injection attacks and remove problems in parsing the content of the textbox. Infact, without a parameter and if your textbox contains a single quote, the string concatenation method would fail with a syntax error.

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • 1
    The error comes again if I do this any help on how to solve? cmd = New OleDbCommand("Select * from PO_Record_Table where Customer=" & Txt_Find.Text, con) – Ahmed Faizan Dec 21 '13 at 19:48
  • 1
    Again, when you want to check the value of a column of type text against a known value you need to encapsulate the value inside a couple of single quotes. But wait a minute and I will update with a more precise answer – Steve Dec 21 '13 at 19:50
  • You have answered the second one aswell. Thankyou steve. Also I will learn the new method soon – Ahmed Faizan Dec 21 '13 at 20:11
2

No value given for one or more required parameters Error comes When your Tablename or filed name not exist , check PO_Record_Table and Customer spelling from your database

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77