Updating Access DB from DropDownList SelectedItem

Posted on 2005-02-27
Medium Priority
Last Modified: 2010-04-23
I have a page that displays all the fields of a record for editing. I use a combination of datareaders to fill the "Values" of textboxes from the underlying data. I also used datareaders to fill DropDownlists with SELECTed values.

I can't figure out how to update the record when the user selects a value from the DropDownList and reutrns t o the same record to continue editing. Do I need to session all the variables and move to another page to update the record or can it be done more simply?

You help would be appreciated:)
Question by:gjpitt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Expert Comment

ID: 13413887
Let me get this straight.  A user edits the values in several textboxes on a page and when he selects a value from the dropdownlist you want all the data to be saved back to your access db?  If that is the case then you need to add the attribute AutoPostBack="True" to the definition of your dropdownlist.  You can then catch the SelectedIndexChanged event in your code behind and do your updates there.


Author Comment

ID: 13413937
Not quite.

When the page loads all the fields are displayed in appropriate textboxes or controls or labels depending on whether the information should be editable. Using postback means that values beign read from the underlying db cancel out selections already pending for record update.

Using Classic asp the Update button would move to the Update.asp page open a recordset and I could then update the fields using something objRS("EntryDate") = request.form("txtDate")

I am hoping that with .net clicking the update button would enable me to collect the record infomration from the labels or textboxes and selected dropdownlist items and update the record - but I don't know how to do it - the updating and adding records that is:)

I'm using the OleDB type connection things as I'm attached to an Access DB. I tried getting the info from using the Dataform Wizard but that error out when it can't recognise the key fields so no joy there. It seems such a simple thing that I'm trying to do but codee examples seem few and far between everything seems geared towards drag and drop and using SQL.

I'm quite new to this .net stuff having moved from classic asp.

Accepted Solution

ShawnG earned 2000 total points
ID: 13415496
Something like this you are looking for maybe?

Dim conn As OleDbConnection
Dim command as oleDbCommand
Dim strConnectionString As String
dim strSql as string

strConnectionString = _
      "Provider=Microsoft.Jet.OLEDB.4.0; " & _
      "Data source=C:\inetpub\wwwroot\project\db.mdb;"

conn = New OleDbConnection(strConnectionString)

strSql = _
  " UPDATE" & _
  " Table" & _
  " SET" & _
  " field_1 = '" & textbox_1.text & "'," & _
  " field_2 = '" & textbox_2.text & "'," & _
  " field_3 = '" & dropdownlist.SelectedItem.Text & "'," & _
  " WHERE" & _
  " id = " & lblID.text

command = New OleDbCommand(strSql, conn)

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13415507
That looks almost perfect - I'll have a play

Author Comment

ID: 13417888
I put the code in the SelectedIndexChange section of the DropDownList box.

If the control is not AutoPostBack nothing happens
If the control is AutoPostBack then the record is updated but it doesn't set it to the correct value it always sets it to the first value in the dropdownlist. Any suggestions?

Expert Comment

ID: 13418050
If you post the code you have in the SelectedIndexChange section I'll take a look at it.

Expert Comment

ID: 13418264
If the dropdownlist is filled again on the postback then that is why you always get the first value.  You have to put this in your page_load:  
If Not Page.IsPostBack Then
  'Here you fill all your textboxes and dropdownlists with values or call a method that does.
End If


Author Comment

ID: 13418437
Yep - I managed to figure that one in the end:)

It all appears to be working - as far as the original question is concerned so I'll close this one now and award you the points. I just need to carry one with sorting out the wiring - I have ten of thses on this page but your solution is making the updating of these records beautiful so I can keep the crows-nest out of sight of the users:)

Many thanks for your help.

Expert Comment

ID: 13419391
Glad I could help :-)

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

762 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