How can we get data from database in manually created column of datagridview

Question

0.00/5 (No votes)

See more:

I am trying to get data from database to datagridview1 to manually created column.
I have create four columns of datagridview1 1.Serial , 2.CrLedgerName , 3.Amount , 4.Narration. i want to get data in these column of datagridview1 which i manually added in my gridview.I am trying the following code below.

What I have tried:

C#

cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select SNo,DrLedgerName,Amount,Narration from ReceiptVoucher where Rid='" + TxtSearch.Text + "'";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.Rows.Add(dt.Rows.Count);
foreach (DataRow item in dt.Rows)
{
    foreach (DataGridViewRow dr in dataGridView1.Rows)
    {
        //MessageBox.Show("");
        //dataGridView1.DataSource = rdr;
        dr.Cells[0].Value = item["SNo"].ToString();
        dr.Cells[1].Value = item["DrLedgerName"].ToString();
        dr.Cells[2].Value = item["Amount"].ToString();
        dr.Cells[3].Value = item["Narration"].ToString();
    }
}
cn.Close();
dataGridView1.Refresh();

Posted 4-Oct-19 2:07am

Member 13608869

Updated 4-Oct-19 4:07am

Richard Deeming

v2

Add a Solution

Comments

Richard Deeming 4-Oct-19 8:11am

cmd.CommandText = "Select SNo,DrLedgerName,Amount,Narration from ReceiptVoucher where Rid='" + TxtSearch.Text + "'";

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

cmd.CommandText = "Select SNo,DrLedgerName,Amount,Narration from ReceiptVoucher where Rid = @Rid";
cmd.Parameters.AddWithValue("@Rid", TxtSearch.Text);

Member 13608869 4-Oct-19 8:19am

sir it results same with previous result

PeejayAdams 4-Oct-19 9:24am

Yes the result would be the same in normal circumstances but what Richard is saying is that if you just append user text to the query string, your software is very easy to hijack.

What would happen if I enter the following in TxtSearch?

';DROP TABLE ReceiptVoucher

Always, always, always use Command.Parameters to pass an argument or you will be in a world of trouble!

Seriously, read some of the articles that Richard linked - SQL injection is one of the most common sources of security vulnerabilities in existence - this is such an important thing - understand it before you do anything else.

Member 13608869 4-Oct-19 10:07am

oh its ok yes i just read it

2 solutions

Solution 1

To add to what Richard says, two things:
1) As reichard says, 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?

2) Dump your code from this line onward:

C#

dataGridView1.Rows.Add(dt.Rows.Count);

Replace it with this:

C#

dataGridView1.DataSource = dt;

and see what happens.

Posted 4-Oct-19 4:05am

OriginalGriff

Comments

Member 13608869 4-Oct-19 11:02am

ok thanks.The code below is running
check this there is some in it

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)

Member 13608869 · Accepted Answer · 2019-10-04T04:07:00

Quote:
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "Select SNo,DrLedgerName,Amount,Narration from ReceiptVoucher where Rid='" + TxtSearch.Text + "'";
cmd.CommandText = "Select SNo,DrLedgerName,Amount,Narration from ReceiptVoucher where Rid = @Rid";
cmd.Parameters.AddWithValue("@Rid", TxtSearch.Text);
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.Rows.Add(dt.Rows.Count);
Serial.DataPropertyName = "SNo";
CrLedgerName.DataPropertyName = "DrLedgerName";
Amount.DataPropertyName = "Amount";
Narration.DataPropertyName = "Narration";
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = dt;