Finding a record on SQL server databse through ADO.Net


To find a record in single table database on SQL server I placed one line of code  in the SelectedIndexChanged event of a  comboBox bound to the ID field on the table. The MyDataRow variable was first declared in the declaration section at the top of the form.


       dim MyDataRow as datarow
'---------------------------------------

        MyDataRow = DataSet11.Tables("Customers").Rows.Find(ComboBox1.SelectedValue)
'-------------------
But when changing the ID value dispalyed by the comboBox I get the following error:
An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'DataRowView' to type 'Integer' is not valid.
tariqanis2001Asked:
Who is Participating?
 
SanclerConnect With a Mentor Commented:
Tariq

Sorry, I should have read your code more closely before.  The complete bindings that you show for combobox2 are

        Me.ComboBox2.DataSource = DataSet11.Tables("Customers")
        Me.ComboBox2.DisplayMember = "LastName"

That is, you don't set its .ValueMember.  So this line

            MyDataRow = DataSet11.Tables("Customers").Rows.Find(ComboBox2.SelectedValue)

will not have any value in ComboBox2.SelectedValue.

Add

        Me.ComboBox2.ValueMember = "CustomerID"

to your binding code, and try again.

Roger
0
 
iboutchkineCommented:
Do you have keys defined in your datatble. I believe that you can do find methpd only on the table that has keys
0
 
tariqanis2001Author Commented:
SORRY... It's an Access databse, not an SQL server

The ID field to which the comboBox is bound is the Primarykey in the table.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
iboutchkineCommented:
you are performing the search not on the database table but on the datatable, Even when Access table has a key - does not mean that datatable has a key, That is why you have to define a key in the datatable to do a search
0
 
SanclerCommented:
It sounds as though what is happening is that the SelectedIndexChanged event is firing during the binding process.  When that happens, the SelectedValue will return a DataRowView rather than the value your are expecting.  One way round this is to put

   Private Loading As Boolean = True

at the start of your form.  Put

   If Loading Then Exit Sub

as the first line of your SelectedIndexChanged Sub.  And put

   Loading = False

after the code that fills your dataset and binds your combobox.  Although I agree with iboutchkine that Find will only work with a defined key, and that may prove to be a problem, it doesn't explain the precise error message you are reporting.

Roger
0
 
tariqanis2001Author Commented:
I am sorry, I seem to be confusing myself and every body else in the process.

I am using almost the same code with both an Access database and an SQL server one... the error mentioned above is generated by the SQL server not Access as mentioned above (one more appology):

I tried Roger's code - assuming that the SelectedIndexChanged event is firing during the binding process - but I got the same error.

The error only disappeared and the dataset moved to the required record when I substituted the Find method with moving the position of my BindingManagerBase (MyBmb) to the comboBox selectedIndex

 MyBmb.Position = Me.ComboBox1.SelectedIndex

So, I still don't know how to use the Find method to locate a record.
0
 
SanclerCommented:
I've just tried this with a SQL table called "MyTable" containing two fields, "MyName" and "MyNumber".

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.MyTableTableAdapter.Fill(Me.Database1DataSet.MyTable)
        Me.Database1DataSet.MyTable.PrimaryKey = New DataColumn() {Me.Database1DataSet.MyTable.Columns("MyNumber")} '<<< MAKE SURE TABLE HAS A PRIMARY KEY
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim mydatarow As DataRow
        If ComboBox1.SelectedIndex <> -1 Then '<<< ONLY CALL WHEN THERE IS A SELECTION
            mydatarow = Me.Database1DataSet.MyTable.Rows.Find(ComboBox1.SelectedValue)
            MsgBox(mydatarow("MyName"))
        End If
    End Sub

