Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Finding a record on SQL server databse through ADO.Net

Posted on 2006-06-17
11
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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