How to link update table with data grid view without command builder

Question

Im trying to use a datagridview to view and edit the tables data, i was using a command builder but i cannot update to multiple tables using it (because im using a view). The error i was recieving was - "Dynamic SQL generation is not supported against multiple base tables.". Was wondering if anyone could explain to me how to approach this thanks

Vulpes · Answer

Instead of using a SqlCommandBuilder you'll need to write your own INSERT UPDATE and DELETE commands and assign them to the SqlDataAdapter's InsertCommand UpdateCommand and DeleteCommand properties respectively. When you call the SqlDataAdapter's Update() method these commands will then be executed for each row in the table that has been inserted updated or changed. There's an example here on MSDN that should give you some idea of what to do: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand.aspx

Luke Baker · Answer

Ok so this is what im trying - [CODE] private SqlConnection connect; private SqlDataAdapter da; private DataTable dt; string conStr = Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NTO.mdf;Integrated Security=True;User Instance=True; public void Fill(string command string update string table) { connect = new SqlConnection(conStr); connect.Open(); da = new SqlDataAdapter(); SqlCommand sqlCommand = new SqlCommand(command connect); da.SelectCommand = sqlCommand; sqlCommand = new SqlCommand(update connect); da.UpdateCommand = sqlCommand; dt = new DataTable(table); da.Fill(dt); } public void Update() { if (da != null) da.Update(dt); } public void CloseConnection() { if (da != null && connect != null) { da.Dispose(); da = null; connect.Close(); connect = null; } } [/CODE] I am calling this functions in this order [CODE] if (txtNew.Text == txtConfirmNew.Text) { select = Select * from staff; update = @UPDATE staff SET password = ' + txtNew.Text + ' + where staff_No = ' + Global.staffID + '; dc.Fill(select update staff); } [/CODE] [CODE] private void button2_Click(object sender EventArgs e) { dc.Update(); MessageBox.Show(updated); } [/CODE] [CODE] private void btnMenu_Click(object sender EventArgs e) { MainMenu menu = new MainMenu(); this.Hide(); menu.Show(); dc.CloseConnection(); } [/CODE]

Vulpes · Answer

You need to separate the various aspects from each other and use some fields rather than local variables: private SqlConnection connect; private SqlDataAdapter da private DataTable dt; public void Fill(string command string update string table) { connect = new SqlConnection(conStr); connect.Open(); da = new SqlDataAdapter(); SqlCommand sqlCommand = new SqlCommand(command connect); da.SelectCommand = sqlCommand; sqlCommand = new SqlCommand(update connect); da.UpdateCommand = sqlCommand; dt = new DataTable(table); da.Fill(dt); } public void Update() { if (da != null) da.Update(dt); } public void CloseConnection() { if (da != null && connect != null) { da.Dispose(); da = null; connect.Close(); connect = null; } } So after you've made some changes you can call the Update() method and when you've finished with the connection call CloseConnection().

Luke Baker · Answer

Do you have any suggestions of how i could do it? im so confused sorry

Vulpes · Answer

It appears that you're trying to load the DataTable from the database and then update it straightaway without actually making any changes?

Luke Baker · Answer

Right so im just trying an update on the table for the minute below is the code im attempting - [code] public void update(string commandstring update string table) { SqlConnection connect = new SqlConnection(conStr); connect.Open(); SqlDataAdapter da = new SqlDataAdapter(); SqlCommand sqlCommand = new SqlCommand(commandconnect); da.SelectCommand = sqlCommand; sqlCommand = new SqlCommand(update connect); da.UpdateCommand = sqlCommand; DataTable dt = new DataTable(table); da.Fill(dt); da.Update(dt); connect.Close(); } [/code] and for some reason no update to the table is taking place