Query error vb 2005

Bellow is the code i  used in order to  update the points of a customer(if they have bonuscard) the problem is that i get error

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. in line    dbcmd.ExecuteNonQuery()
why can anyone help?
Me.Validate()
        Me.OrdersDetailsBindingSource.EndEdit()
        Me.OrdersDetailsTableAdapter.Update(Me.NicolaouDBDataSet.OrdersDetails)
        Me.OrdersDetailsDataGridView.Refresh()
        TotalSum()
        If BonusCardTextBox1.Text.Length > 0 Then
 
            Dim Totalorder As Integer = Int32.Parse(Totalorder)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
"Password = ")
            dbcon.Open()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points " = "Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text
            dbcmd.ExecuteNonQuery()
        Else
            MessageBox.Show("You could have earned " & Integer.Parse(TotalorderTextBox.Text) & " points with this order if you had a bonus card.")
        End If
 
    End Sub

Open in new window

LVL 1
demoniumzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:
dbcmd.CommandText = "Update Customers Set Points " = "Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text

into:

dbcmd.CommandText = "Update Customers Set Points = Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text
0
ToddBeaulieuCommented:
Check out the quotes in that line. They seem out of sorts.

"...Customers Set Points " = "Points

should be

"...Customers Set Points = Points + " & ...
0
Jorge PaulinoIT Pro/DeveloperCommented:
It should be:
 
"Update Customers Set Points = Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
better even to use parameters:
Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points = Points + @points Where bonuscard = @card "
            dbcmd.Parameters.AddWithValue("@points", TotalorderTextBox.Text )
            dbcmd.Parameters.AddWithValue("@card", BonusCardTextBox1.Text)
            dbcmd.ExecuteNonQuery()

Open in new window

0
demoniumzAuthor Commented:
Invalid use of '.', '!', or '()'. in query expression 'Points + ¬223.15'. that give me your suggestion Jpaulino
0
demoniumzAuthor Commented:
angelll

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
If BonusCardTextBox1.Text.Length > 0 Then
 
            Dim Totalorder As Integer = Int32.Parse(Totalorder)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
            "Password = ")
            ' dbcon.Open()
            '   Dim dbcmd As New OleDb.OleDbCommand
            ' dbcmd.Connection = dbcon
            'dbcmd.CommandText = "Update Customers Set Points = Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text
            '  dbcmd.ExecuteNonQuery()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points = Points + @points Where bonuscard = @card "
            dbcmd.Parameters.AddWithValue("@points", TotalorderTextBox.Text)
            dbcmd.Parameters.AddWithValue("@card", BonusCardTextBox1.Text)
            dbcmd.ExecuteNonQuery()
        Else
            MessageBox.Show("You could have earned " & Integer.Parse(TotalorderTextBox.Text) & " points with this order if you had a bonus card.")
        End If

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
' dbcon.Open()
should be
dbcon.Open()

no? :)
0
demoniumzAuthor Commented:
Data type mismatch in criteria expression. :)
in line dbcmd.ExecuteNonQuery()
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please clarify what is the data type of bonuscard
0
demoniumzAuthor Commented:
System.string
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mean, in the table?
0
demoniumzAuthor Commented:
in the ms access is in text if i update it to number the bonus card number is delete and when i try to save it  is give me error  when i save it

overflow
0
demoniumzAuthor Commented:
when i try to re insert it in the table is give me overfloow
0
demoniumzAuthor Commented:
so  what you think?
0
demoniumzAuthor Commented:
i set it bonus card to vb 2005 in decimal is working is save but i get add  7000 points  before is 1000 now is 8000 :P what happend?
0
demoniumzAuthor Commented:
after is 16000 after 3200 lol why that?
0
Jorge PaulinoIT Pro/DeveloperCommented:
You're adding the current value plus the TotalorderTextBox.Text
0
demoniumzAuthor Commented:
yes i want if the points is 1000 and the amount is 20.00 euro to give me 1020 points every one euro is 1 point Jpaulino
0
Jorge PaulinoIT Pro/DeveloperCommented:
Give us more details about what you have in the db, what you have in the textboxes, etc.
0
demoniumzAuthor Commented:
these is my code i  used can u help me to fix it ?
If BonusCardTextBox1.Text.Length > 0 Then
 
            Dim Totalorder As Integer = Integer.Parse(Totalorder)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
            "Password = ")
            dbcon.Open()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points = Points + points Where bonuscard = card "
            dbcmd.Parameters.AddWithValue("points", TotalorderTextBox.Text)
            dbcmd.Parameters.AddWithValue("card", BonusCardTextBox1.Text)
            dbcmd.ExecuteNonQuery()
        Else
            MessageBox.Show("You could have earned " & Integer.Parse(TotalorderTextBox.Text) & " points with this order if you had a bonus card.")
        End If
 
    End Sub

