CODEWITHSUNDEEP
mysqlvb.net
James Stafford
James Stafford

Reputation: 1054

save mysql results to datatable

I have just switched to using parameters for mysql queries and fear I can not find how to save my results to a data table. I want to save the results of this select query to datatable "table". Previously I would have done 

adptr = New MySqlDataAdapter("SELECT * from users WHERE uname='" & MySqlHelper.EscapeString(UsernameTextBox.Text) & "' and pword=md5('" & MySqlHelper.EscapeString(PasswordTextBox.Text) & "')", con)
    Try
        adptr.Fill(table)
    Catch err As Exception
        Dim strError As String = "Exception: & err.ToString()"
    End Try

now I have

    Public table As New DataTable
    Dim cb As New MySqlConnectionStringBuilder
    cb.Database = dbfile
    cb.Server = dbserver
    cb.UserID = dbuser
    cb.Password = dbpassw

    Using cnn As New MySqlConnection(cb.ConnectionString)
        Using cmd As New MySqlCommand("SELECT * from users WHERE uname=&uname and pword=md5(&pword)", cnn)
            cmd.Parameters.AddWithValue("@uname", UsernameTextBox.Text)
            cmd.Parameters.AddWithValue("@pword", PasswordTextBox.Text)

            Try
                cnn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Using
    End Using

how do I save the results to table?

Upvotes: 1

Views: 2993

Answers (1)

Steve
Steve

Reputation: 216243

Use the Command to initialize the adapter and then work as before

Using cnn As New MySqlConnection(cb.ConnectionString)
    Using cmd As New MySqlCommand("SELECT * from users WHERE uname=&uname and pword=md5(&pword)", cnn)
        cnn.Open
        cmd.Parameters.AddWithValue("&uname", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("&pword", PasswordTextBox.Text)
        adptr = New MySqlDataAdapter(cmd)
        adptr.Fill(table)
    End Using
End Using

ExecuteNonQuery should be used for queries that change the database table (INSERT/UPDATE/DELETE) or other DDL commands. In your case the prepared command could be used for one of the overladed constructor of the MySqlDataAdapter (Look at this docs for SqlDataAdapter they have the same logical effect)

Also I am not sure that the MySqlParameter prefix is & (I have always used @ or ?)

Upvotes: 2

Related Questions