Inserting data into a MySQL table using VB NET

Question

I have a problem inserting data into a table on a MySQL database using a VB NET application   I have a simple form where when I set some data to the textboxes and I press a GO button  the code should execute a function called InsertCar   that takes all these values and insert them into the database and then return true if the transaction is done successfully or false otherwise  My problem is that nothing is being inserted into the table   Imports MySql Data MySqlClient Imports System Data Sql Imports System Imports System Data  Public Class Form1  Dim connectionString As String    Server localhost  User Id root  Password 123456  Database uni park db  Dim SQLConnection As MySqlConnection   New MySqlConnection Dim oDt sched As New DataTable      SOME CODE For other buttons       Code for a button where the InsertCar   function is called at the beginning     Public Function InsertCar   As Boolean      SQLConnection   New MySqlConnection       SQLConnection ConnectionString   connectionString     Dim sqlCommand As New MySqlCommand     Dim str carSql As String      Try         str carSql    insert into members car  car id  member id  model  color  chassis id  plate number  code  values       TextBox20 Text           TextBox20 Text           TextBox23 Text           TextBox24 Text           TextBox22 Text           TextBox21 Text           ComboBox1 SelectedItem                MsgBox str carSql          sqlCommand Connection   SQLConnection         sqlCommand CommandText   str carSql         sqlCommand ExecuteNonQuery            Return True      Catch ex As Exception         Return False         MsgBox  Error occured  Could not insert record       End Try  End Function   End Class   I am using this MsgBox str carSql  to test if the SQL statement is correct and it is correct   Any help will be appreciated   UPDATE  I did the following and it stills not working  Public Function InsertCar   As Boolean      SQLConnection   New MySqlConnection       SQLConnection ConnectionString   connectionString     SQLConnection Open       Dim sqlCommand As New MySqlCommand     Dim str carSql As String      Try          str carSql    insert into members car  car id  member id  model  color  chassis id  plate number  code  values   id  m id  model  color  ch id  pt num  code           sqlCommand Connection   SQLConnection         sqlCommand CommandText   str carSql         sqlCommand Parameters AddWithValue   id   TextBox20 Text          sqlCommand Parameters AddWithValue   m id   TextBox20 Text          sqlCommand Parameters AddWithValue   model   TextBox23 Text          sqlCommand Parameters AddWithValue   color   TextBox24 Text          sqlCommand Parameters AddWithValue   ch id   TextBox22 Text          sqlCommand Parameters AddWithValue   pt num   TextBox21 Text          sqlCommand Parameters AddWithValue   code   ComboBox1 SelectedItem          sqlCommand ExecuteNonQuery            Return True      Catch ex As Exception         Return False         MsgBox  Error occured  Could not insert record       End Try  End Function   UPDATE  The Insert is not working either  I will post the entire code maybe a problem is found elsewhere  Imports MySql Data MySqlClient Imports System Data Sql Imports System Imports System Data  Public Class Form1  Dim connectionString As String    Server localhost  User Id root  Password 123456  Database uni park db  Dim SQLConnection As MySqlConnection   New MySqlConnection Dim oDt sched As New DataTable    Private Sub Button1 Click ByVal sender As System Object  ByVal e As System EventArgs  Handles Button1 Click      Try           If SQLConnection State   ConnectionState Open Then             If TextBox1 Text      Then                 MsgBox  Please Input a Valid ID               Else                 Dim myAdapter1 As New MySqlDataAdapter  select m work date as Work  m time in as Start  m time out as End from university members as u inner join members schedule as m on u members schedule id m members schedule id where member id      amp  TextBox1 Text  SQLConnection                  Dim myAdapter As New MySqlDataAdapter  select member id  first name  last name  type from university members  members schedule where university members members schedule id   members schedule members schedule id  AND member id      amp  TextBox1 Text  SQLConnection                  Dim mydatatable As New DataTable                   Dim dataset As New DataSet                   myAdapter Fill mydatatable                   If  mydatatable Rows Count  gt  0 And myAdapter1 Fill dataset   Then                     TextBox2 Text   mydatatable Rows 0  Item  first name                       TextBox3 Text   mydatatable Rows 0  Item  last name                       TextBox4 Text   mydatatable Rows 0  Item  type                       TextBox20 Text   mydatatable Rows 0  Item  member id                       DataGridView1 DataSource   dataset Tables 0                      oDt sched   dataset Tables 0                   Else                     MsgBox  Check Error  ID Not Found  Enter a Valid ID                       TextBox1 Text    Example 123456                       TextBox2 Text                           TextBox3 Text                           TextBox4 Text                       End If             End If         Else             MsgBox  Database Connection Error  Database Connection Not Established  Please Connect First            End If        Catch ex As Exception         MsgBox ex ToString       End Try   End Sub    Private Sub ExitToolStripMenuItem Click ByVal sender As System Object  ByVal e As System EventArgs  Handles ExitToolStripMenuItem Click     Application Exit   End Sub   Private Sub DatabaseConnectToolStripMenuItem Click ByVal sender As System Object  ByVal e As System EventArgs  Handles DatabaseConnectToolStripMenuItem Click      SQLConnection   New MySqlConnection       SQLConnection ConnectionString   connectionString     Try          If SQLConnection State   ConnectionState Closed Then             SQLConnection Open               MsgBox  Database Connection Sccessfully Established           Else             SQLConnection Close               MsgBox  Database Connection Terminated            End If     Catch ex As Exception         MsgBox ex ToString       End Try  End Sub    Private Sub Button4 Click ByVal sender As System Object  ByVal e As System EventArgs  Handles Button4 Click      TextBox1 Text    Example 123456       TextBox2 Text           TextBox3 Text           TextBox4 Text           DataGridView1 Columns Clear       DataGridView1 DataSource   Nothing   End Sub   Private Sub Button2 Click ByVal sender As System Object  ByVal e As System EventArgs  Handles Button2 Click       Dim str sql As String          Dim obj msadapter As MySqlDataAdapter     Dim i maxh As Integer     Dim i beginh As Integer     Dim ods avail As DataSet   New DataSet       Dim str err As String          Dim i strth As Integer     Dim odt avail As New DataTable       Dim odrcol avail   As DataRow     Dim str range As String           Try           perform insert car here  boolean to see if the code continues running                                               If InsertCar   Then             For Each odr As DataRow In oDt sched Rows                 i maxh   odr 2                  i beginh   odr 1                  i strth   odr 1    2                 str range                       str sql    select   from parked cars where pwork date     amp  odr 0  ToString    amp                       ods avail   New DataSet                   obj msadapter   New MySqlDataAdapter str sql  SQLConnection                  obj msadapter Fill ods avail                   odt avail   ods avail Tables 0                   If odt avail Rows Count  lt  210 Then                     While  i strth  lt   i maxh                          odrcol avail   odt avail Select  ptime in      i beginh ToString        and ptime out      i strth ToString                                   If odrcol avail Count  lt  30 Then                             str range    i beginh ToString           i strth ToString                           Else                             str range     0                          End If                          i strth    2                         i beginh    2                     End While                      FillSpots str range  odr 0  ToString                    Else                     str err     no place on day      odr 0  ToString                             MsgBox  No place is found on this day                   End If              Next         End If     Catch ex As Exception         MsgBox          End Try End Sub  Public Function FillSpots ByVal blowf As String  ByVal  day As String  As Boolean      Dim str unit As String     Dim i count As Integer   0     Dim str i strt As String     Dim str i end As String     Dim str sql As String      Try         For Each str unit In blowf Split  0               If str unit  lt  gt     Then                 str i strt   str unit Split      0                  str i end   str unit Split      str unit Split      Length - 1                  str sql    insert into parked cars values       TextBox20 Text            day           str i strt           str i end                    End If         Next          Return True     Catch ex As Exception         Throw ex     End Try End Function   Public Function InsertCar   As Boolean      SQLConnection   New MySqlConnection       SQLConnection ConnectionString   connectionString     SQLConnection Open       Dim sqlCommand As New MySqlCommand     Dim str carSql As String      Try          str carSql    insert into members car  car id  member id  model  color  chassis id  plate number  code  values   id  m id  model  color  ch id  pt num  code           sqlCommand Connection   SQLConnection         sqlCommand CommandText   str carSql         sqlCommand CommandType   CommandType Text         sqlCommand Parameters AddWithValue   id   TextBox20 Text          sqlCommand Parameters AddWithValue   m id   TextBox20 Text          sqlCommand Parameters AddWithValue   model   TextBox23 Text          sqlCommand Parameters AddWithValue   color   TextBox24 Text          sqlCommand Parameters AddWithValue   ch id   TextBox22 Text          sqlCommand Parameters AddWithValue   pt num   TextBox21 Text          sqlCommand Parameters AddWithValue   code   ComboBox1 SelectedItem          sqlCommand ExecuteNonQuery            Return True      Catch ex As Exception         Return False         MsgBox  Error occured  Could not insert record       End Try  End Function    Private Sub Button3 Click ByVal sender As System Object  ByVal e As System EventArgs  Handles Button3 Click      ComboBox1 ResetText       TextBox21 Text           TextBox22 Text           TextBox23 Text           TextBox24 Text           DataGridView2 Columns Clear       DataGridView2 DataSource   Nothing  End Sub   End Class    Thanks everyone for the help  This is the solution that worked for me      Dim iReturn as boolean     Using SQLConnection As New MySqlConnection connectionString          Using sqlCommand As New MySqlCommand               With sqlCommand                  CommandText    INSERT INTO members car   car id    member id    model    color    chassis id    plate number    code   values   xid  m id  imodel  icolor  ch id  pt num  icode                    Connection   SQLConnection                  CommandType   CommandType Text    You missed this line                  Parameters AddWithValue   xid   TextBox20 Text                   Parameters AddWithValue   m id   TextBox20 Text                   Parameters AddWithValue   imodel   TextBox23 Text                   Parameters AddWithValue   icolor   TextBox24 Text                   Parameters AddWithValue   ch id   TextBox22 Text                   Parameters AddWithValue   pt num   TextBox21 Text                   Parameters AddWithValue   icode   ComboBox1 SelectedItem               End With             Try                 SQLConnection Open                   sqlCommand ExecuteNonQuery                   iReturn   TRUE             Catch ex As MySqlException                 MsgBox ex Message ToString                 iReturn   False             Finally                 SQLConnection Close               End Try         End Using     End Using     Return iReturn