It worked OK.  Not only did it throw no exception about invalid casting, it also gave the right result in the message box.  That was in VB.NET 2005, with a strongly typed dataset.  In VB.NET 2003 the TableAdapter would be a DataAdapter.  With an untyped dataset, it would go something like

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MyDataAdapter.Fill(MyDataSet, "MyTable")
        MyDataSet.Tables("MyTable").PrimaryKey = New DataColumn() {MyDataSet.Tables("MyTable").Columns("MyNumber")} '<<< MAKE SURE TABLE HAS A PRIMARY KEY
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim mydatarow As DataRow
        If ComboBox1.SelectedIndex <> -1 Then '<<< ONLY CALL WHEN THERE IS A SELECTION
            mydatarow = MyDataSet.Tables("MyTable").Rows.Find(ComboBox1.SelectedValue)
            MsgBox(mydatarow("MyName"))
        End If
    End Sub

Roger
0
 
tariqanis2001Author Commented:
Sorry to take so much of your time, but I am afraid Roger's code didn't work for me:

I am using VB.NET 2003 with SQL Server Express 2005. I will try installing VB 2005 express to see if it makes a difference.. but here is my code (including the parts recommended by Roger):

At the declaration section at the top of the form:

Dim x As Integer
    Dim MyBmb As BindingManagerBase
    Dim MyDataRow As DataRow
    Private Loading As Boolean = True
------------
The Form1_load event conatins the following code:
SqlDataAdapter1.Fill(DataSet11)
        DataSet11.Tables("Customers").PrimaryKey = New DataColumn() {DataSet11.Tables("Customers").Columns("CustomerID")}
        BindControls()
        Loading = False
        RecordPosition()
----------------------
Private Sub BindControls()
        Me.txtCustomerID.DataBindings.Add("Text", DataSet11, "Customers.CustomerID")
        Me.txtFirstName.DataBindings.Add("Text", DataSet11, "Customers.FirstName")
        Me.txtLastName.DataBindings.Add("Text", DataSet11, "Customers.LastName")
        Me.txtAddress.DataBindings.Add("text", DataSet11, "Customers.Address1")
        Me.txtCity.DataBindings.Add("Text", DataSet11, "Customers.City")
        Me.txtState.DataBindings.Add("Text", DataSet11, "Customers.State")
        Me.txtZipCode.DataBindings.Add("Text", DataSet11, "Customers.ZipCode")
        Me.ComboBox1.DataSource = DataSet11.Tables("Customers")
        Me.ComboBox1.DisplayMember = "CustomerID"
        Me.ComboBox2.DataSource = DataSet11.Tables("Customers")
        Me.ComboBox2.DisplayMember = "LastName"
        MyBmb = Me.BindingContext(DataSet11, "Customers")
    End Sub
---------------
ComboBox1 moves the dataset to the selected CustomerID:

MyBmb.Position = Me.ComboBox1.SelectedIndex
---------------
The problem is in ComboBox2 which now has the following code
 Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        If ComboBox2.SelectedIndex <> -1 Then
            MyDataRow = DataSet11.Tables("Customers").Rows.Find(ComboBox2.SelectedValue)
        End If
    End Sub
0
 
SanclerCommented:
Tariq

Change this

SqlDataAdapter1.Fill(DataSet11)

to

SqlDataAdapter1.Fill(DataSet11, "Customers")

I haven't checked but, just reading through the code, it looks to me like it should work with that change.  The problem is that just using

SqlDataAdapter1.Fill(DataSet11)

the table that is filled is called "Table", not "Customers".

Roger
0
 
tariqanis2001Author Commented:
It didn't work, Roger.

But the same code worked fine with an Access databse... So at least I got the Find method working in one instance... I will set up VB.2005 with SQL Server express on another computer before the day is over and see if that will make a difference
0
 
tariqanis2001Author Commented:
Thanks Roger...

The code worked when I used a simple Try.. Catch block, even in the absence of the ValueMember line

If ComboBox2.SelectedIndex <> -1 Then
            Try
                MyDataRow = DataSet11.Tables("Customers").Rows.Find(ComboBox2.SelectedValue)
            Catch
            End Try
        End If
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.