HELP!! Populating datagrid on the basis of data selected in combobox

P

parv_spn

Member
Joined
Feb 9, 2006
Messages
19
Programming Experience
1-3
Hi all,

I have a problem in my application(vb.net 2005 + oracle).Please try to solve it,if anybody can.

I have a combobox in my winform and a datagrid view.
I need to populate my datagrid on the bases of item selected in combobox.
but somehow I'm not getting any data to appear in my datagrid.

Here is my code and form-->
Imports System.Data
Imports System.Data.OracleClient

PublicClass frmProductsByCustomers

Dim con AsNew OracleClient.OracleConnection
Dim ds AsNew DataSet
Dim da1 As OracleClient.OracleDataAdapter
Dim da2 As OracleClient.OracleDataAdapter

Dim sql1 AsString
Dim sql2 AsString

PrivateSub frmProductsByCustomers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

con.ConnectionString = "Data Source=Parv;Persist Security Info=True;User ID=project;Password=project;Unicode=True"

con.Open()


sql1 = "SELECT customer_name from customer_t"

sql2 = "SELECT product_t.product_ID,product_t.product_description,product_t.product_finish FROM customer_t,product_t,order_t,order_line_t WHERE customer_t.customer_id = order_t.customer_id AND order_t.order_id = order_line_t.order_id AND product_t.product_id = order_line_t.product_id AND customer_name = ' " & cboCustomer.SelectedItem & " ' "

da1 = New OracleClient.OracleDataAdapter(sql1, con)
da2 = New OracleClient.OracleDataAdapter(Sql2, con)



da1.Fill(ds, "customer_t")
da2.Fill(ds, "product_t")



con.Close()


Dim MaxCustomerRows AsInteger
Dim inc AsInteger

MaxCustomerRows = ds.Tables("customer_t").Rows.Count

For inc = 0 To MaxCustomerRows - 1
cboCustomer.Items.Add(ds.Tables("customer_t").Rows(inc).Item(0))
Next

grdProductsByCustomers.DataSource = ds
grdProductsByCustomers.DataMember = "product_t"

EndSub

PrivateSub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

If MessageBox.Show("Are you sure you want to close this form??", "Products by Customers Form", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = Windows.Forms.DialogResult.OK Then

Me.Close()

EndIf

EndSub

EndClass
 

Attachments

  • problem.JPG
    problem.JPG
    17.1 KB · Views: 176
Last edited:
Sort by date Sort by votes
It seems silly but it could be the difference between Product_t and product_t.

-tg
 
Upvote 0 Downvote
TechGnome said:
It seems silly but it could be the difference between Product_t and product_t.

-tg
Click to expand...

nope, I've checked it again and thats not a problem.

God this is really irritating. I can't find any problem with my program but still datagrid is not populated with desired result.
 
Upvote 0 Downvote
Wait... hold on a sec there.... You are trying to load the combo AND the grid in the form load? No wonder you aren't getting data... at the time you build your SQL, the combo is empty.

so this:
VB.NET: 
customer_name = ' " & cboCustomer.SelectedItem & " ' "

results in this in your SQL:
VB.NET: 
customer_name = ''

Um, plus I noticed you had a space between the ' and the " ... might not want to do that as it will include the space in the select and miss the records it shouldn't.

What you should be doing is on the form load, load the combo... then in the SelectedIndexChange (or something like that) of the combo, run your SQL to extract the data from the database and put it in the grid.

-tg
 
Upvote 0 Downvote
TechGnome said:
Wait... hold on a sec there.... You are trying to load the combo AND the grid in the form load? No wonder you aren't getting data... at the time you build your SQL, the combo is empty.

so this:
VB.NET: 
customer_name = ' " & cboCustomer.SelectedItem & " ' "

results in this in your SQL:
VB.NET: 
customer_name = ''

Um, plus I noticed you had a space between the ' and the " ... might not want to do that as it will include the space in the select and miss the records it shouldn't.

What you should be doing is on the form load, load the combo... then in the SelectedIndexChange (or something like that) of the combo, run your SQL to extract the data from the database and put it in the grid.

-tg
Click to expand...

Well, First of all I have removed spaces between ' and the " .
Secondly, I load the combo in the form load and run my SQL in seperate button click event but still problem is same. No data in datagrid.

What should I do now?????
 
Upvote 0 Downvote
now would be a good time to post the code again so we can see what's going on.

-tg
 
Upvote 0 Downvote
o.k. so here is my code and screenshot of the form once again.I haven't included Close button's code because its obvious and damn easy and also to make it readable with less code..Hope this time we'll be able to catch the problem...:( :(

VB.NET: 
[SIZE=1][COLOR=#0000ff]Imports[/COLOR][/SIZE][B][SIZE=1] System.Data
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Imports[/COLOR][/SIZE][B][SIZE=1] System.Data.OracleClient[/SIZE][/B]
[B][SIZE=1] 
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Public[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Class[/COLOR][/SIZE][B][SIZE=1] frmProductsByCustomers
[/B][B][/B][/SIZE][SIZE=1][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] con [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleConnection
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] ds [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] DataSet
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] da1 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] da2 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] da3 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] da4 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] sql1 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]String
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] sql2 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]String
 
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][SIZE=1][COLOR=#0000ff]Private[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Sub[/COLOR][/SIZE][B][SIZE=1] frmProductsByCustomers_Load([/B][/SIZE][SIZE=1][COLOR=#0000ff]ByVal[/COLOR][/SIZE][B][SIZE=1] sender [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] System.Object, [/B][/SIZE][SIZE=1][COLOR=#0000ff]ByVal[/COLOR][/SIZE][B][SIZE=1] e [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] System.EventArgs) [/B][/SIZE][SIZE=1][COLOR=#0000ff]Handles[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]MyBase[/COLOR][/SIZE][B][SIZE=1].Load
 
con.ConnectionString = [/B][/SIZE][SIZE=1][COLOR=#800000]"Data Source=Parv;Persist Security Info=True;User ID=project;Password=project;Unicode=True"
[/COLOR][/SIZE][B][SIZE=1]con.Open()
[/B][B][/B][/SIZE][B][SIZE=1] 
[/B][B][/B][/SIZE][B][SIZE=1]sql1 = [/B][/SIZE][SIZE=1][COLOR=#800000]"SELECT DISTINCT customer_name from customer_t"
[/COLOR][/SIZE][B][SIZE=1] 
da1 = [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter(sql1, con)
[/B][/SIZE] 
 
[SIZE=1][COLOR=#008000]
[/COLOR][/SIZE][B][SIZE=1]da1.Fill(ds, [/B][/SIZE][SIZE=1][COLOR=#800000]"customer_t"[/COLOR][/SIZE][B][SIZE=1])
[/B][/SIZE] 
 
[SIZE=1][COLOR=#008000]
[/COLOR][/SIZE][B][SIZE=1]con.Close()
[/B][B][/B][/SIZE][B][SIZE=1] 
[/B] 
[B][/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] MaxCustomerRows [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Integer
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] inc [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Integer
[/COLOR][/SIZE][B][SIZE=1] 
MaxCustomerRows = ds.Tables([/B][/SIZE][SIZE=1][COLOR=#800000]"customer_t"[/COLOR][/SIZE][B][SIZE=1]).Rows.Count
[/B][B][/B][/SIZE][SIZE=1][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=1][COLOR=#0000ff]For[/COLOR][/SIZE][B][SIZE=1] inc = 0 [/B][/SIZE][SIZE=1][COLOR=#0000ff]To[/COLOR][/SIZE][B][SIZE=1] MaxCustomerRows - 1
cboCustomer.Items.Add(ds.Tables([/B][/SIZE][SIZE=1][COLOR=#800000]"customer_t"[/COLOR][/SIZE][B][SIZE=1]).Rows(inc).Item(0))
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Next
 
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][SIZE=1][COLOR=#008000]
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][SIZE=1][COLOR=#0000ff]End[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Sub
[/COLOR][/SIZE][B][SIZE=1][/B][/SIZE][B][SIZE=1] 
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Private[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Sub[/COLOR][/SIZE][B][SIZE=1] btnLoad_Click([/B][/SIZE][SIZE=1][COLOR=#0000ff]ByVal[/COLOR][/SIZE][B][SIZE=1] sender [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] System.Object, [/B][/SIZE][SIZE=1][COLOR=#0000ff]ByVal[/COLOR][/SIZE][B][SIZE=1] e [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] System.EventArgs) [/B][/SIZE][SIZE=1][COLOR=#0000ff]Handles[/COLOR][/SIZE][B][SIZE=1] btnLoad.Click
 
con.ConnectionString = [/B][/SIZE][SIZE=1][COLOR=#800000]"Data Source=Parv;Persist Security Info=True;User ID=project;Password=project;Unicode=True"
[/COLOR][/SIZE][B][SIZE=1]con.Open()
[/B][B][/B][/SIZE][B][SIZE=1] 
[/B][B]sql2 = [/B][/SIZE][SIZE=1][COLOR=#800000]"SELECT product_t.product_ID,product_t.product_description,product_t.product_finish FROM customer_t,product_t,order_t,order_line_t WHERE customer_t.customer_id = order_t.customer_id AND order_t.order_id = order_line_t.order_id AND product_t.product_id = order_line_t.product_id AND customer_name = '"[/COLOR][/SIZE][B][SIZE=1] & cboCustomer.SelectedItem & [/B][/SIZE][SIZE=1][COLOR=#800000]"' "
[/COLOR][/SIZE][B][SIZE=1] 
da2 = [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleDataAdapter(sql2, con)
[/B][B] 
da2.Fill(ds, [/B][/SIZE][SIZE=1][COLOR=#800000]"product_t"[/COLOR][/SIZE][B][SIZE=1])
[/B][B] 
grdProductsByCustomers.DataSource = ds
grdProductsByCustomers.DataMember = [/B][/SIZE][SIZE=1][COLOR=#800000]"product_t"
[/COLOR][/SIZE][B][SIZE=1] 
con.Close()
[/B][B][/B][/SIZE][B][SIZE=1] 
[/B][/SIZE][SIZE=1][COLOR=#0000ff]End[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]Class[/COLOR][/SIZE]
[SIZE=1][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=1][COLOR=#0000ff]
[/COLOR][/SIZE]
 

Attachments

  • 1.JPG
    1.JPG
    18.1 KB · Views: 172
Upvote 0 Downvote
Try changing to this....

sql2 = "SELECT product_t.product_ID,product_t.product_description,product_t.product_finish FROM customer_t,product_t,order_t,order_line_t WHERE customer_t.customer_id = order_t.customer_id AND order_t.order_id = order_line_t.order_id AND product_t.product_id = order_line_t.product_id AND customer_name = ? "

' Then add a parameter with value set as the selected item from the combobox.
da2 = New OracleClient.OracleDataAdapter(sql2, con)
da2.Fill(ds, "product_t")
grdProductsByCustomers.DataSource = ds
grdProductsByCustomers.DataMember = "product_t"
con.Close()
EndSub
EndClass
 
Upvote 0 Downvote
I tried this -->

VB.NET: 
[B][SIZE=1]con.ConnectionString = [/B][/SIZE][SIZE=1][COLOR=#800000]"Data Source=Parv;Persist Security Info=True;User ID=project;Password=project;Unicode=True"
[/COLOR][/SIZE][B][SIZE=1] 
con.Open()
[/B][B][/B][/SIZE][B][SIZE=1] 
[/B][/SIZE][SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] cmd [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] OracleClient.OracleCommand([/B][/SIZE][SIZE=1][COLOR=#800000]"SELECT product_t.product_ID,product_t.product_description,product_t.product_finish FROM customer_t,product_t,order_t,order_line_t WHERE customer_t.customer_id = order_t.customer_id AND order_t.order_id = order_line_t.order_id AND product_t.product_id = order_line_t.product_id AND customer_name LIKE '%' + @customer + '%'"[/COLOR][/SIZE][B][SIZE=1])
[/B][/SIZE][SIZE=1][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] param [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] OracleParameter = cmd.CreateParameter
 
param.ParameterName = [/B][/SIZE][SIZE=1][COLOR=#800000]"@customer"
[/COLOR][/SIZE][B][SIZE=1]param.Value = cboCustomer.SelectedItem
[/B] 
[B]cmd.Parameters.Add(param)
[/B][/SIZE][SIZE=1][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=1][COLOR=#0000ff]Dim[/COLOR][/SIZE][B][SIZE=1] da2 [/B][/SIZE][SIZE=1][COLOR=#0000ff]As[/COLOR][/SIZE][B][SIZE=1] [/B][/SIZE][SIZE=1][COLOR=#0000ff]New[/COLOR][/SIZE][B][SIZE=1] OracleDataAdapter(cmd)
 
da2.SelectCommand.Connection = con
[/B][/SIZE][B][SIZE=1][/SIZE][/B] 
[B][SIZE=1]da2.Fill(ds, [/B][/SIZE][SIZE=1][COLOR=#800000]"product_t"[/COLOR][/SIZE][B][SIZE=1])
[/B] 
[B]con.Close()
[/B] 
[B][/B][/SIZE][SIZE=1][B]grdProductsByCustomers.DataSource = ds

grdProductsByCustomers.DataMember = [/B][/SIZE][SIZE=1][COLOR=#800000]"product_t"[/COLOR][/SIZE]
[SIZE=1][COLOR=#800000]
[/COLOR][/SIZE]

but now I'm getting this exception-->>(what to do now??:( )




 

Attachments

  • 55.JPG
    55.JPG
    34.7 KB · Views: 160
Upvote 0 Downvote
vis781 said:
There's a prblem with the parameter somewhere by the looks of it. Check and make soure you've got it right
Click to expand...

Well, I've checked it again and again...:( and can't understand what the problem is. Its really frustrating..It could be a silly mistake but how will I find it, I don't know..:confused: :eek:
 
Upvote 0 Downvote
Check Oracle documentation .... Oracle may require that variables be declared differently than the way SQL Server or Access would use them.

-tg
 
Upvote 0 Downvote

Similar threads

S
  • Question
how do i update a record in a table after i populated intxtBoxex.text
Replies
4
Views
2K
sudani1969
S
S
  • Question
Question How can i filter departmentwise report in datagrid or crystal report?
Replies
0
Views
2K
sapna21
S
S
  • Question
Question error in saving the record into MSACCESS database
Replies
2
Views
2K
swethajain
S
D
  • Question
update the db using values in a datagrid
Replies
5
Views
2K
diana_j86
D
I
  • Question
Help with setting SelectedValue on the combobox
Replies
3
Views
5K
iboux
I

Share this page

Latest posts

Back
Top