Combobox and saving to table

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 782 Views - Last Post: 09 May 2013 - 10:41 AM Rate Topic: -----

#1 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Combobox and saving to table

Posted 09 May 2013 - 07:04 AM

Hi

I'm populating a combobox drop down list from a table (tblService) which works fine - here is the code:

        Dim strSQL As String = "SELECT * FROM tblService"
        Dim cbda As New SqlDataAdapter(strSQL, cs)
        Dim cbds As New DataSet
        cbda.Fill(cbds, "tblService")

        With ComboBox1
            .DataSource = cbds.Tables("tblService")
            .DisplayMember = "ServiceName"
            .ValueMember = "ServiceName"
            .SelectedIndex = 0
        End With



When I click the save button, it runs a stored procedure which saves the selected value back to a main table (tblTop). Again here is the code:

        Dim cmd As New SqlCommand("updatetblTop", cs)
        cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("service1", SqlDbType.VarChar).Value = ComboBox1.SelectedValue
cmd.ExecuteNonQuery()



When I check the table, it correctly saves the value.

However - when I run the form it defaults to the first value on the drop down list - not the item that I have saved in the database. Here is the code for when I load my form:



Me.TblTopTableAdapter.Fill(Me.Tree1DataSet.tblTop)




Does anyone have any ideas why this happens?

Thanks

Frank

This post has been edited by frankwright1: 09 May 2013 - 07:17 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Combobox and saving to table

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3358
  • View blog
  • Posts: 11,372
  • Joined: 12-December 12

Re: Combobox and saving to table

Posted 09 May 2013 - 07:35 AM

Quote

However - when I run the form it defaults to the first value on the drop down list - not the item that I have saved in the database.

I'm not sure if I've understood correctly, but why would you expect the drop-down list to select a specific value? That is, unless you write the code that does this selecting.
Was This Post Helpful? 0
  • +
  • -

#3 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Combobox and saving to table

Posted 09 May 2013 - 07:40 AM

wt is got you should write this code .
combobox.selectedindex=0
you are right andrew this is what he wants.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5479
  • View blog
  • Posts: 11,762
  • Joined: 02-June 10

Re: Combobox and saving to table

Posted 09 May 2013 - 07:42 AM

Quote

However - when I run the form it defaults to the first value on the drop down list - not the item that I have saved in the database.


I think you should approach the question from the opposite direction.
Ask yourself "Why would it select the saved value?"

Where are you reading the value from the database and setting the .SelectedIndex of combobox to that value?
Was This Post Helpful? 0
  • +
  • -

#5 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 07:48 AM

Hi Andrew

WHat Im trying to achieve is as follows:

I have a main form with textboxes and one combobox that saves to tblTop.

The textboxes work fine, and I can use the binding navigator to cycle through each record.

With the combobox, it has predefined options to limit the input. The options come from another table tblService.

When the save button is clicked on the binding navigator, the selected value shown in the combobox is saved to the relevant column in tblTop.


However, when I open the form it is just displaying the first item in tblService, not the item shown in tblTop

Thanks tlhIn

so you mean I need to set the selectedIndex item of the combobox from tblTop, with the other parameters coming from tblService?

With ComboBox1
.DataSource = cbds.Tables("tblService")
.DisplayMember = "ServiceName"
.ValueMember = "ServiceName"
.SelectedIndex = 0 <--------------somehow need to set this to come from tblTop?
End With

This post has been edited by frankwright1: 09 May 2013 - 07:49 AM

Was This Post Helpful? 0
  • +
  • -

#6 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5479
  • View blog
  • Posts: 11,762
  • Joined: 02-June 10

Re: Combobox and saving to table

Posted 09 May 2013 - 07:55 AM

View Postfrankwright1, on 09 May 2013 - 08:48 AM, said:

so you mean I need to set the selectedIndex item of the combobox from tblTop, with the other parameters coming from tblService?

With ComboBox1
.DataSource = cbds.Tables("tblService")
.DisplayMember = "ServiceName"
.ValueMember = "ServiceName"
.SelectedIndex = 0 <--------------somehow need to set this to come from tblTop?
End With


Correct. I'm a C# guy, not VB.NET. But I would normally read the value from the db, then set the .SelectedIndex to the index of that value. A simple way of doing this so that you can see it happening is to loop through the items in the combobox. When the given item is the same as the saved value, set that as the index (or .SelectedItem ) There are other ways that do it in less lines, but for learning purposes I think the loop makes it very clear what is happening.
Was This Post Helpful? 0
  • +
  • -

