J.Don J.Don - 3 months ago 7
MySQL Question

Insert data to MySql and display in datagridveiw

Recently i developed a pos but i have an problem inserting the data and displaying the names of the table in datagrid view
here is some code :

Dim Query As String
Query = "insert into baza.artikli(barkod,naziv,kupovna,prodazna,kolicina,proizvoditel,opis) values ('" & TextBoxBarkod.Text & "','" & TextBoxNaziv.Text & "','" & kupovnacena & "','" & prodaznacena & "','" & kolicina & "','" & TextBoxProizvoditel.Text & "','" & TextBoxOpis.Text & "')"
COMMAND = New MySqlCommand(Query, konekcija)
READER = COMMAND.ExecuteReader



MessageBox.Show("Артиклот е успешно внесен !")
TextBoxBarkod.Text = ""
TextBoxKupovna.Text = ""
TextBoxNaziv.Text = ""
TextBoxOpis.Text = ""
TextBoxProdazna.Text = ""
TextBoxProizvoditel.Text = ""
TextBoxKolicina.Text = ""
konekcija.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
konekcija.Dispose()


And i tried:

Private Sub prikazitabela()

konecija = New MySqlConnection
konecija.ConnectionString =
"server=localhost;userid=root;password=root;database=bazaartikli123"
Dim SDA As New MySqlDataAdapter
Dim bazaDataSet As New DataTable
Dim bajndsors As New BindingSource

Try
konecija.Open()
Dim Query As String
Query = "select barkod as 'Баркод',naziv as 'Назив на артикал',kupovna as 'Куповна цена',prodazna as 'Продажна цена',opis as'Опис',ddv as 'ДДВ',makproizvod as 'Македонски прозивод' from bazaartikli123.artikli"
COMMAND = New MySqlCommand(Query, konecija)
SDA.SelectCommand = COMMAND
SDA.Fill(bazaDataSet)
bajndsors.DataSource = bazaDataSet
DataGridView1.DataSource = bajndsors
SDA.Update(bazaDataSet)
konecija.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
konecija.Dispose()
End Try

End Sub`

Answer

There are several things that can be improved, starting with using SQL parameters and executing your query:

Dim Query As String = <sql>
                         insert into baza.artikli 
                            (barkod,naziv,kupovna,prodazna,kolicina,proizvoditel,opis) 
                        values (@p1,@p2,@p3,@p4,@p5,@p6,@p7 )
                      </sql>.Value

Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(Query, dbcon)

        ' I dont really know what datatype these really are
        cmd.Parameters.Add("@p1", MySqlDbType.VarChar).Value = TextBoxBarkod.Text
        cmd.Parameters.Add("@p2", MySqlDbType.DateTime).Value = DTPNaziv.Value
        cmd.Parameters.Add("@p3", MySqlDbType.Int32).Value = Convert.ToInt32(Textkupovna.Text)
        ' ...
        dbcon.Open()
        ' this was missing: 
        cmd.ExecuteNonQuery()
    End Using

    dt = New DataTable
    Using cmd As New OleDbCommand("SELECT * FROM baza.artikli")

        dt.Load(cmd.ExecuteReader())
        dgv2.DataSource = dt
    End Using
End Using
  • I used an XML literal for the SQL mainly to avoid scrolling here, but it can make your code much more readable
  • There is little context for the code in the question, but connections ought be created as needed; DBCommand objects are highly query specific, so they too ought be created as needed rather than using global ones.
  • Use Using blocks to assure that DbConnections and other objects with a Dispose() method are properly disposed. The code closes, but does not Dispose of the connection.
  • Use SQL Parameters always. These assure the correct data type is passed, avoid the cruft of " Foo -'" & foovar & "' AND ..." in code, protect against special characters in strings as well as prevent SQL injection attacks.
  • Then cmd.ExecuteNonQuery() performs the insert.

After that, you can run a new query to get whatever data you want to display. Note that you do not need to create a DataAdapter to fill a table. It is not clear what you want to display, so that will also have to be modified for what you want.

When AutoGenerateColumns is True, they will be created when you set the datasource and the column names (== 'names of the table' ?) will automatically show. If you want different text to display for the headers, you can either set them manually or uses aliases for them in your SQL, as shown on a previous answer