Saving data in a database

[Full House]

Hello -

I am using an Access database (2003) to store data - well not actually yet as I am unable to get the connection to work properly - and am having difficulty doing so. Here is what I have:

Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\dummy\Desktop\Database stuff\DatabaseExample\bin\Debug\db1.mdb;")
        Dim command As New OleDb.OleDbCommand("INSERT INTO Orders (CustomerName, Quantity, Size) VALUES (@CustomerName, @Quantity, @Size)", connection)
        command.Parameters.AddWithValue("@CustomerName", clientDescriptionTable)
        command.Parameters.AddWithValue("@Quantity", qtyTable)
        command.Parameters.AddWithValue("@Size", pprSize)
        connection.Open()
        command.ExecuteNonQuery()
        connection.Close()

All of the values are String - but for some reason it is failing? Maybe I am using the wrong syntax, but I'm pretty new to programming so if you see my error please let me know

thanks

 

[shawne]

Keep this in your back pocket:

http://www.connectionstrings.com/

I've gone back to it countless times.

 

[Full House]

Thanks -

I actually mis-typed....I meant to say that the error is "Syntax Error in INSERT INTO Statement" - I should have been more clear.

If I break down that statement maybe someone will see where I am failing:

Dim command As New OleDb.OleDbCommand("INSERT INTO Orders

Orders is the table in the database I am wanting to save the info to

(CustomerName, Quantity, Size)

These are columns in the Orders database

VALUES (@CustomerName, @Quantity, @Size)", connection)

These are where I will be placing the info as below

command.Parameters.AddWithValue("@CustomerName", clientDescriptionTable)
        command.Parameters.AddWithValue("@Quantity", qtyTable)
        command.Parameters.AddWithValue("@Size", pprSize)

clientDescriptionTable is a string value typed in from the user, should be stored in the "CustomerName" column
qtyTable is a String value to be stored in Quantity
pprSize is a String value to be stored in the Size column

 

[Full House]

Thought I would throw an update up -

The issue lied withing "Size"

Size is an SQL reserved word, so adding [] around it fixed the issue. Wish I could take back the last 36 hours....oh well. Here's the working code for anyone else that comes across this issue:

Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb")
        Dim command As New OleDb.OleDbCommand("INSERT INTO Orders (CustomerName, Quantity, [Size]) VALUES (@CustomerName, @Quantity, [@Size])", connection)
        command.Parameters.AddWithValue("@CustomerName", clientDescriptionTable)
        command.Parameters.AddWithValue("@Quantity", qtyTable)
        command.Parameters.AddWithValue("[@Size]", pprSize)
        connection.Open()
        command.ExecuteNonQuery()
        connection.Close()