How to save in the same row?

[lielee]

Hi.
I have a combobox that has list of food and a textbox to enter the amount. When select a food, then enter the amount, it will save in Access after clicked the add button. But if I do it again but different food, it will save below the 1st food.

I want to save it in the same row. Can it be done? This is my save/add code.

con.Open()
        sql = "SELECT * FROM CakePie"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "CakePie")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsnewrow As DataRow

        dsnewrow = ds.Tables("CakePie").NewRow()

        With CakeComboBox
            If .Text = "Tiramisu Cake" Then
                dsnewrow.Item("TiramisuCake") = QuantTextBox.Text
            ElseIf .Text = "Red Velvet Cake" Then
                dsnewrow.Item("RedVelvetCake") = QuantTextBox.Text
            ElseIf .Text = "Vanilla French Cake" Then
                dsnewrow.Item("VanillaFrenchCake") = QuantTextBox.Text
            ElseIf .Text = "Key Lime Pie" Then
                dsnewrow.Item("KeyLimePie") = QuantTextBox.Text
            ElseIf .Text = "Pecan Pie" Then
                dsnewrow.Item("PecanPie") = QuantTextBox.Text
            ElseIf .Text = "Lemon Meringue Pie" Then
                dsnewrow.Item("LemonMeringuePie") = QuantTextBox.Text
            ElseIf .Text = "Extra Cheese Cheesecake" Then
                dsnewrow.Item("ExtraCheeseCheesecake") = QuantTextBox.Text
            ElseIf .Text = "All Berry Cake" Then
                dsnewrow.Item("AllBerryCake") = QuantTextBox.Text
            End If
        End With

        ds.Tables("CakePie").Rows.Add(dsnewrow)
        da.Update(ds, "CakePie")
        con.Close()

I tried with the "If" but it still save in the next row. I have no idea.

[Mitja Bonca]

Hi,
are you talking about "saving" into dataBase?

This like of code:

ds.Tables("CakePie").Rows.Add(dsnewrow)

Inserts a new line to a dataTable CakePie, and it when with the line bellow (ds.Update) it gets inserted into the dataBase. When you do the Selection ones agiain, the last inserted value in now in the last place (or just bellow the edited one - if you order results).

So do you want to insert into a new row or not?

[Mitja Bonca]

If I understand your database structure, you have two (or more) columns in the table "CakePie". Important are two: CakeName and CakeAmount.
You would like to insert an amount of cakes beside the selected cake (from comboBox). Am I right?

[Mitja Bonca]

if this is so, I did a solution, which might be the right one:

Private Sub button1_Click(sender As Object, e As EventArgs)
	Dim amount As Integer = 0
	If Integer.TryParse(textBox1.Text, amount) Then
		Dim cake As String = comboBox1.Text
		For i As Integer = 0 To table.Rows.Count - 1
			If ds.Tables("CakePie").Rows(i)("Name").ToString() = cake Then
				ds.Tables("CakePie").Rows(i)("Amount") = amount
				da.Update(ds, "CakePie")
				con.Close()
				Exit For
			End If
		Next
	Else
		MessageBox.Show("Input is not in correct format.")
	End If
End Sub

Be aware of one thing: Names as: "Name" and "Amount" in the for loop are the same names as your dataBase names for CakeName and CakeAmount.

[lielee]

Sorry if my question is not clear. Actually, the table has the foods as the column (eg. Tiramisu column, RedVelvet column). The combobox actually used to select the column (eg. if Tiramisu was selected, enter amount then add, the amount will go under Tiramisu column).

[Mitja Bonca]

Just a question: Why do you have this kind of dataBase structure. Correct me if Iam wrong, but this seems not to be correct at all. But anyway, this is not my problem.
This way you will never get the best results. Anyway, you can use my code and re-edit it to suit your needs.

So Row 0 is the cakeName and Row 1 is its Amount!
You can do it:

Dim amount As Integer = 0
If Integer.TryParse(textBox1.Text, amount) Then
	Dim cake As String = comboBox1.Text
	ds.Tables("CakePie").Rows(1)("YourCakeName") = amount
	da.Update(ds, "CakePie")
	con.Close()
	Exit
Else
	MessageBox.Show("Input is not in correct format.")
End If

[P.manidas]

Lielee, What i understand from your two post in this thread that you have design your database as like this

TiramisuCake--RedVelvetCake--VanillaFrenchCake----> these are column name (food name)
----------50----------------100-------------20----------------> price of food

You need to enter the price of food under the food column name as shown above

If it is the case then i have questions for you.

1. If you need to enter the new food item what you will do?
2. From where you have loaded the food item into combobox?

My suggession is to design your database like this. See, if it is suitable for you

NameofFood-----------PirceOfFood
TiramisuCake-------------50
RedVelvetCake------------100
VanillaFrenchCake--------20

Here you can insert new food items whenever you needed. You can insert food item and price of food. or if you insert the food item first and later on price of food then you have to edit that perticular row's filed i.e price of food.

Hope i understand your database. if not sorry.

[lielee]

Hehehe. Sorry if my database abit crazy. My program is like an inventory of a cafe. The food is fixed. So, like everyday, manager will input amount of that food. That's why the database's like that, it has date and time (not in the code) that will save whenever the manager finish adding the inventory. As long as the window is not close, it will save in the same row.

Mitja, I will try your 2nd code. Thank you.

P.manidas, your ques,
1. If the cafe want to add new food, they have to go to me.
2. like my code above, when user select "Tiramisu Cake", it will save the amount in Tiramisu column. The food I have written it in the collection of the combobox.

[P.manidas]

Ok lielee, tables food items are fixed. Whenever they want to add new food item they must have to call you. You have added field as food item into table in design mode or at the run time, whatever it may be.

And you are trying to add the price of food into the respective column (food item) at the first row of the table. And you are getting problem whenever you tried to add the second item's price of food (that is taking as new row)

If it is the case, then you can add the price of food as .....

1. First in the design mode(or at the run time, whatever you used) put zero into the 1st row of the table (in every cell in respective column i.e your food item).

2. Next you edit(update) the data(i.e. zero) giving condition to your Column (food item)

I think by this way you can solve your problem.