derek7467
asked on
vb.net update access db
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()
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?
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
The items in
Were not in the same order as below:
Once i corrected, it worked fine.
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"
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))
Once i corrected, it worked fine.
Seems odd, since you've explicitly named the Parameters. Still, long as it works, glad you got it straight.
ASKER
was able to figure it out