[Webinar] Streamline your web hosting managementRegister Today


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
  • 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)

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

612 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