vb.net update access db

derek7467
derek7467 used Ask the Experts™
on
I made a personal program that will keep track of the maintenance i perform on my car and at the different mileage intervals.  I got it to read from an access database, but when i go to update its a bit wonky.  I click update and different textboxes are updated that what should be.  For instance in the cabin filter textbox, if i put completed and hit save, it shows up on the air filter textbox.  Any ideas?

 Dim sqlupdate As String
        sqlupdate = "UPDATE BMW_Maintenance SET Oil_Change=@Oil_Change, Cabin_Filter=@Cabin_Filter, " & _
           "Air_Filter=@Air_Filter, Engine_Coolant=@Engine_Coolant, Differential_Oil=@Differential_Oil, " & _
           "Spark_Plugs=@Spark_Plugs, Trans_Fluid_and_Filter=@Trans_Fluid_and_Filter, Engine_Drive_Belts=@Engine_Drive_Belts, " & _
           "O2_Sensors=@O2_Sensors, Water_and_Fuel_Hoses=@Water_and_Fuel_Hoses, Fuel_Filter=@Fuel_Filter, " & _
           "Tires=@Tires, Wheel_Alignment=@Wheel_Alignment, Notes=@Notes, Battery=@Battery, " & _
             "Brake_Fluid=@Brake_Fluid, Power_Steering=@Power_Steering WHERE ID=@id"
        Dim cmd As New OleDbCommand(sqlupdate, con1)
        ' This assigns the values for our columns in the DataBase.   
        ' To ensure the correct values are written to the correct column  
        cmd.Parameters.AddWithValue("@Oil_Change", tbOil.Text)
        cmd.Parameters.AddWithValue("@Cabin_Filter", tbCabin.Text)
        cmd.Parameters.AddWithValue("@Brake_Fluid", tbBF.Text)
        cmd.Parameters.AddWithValue("@Power_Steering", tbPS.Text)
        cmd.Parameters.AddWithValue("@Air_Filter", tbAF.Text)
        cmd.Parameters.AddWithValue("@Engine_Coolant", tbCoolant.Text)
        cmd.Parameters.AddWithValue("@Differential_Oil", tbdiff.Text)
        cmd.Parameters.AddWithValue("@Spark_Plugs", tbspark.Text)
        cmd.Parameters.AddWithValue("@Trans_Fluid_and_Filter", tbtrans.Text)
        cmd.Parameters.AddWithValue("@Engine_Drive_Belts", tbbelts.Text)
        cmd.Parameters.AddWithValue("@O2_Sensors", tbO2.Text)
        cmd.Parameters.AddWithValue("@Water_and_Fuel_Hoses", tbhoses.Text)
        cmd.Parameters.AddWithValue("@Fuel_Filter", tbFF.Text)
        cmd.Parameters.AddWithValue("@Tires", tbtires.Text)
        cmd.Parameters.AddWithValue("@Wheel_Alignment", tbwheel.Text)
        cmd.Parameters.AddWithValue("@Notes", tbnotes.Text)
        cmd.Parameters.AddWithValue("@Battery", tbbat.Text)
        cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(tbid.Text))
        con1.Open()
        cmd.ExecuteNonQuery()
        con1.Close()

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hard to say, since we don't really know how you're filling those Textboxes. Are you code to fill them, or a DataSource control of some sort?

Author

