need some help in my code please

[some one]

hi every one i am trying to create shopping cart working with VB & access when i wrote add function an error occur i do not know how to solve"Syntax error in INSERT INTO statment" it please help me to solve the problem
a shot screen is attached contain page and exeption msg here is the function

Protected Sub getproduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles getproduct.Click
        Dim x As String
        x = Session("user")
        Dim LongTimeString As String = ""
        Dim DateToString As String = ""
        Dim ShortTimeString As String = ""
        Dim LongDateString As String = ""
        Dim Daysinmonth As Integer = 0
        Dim ErrorStr As String = ""
        Dim ErrorStr1 As String = ""
        con = New OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand
        Dim Add As String
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\USER\Desktop\my\WebSite2\App_Data\EC.mdb';Persist Security Info=True"
        If con.State = Data.ConnectionState.Closed Then
            con.Open()
        End If
        Dim OrderDate = System.DateTime.Now
        LongTimeString = OrderDate.ToLongTimeString
        Daysinmonth = System.DateTime.DaysInMonth(OrderDate.Year, OrderDate.Month)
        DateToString = OrderDate.ToString
        ShortTimeString = OrderDate.ToShortTimeString
        LongDateString = OrderDate.ToLongDateString
        If (product.Text = "") Then

            ErrorStr = ErrorStr & " Please Enter Product number, "
        End If
        If (Quantity.Text = "") Then

            ErrorStr1 = ErrorStr1 & " Please Enter Product quantity, "
        End If
        Dim pri = "SELECT price FROM product WHERE ProductNO='" & product.Text & "'"

        cmd.Connection = con
        cmd.CommandText = pri
        Dim s As String = cmd.ExecuteScalar()
        Add = "INSERT INTO order Values ('" & 1 & "','" & Quantity.Text & "','" & pri & "','" & Daysinmonth & "','" & x & "','" & product.Text & "');"
        cmd.CommandText = Add
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try




    End Sub

[some one]

I try to change in the statment but still the same error occour please help me

While (i <= 100)
            i = (i + 1)


            Dim pri = "SELECT price FROM product WHERE ProductNO='" & product.Text & "'"

            cmd.Connection = con
            cmd.CommandText = pri
            Dim s As String = cmd.ExecuteScalar()
            Dim a As String
            a = ("INSERT INTO order Values ( i  And Quantity.Text  And pri And  Daysinmonth And  x And  product.Text );")
            cmd.CommandText = a
        End While

[SheSaidImaPregy]

These are your problems:
1. You cannot add an integer into an integer column with quotes.
2. You're inserting directly into the SQL statement. Use parameters.

Add = "INSERT INTO order Values ('" & 1 & "','" & Quantity.Text & "','" & pri & "','" & Daysinmonth & "','" & x & "','" & product.Text & "');"

Do this insert statement, and please change your code to use parameters. It will protect you from SQL INJECTION. Cause of right now, I can easily type into any of these fields a drop command and your table will be dropped with all data. Anyway, here's your fixed insert statement.

Add = "INSERT INTO order Values (1," & Quantity.Text & "," & pri & "," & Daysinmonth & "," & x & "," & product.Text & ");"

This assumes all your fields are for integers and not text. Text columns you wrap in your quotes (" '" & .. & "' "), all integer fields you don't (" " & .. & " ")

[some one]

thank you but still the same msg appear
it does not work

[SheSaidImaPregy]

alright, well then there can be a few problems. Make sure all data types of your columns meet the correct ways of entering them (integer - no quotes in query :: text - quotes in query), then make sure you have the right number of columns in the query as you do on your database table. If you have 7 columns on your table and 6 or 8 in your query, you will get this error. Safest bet is to do an insert like this:

INSERT INTO tblname (column1, column2, column3.....) VALUES (value1, value2, value3.....)

Those should be your only two problems. Have you attempted to use parameters instead of direct inserts? It will help reduce errors ten fold.

Add = "INSERT INTO tblname (column1, column2, column3, ....) VALUES (?, ?, ?, ....)"
cmd.Parameters.AddWithValue( "?ColumnName", value )

[some one]

