Solved

vb.net update access db

Posted on 2014-11-20
7
175 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 84
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84
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 84
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now