Vb.net how do I execute a looping query and display its result in 1 datagridview

Question

0.00/5 (No votes)

See more:

I have an Excel sheet loaded in

VB

dt_data

,and i want to compare the values of

VB

dt_data

to my database and display it on another datagridview which is

VB

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:

VB

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 6-Oct-19 16:38pm

Member 14153541

Updated 17-Oct-19 16:11pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2019-10-06T22:51:00

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[^]

SQL

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:

VB

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[^]

Afzaal Ahmad Zeeshan · Answer 2 · 2019-10-06T17:25:00

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:

SQL

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.

Vb.net how do I execute a looping query and display its result in 1 datagridview

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0