still did not work
how can I use the parameter if you do not mind can I attach the whole program our p[roject is about online order resturant

[some one]

still did not work
how can I use the parameter if you do not mind can I attach the whole program
we are supposed to make an online order resturant
thanks

[SheSaidImaPregy]

I am actually at work so I can't use the files anyway.

I gave you an example on how to use the parameters. It's quite simple.

Add = "INSERT INTO tblname (column1, column2, column3, ....) VALUES (?, ?, ?, ....)"
cmd.Parameters.AddWithValue( "?Column1", value1 )
cmd.Parameters.AddWithValue( "?Column2", value2 )
cmd.Parameters.AddWithValue( "?Column3", value3 )

Do that for each parameter you have. THey must be in the same order as your SQL statement.

[some one]

thanks I will try if you can have a lock at the file after work or even tommorow i will be thankfull for you

[some one]

I try it but still and another problem occur when using session I attach a shotscreen of it
thanks

[SheSaidImaPregy]

Session's are handled as strings normally, so try setting it to ' "0" ' and checking for Session("user") = "0"

[some one]

that what i do but when i log in it appear an error

Conversion from string"some one" to type "Double" is not valid

here is code with session statment

Dim x As String
        Dim u As String
        u = Session.Contents("user")
        x = Session(u) = 0
        If Session("user") = 0 Then   at this statement the error appear            Server.Transfer("login.aspx")
        Else
            x = Session("user")
            cmd = New OleDbCommand
            Dim getname = "SELECT member_userName FROM member/product WHERE member_userName = '" & (x & "'")
            cmd.CommandText = getname
            Dim l As String = cmd.ExecuteScalar()

[SheSaidImaPregy]

oh that's why! First of all, before I get into it, check your syntax on the getname SELECT statement near the "x". Should drop the "(" before the x and everything after it.

The problem is that you cannot assign the values like that. x = sessionuser = 0, it doesn't work that way. You first have to assign session(u) = 0 then x = Session(u). And "u" was already set to the username of "some one". You Then you tried to set it to zero, but you are setting it to an integer, not a string. You must put it exactly as:

Session("user") = "0"

That way it treats it as a string. Without the quotes, it treats it as an integer (or double). But can I ask you this.. why are you setting it to zero anyway?

From what I see, you set "u" equal to a sessions.contents, then you're setting x equal to zero... I am so confused on this one. It seems as if there is no point for x in the first part. Session("user") will be different than Session(u) just about Always.

And becareful about server.transfer, it transfer's the page correctly, but the url will stay the same. Redirect will transfer the page and change the url.

[some one]

i set session("user")=0 to be sure that the customer is a member
when I put it Session("user") = "0"
it doesn't compare if the customer is member or not

[SheSaidImaPregy]

well that session was set at first to be a string. Your only hope is to then set the session("user") to nothing then set it again to zero. That should work for you.

Session("user") = Nothing
Session("user") = 0

or user a completely different Session object for the user.

[some one]

thanks I will try
I hope it work

[some one]

STILL THE SAME PROBLEM IN INSERT NOT SOLVED YET

Partial Class cart
    Inherits System.Web.UI.Page
    Dim con As OleDbConnection
    Dim cmd As OleDbCommand

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim con As OleDbConnection

        con = New OleDbConnection

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\USER\Desktop\my\WebSite4\App_Data\EC.mdb';Persist Security Info=True"
        If con.State = Data.ConnectionState.Closed Then
            con.Open()
        End If

    End Sub


    Protected Sub getproduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles getproduct.Click
        Dim x As String
        x = Session("user")
        
        Dim LongTimeString As String = ""
        Dim DateToString As String = ""
        Dim ShortTimeString As String = ""
        Dim LongDateString As String = ""
        Dim Daysinmonth As Integer = 0
        Dim ErrorStr As String = ""
        Dim ErrorStr1 As String = ""
        con = New OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\USER\Desktop\my\WebSite2\App_Data\EC.mdb';Persist Security Info=True"
        If con.State = Data.ConnectionState.Closed Then
            con.Open()
        End If
        Dim OrderDate = System.DateTime.Now
        LongTimeString = OrderDate.ToLongTimeString
        Daysinmonth = System.DateTime.DaysInMonth(OrderDate.Year, OrderDate.Month)
        DateToString = OrderDate.ToString
        ShortTimeString = OrderDate.ToShortTimeString
        LongDateString = OrderDate.ToLongDateString
        If (product.Text = "") Then
            ErrorStr = ErrorStr & " Please Enter Product number, "
        End If
        If (Quantity.Text = "") Then

            ErrorStr1 = ErrorStr1 & " Please Enter Product quantity, "
        End If
        Dim pri = "SELECT price FROM product WHERE ProductNO='" & product.Text & "'"
        cmd.Connection = con
        cmd.CommandText = pri
        Dim s As String = cmd.ExecuteScalar()
        Dim Add As String
        If Session("user") = Nothing Then
            Response.Redirect("login.aspx")
        Else
            Add = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & "Quantity.Text" & "," & "pri" & "," & Daysinmonth & "," & x & "," & "product.Text" & ");"
            cmd.Parameters.AddWithValue("?OrderNo", 2)
            cmd.Parameters.AddWithValue("?quantity", Quantity.Text)
            cmd.Parameters.AddWithValue("? price", pri)
            cmd.Parameters.AddWithValue("?date", Daysinmonth)
            cmd.Parameters.AddWithValue("?member_UserName", x)
            cmd.Parameters.AddWithValue("?Product_Number", product.Text)
            cmd.CommandText = Add
        End If

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try




    End Sub


    Protected Sub remove_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles remove.Click
        
        Dim x As New OleDbCommand
        x.Connection = con
        x.CommandType = CommandType.Text
        x.CommandText = " DELETE * FROM order WHERE Product_Number='" & productNum.Text & "'"

        x.ExecuteNonQuery()

        Response.Write(" Product number# " & productNum.Text & " have been removed from your cart.<BR><BR>" & vbCrLf)
       
    End Sub
    Protected Sub check_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles check.Click
        MsgBox("Thank you for ordering we hope you  visit us again   ")
    End Sub
End Class

[SheSaidImaPregy]

for better debugging as it seems as if it is a problem in your code, trade out your TRY CATCH statement for this:

Try
  cmd.ExecuteNonQuery()
Catch ex As OleDbException
  MsgBox(ex.Message)
End Try

This one will give you the exception that the database is throwing.

[some one]

when I put it the msg appear is syntax error in INSERT INTO Statment

[SheSaidImaPregy]

nvm, this is why:

dd = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & "Quantity.Text" & "," & "pri" & "," & Daysinmonth & "," & x & "," & "product.Text" & ");"

You cannot use the quotes like that, here:

Add = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & Quantity.Text & "," & pri & "," & Daysinmonth & "," & x & "," & product.Text & ");"

Gotta remove the quotes around your Text fields :)

