Solved

Finding a record on SQL server databse through ADO.Net

Posted on 2006-06-17
11
208 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Connections Not being returned to Connection Pool 7 27
Footer for each row on Gridview 2 21
VB.net and sql server 4 36
RLDC Reporting in Visual studio 11 16
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 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