CODEWITHSUNDEEP
c#.netdatagrid
Vimesh Shah
Vimesh Shah

Reputation: 129

Update Database From DataGrid in C#.NET

I have this code which I used for update database from DataGrid

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace datagrid
{
public partial class Form1 : Form
{
    private MySqlConnection conn;
    private DataTable data;
    private MySqlDataAdapter da;
    private MySqlCommandBuilder cb;
    public Form1()
    {
        InitializeComponent();
    }

    private void btnshow_Click(object sender, EventArgs e)
    {
        string c = "server=localhost;database=std;uid=root;password=";
        conn = new MySqlConnection(c);
        conn.Open();
        data = new DataTable();

        da = new MySqlDataAdapter("SELECT * FROM general",conn);
        cb = new MySqlCommandBuilder(da);

        da.Fill(data);

        dataGridView1.DataSource = data;
    }

    private void btnupdate_Click(object sender, EventArgs e)
    {
        DataTable changes = data.GetChanges();
        da.Update(changes);
        data.AcceptChanges();
    }
 }
}

now when I press the update button it shows me this exception:

{"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."}

Now please tell me what should I do?

Exception like this

Upvotes: 0

Views: 271

Answers (2)

user3167664
user3167664

Reputation: 1

    Try
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        global_command = New SqlCommand("UPDATE products_tbl set running_no = '" & txt_running.Text & "' where template_code = 'n'and prod_no = '" & txt_product.Text & "'", con)
        global_command.ExecuteNonQuery()
        global_command.Dispose()

        MsgBox("Successfully updated!", MsgBoxStyle.Information, "Message")
        where = vbNullString

    Catch ex As Exception
        MsgBox("Trace No 4: System Error or Data Error!" + Chr(13) + ex.Message + Chr(13) + "Please Contact Your System Administrator!", vbInformation, "Message")
    End Try

    Try
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        Dim dset As New DataSet
        Dim dbind As New BindingSource
        Dim strquery As String
        strquery = "SELECT prod_code, prod_no, prod_suffix, prod_lvl, customer, model, family, company_code, company_name, company_address, running_no, template_code FROM products_tbl where template_code = 'n' and prod_no = '" & txt_product.Text & "'  and prod_suffix = '" & txt_suffix.Text & "' and prod_lvl = '" & txt_level.Text & "'"
        Dim adap As New SqlDataAdapter(strquery, con)
        dset = New DataSet
        adap.Fill(dset)
        dbind.DataSource = dset.Tables(0)
        dg.DataSource = dbind
    Catch ex As Exception
        MsgBox("Trace No 3: System Error or Data Error!" + Chr(13) + ex.Message + Chr(13) + "Please Contact Your System Administrator!", vbInformation, "Message")
    End Try
End Sub

Upvotes: 0

Chris
Chris

Reputation: 8647

From this http://social.msdn.microsoft.com/Forums/en-US/5dec5633-ac84-48d9-8fd6-5c7601be4ccd/exception-dynamic-sql-generation-for-the-updatecommand-is-not-supported-against-a-selectcommand and a lot of others articles

I'm pretty sure that those exceptions mean that your select query needs to return the primary key of the table. If you table doesn't have a primary key, you need to set one.

Otherwise, you should create your DeleteCommand and UpdateCommand manually and assign them to the respective properties.

Upvotes: 1

Related Questions