how to add rows to mysql database table and display it in a listbox in vb.net?

Question

I have the following controls:

textbox1 - this is where i type a message.
listbox1 - this is where i display the messages.
button1 - this is what posts my messages to the server.
StatusStrip1.Title1 - this is where the current user's title will go. (i.e: Administrator, manager... etc.)

I have a table on a MYSQL database called "messages" and a column called "message". When i type something into textbox1 and click button1 i want to add a row to "messages" under the column "message" with the title and message as its value.(separated by a hyphen, dash, or other small delimiter)Then reload listbox1's contents to show the new message.

so i want the final message to be something like:

Administrator - Hello World!

I currently have the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sqlpost As MySqlCommand = New MySqlCommand("INSERT INTO messages(message) VALUES(?name - ?message)"";", con)
    sqlpost.Parameters.AddWithValue("?name", Title2.Text)
    sqlpost.Parameters.AddWithValue("?message", TextBox1.Text)
    Try
        con.Close()
        con.Open()

        ' Sending message.
        If TextBox1.Text = "" Then
            MessageBox.Show("You forgot to type a message!")
        Else
            sqlpost.ExecuteNonQuery()

            ' Reloading messages.

            ListBox1.Items.Clear()
            reader = sqlmessages.ExecuteReader()
            While (reader.Read)
                ListBox1.Items.Add(reader.Item("message"))
                ListBox1.Items.Add("")

            End While
            reader.Close()
            con.Close()
            TextBox1.Text = ""
            Label4.Text = "Message Sent!"
            Timer2.Start()
        End If

    Catch myerror As MySqlException
        MessageBox.Show("Error sending message to server: " & myerror.Message)
    End Try

End Sub

I had it working before, but when i made changes to it, it came up with various sql statement syntax errors... (stuff like invalid truncated value of "Administrator", or invalid DOUBLE value "hello") and now it won't even display any messages currently in the "messages" table...)

If anyone could tell me what i'm doing wrong, or a more efficient way of doing this then i would greatly appreciate it! Thanks!

Kibbee · Accepted Answer · 2011-09-25 01:57:09Z

2

One thing I see that looks incorrect is that you are using "?" for you parameter names in the query. The default character to use for parameter names is to start them with "@". Although I think there's a connection string option to use "?" as that is what was used in the older versions of the MySQL connector, but the current default is "@". Also, there seems to be some problems with your command in general. You have:

Dim sqlpost As MySqlCommand = New MySqlCommand("INSERT INTO messages(message) VALUES(?name - ?message)"";", con)

it should probably be something more like

Dim sqlpost As MySqlCommand = New MySqlCommand("INSERT INTO messages(message) VALUES(@message);", con)

I fixed up the query a bit, and replaced the ? with @. You you can do the concatenation of name and message (as you cleared up in the comment) in VB, with some code such as this:

sqlpost.Parameters.AddWithValue("@message", Title2.Text & " - " & Title2.Text)

I also see that you are calling

sqlmessages.ExecuteReader()

but I don't see where this is being initialized.

edited Sep 25, 2011 at 1:57

answered Sep 25, 2011 at 1:35

Kibbee

66.2k28 gold badges145 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

daniel11 Over a year ago

interesting, although my intention was to make the final message a combination of the persons title (represented by the name parameter) and the message they typed (represented by the message parameter) and separate them with a "-" character. so if the title was "admin" and the message was "hello world" i want the final message that is displayed to be "admin - hello world"

KV Prajapati Over a year ago

@Kibbee - MySql connector don't use @ syntax to assign parameter name.

Kibbee Over a year ago

See my recent changes for how to accomplish this.

Kibbee Over a year ago

@AVD Yes, the MySQL connector does use @ for parameter names, unless you are using the "OldSyntax" option in your connection string, as outlined here. dev.mysql.com/doc/refman/5.1/en/… Or you are using a really old version of the connector. Reading further the OldSyntax paramter isn't even supported anymore.

KV Prajapati · Accepted Answer · 2011-09-25 01:54:58Z

0

You should have to use single parameter.

Dim con as New MySqlConnection
con.ConnectionString="set_connection_string_here"
Dim sqlpost As MySqlCommand = New MySqlCommand("INSERT INTO `messages` (`message`) VALUES (@name)",con)
sqlpost.Parameters.AddWithValue("@name", Title2.Text & " - " & TextBox1.Text)

If TextBox1.Text = "" Then
    MessageBox.Show("You forgot to type a message!")
Else
  con.Open()
  sqlpost.ExecuteNonQuery()
  con.Close()

  ListBox1.Items.Clear()
  sqlmessages.CommandText="select * from `messages`"
  sqlmessages.Connection=con
  reader = sqlmessages.ExecuteReader()
  While (reader.Read)
          ListBox1.Items.Add(reader.Item("message"))
  End While
  reader.Close()
  con.Close()
End If

answered Sep 25, 2011 at 1:54

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Collectives™ on Stack Overflow

how to add rows to mysql database table and display it in a listbox in vb.net?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related