How I Can Update Database From Datagridview In Windows Form

Question

1.00/5 (1 vote)

See more:

How i can update database from datagridview in windows form, i try to update the table in database but nothing is changed in the database, why the databse is not changed, i am using visual studio 2012

C#

public partial class Form1 : Form
{
    SqlConnection con;
    SqlDataAdapter adap;
    DataSet ds;
    SqlCommandBuilder scb;
    SqlCommand scmd;

    public Form1()
    {
        InitializeComponent();
        load_student();
    }

    private void load_student()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["test_update.Properties.Settings.dbupdateConnectionString"].ConnectionString; ;
            con.Open();
            string sql = "select * from student";
            adap = new SqlDataAdapter(sql, con);
            ds = new System.Data.DataSet();
            adap.Fill(ds, "student");
            dataGridView1.DataSource = ds.Tables[0];
            }
        catch (Exception ex)
        {
            MessageBox.Show("Error! " + ex.Message);
        }
        finally
        {
            con.Close();
        }     
    }
     
    private void update_btn_Click(object sender, EventArgs e)
    {
        try
        {
            /*scb = new SqlCommandBuilder(adap);
            adap.Update(ds, "student");*/
            string sql = "update student set name='ali' where id=2";
            scmd = new SqlCommand(sql, con);
            
            con.Open();
            scmd.ExecuteNonQuery();
            MessageBox.Show("Updating is had been successfully");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error " + ex.Message);
        }
        finally
        {
            con.Close();
            scb = new SqlCommandBuilder(adap);
            adap.UpdateCommand = scmd;
            adap.Update(ds, "student");
            ds.AcceptChanges();
            load_student();
        }
    }
     
    private void button1_Click(object sender, EventArgs e)
    {
        con = new SqlConnection();
        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["test_update.Properties.Settings.dbupdateConnectionString"].ConnectionString; ;
        string sql1 = "delete from student where id=1";
        scmd = new SqlCommand(sql1, con);
        try
        {
            con.Open();
            scmd.ExecuteNonQuery();
            MessageBox.Show("Successfully deleting");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            con.Close();
            load_student();
        }
    }
}

Posted 4-Sep-14 20:05pm

Mustafa77

Updated 4-Sep-14 21:57pm

George Jonsson

v2

Add a Solution

Comments

George Jonsson 5-Sep-14 2:07am

Do you have an UPDATE or INSERT statement that you send to the database when the DataGridView is updated?
No one can really help you with the little information you provide.

Mustafa77 5-Sep-14 2:32am

Oki my friend, my code in windows form as:
public partial class Form1 : Form
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds;
SqlCommandBuilder scb;
SqlCommand scmd;
public Form1()
{
InitializeComponent();
load_student();
}
private void load_student()
{
try
{
con = new SqlConnection();
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["test_update.Properties.Settings.dbupdateConnectionString"].ConnectionString; ;
con.Open();
string sql = "select * from student";
adap = new SqlDataAdapter(sql, con);
ds = new System.Data.DataSet();
adap.Fill(ds, "student");
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("Error! " + ex.Message);
}
finally
{
con.Close();
}

}

private void update_btn_Click(object sender, EventArgs e)
{
try
{
/*scb = new SqlCommandBuilder(adap);
adap.Update(ds, "student");*/
string sql = "update student set name='ali' where id=2";
scmd = new SqlCommand(sql, con);

con.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Updating is had been successfully");
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
finally
{
con.Close();
scb = new SqlCommandBuilder(adap);
adap.UpdateCommand = scmd;
adap.Update(ds, "student");
ds.AcceptChanges();
load_student();
}
}

private void button1_Click(object sender, EventArgs e)
{
con = new SqlConnection();
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["test_update.Properties.Settings.dbupdateConnectionString"].ConnectionString; ;
string sql1 = "delete from student where id=1";
scmd = new SqlCommand(sql1, con);
try
{
con.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Successfully deleting");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
load_student();
}
}
}
I am waiting your helping my friend, there are any errors in code

George Jonsson 5-Sep-14 3:53am

Why is it so difficult to use the Improve Question widget?
Please don't put all your code in the comment section.
Well formatted code is so much easier to read.

Mustafa77 5-Sep-14 9:02am

oki, sorry for that my friend about that

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)

Sanchayeeta · Accepted Answer · 2014-09-04T21:21:00

Solution 1

I have run your code, for me it has updated the DB.

Please check your table name, connection sting is it correct or not.
Check once that which data you are updating is present in DB or not.

Posted 4-Sep-14 21:21pm

Sanchayeeta

Comments

Mustafa77 5-Sep-14 4:14am

<connectionstrings>
<add name="test_update.Properties.Settings.dbupdateConnectionString" connectionstring="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\dbupdate.mdf;Integrated Security=True" providername="System.Data.SqlClient">

in app.Config
no there any problem in it
the problem is the changing was happened in the gridview but that no reflects on the database

Mustafa77 5-Sep-14 4:43am

the connection string in App.config is:
<add name="test_update.Properties.Settings.dbupdateConnectionString"
="" connectionstring="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\dbupdate.mdf;Integrated Security=True" providername="System.Data.SqlClient">

Sanchayeeta 5-Sep-14 5:19am

For me its reflecting DB even.

How it is possible that reflecting in GridView and not reflecting Database, because Every time Gridview is retrieving data from DB only. Without updating data in DB how Gridview can get updated data!!!

User 8696850 · Accepted Answer · 2014-09-04T22:51:00

Solution 2

I think you are fixed the condition in your query :

"update student set name='ali' where id=2";

"delete from student where id=1";

you should use something like that :
if you need to update one recorde the selected one use :

int k = dataGridView1.CurrentRow.Index;

"update student set name='"+dataGridView1.Rows[k].Cells["the cell name"].Value.ToString()+"' where id="+dataGridView1.Rows[k].Cells["the number of the cell or the cell id name"].Value

if you need to update them all you should use loop
for(int i =0;i<datagridview1.rows.count;i++)>
and the work here

Posted 4-Sep-14 22:51pm

User 8696850

Updated 4-Sep-14 22:52pm

v2

Comments

Mustafa77 5-Sep-14 5:02am

My friend i try that (id= + dataGridView1.Rows[k].Cells[].....) before, i placed id=1 just only to see if that changing is reflect in the database, nothing was happened in the database...
My friend i think that simple but i cannot to see where is the error in my code....
thank you again to see my problem

[no name] 5-Sep-14 5:45am

try to check this link maybe it help :
http://lamahashim.blogspot.com/2010/04/c-read-insert-update-delete-from-sql.html

they use something like this :

static void Update()
{
try
{
string connectionString =
"server=.;" +
"initial catalog=employee;" +
"user id=sa;" +
"password=sa123";
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand("UPDATE EmployeeDetails SET Name=@NewName, Address=@NewAddress" +
" WHERE Id=@Id", conn))
{
cmd.Parameters.AddWithValue("@Id", 1);
cmd.Parameters.AddWithValue("@Name", "Munna Hussain");
cmd.Parameters.AddWithValue("@Address", "Kerala");

int rows = cmd.ExecuteNonQuery();

//rows number of record got updated
}
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
}

Mustafa77 5-Sep-14 8:04am

I am using Visual Studio 2012, Please any one help me
connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\dbupdate.mdf;Integrated Security=True"

[no name] 5-Sep-14 11:21am

what type of help ? the connection string is the same that you use for the select :
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["test_update.Properties.Settings.dbupdateConnectionString"].ConnectionString;