Click here to Skip to main content
14,330,747 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have an Excel sheet loaded in
dt_data
,and i want to compare the values of
dt_data
to my database and display it on another datagridview which is
dt_sample
, inside my database there is past 3 months record and i want to get them all to be displayed here is my code below

What I have tried:

Try
            For i As Integer = 0 To dt_data.RowCount - 3
                Dim meter_number As String
                meter_number = dt_data.Rows(i).Cells(3).Value
                Dim query As String = "Select * from customer where meter_num = @meter_num"
                conn.Open()
                Dim command As New SqlCommand(query, conn)
                command.Parameters.AddWithValue("@meter_num", meter_number)
                Dim da As New SqlDataAdapter(command)
                Dim ds As New DataSet
                da.Fill(ds, "customer")
                dt_sample.DataSource = ds.Tables(0)


                conn.Close()

            Next
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
            'End Try
            'Catch ex As Exception
            'MessageBox.Show(String.Format("Error: {0}", ex.Message), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
        End Try


the output for that would be only the last cell being showed.

https://i.imgur.com/23WVp8z.png[^]
Posted
Updated 13hrs ago
v3
Rate this:
Please Sign up or sign in to vote.

Solution 2

If you would like to compare Excel data with SQL Server database data, i'd suggest to do that on database level using JOIN + OPENROWSET function. See: Import data from Excel to SQL - SQL Server | Microsoft Docs[^]

SELECT ExData.*
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]) AS ExData
    INNER JOIN [dbo].[YourTable] AS DbData ON ExData.meter_num= DbData.meter_num;


More infromation you'll find here: Using OPENROWSET to Read Excel Worksheets from SQL Server: Part 2 – How to Define and Query a Linked Server[^]

Note: Please, read carefully information under the links!

Another way is yo use Linq[^] and compare data on client level.
Note: This sould be used for small portion of data!
How to do achieve that?

1. Open SQLConnection[^] to the SQL Server database and load[^] data to the datatable[^] (let's name it: SqlDt) by using SqlDataReader[^]
2. Open OleDbConnection[^] to Excel file and load data to another datatable (let's name it: ExcDt) by using OleDbDataReader[^]
3. Compare data:


Dim CommonData = (From sd In SqlDt.AsEnumerable() _
	Join ed In ExcDt.AsEnumerable() On sd.Field(Of Integer)("meter_num") Equals ed.Field(Of Integer)("meter_num")
	Select New With 
	{
		.meter_num = sd.Field(Of Integer)("meter_num"),
		.AnotherField = ed.Field(Of Integer)("ExcelField")
	}) _
	.ToList()


For further details, please see:
Retrieving Data Using a DataReader | Microsoft Docs[^]
How to: Combine Data with LINQ by Using Joins (Visual Basic) | Microsoft Docs[^]
   
Comments
Member 14153541 7-Oct-19 20:33pm
   
I'll try the first one, can the Linq handles tens of thousands of data in the database?
Maciej Los 8-Oct-19 2:13am
   
Yes, Linq will handle tons of data, but it would be less effective then on server side.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
i want to compare the values of dt_data to my database and display it on another datagridview which is dt_sample
I believe your requirement is just to query the data from database and show it in the grid, no comparison needed—apart from a WHERE for the meter_num.

Instead of a loop—which is awfully bad for performance in this case—I would recommend that you use SQL IN() construct. That would be like this:
SELECT * FROM [Customer] WHERE [meter_num] IN (@parameters);
Although this is a bit difficult to code in .NET based languages, see this thread, c# - How to pass sqlparameter to IN()? - Stack Overflow[^]. But the overall outcome of this will be great.

IN (Transact-SQL) - SQL Server | Microsoft Docs[^]

Your database engine will be returning the results based on the WHERE condition, and you will have to create just a single grid to render the output. You can even try to create a stored procedure that can help you with this.

But if you still want to continue with that approach, you should first read all the data (keep appending to the list), and then use a single list of records to support the DataSource field. This will also improve the performance, due to lesser number of memory allocations.
   
Comments
Maciej Los 7-Oct-19 4:11am
   
Unfair down-vote. Fixed!
I agree that using IN clause may help resolve OP's issue, but i would not recommend that this time. I'd recommend to use JOIN's.
Member 14153541 yesterday
   
@afzaal regarding on this statement "you should first read all the data (keep appending to the list), and then use a single list of records to support the DataSource field", how should i properly do it?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100