-1

I searched on some codes on how to do a Search Button in VB.net. But somehow, it won't work because of an error. And simply because, I cannot understand its algorithm and function. Newbie here. Anyway, here is the code for the search button:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    myConnection.Open()
    crd.Clear()
    fn.Clear()
    ln.Clear()
    Dim str As String
    str = "SELECT * FROM tblReg WHERE (Code = '" & src.Text & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader
    While dr.Read()
        crd.Text = dr("crd").ToString
        fn.Text = dr("fName").ToString
        ln.Text = dr("lName").ToString
    End While
    myConnection.Close()
End Sub

And the error was on:

dr = cmd.ExecuteReader

And VB said:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Gaaaaaab
  • 99
  • 8
  • Possible duplicate: http://stackoverflow.com/questions/18112264/c-sharp-oledb-exception-no-value-given-for-one-or-more-required-parameters-whi – IronAces Dec 07 '16 at 08:48
  • @DanielShillcock The OP already has the single quotes around the value. – GSerg Dec 07 '16 at 08:50
  • 1
    @GSerg And if `src.Text` contains an apostrophe? ;-) OP should be using parameterised queries. – IronAces Dec 07 '16 at 08:51
  • 2
    Even worse than an apostrophe was `'; Drop Table tblReg;` – Tim Schmelter Dec 07 '16 at 08:53
  • @DanielShillcock Then it would be an invalid SQL expression error, unless `src.Text` [also contains `--`](http://stackoverflow.com/q/332365/11683), but then there would be no error at all. They should be using [parameterized queries](http://stackoverflow.com/q/542510/11683), it's just that particular question is not the relevant one. – GSerg Dec 07 '16 at 08:54
  • @GSerg Perhaps, but if you read the upvoted comment in that question, it's well worthy of mention. – IronAces Dec 07 '16 at 08:55
  • If `src.Text` is numeric, you must remove the apostrophe here `Code = '" & src.Text & "'` then will be `Code = " & src.Text & "` – Tyler Dec 07 '16 at 08:56
  • So uh, what do I do? I changed it, but then, I still got errors. – Gaaaaaab Dec 07 '16 at 09:42

2 Answers2

0

One should not follow online tutorials that teach very bad code. That code is very bad because it contains SQL injection and leaves database objects opened.

You should rewrite your code as follows:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    myConnection.Open()
    crd.Clear()
    fn.Clear()
    ln.Clear()

    Using cmd = New OleDbCommand("SELECT * FROM tblReg WHERE Code = ?", myConnection)
        cmd.CommandType = CommandType.Text

        With cmd.Parameters.Add(Nothing, OleDbType.VarChar, 50)
            .Direction = ParameterDirection.Input
            .Value = src.Text
        End With

        Using dr = cmd.ExecuteReader()
            While dr.Read()
                crd.Text = dr("crd").ToString
                fn.Text = dr("fName").ToString
                ln.Text = dr("lName").ToString
            End While
        End Using
    End Using

    myConnection.Close()
End Sub

You have to use question marks in place of parameters because you are using OleDbCommand that does not support named parameters.

Change OleDbType.VarChar to your actual column type.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
-1

Is this the link where you get the code?

http://www.visual-basic-tutorials.com/ReadFromAccess.htm

Kindly do not get the code read each data on the output shows and also check this part of the code.

 crd.Text = dr("crd").ToString
        fn.Text = dr("fName").ToString
        ln.Text = dr("lName").ToString

are you sure crd,fname,lname are the name of your fields in your table? pls check it and also what is the field type of code? is it a text or INT that is Auto Increment? or just an INT? no matter what it is change your code.

from

str = "SELECT * FROM tblReg WHERE (Code = '" & src.Text & "')"

to

str = "SELECT * FROM tblReg WHERE Code =" & src.Text

Updated

I suggest better read or follow the whole instruction based on link where you get your code. I suggest do the same as what the link said create the same and when the program runs with no error then incorporate it with your program beacuse I tried it using VB.NET and Access and it worked Im sure you dont read it. Do this and Im sure you will not just get the code you need you will also learn.

Shadow Fiend
  • 351
  • 6
  • 18