Dataset & DataGrid???

I have a dataset that holds some data, and when i show it in the data grid i would like it to be editable, and changed in my dataset, can anyone send me a link or shed some light on how i should get this done

Answer this question

Dataset & DataGrid???

Manash

it should be:

SqlCommand scmd = new SqlCommand(str, connectionstring);

this.da = new SqlDataAdapter(scmd);

as for the command builder - try SqlCommandBuilder, that is if you are not using the above approach

Stanomatic

Well it stop throwing the error but my changes dont commit....Do i need to add the AceptChanges() method to the dataset or something here is the code that gives me no results other than just showing the data in the datagrid....

private void button1_Click(object sender, System.EventArgs e)
{
try

{
string connect = System.Configuration.ConfigurationSettings.AppSettings["connstring"];
SqlConnection connectionstring = new SqlConnection(connect);
string str = "Select * From Customers";
SqlCommand scmd = new SqlCommand(str, connectionstring);

this.da = new SqlDataAdapter(str, connectionstring);
connectionstring.Open();
da.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
//string o = ds.Tables[0].Rows[2].ToString();

//MessageBox.Show(o);

}
catch(Exception es)
{
MessageBox.Show(es.Message.ToString());
}
}
private void button2_Click(object sender, System.EventArgs e)
{
try

{
this.da.InsertCommand = new SqlCommandBuilder(this.da).GetInsertCommand();
}
catch(Exception da)
{
MessageBox.Show(da.Message.ToString());
}
}
}

GazCoder

do not use acceptchanges() otherwise when you update, it will not update the changes to the database since the rows modified in the dataset have been marked to "not needed to be updated"

Remember, you need to rebind the datagridview after you have done an update() - do you see the updates commited in SQL Server database/table To be safe for now, I would suggest you comment out the SqlCommandBuilder and just do an Update() directly after making sure that your SqlDataAdapter has the SqlCommand in its constructor as explained earlier. If this does not work (it should) then of course, use the CommandBuilder
PeterVrenken

yes, you need to call the Update(dataset) method of the DataAdapter to finally commit the changes back to SQL, the changes will be made to the dataset but you need to Update() the database so it will look for the rows marked as say, "changed", which will then appropriately update the database.

the DataAdapters' Update() method requires the dataset as the parameter, or a datatable containing the records filled with.
GT1000

So I would also need to create an Insert Statement to do this, cause i am getting an exception

"Update requires a valid InsertCommand when passed DataRow collection with new rows." here is my stacktrace

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)

at WindowsApplication12.Form1.button2_Click(Object sender, EventArgs e) in c:\

so i should just create another sqlcommand for inserting into the db and just pass that in my Update()
learning_new

I dont see the CommandBuilder in the intellisense.....Also I didnt pass the sqlcommand to the adapter.... I thought i did that with this line of code here

SqlCommand scmd = new SqlCommand(str, connectionstring);

this.da = new SqlDataAdapter(str, connectionstring);

when i tried to pass it "scmd" the compiler got madd and said i can not convert command tostring()......
Jumperboy

theDataGridView.DataSource = theDataSet.Tables[0].DefaultView;

is how you would rebind the dataset to the datagridview

look into SQL Server table directly (open SQL Server) and see if the changes are being committed
Kurt Jaegers
Can i do that in the same button click event Because if i click the button the data loads then its going to call the update before I insert the data in the grid right so what should be the best way to get this done should i just make a another button that just handles the update
paulabq

yes, another button like a "save" button but a method would be ideal so not only can you call it from a "save" button but also when you are exiting the application, but remember to make your dataAdapter and dataset private global variables as to me it seems like you declared them in the local scope of a button, so add them to the top of the class, just after the class declaration

something like:

public class theClass

{

private DataSet ds;

private SqlDataAdapter da;

...

...

}

then to access it from within the class:

this.ds........

this.da.........

edmsing

The changes are not be commited to the SqlServer, and this code below does not change my results......what am i missing here, I am using the Northwind DB in SqlServer to test this code.....

private void button1_Click(object sender, System.EventArgs e)
{
try

{
string connect = System.Configuration.ConfigurationSettings.AppSettings["connstring"];
SqlConnection connectionstring = new SqlConnection(connect);
string str = "Select * From Customers";
SqlCommand scmd = new SqlCommand(str, connectionstring);

this.da = new SqlDataAdapter(scmd);
connectionstring.Open();
da.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
//string o = ds.Tables[0].Rows[2].ToString();

//MessageBox.Show(o);

}
catch(Exception es)
{
MessageBox.Show(es.Message.ToString());
}
}
private void button2_Click(object sender, System.EventArgs e)
{
try

{
this.da.UpdateCommand = new SqlCommandBuilder(this.da).GetUpdateCommand();
//this.da.Update(ds);

dataGrid1.DataSource = ds.Tables[0].DefaultView;

}
catch(Exception da)
{
MessageBox.Show(da.Message.ToString());
}
}

Al Stark

Ok maybe i am just not doing it write.....I run my application which has button control and when i click the button my datagrid become populated with data...then i got to the bottom of my datagird and i enter data in the columns, then i exit my application, now when i run it again the data is not there.....thats the problem i am having....how could i go about solving it

take a look at the code is stright forward

private void button1_Click(object sender, System.EventArgs e)
{
try

{
string connect = System.Configuration.ConfigurationSettings.AppSettings["connstring"];
SqlConnection connectionstring = new SqlConnection(connect);
string str = "Select * From Customers";
SqlCommand scmd = new SqlCommand(str, connectionstring);
DataSet ds = new DataSet("Customers");
SqlDataAdapter da = new SqlDataAdapter(str, connectionstring);
connectionstring.Open();
da.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
string o = ds.Tables[0].Rows[2].ToString();
MessageBox.Show(o);
}
catch(Exception es)
{
MessageBox.Show(es.Message.ToString());
}
}
}
}

D.A.V.E.

thread moved to the appropriate forum

usually it is done automatically when you bind the datagrid to the dataset in the datasource, changes made in the datagrid will be automatically made to the dataset. What problems are you having
04-digit0l
"you need to rebind the datagridview after you have done an update()" how do i do that I also did the update directly and it threw the error again, and i also added the scmd to the adapter before i did that....When i use the commandbuilder it works but the changes are not commited, so maybe thats because i did not rebind the datagridview after i did the update.....
Will Merydith

you can use a CommandBuilder but usually if you have a Select command, it should be automatically generated for you.

So...

this.da.UpdateCommand = new CommandBuilder(this.da).GetUpdateCommand();

then try to perform your update

I've also just noticed you did not pass the SqlCommand into the SqlDataAdapter constructor - this could be one reason why it is throwing the error
Dataset & DataGrid???

Answer this question