Open in new window

0
demoniumzAuthor Commented:
also  now i see it  points is  dbcmd.Parameters.AddWithValue("points", PointsTextBox.Text)

why these?dbcmd.Parameters.AddWithValue("points", TotalorderTextBox.Text)


???
0
demoniumzAuthor Commented:
please help me:) Jpaulino?
0
Jorge PaulinoIT Pro/DeveloperCommented:
Access doesn't use paramized querys so you should change:
"Update Customers Set Points = Points + points Where bonuscard = card "
To:
"Update Customers Set Points = Points + ? Where bonuscard = ?"
 
Also set this way the parameters that allows you to specify the type of data (check if they are correct)

dbcmd.Parameters.Add("@points", OleDbType.Integer).Value = TotalorderTextBox.Text
dbcmd.Parameters.Add("@card", OleDbType.VarChar).Value = BonusCardTextBox1.Text
0
demoniumzAuthor Commented:
let check it jpaulino  but i have a question ? Must tell that 1 euro =1 points in the code or is not needed?
oledb is not declare in lines

dbcmd.Parameters.Add("@points", OleDbType.Integer).Value = TotalorderTextBox.Text
dbcmd.Parameters.Add("@card", OleDbType.VarChar).Value = BonusCardTextBox1.Text
0
demoniumzAuthor Commented:
can i upload it my project here to help me to fix it?
0
Jorge PaulinoIT Pro/DeveloperCommented:
>> can i upload it my project here to help me to fix it?
Ok
0
demoniumzAuthor Commented:
My project and my database is in  Your hands i believe you can  help me i appreciate the help  here from experts and apologize for the questions i ask sometimes maybe stupid but if you don't ask you don't learn..

Thanks again demoniumz

ps:i  have a  problem with  zip and rar so i upload it to rapidshare thanks

Links: http://rapidshare.com/files/237998038/NicolaouDb.zip.html
          http://rapidshare.com/files/237998988/ggg.zip.html         
0
Jorge PaulinoIT Pro/DeveloperCommented:
Can you tell where the problem is ? The form.
0
demoniumzAuthor Commented:
The form is the Norders and the problem is here
 
bellow is the code  in order to  update points when a user buy something need to  update the points  of a customer.

1euro that a customer spend in the store =1point

Regards demoniumz




 Private Sub OrdersDetailsBindingSourceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.OrdersDetailsBindingSource.EndEdit()
        Me.OrdersDetailsTableAdapter.Update(Me.NicolaouDBDataSet.OrdersDetails)
        Me.OrdersDetailsDataGridView.Refresh()
        TotalSum()
        If BonusCardTextBox1.Text.Length > 0 Then
 
            Dim Totalorder As Integer = Integer.Parse(Totalorder)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
            "Password = ")
            dbcon.Open()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points = Totalorder +points Where bonuscard = @bonuscard "
            dbcmd.Parameters.AddWithValue("@points", PointsTextBox.Text)
            dbcmd.Parameters.AddWithValue("@bonuscard", BonusCardTextBox1.Text)
            dbcmd.Parameters.AddWithValue("@Totalorder", TotalorderTextBox.Text)
 
            dbcmd.ExecuteNonQuery()
        Else
            MessageBox.Show("You could have earned " & Integer.Parse(TotalorderTextBox.Text) & " points with this order if you had a bonus card.")
        End If

Open in new window

0
Jorge PaulinoIT Pro/DeveloperCommented:
Now I'm completly lost!
If you calling a field "Points" that doesnt exists in the table Customers . You should change that to "bonuscard" and use the customerID for the WHERE clause.
Something like:

            Dim Totalorder As Integer = Integer.Parse(Totalorder)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
            "Password = ")
            dbcon.Open()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set BonusCard = BonusCard + ? Where CustomerID = ? "
            dbcmd.Parameters.Add("@BonusCard", OleDb.OleDbType.Integer).Value = PointsTextBox.Text
            dbcmd.Parameters.Add("@CustomerID", OleDb.OleDbType.Integer).Value = 1 'CustomerIDComboBox.SelectedValue
 
            dbcmd.ExecuteNonQuery()

Open in new window

0
demoniumzAuthor Commented:
Is exist in database customer table called Points and in the vb 2005 called Points
points.bmp
points2.PNG
0
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, is in the second database (second file). The first link also have a db without that field.
But you should use the CustomerID field for determinate in which field you will update, and use the TotalorderTextBox.Text
Like this:
 
 PS: I didn't tested if it's CustomerIDComboBox.SelectedValue returns the ID of the customer


dbcmd.CommandText = "Update Customers Set Points = Points + ? Where CustomerID = ? " 
dbcmd.Parameters.Add("@BonusCard", OleDb.OleDbType.Integer).Value = TotalorderTextBox.Text
dbcmd.Parameters.Add("@CustomerID", OleDb.OleDbType.Integer).Value = CustomerIDComboBox.SelectedValue

Open in new window

0
demoniumzAuthor Commented:
The all idea behind is when  a customer have a bonus card bonuscard= maybe 1000  from points  + the points that earn from the Totalorder maybe a total 20.00euro =1020

i euro means 1 points
0
Jorge PaulinoIT Pro/DeveloperCommented:
That's what this code is supposed to do ;)
For a specific customer ID you will increase to the available points the total of orders.
0
demoniumzAuthor Commented:
cant save it to integer bonus card
0
demoniumzAuthor Commented:
can u tested please the program? and tell me your opinion?
0
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, you want to use the bonus card. Then try this way:
dbcmd.CommandText = "Update Customers Set Points = Points + ? Where BonusCard = ? " 
dbcmd.Parameters.Add("@points", OleDb.OleDbType.Integer).Value = TotalorderTextBox.Text
dbcmd.Parameters.Add("@BonusCard", OleDb.OleDbType.VarChar).Value = BonusCardTextBox1.Text

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
demoniumzAuthor Commented:
the total order in  what data type must be save in vb 2005?in ms access is Number but somewhere is declare as string  cannot convert to  integer
 
  Me.TotalorderTextBox.Text = sum
        Me.TotalorderTextBox.Text = sum.ToString("¬0.00")
        OrdersDetailsDataGridView.Columns(6).DefaultCellStyle.Format = ("¬0.00")
0
Jorge PaulinoIT Pro/DeveloperCommented:
You should use Integer , but what's that ("¬0.00") ?
0
demoniumzAuthor Commented:
is  the symbol of Euro but here is give it ("¬0.00") not worry in  vb 2005 is ok.where i  must use integer?
0
Jorge PaulinoIT Pro/DeveloperCommented:
You cannot use that ... only use numbers.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Me.TotalorderTextBox.Text  must be a valid number ...
0
demoniumzAuthor Commented:
Me.TotalorderTextBox.Text = sum
        Me.TotalorderTextBox.Text = sum.ToString("0.00")

if  is gonna work i must change  these or give the same results
   Me.TotalorderTextBox.Text = sum.ToString("0.00") used to display the sum in  totalordertextbox.text
0
demoniumzAuthor Commented:
Now lol the points system is working but in the  form bonus card is not add a bonus card lol is tell me cannot convert decimal to int32
0
demoniumzAuthor Commented:
Problem  fixed thnks Jpaulino Your the best

Private Sub OrdersDetailsBindingSourceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrdersBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.OrdersDetailsBindingSource.EndEdit()
        Me.OrdersDetailsTableAdapter.Update(Me.NicolaouDBDataSet.OrdersDetails)
        Me.OrdersDetailsDataGridView.Refresh()
        TotalSum()
        If BonusCardTextBox1.Text.Length > 0 Then

            Dim Totalorder As String = Decimal.Parse(TotalorderTextBox.Text)
            Dim dbcon As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data " & "Source=D:\NicolaouDB.mdb ; User Id = ; " & _
            "Password = ")
            dbcon.Open()
            Dim dbcmd As New OleDb.OleDbCommand
            dbcmd.Connection = dbcon
            dbcmd.CommandText = "Update Customers Set Points = Points + ? Where BonusCard = ? "
            dbcmd.Parameters.Add("@points", OleDb.OleDbType.Decimal).Value = TotalorderTextBox.Text
            dbcmd.Parameters.Add("@BonusCard", OleDb.OleDbType.VarChar).Value = BonusCardTextBox1.Text
            dbcmd.ExecuteNonQuery()
        Else
            MessageBox.Show("You could have earned " & Integer.Parse(TotalorderTextBox.Text) & " points with this order if you had a bonus card.")
        End If

    End Sub
0
demoniumzAuthor Commented:
Problem fixed thnks all pepoles :) cheers
0
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help!
jpaulino
0
demoniumzAuthor Commented:
ipaulino lol  problem is existing   customers form  is not save update so i must re open a new question about these apolgize
0
demoniumzAuthor Commented:
not needed problem resolved again thnks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.