CODEWITHSUNDEEP
vb.netwinformsms-accessinsert
Rara Arar
Rara Arar

Reputation: 401

I have an error with the INSERT INTO statement

Here is my code:

Imports System.Data
Imports System.Data.OleDb

Public Class frmAdd
Dim con As New OleDbConnection
Dim com As New OleDbCommand
Dim ins As New OleDbCommand
Dim upd As New OleDbCommand
Dim strcon = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = Supplies.mdb")

Private Sub frmAdd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    con.ConnectionString = strcon
    con.Open()
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    ins.CommandText = "INSERT INTO [product info] ([Product Name:],[Description:],[Quantity:],[Type:],[Date Received:],[Barcode:],[Price:]) VALUES ('" & txtItemName.Text & "', '" & txtDescription.Text & "', " & txtItemCount.Text & ", '" & cmbItemType.Text & "', " & txtDate.Text & ", '" & txtBarcode.Text & "', '" & txtPrice.Text & "',);"
    ins.Parameters.AddWithValue("@name", txtItemName.Text)
    ins.Parameters.AddWithValue("@desc", txtDescription.Text)
    ins.Parameters.AddWithValue("@count", Convert.ToInt32(txtItemCount.Text))
    ins.Parameters.AddWithValue("@type", cmbItemType.Text)
    ins.Parameters.AddWithValue("@dt", Convert.ToDateTime(txtDate.Text))
    ins.Parameters.AddWithValue("@code", txtBarcode.Text)
    ins.Parameters.AddWithValue("@price", txtPrice.Text)

    ins.CommandType = CommandType.Text
    ins.Connection = con
    ins.ExecuteNonQuery()
    ins.Dispose()

    con.Close()

After I fill up all the textboxes, i hit the save button and when i hit the save button the error "Syntax error in INSERT INTO statement."

Upvotes: 0

Views: 1654

Answers (4)

Steve
Steve

Reputation: 216293

Table names with spaces should be enclosed in square brackets (product info has space in its name), the same is true for column names (Product Name, Date Received).
Then, if you really have a : in all of your columns name, then use everywhere the square brackets, otherwise remove the : from the sql text (and from the database fields).

ins.CommandText = "INSERT INTO [product info] ([Product Name:], [Description:], " + 
                 "[Quantity:],[Type:],[Date Received:],[Barcode:],[Price:]) " + 
                 "VALUES (?,?,?,?,?,?,?)"

Said that, never use string concatenation to build a sql text to pass to the database engine.
You avoid problems with date and text parsing (for example, if one of your input text contains a quote, everything will fail) and moreover you avoid Sql Injection Attacks

ins.Parameters.AddWithValue("@name", txtItemName.Text)
ins.Parameters.AddWithValue("@desc", txtDescription.Text)
ins.Parameters.AddWithValue("@count", Convert.ToInt32(txtItemCount.Text))
ins.Parameters.AddWithValue("@type", cmbItemType.Text)
ins.Parameters.AddWithValue("@dt", Convert.ToDateTime(txtDate.Text)
ins.Parameters.AddWithValue("@code", txtBarcode.Text)
ins.Parameters.AddWithValue("@price", Convert.ToDecimal(txtPrice.Text)

I am assuming that Quantity is a numeric column, Date Received is a DateTime column and Price a numeric column.

Upvotes: 1

WozzeC
WozzeC

Reputation: 2660

Whats with the : after each column name, feel free to explain in comments. My guess is that it is wrong...

" & txtItemName.Text & " needs to be encapsulated in ' since it is text.

" & cmbItemType.Text & " needs to be encapsulated in ' since it is text.

" & txtDate.Text & " needs to be converted into a date.

" & txtBarcode.Text & " needs to be encaspulated in ' since it is text.

",);" The last comma needs to be removed.

I'm not entirely sure you can have spaces in table names and or column names, feel free to leave a comment about this anyone.

My guess is however that this is some kind of school task, because there is more wrong with this query than what is right...

Upvotes: 1

arvin_codeHunk
arvin_codeHunk

Reputation: 2390

ins.CommandText = "INSERT INTO product info (Product Name:,Description:,Quantity:,Type:,Date Received:,Barcode:,Price:) VALUES (" & txtItemName.Text & ", '" & txtDescription.Text & ", " & txtItemCount.Text & ", " & cmbItemType.Text & ", " & txtDate.Text & ", " & txtBarcode.Text & ", " & txtPrice.Text & ",);"

please correct your statement i.e. there is one extra apostrophe (') in your query. just remove that apostrophe .I guess i would work fine

Upvotes: 1

Ric
Ric

Reputation: 13248

Your insert code:

  ins.CommandText = "INSERT INTO product info (Product Name:,Description:,Quantity:,Type:,Date Received:,Barcode:,Price:) VALUES (" & txtItemName.Text & ", '" & txtDescription.Text & ", " & txtItemCount.Text & ", " & cmbItemType.Text & ", " & txtDate.Text & ", " & txtBarcode.Text & ", " & txtPrice.Text & ",);"

has ":" in between columns. Don't know whether or not that is intentional.

Also, your insert into statement is wrong. It should be something like this:

INSERT INTO ProductInfo(ProductName, Description, Quantity,.....etc)

Not sure how to handle the table if it does have a space ie Product Info, maybe use [Product Info]

And finally ensure that there are no illegal characters in the insert into statement when you get the values from the controls.

Maybe consider using an SP and paramaterize the values OR sanitize the input.

Just an idea.

Upvotes: 1

Related Questions