Can you help me to show datagridview and Refresh after update?

Question

This is my code and error message when you running say:

An unhandled exception of type System.Data.SqlClient.SqlException occurred in System.Data.dll

on this da.fill(dt);

SqlConnection con = new SqlConnection("Data Source=ANTONIANGGA-PC\\SQLEXPRESS;Initial Catalog=FullandStarving;Integrated Security=True");
SqlCommand cmd;
SqlDataAdapter da;
DataTable dt = new DataTable();

public FormProduksi()
{
   InitializeComponent();
   showgridview();     
}

private void showgridview()
{
    con.Open();
    dt.Clear();
    cmd = new SqlCommand("SELECT * FROM Produksi", con);
    //cmd.CommandType = CommandType.StoredProcedure; done :D
    da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    dataGridView1.DataSource = dt;
    con.Close();
}

private void button2_Click(object sender, EventArgs e)
{
    //Datetimepicker to Database
    string dProduksi = DateTime.Parse(dtmProduksi.Text).ToString("yyyy-MM-dd");

    try{
        con.Open();
        cmd = new SqlCommand("insert into Produksi (IDProduksi,IDPhoto,TanggalProduksi,NamaKaryawan,KeteranganPhoto) Values('" + txtIdpro.Text + "','" + txtIdPhoto.Text + "','" + dProduksi + "','" + txtNamaKaryawan.Text + "','" + rxtKtrphoto.Text + "')", con);
        cmd.ExecuteNonQuery();
        MessageBox.Show("Update telah di jalankan");
        showgridview();
        clear();
        con.Close();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

that update successfully but cant refresh, so i do quit that form and open can see it

You are closeing connection and then after calling function. When you select query to required connection to open... — Mukesh Kalgude, Dec 31 '15 at 04:48
so what i should do ? where i put code 'con.Open();' and 'con.Close()' ? — Antoni Angga, Dec 31 '15 at 04:53

score 3 · Answer 1 · edited May 23 '17 at 11:55

You are closing the connection

con.Close();

and then using

da.Fill(dt);

Just swap this lines:

showgridview();
con.Close();

For example with DbDataAdapter.Fill:

Notes:

1

Yoy should use parametrized queries so you avoid SQL Injection attacks:

var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con);
cmd.Parameters.AddWithValue("@id", id.Text);

2

Wrap SqlConnection and SqlCommand into using so any resources used by those would disposed:

string position;

using (SqlConnection con = new SqlConnection("server=free-pc\\FATMAH; Integrated Security=True; database=Workflow; "))
{
  con.Open();

  using (var cmd = new SqlCommand("SELECT EmpName FROM Employee WHERE EmpID = @id", con))
  {
    cmd.Parameters.AddWithValue("@id", id.Text);

    var name = cmd.ExecuteScalar();

    if (name != null)
    {
       position = name.ToString();
       Response.Write("User Registration successful");
    }
    else
    {
        Console.WriteLine("No Employee found.");
    }
  }
}

Credit

score 2 · Accepted Answer · edited Jul 20 '17 at 05:00

Just change the showgridview() function as below where connection is opened & closed properly.

Also check your sql query ,provide space and maintain syntax of query :

SELECT * FROM Produksi

Error screenshot clearly depicts that stored procedure with such name don't exist

comment out those lines as code below :

void showgridview()
{
    con.Open();
    dt.Clear();
    cmd = new SqlCommand("SELECT * FROM Produksi", con);
    //cmd.CommandType = CommandType.StoredProcedure;
    da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    dataGridView1.DataSource = dt;
    con.Close();
}

Then you wont be having connection issues and errors related .

Button Click code change the closing connection as below:

private void button2_Click(object sender, EventArgs e)
{
    //Datetimepicker to Database
    string dProduksi = DateTime.Parse(dtmProduksi.Text).ToString("yyyy-MM-dd");

    try
    {
        con.Open();
        cmd = new SqlCommand("insert into Produksi (IDProduksi,IDPhoto,TanggalProduksi,NamaKaryawan,KeteranganPhoto) Values('" + txtIdpro.Text + "','" + txtIdPhoto.Text + "','" + dProduksi + "','" + txtNamaKaryawan.Text + "','" + rxtKtrphoto.Text + "')", con);
        cmd.ExecuteNonQuery();
        MessageBox.Show("Update telah di jalankan");
        con.Close();
        showgridview();
        clear();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Also, for further reading:

parameterized queries vs. SQL injection

Why do we always prefer using parameters in SQL statements?

Can you help me to show datagridview and Refresh after update?

2 Answers2

1

2