how to populate two list boxes with items on combo box selection

Question

1.44/5 (3 votes)

See more:

I have 3tables

agent(brokerid,name,place,date,year)
buyer(id,name)
item(brokerid-forign key,name,place,date,year,lotno,lname,description,amt)

now i have inserted records into item table from form end.

now i have taken 2list box and 1 combobox in another form,

i want to add all the lotno and their respective amt based on combobox selection.

example. 1 single agent id has many lotno and their amt .

so upon combo box selection of agentid,the respective all the lot no and amt will be populated in 2 separate listbox.

can anyone help me plz.

i am new to this.

thanks in advance.

Posted 13-Aug-13 17:58pm

sudeshna from bangkok

Updated 13-Aug-13 18:10pm

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 14-Aug-13 0:05am

I removed all your comments. Please put the whole question in the body of the question.
First of all, ComboBox — which one? Exact full name of the type, please.
—SA

sudeshna from bangkok 14-Aug-13 0:08am

sorry the 2nd table is buyer not broker,my typing mistake

sudeshna from bangkok 14-Aug-13 0:09am

so combo box for brokerid from agent table which is forign key in item table

sudeshna from bangkok 14-Aug-13 0:14am

Now can you help me plz

sudeshna from bangkok 14-Aug-13 0:38am

Can anyone help me plz, i m new to it.

Thanks in advance

4 solutions

Solution 1

In selected index changed event, get the selected value (brokerid) of the combobox.

1. now one select query for the lot no :
"select lotno from item where brokerid = [SelectedValue]"

Bind all the lotnos in one list box.

2. another select query for the amount :
"select amt from item where brokerid = [SelectedValue]"

Bind all the amounts in another list box.

Posted 13-Aug-13 18:57pm

CodeBlack

Updated 13-Aug-13 18:58pm

v2

Comments

sudeshna from bangkok 14-Aug-13 1:28am

but how can i bind all of them, as it is stored in database and will come in runtime from database.

sudeshna from bangkok 14-Aug-13 1:29am

this is what i have done,my code but list box is not getting populated upon combobox selection

sudeshna from bangkok 14-Aug-13 1:39am

can you give me the code,i cant understand. i have binded the data.
then next what do i do? i am new to this concept

sudeshna from bangkok 14-Aug-13 1:40am

i have given my code in solution, what i have done

Solution 2

VB

Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
        'Dim da As New SqlDataAdapter
        Dim cmd As New SqlCommand
        Dim ds As New DataSet
        Dim dt As DataTable


        'Try

        cmd.Connection = cn
        cn.Open()

        Dim da As New SqlDataAdapter("select * from item", cn)
        dt = New DataTable

        ds = New DataSet
        da.Fill(ds, "item")

        For i As Integer = 0 To ds.Tables("item").Rows.Count - 1
            If ComboBox1.SelectedItem = ds.Tables("item").Rows(i).Item("broker_id").ToString() Then
                TextBox1.Text = ds.Tables("item").Rows(i).Item("name").ToString()
                TextBox2.Text = ds.Tables("item").Rows(i).Item("place").ToString()
                TextBox3.Text = ds.Tables("item").Rows(i).Item("date").ToString()
                TextBox4.Text = ds.Tables("item").Rows(i).Item("year").ToString()
                ListBox1.Text = ds.Tables("item").Rows(i).Item("lot_no").ToString()
                ListBox2.Text = ds.Tables("item").Rows(i).Item("amt").ToString()



            End If

        Next


        cn.Close()

Posted 13-Aug-13 19:28pm

sudeshna from bangkok

Comments

sudeshna from bangkok 15-Aug-13 6:47am

Thank u very much.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeBlack · Accepted Answer · 2013-08-13T20:49:00

Solution 3

See Below Updated Code :

VB

Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
        Dim cmd As New SqlCommand
        Dim ds As New DataSet
        Dim dt As DataTable

        cmd.Connection = cn
        cn.Open()

        Dim da As New SqlDataAdapter("select * from item where broker_id = " + ComboBox1.SelectedValue, cn)
        dt = New DataTable

        ds = New DataSet
        da.Fill(ds, "item")

        For Each row As var In ds.Tables("item").Rows
            ListBox1.Items.Add(DirectCast(row, System.Data.DataRow)("lot_no").ToString())
            ListBox2.Items.Add(DirectCast(row, System.Data.DataRow)("amt").ToString())
        Next

Posted 13-Aug-13 20:49pm

CodeBlack

Comments

sudeshna from bangkok 14-Aug-13 3:09am

what is this var? a variable? an error showing, its saying its not defined