[some one]

sure here is the screen shot
the first one is when I click add the second when remove

[some one]

still the same msg :(

[SheSaidImaPregy]

nvm, this is why:

dd = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & "Quantity.Text" & "," & "pri" & "," & Daysinmonth & "," & x & "," & "product.Text" & ");"

You cannot use the quotes like that, here:
(Toggle Plain Text)

Add = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & Quantity.Text & "," & pri & "," & Daysinmonth & "," & x & "," & product.Text & ");"

Add = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number)Values (1," & Quantity.Text & "," & pri & "," & Daysinmonth & "," & x & "," & product.Text & ");"
Gotta remove the quotes around your Text fields

------------------------------------------------------------------------

Okay, for your second problem with the delete, add the following before your executenonquery and obviously replace connectionname with your connection name:

if Not connectionname.State = ConnectionState.Open then connectionname.Open()

'' and after your executenonquery:

if connectionname.State = ConnectionState.Open then connectionname.Close()

[SheSaidImaPregy]

Okay, woops. I really have to read your code before I post. Change this entire section to this:

If Session("user") = Nothing Then
            Response.Redirect("login.aspx")
        Else
            Add = "INSERT INTO order (OrderNo, quantity, price, date, member_UserName, Product_Number) Values (?, ?, ?, ?, ?, ?);"
            cmd.CommandText = Add
            cmd.Parameters.AddWithValue("?OrderNo", 2)
            cmd.Parameters.AddWithValue("?quantity", Trim(Quantity.Text))
            cmd.Parameters.AddWithValue("?price", pri)
            cmd.Parameters.AddWithValue("?date", Daysinmonth)
            cmd.Parameters.AddWithValue("?member_UserName", x)
            cmd.Parameters.AddWithValue("?Product_Number", Trim(product.Text))
        End If

[SheSaidImaPregy]

I don't normally use command text and all those little things. I just normally do:

cmd = New OleDbCommand( "INSERT INTO....", connectionname )
cmd.Parameters.AddWithValue...
....

[some one]

both didn't work the error whe i deleting has change i attach it as screen shot

[some one]

If you do not mind would you make it for me as the way you usually do here is the my connection path

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\USER\Desktop\my\WebSite4\App_Data\EC.mdb';Persist Security Info=True"

[SheSaidImaPregy]

its saying that the "con" variable isn't set. You never set the con variable in that function, and if it is a public variable, then it should be okay. If not, that's the reason why. Try setting "con" to the right connection string. THat is the problem you got this time and last. "con" is not set.

[SheSaidImaPregy]

Dim u As String = Session("user")
Dim con As New OldDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\USER\Desktop\my\WebSite4\App_Data\EC.mdb';Persist Security Info=True" )
Dim x As New OleDbCommand( "DELETE * FROM order WHERE Product_Number=?", con )
x.Parameters.AddWithValue( "?Product_Number", Trim(productNum.Text) )
if Not con.State = ConnectionState.Open() then con.Open()
Dim recs As Integer = x.ExecuteNonQuery()
if con.State = ConnectionState.Open() then con.Close()

if recs > 0 then
  'successfully updated a record
  Response.Write (" Product successfully removed..... ")
else
  'failed to update record
  Response.Write (" We're sorry, there was an error processing your request. Please try again. ")
end if

[some one]

This error appear when I use your code

it is in the attachment