How to fill datagrid without changing the header name.

Question

1.00/5 (1 vote)

See more:

I want to reuse a datagrid with fixed header name and properties to lessen my windows forms since the function will be almost the same.

What should I add to display the data?
Or is there a way to display the data without changing the header names?

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

	sql = "Select col1,col2,col3 from tbl_detailrec WHERE tc_no ='" & txtgetData.Text & "' order by col1"
	OpenConnection()
	Dim sqlReader As SqlDataReader = command.ExecuteReader()

        If sqlReader.HasRows Then
		Dim dt = New DataTable()
            	dt.Load(sqlReader)
            	DataGridView1.AutoGenerateColumns = False
            	DataGridView1.DataSource = dt
		DataGridView1.Refresh()
	End If

        sqlReader.Close()
 	connection.Close()
End Sub

What I have tried:

I tried to remove the columns then set the AutoGenerateColumns to True but it will remove the properties also.

Posted 8-May-19 20:02pm

heyitsPJ

Updated 9-May-19 15:40pm

v2

Add a Solution

4 solutions

Solution 1

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Posted 8-May-19 20:17pm

OriginalGriff

Comments

heyitsPJ 9-May-19 3:13am

Hi, yes I know this, that query is for sample only. My original query is longer and safe from SQL injection. But my problem here is about displaying the data from my query to datagrid without changing the header names/properties. Well, thanks for this anyway, will help others also.

Solution 3

You're saying the two "data row" sets are compatible.

In that case, just add / replace / merge / whatever the rows in the first "data table" (bound to the grid) with the rows from the second data table / query.

Posted 9-May-19 5:44am

Gerry Schmitz

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 · Accepted Answer · 2019-05-09T01:09:00

As MSDN documentation states DataGridView.AutoGenerateColumns Property[^] gets or sets a value indicating whether columns are created automatically when the DataSource or DataMember properties are set.

So, if you want to keep your custom collection of columns with their properties, you have to use custom method to full fill a DataGridView component.
You can use: Rows.Add[^] method.

As to your comment to OriginalGrif's answer:

Quote:
Hi, yes I know this, that query is for sample only. My original query is longer and safe from SQL injection. But my problem here is about displaying the data from my query to datagrid without changing the header names/properties. Well, thanks for this anyway, will help others also.

No, your query is not safe and is vulnerable to SqlInjection attacks. Please, read OG answer carefully.

A proper way is to use parameters! See:

VB

Function GetMyData(sql As String, parameters As List<SqlParameter>)
dt As DataTable = New DataTable()

Using connection As SqlConnection = New SqlConnection(connectionstring)
    connection.Open()
    Using command As SqlCommand = New SqlCommand(sql, connection)
        For Each p As SqlParameter
            command.Parameters.Add(p);
        Next 'p
        Using sqlReader As SqlDataReader = command.ExecuteReader()
            dt.Load(sqlReader)
        End Using 'reader
    End Using 'command
End Using 'connection

Usage:

VB

sql = "Select col1,col2,col3 from tbl_detailrec WHERE tc_no =@tc_no order by col1"
Dim parameters As List<SqlParameter> = New List<SqlParameter>()
parameters.Add(New SqlParameter("@tc_no", SqlDbType.VarChar).Value="some_value")
'or: Dim parameters As List<SqlParameter> = New List<SqlParameter>(){New SqlParameter("@tc_no", SqlDbType.VarChar).Value="some_value"}
 

'clear rows
DataGridView1.Rows.Clear()

Dim dt As DataTable = GetMyData(sql, parameters)
For Each r As DataRow in dt.Rows
    DataGridView1.Rows.Add(r)
Next

heyitsPJ · Accepted Answer · 2019-05-09T15:41:00

Solution 4

The solution above (by Maciej Los) doesn't work for me but gave me an idea so I changed it a little.
Instead of

For Each r As DataRow in dt.Rows
    DataGridView1.Rows.Add(r)
Next

I use this:

While sqlReader.Read
           Dim values(sqlReader.VisibleFieldCount) As Object
           sqlReader.GetValues(values)

           DataGridView1.Rows.Add(values)
 End While

And it gives me the output I wanted.

Posted 9-May-19 15:41pm

heyitsPJ

Comments

Maciej Los 10-May-19 4:59am

I wrote above code direct from my mind (didn't testing it). Maybe you should use type casting, such as:

For Each r As DataRow in dt.Rows
    DataGridView1.Rows.Add(DirectCast(r, Object))
Next

heyitsPJ 10-May-19 20:59pm

I'm making another datagrid and I will try to use this one. It's actually an old project, half of it is created by another person, I'm just continuing it now. Thanks for your time and answers.