Commented:
heres how i fill them:

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sqlupdate As String
        sqlupdate = "SELECT * FROM BMW_Maintenance Where MileMarker='" & ComboBox1.SelectedItem & "'"
        Dim cmd As New OleDbCommand(sqlupdate, con1)
        con1.Open()
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While dr.Read()
            tbOil.Text = (dr("Oil_Change")).ToString
            tbCabin.Text = (dr("Cabin_Filter")).ToString
            tbBF.Text = (dr("Brake_Fluid")).ToString
            tbPS.Text = (dr("Power_Steering")).ToString
            tbAF.Text = (dr("Air_Filter")).ToString
            tbCoolant.Text = (dr("Engine_Coolant")).ToString
            tbdiff.Text = (dr("Differential_Oil")).ToString
            tbspark.Text = (dr("Spark_Plugs")).ToString
            tbtrans.Text = (dr("Trans_Fluid_and_Filter")).ToString
            tbbelts.Text = (dr("Engine_Drive_Belts")).ToString
            tbO2.Text = (dr("O2_Sensors")).ToString
            tbhoses.Text = (dr("Water_and_Fuel_Hoses")).ToString
            tbFF.Text = (dr("Fuel_Filter")).ToString
            tbtires.Text = (dr("Tires")).ToString
            tbwheel.Text = (dr("Wheel_Alignment")).ToString
            tbnotes.Text = (dr("Notes")).ToString
            tbbat.Text = (dr("Battery")).ToString
            tbid.Text = (dr("ID")).ToString
        End While
        con1.Close()
    End Sub

Open in new window

figured it out, when im calling these items, they werent in the right order.  Its fixed now.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
That's somewhat odd - the "order" in which you call these items shouldn't make a difference. Could you provide more details, in case someone else has the same issue?

Author

Commented:
The items in
sqlupdate = "UPDATE BMW_Maintenance SET Oil_Change=@Oil_Change, Cabin_Filter=@Cabin_Filter, " & _
           "Air_Filter=@Air_Filter, Engine_Coolant=@Engine_Coolant, Differential_Oil=@Differential_Oil, " & _
           "Spark_Plugs=@Spark_Plugs, Trans_Fluid_and_Filter=@Trans_Fluid_and_Filter, Engine_Drive_Belts=@Engine_Drive_Belts, " & _
           "O2_Sensors=@O2_Sensors, Water_and_Fuel_Hoses=@Water_and_Fuel_Hoses, Fuel_Filter=@Fuel_Filter, " & _
           "Tires=@Tires, Wheel_Alignment=@Wheel_Alignment, Notes=@Notes, Battery=@Battery, " & _
             "Brake_Fluid=@Brake_Fluid, Power_Steering=@Power_Steering WHERE ID=@id"

Open in new window


Were not in the same order as below:
       
Dim cmd As New OleDbCommand(sqlupdate, con1)
        ' This assigns the values for our columns in the DataBase.   
        ' To ensure the correct values are written to the correct column  
        cmd.Parameters.AddWithValue("@Oil_Change", tbOil.Text)
        cmd.Parameters.AddWithValue("@Cabin_Filter", tbCabin.Text)
        cmd.Parameters.AddWithValue("@Brake_Fluid", tbBF.Text)
        cmd.Parameters.AddWithValue("@Power_Steering", tbPS.Text)
        cmd.Parameters.AddWithValue("@Air_Filter", tbAF.Text)
        cmd.Parameters.AddWithValue("@Engine_Coolant", tbCoolant.Text)
        cmd.Parameters.AddWithValue("@Differential_Oil", tbdiff.Text)
        cmd.Parameters.AddWithValue("@Spark_Plugs", tbspark.Text)
        cmd.Parameters.AddWithValue("@Trans_Fluid_and_Filter", tbtrans.Text)
        cmd.Parameters.AddWithValue("@Engine_Drive_Belts", tbbelts.Text)
        cmd.Parameters.AddWithValue("@O2_Sensors", tbO2.Text)
        cmd.Parameters.AddWithValue("@Water_and_Fuel_Hoses", tbhoses.Text)
        cmd.Parameters.AddWithValue("@Fuel_Filter", tbFF.Text)
        cmd.Parameters.AddWithValue("@Tires", tbtires.Text)
        cmd.Parameters.AddWithValue("@Wheel_Alignment", tbwheel.Text)
        cmd.Parameters.AddWithValue("@Notes", tbnotes.Text)
        cmd.Parameters.AddWithValue("@Battery", tbbat.Text)
        cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(tbid.Text))

Open in new window


Once i corrected, it worked fine.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Seems odd, since you've explicitly named the Parameters. Still, long as it works, glad you got it straight.

Author

Commented:
was able to figure it out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial