The multi-part identifier "System.Data.DataRowView" could not be bound.

[shruti]

Can someone please help me with this error

I have combo boxes where i populate data.
From there I take the selected value and fetch rest of the data from table to display it in datagridview.

Thanks in advance

Imports System.Data
Imports System.Data.SqlClient

Public Class binsearch

    Private Sub btn_binsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_binsearch.Click
        Dim con As New SqlConnection("xxxx")
        con.Open()
        Dim cmd As New SqlCommand
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select Cylnumber,date  from [dbo].[Voltaix_BINCYL_Location] where Binnumber=" & cb_bin.SelectedValue

        Dim da As New SqlDataAdapter
        da.SelectCommand = cmd
        Dim dt As New DataTable()
        da.Fill(dt)
        Dim source As New BindingSource()
        source.DataSource = dt
        dgv_data.DataSource = source
        con.Close()

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        ' Connecting to the database
      
        Dim conn As New SqlConnection("xxxxxx")
        conn.Open()
        Dim cmd1 As New SqlCommand()
        Dim cmd2 As New SqlCommand()

        cmd1.Connection = conn
        cmd2.Connection = conn

        cmd1.CommandType = CommandType.Text
        cmd2.CommandType = CommandType.Text

        cmd1.CommandText = "Select  Binnumber from [dbo].[Voltaix_BINCYL_Location]"
        cmd2.CommandText = "Select  Cylnumber  from [dbo].[Voltaix_BINCYL_Location]"

        Dim da1 As New SqlDataAdapter
        Dim da2 As New SqlDataAdapter

        Dim dt1 As New DataTable
        Dim dt2 As New DataTable

        da1.SelectCommand = cmd1
        da2.SelectCommand = cmd2

        da1.Fill(dt1)
        da2.Fill(dt2)

        Dim source1 As New BindingSource()
        Dim source2 As New BindingSource()

        source1.DataSource = dt1
        source2.DataSource = dt2

        cb_bin.DataSource = source1
        cb_cyl.DataSource = source2

        cb_bin.DisplayMember = "Binnumber"
        cb_bin.ValueMember = "Binnumber"

        cb_cyl.DisplayMember = "Cylnumber"
        cb_cyl.ValueMember = "Cylnumber"
        'cb_cyl.SelectedIndex = 0
        'cb_bin.SelectedIndex = 0

        'conn.Close()


    End Sub

    Private Sub btn_cylsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cylsearch.Click
        Dim con3 As New SqlConnection("xxxxxx")
        con3.Open()
        Dim cmd3 As New SqlCommand
        cmd3.Connection = con3
        cmd3.CommandType = CommandType.Text
        'cmd3.CommandText = "select Binnumber,date  from [dbo].[Voltaix_BINCYL_Location] where Cylnumber=" & cb_cyl.SelectedValue
        cmd3.CommandText = "select Binnumber,date  from [dbo].[Voltaix_BINCYL_Location] where Cylnumber =" & cb_cyl.SelectedItem.ToString


        'cmd3.CommandText = "Select Convert(Int, ParseName(Cylnumber,2)), date from [dbo].[Voltaix_BINCYL_Location] where Binnumber=" & cb_bin.SelectedValue

        Dim da3 As New SqlDataAdapter
        da3.SelectCommand = cmd3
        Dim dt As New DataTable()
        da3.Fill(dt)
        Dim source As New BindingSource()
        source.DataSource = dt
        dgv_data.DataSource = source
        con3.Close()
    End Sub
End Class

 

[MattP]

I'd suggest taking 8 minutes out of your development time and watch this: How Do I: Filter Data on the Client?.

You'll end up writing a whopping 1 line of code to do the same thing.

 

[jmcilhinney]

While Matt's advice is sound enough, filtering at the client is not always desirable. What if you have millions of records in total? In that case filtering at the clinet would be slow to load the data initially and slow to filter each time. Retrieving a subset of data from the database each time would be a better choice. Local filtering is certainly easier and potentially quicker for relatively small amounts of data though.

As for the original question, maybe you could point out to us where the exception is thrown, so we don't have to search the code to find the problem in the first place.

 

[shruti]

This error occours near the da.fill()
When i run this query with hard coded values then it executes perfect in the SQL Server 2005.

If I change the query into :
cmd3.CommandText = "select Binnumber,date from [dbo].[Voltaix_BINCYL_Location] where Cylnumber =" & cb_cyl.SelectedValue

I get error:
Conversion failed when converting the varchar value 'fasf' to data type int.

If I change the query into:
cmd3.CommandText = "select Binnumber,date from [dbo].[Voltaix_BINCYL_Location] where Cylnumber = '& cb_cyl.SelectedValue.ToString'"

I get only the headers of the columns and no data when actually there is one.

 

[shruti]

Specifically I am doing a test of the last part of my code.
So the exception is shown near the da3.fill(dt)

 

[shruti]

Ok I got it
it was just a silly syntax error

huh

I replaced the query by :

cmd3.CommandText = "select Binnumber,date from [dbo].[Voltaix_BINCYL_Location] where Cylnumber='" & cb_cyl.SelectedValue & "'"


And it worked!!!!

 

[MattP]

While Matt's advice is sound enough, filtering at the client is not always desirable. What if you have millions of records in total? In that case filtering at the clinet would be slow to load the data initially and slow to filter each time. Retrieving a subset of data from the database each time would be a better choice. Local filtering is certainly easier and potentially quicker for relatively small amounts of data though.

As for the original question, maybe you could point out to us where the exception is thrown, so we don't have to search the code to find the problem in the first place.

True.

I'll recommend cjard's thread on PQs for further reading shruti: The Ins and OUTs of Parameterized Queries... - Visual Basic .NET Forums

 

[cjard]

While Matt's advice is sound enough, filtering at the client is not always desirable. What if you have millions of records in total? In that case filtering at the clinet would be slow to load the data initially and slow to filter each time. Retrieving a subset of data from the database each time would be a better choice. Local filtering is certainly easier and potentially quicker for relatively small amounts of data though.

Though I'm sure that DW2 will containa valid lin kfor adding a filtering query to a tableadapter.. Check the Creating a Form to Search Data link