Can somebody help me here? i have managed to import excel data into a datagridview but am having problems saving and updating it. It is saving alright but when it comes to updating that's when i have problems.

here is my code:

If mysqldr.HasRows Then
mysqldr.Close()

newcommand = New MySqlCommand(" Update item_stock set Item_Name = '" &  Replace(itemname, "'", "''") & "' ,Sale_Price = "' & Retailprice &"'" , newconnection) newcommand.ExecuteNonQuery()
 
 Else
mysqldr.Close()
sqrs = "insert into item_stock(Item_Name,Vehiclename,VAT_Rate,Supp_Name,Binno,Gearboxtype,Superseedingno,Engineno,Partno,oemname,model,Qty,Buyingprice,Sale_Price)values('" & _
 Replace(itemname, "'", "''") & "','" & vname & "',16,'" & suppname & "'" )"
 newcommand = New MySqlCommand(sqrs, newconnection)
newcommand.ExecuteNonQuery()
Me.ProgressBar1.Increment(id)
End If
id = id + 1
ProgressBar1.Maximum = DataGridView1.RowCount - 1

if somebody can help me out i would really appreciate it.Please......... :-)

Always use parameterized queries.

Sample:

Dim cn As New MySqlConnection("Server=localhost;Database=testdb;Uid=root;Pwd=;")
        Dim cmd As New MySqlCommand
        cmd.CommandText = "update testTable set col1=?p1, col2=?p2 where col3=?p3"
        cmd.Connection = cn
        cmd.Parameters.AddWithValue("?p1", value1)
        cmd.Parameters.AddWithValue("?p2", value2)
        cmd.Parameters.AddWithValue("?p3", value3)
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

Always use parameterized queries.

Sample:

Dim cn As New MySqlConnection("Server=localhost;Database=testdb;Uid=root;Pwd=;")
        Dim cmd As New MySqlCommand
        cmd.CommandText = "update testTable set col1=?p1, col2=?p2 where col3=?p3"
        cmd.Connection = cn
        cmd.Parameters.AddWithValue("?p1", value1)
        cmd.Parameters.AddWithValue("?p2", value2)
        cmd.Parameters.AddWithValue("?p3", value3)
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

This approach works but my problem is that i want to search for the values in the database, if they exist i would like to update them but if they dont then i would like to insert them.Thanks for your help.

What I am suggesting that you have to rewrite code using Parameter. If you're stuck post what you have.

Thanks man, i have done that and it has actually worked! Cheers Man! thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.