Solved

Finding a record on SQL server databse through ADO.Net

Posted on 2006-06-17
11
206 Views
Last Modified: 2010-04-23

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.
0
Comment
Question by:tariqanis2001
  • 5
  • 4
  • 2
11 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 16927314
Do you have keys defined in your datatble. I believe that you can do find methpd only on the table that has keys
0
 

Author Comment

by:tariqanis2001
ID: 16927334
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
 
LVL 28

Expert Comment

by:iboutchkine
ID: 16927337
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
 
LVL 34

Expert Comment

by:Sancler
ID: 16927359
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
 

Author Comment

by:tariqanis2001
ID: 16928130
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:Sancler
ID: 16929209
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
 

Author Comment

by:tariqanis2001
ID: 16929919
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
 
LVL 34

Expert Comment

by:Sancler
ID: 16930139
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
 

Author Comment

by:tariqanis2001
ID: 16930189
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
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 16930252
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
 

Author Comment

by:tariqanis2001
ID: 16930395
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now