?
Solved

vb.net update access db

Posted on 2014-11-20
7
Medium Priority
?
185 Views
Last Modified: 2014-11-25
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

0
Comment
Question by:derek7467
  • 4
  • 3
7 Comments
 
LVL 85
ID: 40456059
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?
0
 

Author Comment

by:derek7467
ID: 40456067
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

0
 

Accepted Solution

by:
derek7467 earned 0 total points
ID: 40456106
figured it out, when im calling these items, they werent in the right order.  Its fixed now.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 85
ID: 40457080
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?
0
 

Author Comment

by:derek7467
ID: 40458330
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.
0
 
LVL 85
ID: 40459110
Seems odd, since you've explicitly named the Parameters. Still, long as it works, glad you got it straight.
0
 

Author Closing Comment

by:derek7467
ID: 40464090
was able to figure it out
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question