User · Answer

You need to open the connection first    SQLConnection Open

User · Answer

Dim connString as String   server localhost userid root password 123456 database uni park db  Dim conn as MySqlConnection connString  Dim cmd as MysqlCommand Dim dt as New DataTable Dim ireturn as Boolean  Private Sub Insert Car    Dim sql as String    insert into members car  car id  member id  model  color  chassis id  plate number  code  values   car id  member id  model  color  chassis id  plate number  code    Dim cmd   new MySqlCommand sql  conn       cmd Paramaters AddwithValue   car id   txtCar Text      cmd Paramaters AddwithValue   member id   txtMember Text      cmd Paramaters AddwithValue   model   txtModel Text      cmd Paramaters AddwithValue   color   txtColor Text      cmd Paramaters AddwithValue   chassis id   txtChassis Text      cmd Paramaters AddwithValue   plate number   txtPlateNo Text      cmd Paramaters AddwithValue   code   txtCode Text       Try         conn Open           If cmd ExecuteNonQuery    gt  0 Then             ireturn   True         End If           conn Close         Catch ex as Exception         ireturn   False         conn Close       End Try  Return ireturn  End Sub

User · Answer

You need to use  param instead of  param when performing queries to MySQL   str carSql    insert into members car  car id  member id  model  color  chassis id  plate number  code  values   id  m id  model  color  ch id  pt num  code           sqlCommand Connection   SQLConnection         sqlCommand CommandText   str carSql         sqlCommand Parameters AddWithValue   id   TextBox20 Text          sqlCommand Parameters AddWithValue   m id   TextBox20 Text          sqlCommand Parameters AddWithValue   model   TextBox23 Text          sqlCommand Parameters AddWithValue   color   TextBox24 Text          sqlCommand Parameters AddWithValue   ch id   TextBox22 Text          sqlCommand Parameters AddWithValue   pt num   TextBox21 Text          sqlCommand Parameters AddWithValue   code   ComboBox1 SelectedItem          sqlCommand ExecuteNonQuery     Change the catch block to see the actual exception     Catch ex As Exception          MsgBox ex Message               Return False      End Try

User · Answer

First  You missed this one  sqlCommand CommandType   CommandType Text Second  Your MySQL Parameter Declaration is wrong  It should be   and not     try this   Public Function InsertCar   As Boolean      Dim iReturn as boolean     Using SQLConnection As New MySqlConnection connectionString          Using sqlCommand As New MySqlCommand               With sqlCommand                  CommandText    INSERT INTO members car   car id    member id    model    color    chassis id    plate number    code   values   xid  m id  imodel  icolor  ch id  pt num  icode                    Connection   SQLConnection                  CommandType   CommandType Text    You missed this line                  Parameters AddWithValue   xid   TextBox20 Text                   Parameters AddWithValue   m id   TextBox20 Text                   Parameters AddWithValue   imodel   TextBox23 Text                   Parameters AddWithValue   icolor   TextBox24 Text                   Parameters AddWithValue   ch id   TextBox22 Text                   Parameters AddWithValue   pt num   TextBox21 Text                   Parameters AddWithValue   icode   ComboBox1 SelectedItem              End With             Try                 SQLConnection Open                   sqlCommand ExecuteNonQuery                   iReturn   TRUE             Catch ex As MySqlException                 MsgBox ex Message ToString                 iReturn   False             Finally                 SQLConnection Close               End Try         End Using     End Using     Return iReturn  End Function

User · Answer

your str carSql should be exactly like this     str carSql    insert into members car  car id  member id  model  color  chassis id  plate number  code  values   id  m id  model  color  ch id  pt num  code     Good Luck

User · Answer

After instantiating the connection  open it     SQLConnection   New MySqlConnection     SQLConnection ConnectionString   connectionString   SQLConnection Open     Also  avoid building SQL statements by just appending strings  It s better if you use parameters  that way you win on performance  your program is not prone to SQL injection attacks and your program is more stable  For example    str carSql    insert into members car                  car id  member id  model  color  chassis id  plate number  code                  values                   id  m id  model  color  ch id  pt num  code     And then you do this   sqlCommand Parameters AddWithValue   id  TextBox20 Text  sqlCommand Parameters AddWithValue   m id  TextBox23 Text    And so on       Then you call    sqlCommand ExecuteNonQuery

[mysql] Inserting data into a MySQL table using VB.NET

Examples related to mysql

Examples related to vb.net