Dataset Comparison, Connection to Excel and SQL Sever

microsoft.public.dotnet.framework.adonet

3 Nov 2005 3:26 PM

rpoole

Dataset Comparison, Connection to Excel and SQL Sever

Problem, mind is a blank to the best way.

Overview:

Importing Excel data into VB .Net Application using JET and Dataset.
Compare Excel Dataset with SQL connection dataset to select invalid
orders before updating the SQL connection with the Excel Dataset.

Bit of a newbe at this.

Details:

Dim objdataset1 As New DataSet

''Excel Connection String
Dim Read_Conection_String As String =
"Provider=Microsoft.jet.oledb.4.0;" & "Data Source=" &
Me.TextEdit1.Text & ";" & "Extended Properties=""Excel 8.0; HDR=No;
IMEX=1;"""

''SQL Connection String
Dim SQL_Conection_String As String = "data source = ~~ ; initial
catalog= ~~ ; user=~~ ; password =~~"

Dim SQL_Conection As New SqlClient.SqlConnection(SQL_Conection_String)

Dim Read_conection As New OleDbConnection(Read_Conection_String)

''get excel data, f1,f2,f3 being default
Dim Read_command As New OleDbCommand("select [f1],[f2],[F3] from
[sheet1$]", Read_conection)

''SQL data
Dim READ_sql_data As New SqlClient.SqlCommand("SELECT RTRIM(CUSTNMBR)
AS CUSTNMBR , RTRIM(UPSZONE) AS UPSZONE, RTRIM(CUSTCLAS) AS CUSTCLAS
FROM RM00101", SQL_CONECTION)

Dim READ_sql_data_ADT As New SqlClient.SqlDataAdapter(READ_sql_data)
Dim Read_Adapter As New OleDbDataAdapter(Read_command)

Try
SQL_Conection.Open()
READ_sql_data_ADT.Fill(objdataset1, "RM00101")
Catch es As Exception
MessageBox.Show(es.Message)
End Try

SQL_Conection.Close()

Try
Read_conection.Open()
Catch es As Exception
MessageBox.Show(es.Message)
End Try

Try
Read_Adapter.Fill(objdataset1, "XLdata")
Catch es As Exception
MessageBox.Show(es.Message)
End Try

Read_conection.Close()

-------------------------------------

Now what I want to do is to produce a 3rd dataset table / dataset to
hold the orders which do not have valid customer code.

So something like this

select f1,f2,f3 from XLdata where f1 not in (select custno from
rm00101)

Just dont know how to do it?