Query error vb 2005

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

Check out the quotes in that line. They seem out of sorts.

"...Customers Set Points " = "Points

should be

"...Customers Set Points = Points + " & ...

It should be:
 
"Update Customers Set Points = Points + " & TotalorderTextBox.Text & " Where bonuscard = " & BonusCardTextBox1.Text

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()

Select allOpen in new window

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

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

Select allOpen in new window

' dbcon.Open()
should be
dbcon.Open()

no? :)

Data type mismatch in criteria expression. :)
in line dbcmd.ExecuteNonQuery()

please clarify what is the data type of bonuscard

System.string

I mean, in the table?

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

when i try to re insert it in the table is give me overfloow

so  what you think?

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?

after is 16000 after 3200 lol why that?

You're adding the current value plus the TotalorderTextBox.Text

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

Give us more details about what you have in the db, what you have in the textboxes, etc.

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

Select allOpen in new window

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

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


???

please help me:) Jpaulino?

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

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

can i upload it my project here to help me to fix it?

>> can i upload it my project here to help me to fix it?
Ok

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         

Can you tell where the problem is ? The form.

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

Select allOpen in new window

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()

Select allOpen in new window

Is exist in database customer table called Points and in the vb 2005 called Points
points.bmp
points2.PNG

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

Select allOpen in new window

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

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.

cant save it to integer bonus card

can u tested please the program? and tell me your opinion?

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")

You should use Integer , but what's that ("¬0.00") ?

is  the symbol of Euro but here is give it ("¬0.00") not worry in  vb 2005 is ok.where i  must use integer?

You cannot use that ... only use numbers.

Me.TotalorderTextBox.Text  must be a valid number ...

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

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

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

Problem fixed thnks all pepoles :) cheers

Glad I could help!
jpaulino

ipaulino lol  problem is existing   customers form  is not save update so i must re open a new question about these apolgize

not needed problem resolved again thnks