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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'll post an example in few minutes