adding data to database from datagridview c#

Question

0.00/5 (No votes)

See more:

, +

XML

while i was searching in google i find a code to add the items from datagridview to database using c# 

<pre lang="c#">for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {



                string strCmd = @"Insert into Bills(itemname) values(" + dataGridView1.Rows[i].Cells[0].Value + ")";
                SqlCommand Cmd = new SqlCommand(strCmd, Con);
                Cmd.ExecuteNonQuery();

            }

the was like that dataGridView1.Rows[i].Cells[1].Value + ")"; when i chang it to .Cells[0]

i got that error

incorrect syntax near 3

Posted 4-Aug-15 3:55am

ÃHmed Élkady

Add a Solution

Comments

Richard Deeming 4-Aug-15 10:02am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

ÃHmed Élkady 4-Aug-15 10:03am

i use parameters but now iam trying after i make the code i convert it to parameters

[no name] 4-Aug-15 10:05am

Why not just do it correctly to begin with?

Richard Deeming 4-Aug-15 10:06am

Bad idea. There's a good chance that you'll either forget or run out of time. You're also making problems for yourself which wouldn't exist if you started with properly parameterized queries.

ÃHmed Élkady 4-Aug-15 10:10am

how i can fix that

Richard Deeming 4-Aug-15 10:10am

See Solution 1 below.

ÃHmed Élkady 4-Aug-15 10:18am

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

ÃHmed Élkady 4-Aug-15 10:25am

The parameterized query '(@ItemName varchar(50))INSERT INTO Bills (itemname) VALUES (@ite' expects the parameter '@ItemName', which was not supplied.

ÃHmed Élkady 4-Aug-15 10:34am

The parameterized query '(@ItemName varchar(50))INSERT INTO Bills (itemname) VALUES (@Ite' expects the parameter '@ItemName', which was not supplied.

ÃHmed Élkady 4-Aug-15 10:34am

same

ÃHmed Élkady 4-Aug-15 10:19am

ah sry i got it the connction is closed

1 solution

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)

Richard Deeming · Accepted Answer · 2015-08-04T04:07:00

Solution 1

Using a properly parameterized query will fix the SQL Injection[^] vulnerability in your code, and also fix the error you're seeing:

C#

using (SqlConnection connection = new SqlConnection("-YOUR CONNECTION STRING-"))
using (SqlCommand command = new SqlCommand("INSERT INTO Bills (ItemName) VALUES (@ItemName)", connection))
{
    SqlParameter pItemName = command.Parameters.Add("@ItemName", SqlDbType.VarChar, 50);

    connection.Open();
    
    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        pItemName.Value = dataGridView1.Rows[i].Cells[0].Value ?? DBNull.Value;
        command.ExecuteNonQuery();
    }
}

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[^]
SQL injection attack mechanics | Pluralsight [^]

Posted 4-Aug-15 4:07am

Richard Deeming

Updated 4-Aug-15 5:41am

v3

Comments

ÃHmed Élkady 4-Aug-15 10:16am

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

???

Richard Deeming 4-Aug-15 10:21am

Sorry, I missed the connection.Open() call. Try the updated solution.

ÃHmed Élkady 4-Aug-15 10:24am

The parameterized query '(@ItemName varchar(50))INSERT INTO Bills (itemname) VALUES (@ite' expects the parameter '@ItemName', which was not supplied.

ÃHmed Élkady 4-Aug-15 10:27am

i will se :) (Y)

ÃHmed Élkady 4-Aug-15 10:28am

The parameterized query '(@ItemName varchar(50))INSERT INTO Bills (itemname) VALUES (@Ite' expects the parameter '@ItemName', which was not supplied.

ÃHmed Élkady 4-Aug-15 10:45am

i cant see anything using (SqlCommand command = new SqlCommand("INSERT INTO Bills (ItemName) VALUES (@ItemName)", connection))
{
SqlParameter pItemName = command.Parameters.Add("@ItemName", SqlDbType.VarChar, 50);

connection.Open();

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
pItemName.Value = dataGridView1.Rows[i].Cells[0].Value;
command.ExecuteNonQuery();
}
}

ÃHmed Élkady 4-Aug-15 10:51am

everything working except this pls help me that is the most important thin

ÃHmed Élkady 4-Aug-15 10:55am

ok

ÃHmed Élkady 4-Aug-15 10:28am

same

[no name] 4-Aug-15 10:56am

You might need to add a value with the parameter. I always use AddWithValue to avoid the extra step.

Richard Deeming 4-Aug-15 11:00am

The value is set inside the for loop. That way, you don't have to keep clearing the parameter collection and re-adding the parameter.

ÃHmed Élkady 4-Aug-15 11:01am

so can you show me how i can writ th code

ÃHmed Élkady 4-Aug-15 11:06am

what ??? do yu update it??

ÃHmed Élkady 4-Aug-15 11:11am

but why that error occure O.o

ÃHmed Élkady 4-Aug-15 11:15am

tell me pls i write it correctly like that

using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=inventory;Integrated Security=True"))
using (SqlCommand command = new SqlCommand("INSERT INTO Bills (itemname) VALUES (@ite)", connection))
{
SqlParameter pItemName = command.Parameters.Add("@ite", SqlDbType.VarChar, 50);

connection.Open();

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
pItemName.Value = dataGridView1.Rows[i].Cells[1].Value;
command.ExecuteNonQuery();
}

ÃHmed Élkady 4-Aug-15 11:23am

i chang it now to try diffrent name and i will try again and sw you

ÃHmed Élkady 4-Aug-15 11:24am

write it like this and still same using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=inventory;Integrated Security=True"))
using (SqlCommand command = new SqlCommand("INSERT INTO Bills (itemname) VALUES (@ItemName)", connection))
{
SqlParameter pItemName = command.Parameters.Add("@ItemName", SqlDbType.VarChar, 50);

connection.Open();

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
pItemName.Value = dataGridView1.Rows[i].Cells[0].Value;
command.ExecuteNonQuery();
}
}

ÃHmed Élkady 4-Aug-15 11:33am

same error look image
http://i.imgur.com/FAxVVXj.png

Richard Deeming 4-Aug-15 11:40am

Aha! It's the error message which is truncating the query! That makes more sense.

It looks like at least one of the cells doesn't have a value, and is returning null. The parameter treats that as if you haven't provided a value, rather than passing null to the database.

Try changing that line to:
pItemName.Value = dataGridView1.Rows[i].Cells[0].Value ?? DBNull.Value;

ÃHmed Élkady 4-Aug-15 11:45am

yes it work bro ty :)

ÃHmed Élkady 4-Aug-15 11:51am

but add null to db any way to fix it

Richard Deeming 4-Aug-15 12:05pm

You need to work out why the value from your DataGridView is null. Are you sure you're looking at the right column?

ÃHmed Élkady 4-Aug-15 12:16pm

http://i.imgur.com/nhiOAEM.png look

Richard Deeming 4-Aug-15 13:15pm

So that screenshot confirms that the item in the DataGridView is null.

You'll need to debug the code which loads the data to find out why that is.

ÃHmed Élkady 4-Aug-15 11:53am

null + the item in two diffrent columns

[no name] 4-Aug-15 11:14am

Ah my bad. Didn't see that.

Richard Deeming 4-Aug-15 11:42am

Except that value is sometimes null, which ADO.NET doesn't handle properly! :doh:

[no name] 4-Aug-15 17:20pm

That would do it. 5 for the answer, 10 for the patience.

ÃHmed Élkady 4-Aug-15 11:00am

wait i will try and give you feedback