#7 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Combobox and saving to table

Posted 09 May 2013 - 08:03 AM

With ComboBox1
.DataSource = cbds.Tables("tblService")
.DisplayMember = "ServiceName"
.ValueMember = "ServiceName"
.SelectedIndex = 0 <--------------somehow need to set this to come from tblTop?
End With
[/quote]
This is what it comes in my mind why don't u use inner join query with your tblTop.
SELECT ts.columns FROM tblService  ts inner join tblTop tt on ts.column_name=tt.column_name order by tt.column_name


hope so this is what you want
Was This Post Helpful? 0
  • +
  • -

#8 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 08:03 AM

Im afraid Im going to need a bit more guidance with this.

Is there an example or tutorial that you know of?

Thanks

Frank
Was This Post Helpful? 0
  • +
  • -

#9 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 08:09 AM

vks - thanks for the code which I wrote as follows:

        Dim strSQL As String = "SELECT ts.ServiceName FROM tblService ts inner join tblTop tt on ts.ServiceName=tt.service1 order by tt.service1"
        Dim cbda As New SqlDataAdapter(strSQL, cs)
        Dim cbds As New DataSet
        cbda.Fill(cbds, "tblService")



        With Service1ComboBox
            .DataSource = cbds.Tables("tblService")
            .DisplayMember = "ServiceName"
            .ValueMember = "ServiceName"
            .SelectedIndex = 0
        End With
        cs.Close()



Unfortunately this drops down all the items from tblTop, not the items from tblService
Was This Post Helpful? 0
  • +
  • -

#10 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Combobox and saving to table

Posted 09 May 2013 - 08:14 AM

View Postfrankwright1, on 09 May 2013 - 08:09 AM, said:

vks - thanks for the code which I wrote as follows:

        Dim strSQL As String = "SELECT ts.ServiceName FROM tblService ts inner join tblTop tt on ts.ServiceName=tt.service1 order by tt.service1"
        Dim cbda As New SqlDataAdapter(strSQL, cs)
        Dim cbds As New DataSet
        cbda.Fill(cbds, "tblService")



        With Service1ComboBox
            .DataSource = cbds.Tables("tblService")
            .DisplayMember = "ServiceName"
            .ValueMember = "ServiceName"
            .SelectedIndex = 0
        End With
        cs.Close()



Unfortunately this drops down all the items from tblTop, not the items from tblService


here try this left outer join
http://www.w3schools...l_join_left.asp
im also new in all this .

This post has been edited by vks.gautam1: 09 May 2013 - 08:15 AM

Was This Post Helpful? 0
  • +
  • -

#11 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 08:32 AM

Thanks for the link - Im not sure that the problem is in the sql statement though - I have five unique rows in tblService. These are used to populate the five items in the drop down list of combobox1 - this part is working fine.

I would have thought this was a fairly routine thing for databases, to have a selection from a combobox. I guess I could just populate the combobox from code using


ComboBox1.Items.Add(option1)
ComboBox1.Items.Add(option2)
ComboBox1.Items.Add(option3)



I was trying to avoid this as the combobox options might change
Was This Post Helpful? 0
  • +
  • -

#12 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5479
  • View blog
  • Posts: 11,762
  • Joined: 02-June 10

Re: Combobox and saving to table

Posted 09 May 2013 - 08:36 AM

Stop.

What you are talking about is fairly common and you're making more of it than you need to.

Walk away from the project. Get a meal. Then come back.

Do not do that ugly coding of the contents.

Populating the combobox and selecting the initial value on startup are simply two different steps. NOT a big deal.
Was This Post Helpful? 0
  • +
  • -

#13 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 08:41 AM

tlhIn`toq - thanks - that made me smile
Was This Post Helpful? 0
  • +
  • -

#14 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 08:51 AM

Sill no further forward. Maybe after pudding.
Was This Post Helpful? 0
  • +
  • -

#15 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Combobox and saving to table

Posted 09 May 2013 - 09:03 AM

Nope - sorry, Im going to have to resort to the ugly code. BTW Im not trying to select an initial value on startup - just what ever is against that column/row in tblTop

This post has been edited by frankwright1: 09 May 2013 - 09:07 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2