The Parameter '@AAA" must be defined error?

[savedlema]

Hi!
I just learnt to use the "Parameters.AddWithValue" to prevent harm of sql injections.However, I have only been able to do this with the INSERT, but I have trouble using that with SELECT and UPDATE. Please Look at my code with UPDATE

        Try
            ConnectionSettings()
            con.Open()

            Dim SqlUpdate As String

            SqlUpdate = "UPDATE tblStudents SET Surname = @Surname"
            cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)

            cmd = New MySqlCommand(SqlUpdate, con)
            cmd.ExecuteNonQuery()

            MsgBox("Updated")
            con.Close()

        Catch ex As MySqlException
            MsgBox("Error " & ex.Message)
        End Try

The cmd.ExecuteNonQuery() was highlighted and I can also see the error msg "The Parameter '@Surname' must be defined", I thought I had defined it by cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)? I get the same with SELECT statement.I've tried a Google search to no avail.

Any help please?

Thanks.

[savedlema]

Update:
I have been able to solve this by adding:

     cmd = New MySqlCommand("", con)
    cmd.CommandText = "UPDATE tblStudents SET Surname = @Surname

But I would love some explanation please (I'm new to VB+Mysql)

[pritaeas]

The cmd.Parameters can only be used if the cmd has a query (CommandText) set which defines that parameter. In your OP you used a string for your query, instead of the CommandText.

[Reverend Jim]

You can find sample code here

[savedlema]

Thanks all..Then, why wouldn't this work? I want to fill the SELECT results to the dataset, and then fetch them from the dataset to textboxes..but this piece of code...

       ConnectionSettings()
        con.Open()
        Dim sql As String
        Dim ds As DataSet = New DataSet
        Dim da As MySqlDataAdapter
        Dim RowsNo As Integer

        cmd = New MySqlCommand("", con)
        sql = "SELECT * FROM tblStudents WHERE StudentID = @studentid"
        cmd.CommandText = sql

        cmd.Parameters.AddWithValue("@studentid", txtStudentID.Text)
        cmd.ExecuteNonQuery()

        da = New MySqlDataAdapter(sql, con)
        da.Fill(ds, "Students")

Does not work. With this, I get the error "The parameter @StudentID must be defined"

If I comment out:

            'da = New MySqlDataAdapter(sql, con)
            'da.Fill(ds, "Students")

I get no error msg (I assume no problem up to that code), even when I add Msgbox ("Success") after the 'cmd.ExecuteNonQuery() 'Sucess' will be displayed, meaning there is no problem to that line. I think I need a little help on how to put the results of the above SELECT to a dataset (MySQL)
Thanks all.

[Reverend Jim]

If you are using an oledb interface then you have to use "?" when adding values as in

'OLEDB is more generic (can be used for different data sources) and supports
'parameters but they are unnamed and positional

ListView1.Items.Clear()

Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=Yes;")
Dim cmd As New OleDbCommand("", con)

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname like ?"
cmd.Parameters.Add("@pan", OleDbType.VarChar).Value = "D%"

con.Open()
Dim rdr As OleDbDataReader = cmd.ExecuteReader()

Do While rdr.Read
    ListView1.Items.Add(New ListViewItem({rdr.GetString(0), rdr.GetString(1), rdr.GetString(2)}))
Loop

rdr.Close()
con.Close()

OleDb does not support named parameters. You can see the difference between SqlClient and OleDb here. That's the same sample code I posted a link to just above.

[TnTinMN]

You have mixed together various statements that are not necessarily related.

     ConnectionSettings() ' this does what?

     ' I assume that con is a MySqlConnection defined elsewhere
    ' don't need to open connect, the dataadapter will handle open/close
    'con.Open()

    Dim sql As String

    ' you do realize that this dataset will only exist in the context of this code block?
    Dim ds As New DataSet
    Dim da As MySqlDataAdapter

    Dim RowsNo As Integer ' purpose for this ?

    Dim cmd as New MySqlCommand()
    cmd.Connection = con

    sql = "SELECT * FROM tblStudents WHERE StudentID = @studentid"
    cmd.CommandText = sql
    cmd.Parameters.AddWithValue("@studentid", txtStudentID.Text)

    ' why this is here I have no clue
    'cmd.ExecuteNonQuery()

    ' feed the dataadapter the command
    dim da as New MySqlDataAdapter(cmd)

    ' use the adapter to create and fill the table Students
    da.Fill(ds, "Students")

    msgbox(ds("Students").Rows.count.ToString)

[savedlema]

There is a success. Thank you all. SOLVED.