{Error} Insert table access form the value string in VB.Net

[Vu Anh Tran]

I have a problem about insert. - error form visual studio 2008 Conversion from string "INSERT INTO T_inTem(maSKU,tenHan" to type 'Double' is not valid. - error ByVal maUPC in Sub inert_inTem when convert to string - ex: dataType maUPC is text in access. maUPC is '2000138388909' insert table access. Please, help me. Thanks u very much.

Sub insert_inTem(ByVal maSKU As Long, ByVal tenHang As String, ByVal maUPC As String, ByVal sl As Long, ByVal gia As Long, ByVal maNCC As Long, ByVal ngayIn As String)
Connect()
cmd = New OleDbCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text

cmd.CommandText = "INSERT INTO T_inTem(maSKU,tenHang,maUPC,SL,gia,maNCC,ngayIn) VALUES(" + maSKU + ",'" + Convert.ToString(tenHang) + "','" + Convert.ToString(maUPC) + "'," + sl + "," + gia + "," + maNCC + ",'" + Convert.ToString(ngayIn) + "')"

cmd.ExecuteNonQuery()
End Sub

- Button click -

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
kn = New KetnoiDB()

sku = Convert.ToInt64(dttest.Rows(0)(0).ToString)
tenHang = dttest.Rows(0)(1).ToString
upc = dttest.Rows(0)(2).ToString
gia = Convert.ToInt64(dttest.Rows(0)(3).ToString)
maNCC = Convert.ToInt64(dttest.Rows(0)(4).ToString)
sl = Convert.ToInt64(txtSL.Text)
ngayin = Date.Now().ToString

kn.insert_inTem(sku, tenHang, upc, sl, gia, maNCC, ngayin)
kn.Close()

End Sub

[Bernhard Hiller]

Do not create the sql query by concatenating strings. Use a parameterized query instead. Likely the problems will disappear already with that change, as a parameterized query also avoids locale dependent formatting issues.

[Lost User]

You are calling Convert.ToString on variables tenHang, maUPC and ngayIn, which are already string objects, so serving no purpose. You are also trying to concatenate numeric values into the string without converting them, so causing the above error. As Bernhard says above, you should use proper parameterized commands in order to a) avoid errors such as this, and b) (and more importantly) protect your database from SQL injection attacks.

[Richard Deeming]

Repeating what the other answers have said, your code is susceptible to SQL Injection: http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html[^] To fix it, use a parameterized command:

cmd.CommandText = "INSERT INTO T_inTem (maSKU, tenHang, maUPC, SL, gia, maNCC, ngayIn) VALUES (@maSKU, @tenHang, @maUPC, @SL, @gia, @maNCC, @ngayIn)"

' NB: For OLEDB commands, the parameter names don't matter; only the order in which they're added.
cmd.Parameters.AddWithValue("@maSKU", maSKU)
cmd.Parameters.AddWithValue("@tenHang", tenHang)
cmd.Parameters.AddWithValue("@maUPC", maUPC)
cmd.Parameters.AddWithValue("@SL", sl)
cmd.Parameters.AddWithValue("@gia", gia)
cmd.Parameters.AddWithValue("@maNCC", maNCC)
cmd.Parameters.AddWithValue("@ngayIn", ngayIn)

cmd.ExecuteNonQuery()

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer