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

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

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:

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.

[^]]

Posted 21-Oct-19 14:20pm

Member 14153541

Updated 22-Oct-19 5:07am

Add a Solution

2 solutions

Solution 1

Don't put these lines in your For loop:

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()

And construct your query like this:

Dim query As String = "Select * from customer where meter_num IN @meter_numbers"

[Edit] see the solution by Richard Deeming, or try one of the methods mentioned here: sql server - Parameterize an SQL IN clause - Stack Overflow[^]

Posted 21-Oct-19 21:25pm

RickZeeland

Updated 22-Oct-19 6:36am

v2

Comments

Richard Deeming 22-Oct-19 10:59am

Unfortunately, you can't use a single parameter for an IN query.

RickZeeland 22-Oct-19 12:31pm

Thanks for pointing that out, I will update the solution :)

Add a Solution

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

Richard Deeming · Accepted Answer · 2019-10-22T05:07:00

Try something like this:

VB.NET

Using command As New SqlCommand()
    command.Connection = conn
    
    Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
    For i As Integer = 0 To dt_data.RowCount - 3
        Dim parameterName As String = "@meter_num_" & i
        Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
        command.Parameters.AddWithValue(parameterName, meter_number)
        parameterNames.Add(parameterName)
    Next
    
    command.CommandText = String.Format("SELECT * FROM customer WHERE meter_num IN ({0})", String.Join(",", parameterNames))
    
    Dim da As New SqlDataAdapter(command)
    Dim ds As New DataSet
    da.Fill(ds, "customer")
    dt_sample.DataSource = ds.Tables(0)
End Using

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

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

Existing Members

...or Join us