sudeshna from bangkok 14-Aug-13 3:22am

Hello Sir,can you rectify that error,otherwise i cant run my code and check whether your code working or not

CodeBlack 14-Aug-13 3:26am

replace 'var' with 'DataRow' as mentioned below :

For Each row As DataRow In ds.Tables("item").Rows

sudeshna from bangkok 14-Aug-13 3:32am

Showing error in the following line
da.Fill(ds, "item")

Incorrect syntax near '='.

sudeshna from bangkok 14-Aug-13 3:37am

i changed the combobox1.selecteditem instead of selected value, so now its coming.

but my other text fields are not showing as you have changed the full loop statement

sudeshna from bangkok 14-Aug-13 3:37am

For i As Integer = 0 To ds.Tables("item").Rows.Count - 1
If ComboBox1.SelectedItem = ds.Tables("item").Rows(i).Item("broker_id").ToString() Then
TextBox1.Text = ds.Tables("item").Rows(i).Item("name").ToString()
TextBox2.Text = ds.Tables("item").Rows(i).Item("place").ToString()
TextBox3.Text = ds.Tables("item").Rows(i).Item("date").ToString()
TextBox4.Text = ds.Tables("item").Rows(i).Item("year").ToString()

sudeshna from bangkok 14-Aug-13 3:38am

this code how to enter in that same for each statement?

sudeshna from bangkok 14-Aug-13 3:43am

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable

cmd.Connection = cn
cn.Open()

Dim da As New SqlDataAdapter("select * from item where broker_id = " + ComboBox1.SelectedItem, cn)
dt = New DataTable

ds = New DataSet
da.Fill(ds, "item")

For Each row As DataRow In ds.Tables("item").Rows
TextBox1.Text = ds.Tables("item").Rows(i).Item("name").ToString()
TextBox2.Text = ds.Tables("item").Rows(i).Item("place").ToString()
TextBox3.Text = ds.Tables("item").Rows(i).Item("date").ToString()
TextBox4.Text = ds.Tables("item").Rows(i).Item("year").ToString()
ListBox1.Items.Add(DirectCast(row, System.Data.DataRow)("lot_no").ToString())
ListBox2.Items.Add(DirectCast(row, System.Data.DataRow)("amt").ToString())
Next
cn.Close()

sudeshna from bangkok 14-Aug-13 3:44am

Showing error that i not declared. i know i have not declared it anywhere,but how do i use it in your code to retreive the other data into textbox same like listbox

CodeBlack 14-Aug-13 5:09am

use below code :

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable

cmd.Connection = cn
cn.Open()

Dim da As New SqlDataAdapter("select * from item where broker_id = " + ComboBox1.SelectedItem, cn)
dt = New DataTable

ds = New DataSet
da.Fill(ds, "item")

For Each row As DataRow In ds.Tables("item").Rows
TextBox1.Text = DirectCast(row, System.Data.DataRow)("name").ToString()
TextBox2.Text = DirectCast(row, System.Data.DataRow)("place").ToString()
TextBox3.Text = DirectCast(row, System.Data.DataRow)("date").ToString()
TextBox4.Text = DirectCast(row, System.Data.DataRow)("year").ToString()
ListBox1.Items.Add(DirectCast(row, System.Data.DataRow)("lot_no").ToString())
ListBox2.Items.Add(DirectCast(row, System.Data.DataRow)("amt").ToString())
Next
cn.Close()

dont forget to mark it as Answer and rate this solution

sudeshna from bangkok 15-Aug-13 4:08am

hello,can i ask another doubt regarding this only.i have taken another textbox6 and takebox7 where i want to display the sum of the amt column which is being populated in listbox2.and in textbox 7,it will show premium(17.5% of the sum(amt))

where do i write the query of sum(amt) and premium:-
da = New SqlDataAdapter("select sum(amt) as Total_amt, 0.175* sum(amt) as premium from item group by broker_id order by broker_id", cn)

sudeshna from bangkok 15-Aug-13 4:09am

and display it in textbox 6 and textbox7 on combobox selection of broker_id.

CodeBlack 15-Aug-13 4:30am

Find my answer as solution 4

sudeshna from bangkok 14-Aug-13 6:16am

Thanks a lot. you solved my half the solution

CodeBlack · Accepted Answer · 2013-08-14T22:29:00

Solution 4

VB

Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=stamp;user=sa;password=gariahat")
        Dim cmd As New SqlCommand
        Dim ds As New DataSet
        Dim dt As DataTable

        cmd.Connection = cn
        cn.Open()

        Dim da As New SqlDataAdapter("select * from item where broker_id = " + ComboBox1.SelectedItem, cn)
        dt = New DataTable

        ds = New DataSet
        da.Fill(ds, "item")

        Dim sumAmount As Double = 0

        For Each row As DataRow In ds.Tables("item").Rows
            TextBox1.Text = DirectCast(row, System.Data.DataRow)("name").ToString()
            TextBox2.Text = DirectCast(row, System.Data.DataRow)("place").ToString()
            TextBox3.Text = DirectCast(row, System.Data.DataRow)("date").ToString()
            TextBox4.Text = DirectCast(row, System.Data.DataRow)("year").ToString()
            ListBox1.Items.Add(DirectCast(row, System.Data.DataRow)("lot_no").ToString())
            ListBox2.Items.Add(DirectCast(row, System.Data.DataRow)("amt").ToString())

            sumAmount = sumAmount + Convert.ToDouble(DirectCast(row, System.Data.DataRow)("amt"))

        Next

        TextBox6.Text = sumAmount.ToString()
        TextBox7.Text = (0.175 * sumAmount).ToString()

        cn.Close()

Posted 14-Aug-13 22:29pm

CodeBlack

Comments

sudeshna from bangkok 15-Aug-13 23:18pm

Hello,sorry to bother you again, if i want to add now textbo6 and textbox 7 values and put the value in textbox 8,how do i do it?
i did like this:-
textbox8.text=textbox6.text+textbox7.text

but its just concatinating the values and putting it. its not adding it.

sudeshna from bangkok 15-Aug-13 23:23pm

i did it myself

sudeshna from bangkok 15-Aug-13 23:24pm

TextBox6.Text = sumAmount.ToString()
TextBox5.Text = (0.175 * sumAmount).ToString()
TextBox7.Text = (0.07 * TextBox5.Text).ToString()
TextBox9.Text = Val(TextBox5.Text) + Val(TextBox6.Text) + Val(TextBox7.Text)

sudeshna from bangkok 15-Aug-13 23:25pm

but my textbox 7 which is a vat calculating and textbox 9 which is the actual total calculating.

but both these values after decimal coming 4 places, i want it only decimal 2 places,how do i do it?

the value coming as 1234.6789
but i want it as 1234.68

CodeBlack 16-Aug-13 0:45am

you can use Math.Round method for that

sudeshna from bangkok 16-Aug-13 0:50am

where to put it?

sudeshna from bangkok 16-Aug-13 1:12am

hello, i have takent amt column datatype as decimal(18,0) is it correct?

i mean i can have values for amt column as 234.78

or do i need to change it to any other datatype?

CodeBlack 16-Aug-13 1:33am

this is correct

sudeshna from bangkok 16-Aug-13 3:08am

ok.thanks

sudeshna from bangkok 16-Aug-13 3:08am

TextBox6.Text = FormatCurrency(sumAmount.ToString(), 2)
TextBox5.Text = FormatCurrency((0.175 * sumAmount).ToString(), 2)
TextBox7.Text = FormatCurrency((0.07 * TextBox5.Text).ToString(), 2)
TextBox9.Text = Val(TextBox5.Text).ToString() + Val(TextBox6.Text).ToString() + Val(TextBox7.Text).ToString()

sudeshna from bangkok 16-Aug-13 3:09am

this is my code. i want to add all the 3 textbox5,textbox6,textbox7 and place the value in textbox 9.

but its not showing the value,i tried it. its showing 000
i even used formatcurrency to show pound sign before amt

sudeshna from bangkok 16-Aug-13 3:10am

is there any wrong in the last line of code?

CodeBlack 16-Aug-13 4:21am

what is not working in your code ?

CodeBlack 16-Aug-13 4:43am

See my below code :

Dim percentValue As Double
percentValue = (0.175 * sumAmount)
TextBox9.Text = Math.Round(sumAmount + percentValue + (0.07 * percentValue), 2).ToString()

CodeBlack 16-Aug-13 4:44am

and please remove unnecessary comments.

sudeshna from bangkok 16-Aug-13 5:17am

in the code you gave just now,
where is my textbox7 where the premium amt will be shown.
you took percentvalue a variable as double.

but then where do i place the 0.175*sumamount value?
it should be shown in textbox7

TextBox7.Text = (0.175 * sumAmount).ToString()
like this,like you did earlier for me

CodeBlack 16-Aug-13 5:22am

just copy and paste this and you will get everything you want.

sudeshna from bangkok 16-Aug-13 5:28am

its working,sorry,i made a silly misatke,so was not working,now its fine.thanks a lot