Link to home
Start Free TrialLog in
Avatar of Despotic
Despotic

asked on

Comparing a table from SQl to a table from Access with similar information

I have written code that displays information from an SQL DB in a datagridview. What I would like to do is be able to doubleclick on a title that is listed in datagridview and have that initiate a check to see if that same title record exsists in an access db. I become completely frustrated trying to figure this out through google and was never able to figure it out. I assume this will just use 2 datasets or tables and compare the records matching the title doubleclicked...

How can I accomplish this?

I have attached my current code.


Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
Public Class Form6




    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim dr As SqlDataReader
        'Dim Location As String
        'Location = " progress table"

        myConnection = New SqlConnection("Server=test;Database=MasterControl;Trusted_Connection=Yes")
        'establishing connection. you need to provide password for sql server
        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("Select [Title], [Stereo Received], [FF/DE Received], [FF HD Dolby E Received], [FVOD FFST ingested date], [FVOD FFDE ingested date], [FVOD HD ingested date], [IP FFST ingested date], [IP HD ingested date] from [Progress Table]", myConnection)
            'executing the command and assigning it to connection
            dr = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader


                Dim dt As New DataTable
                Dim i As Integer
                Dim count As Integer = dr.FieldCount - 1

                'add columns
                For i = 0 To count
                    dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i))
                Next

                'add rows
                Do While dr.Read()
                    Dim r As DataRow = dt.NewRow
                    For i = 0 To count
                        r(i) = dr.Item(i)
                    Next
                    dt.Rows.Add(r)
                Loop

                DataGridView1.DataSource = dt





                'displaying the data from the table
            End While
            dr.Close()
            myConnection.Close()
        Catch b As Exception
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim dr As SqlDataReader
        'Dim Location As String
        'Location = " progress table"

        myConnection = New SqlConnection("Server=test;Database=MasterControl;Trusted_Connection=Yes")
        'establishing connection. you need to provide password for sql server
        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("Select [Title], [Stereo Received], [FF/DE Received], [FF HD Dolby E Received], [FVOD FFST ingested date], [FVOD FFDE ingested date], [FVOD HD ingested date], [IP FFST ingested date], [IP HD ingested date] from [Progress Table]WHERE Title LIKE " & "'%" & TextBox1.Text & "%'", myConnection)
            'executing the command and assigning it to connection
            dr = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader


                Dim dt As New DataTable
                Dim i As Integer
                Dim count As Integer = dr.FieldCount - 1
                'add columns
                For i = 0 To count
                    dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i))
                Next                'add rows
                Do While dr.Read()
                    Dim r As DataRow = dt.NewRow
                    For i = 0 To count
                        r(i) = dr.Item(i)
                    Next
                    dt.Rows.Add(r)
                Loop
                'Dim dv As New DataView(dt)
                ' dv.RowFilter = "Title = '" & TextBox1.Text & "'"
                DataGridView1.DataSource = dt

                'displaying the data from the table
            End While
            dr.Close()
            myConnection.Close()
        Catch b As Exception


        End Try
    End Sub
End Class

Open in new window

Avatar of Jesus Rodriguez
Jesus Rodriguez
Flag of United States of America image

You can create in the datagrid a Template Conlumn and the Insert right there a linkbutton. In Binding Property of the button associated the Text property with the Id of the Item that you want to look in access and then create a Script and run the query from the script.

I'll post an example in few minutes
ASKER CERTIFIED SOLUTION
Avatar of Kelvin McDaniel
Kelvin McDaniel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial