1,105,177 Community Members

The Parameter '@AAA" must be defined error?

Member Avatar
savedlema
Junior Poster
104 posts since Sep 2012
Reputation Points: 8 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
savedlema
Junior Poster
104 posts since Sep 2012
Reputation Points: 8 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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)

Member Avatar
pritaeas
mod_pritaeas
11,298 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,833 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
Reverend Jim
Noli mentula
5,406 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 647 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

You can find sample code here

Member Avatar
savedlema
Junior Poster
104 posts since Sep 2012
Reputation Points: 8 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
Reverend Jim
Noli mentula
5,406 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 647 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
1
 

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.

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
1
 

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)
Member Avatar
savedlema
Junior Poster
104 posts since Sep 2012
Reputation Points: 8 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

There is a success. Thank you all. SOLVED.

Question Answered as of 1 Year Ago by Reverend Jim, pritaeas and TnTinMN
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article