?
Solved

Finding a record on SQL server databse through ADO.Net

Posted on 2006-06-17
11
Medium Priority
?
214 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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 video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 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