[RESOLVED] How to read values from a datatable?

[gggram2013]

Hi, I know this seems very simple, I have the datatable filled with the records and all i want is to increment it. My code looks like this:

SqlDataAdapter da = new SqlDataAdapter(cmd);
//this is creating a data table
DataTable dt = new DataTable();
int invNum = da.Fill(dt);

invNum = invNum + 1;

Is this code correct?

[marceln]

Not sure what you mean? Increment what? Your thread title says otherwise. I understand that you want to read the rows from the data table.
Well, you can do it like this:

foreach(DataRow dr in dt.Rows)
{
string column1 = dr["your_first_column_name"];
//... and so on.
}


Regards

[gggram2013]

Yea I guess I it was kinda vague, well I'm calling a function in sql server that obtains the highest number from a column called InvoiceNumber in table Sales. In visual Studio 2005 I call the function then when i fill (dt) datatable, it gives 13 as the highest (eg.max(InvoiceNumber)=13)
then when I get that I want to read it as an int and increment it so it becomes 14 and then display 14 at run time...I hope that is more clear. My problem is when i run the program with the code I posted i only get 13...it doesn't increment to 14. Thanks in advance...

[jshultz]

I know this doesn't answer your question, but dataadapter.fill() returns the number of rows affected by your query, not the result of your query, that should be being stored in the datatable.

[gggram2013]

OK here's my code:

SqlConnection dbConnection = new SqlConnection(xmldb);
SqlCommand cmd = new SqlCommand(" SELECT * FROM dbo.fnInvoiceID() ", dbConnection);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

int invNum = da.Fill(dt);
invNum = invNum + 1;

comboBox4.DataSource = dt;
comboBox4.DisplayMember = "InvoiceNumber";
comboBox4.ValueMember = "InvoiceNumber";

I would like to be able to put:
comboBox4.DataSource = invNum;
In this manner I would get the +1 increment that I want. I succesfully obtain 13 from the function...any other suggestions?

[gggram2013]

When I try:
comboBox4.DataSource = invNum;
I get this error:
"Complex DataBinding accepts as a data source either an IList or an IListSource."

[dglienna]

It's telling you the same thing the we did.

dataadapter.fill() returns the number of rows affected by your query

so that is the wrong way to get data.

[Thread1]

OK here's my code:

SqlConnection dbConnection = new SqlConnection(xmldb);
SqlCommand cmd = new SqlCommand(" SELECT * FROM dbo.fnInvoiceID() ", dbConnection);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

int invNum = da.Fill(dt);
invNum = invNum + 1;

comboBox4.DataSource = dt;
comboBox4.DisplayMember = "InvoiceNumber";
comboBox4.ValueMember = "InvoiceNumber";

I would like to be able to put:
comboBox4.DataSource = invNum;
In this manner I would get the +1 increment that I want. I succesfully obtain 13 from the function...any other suggestions?

just to get you going you can add another column to your SQL that says "InvoiceNumber + 1" and then use that column in your databinding..



SqlConnection dbConnection = new SqlConnection(xmldb);
SqlCommand cmd = new SqlCommand(" SELECT *, (InvoiceNumber + 1) AS InvPlusOne FROM dbo.fnInvoiceID() ", dbConnection);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

int invNum = da.Fill(dt);
invNum = invNum + 1;

comboBox4.DataSource = dt;
comboBox4.DisplayMember = "InvPlusOne";
comboBox4.ValueMember = "InvPlusOne";


however, if your SQL will return only one row and a column you may just use the <command object>.ExecuteScalar() and then assign the value straight to a textbox.

[JonnyPoet]

When I try:
comboBox4.DataSource = invNum;
I get this error:
"Complex DataBinding accepts as a data source either an IList or an IListSource."Thats totally correct as an int is an int and incremented or not, never a datasource. BTW incrementing simple can be done with
invNum++;

But understand this is an invoicenumber but no dataobject !! So the idea you had
I would like to be able to put:
comboBox4.DataSource = invNum;
In this manner I would get the +1 increment that I want. I succesfully obtain 13 from the function...any other suggestions?Thats a totally wrong concept. Because its a counter only, So incrementing it doesn't give you the next record or whatever you want to achieve.

[gggram2013]

By the way, Thanks Thread1! Your example works perfectly, appreciate it. How would it change if I wanted the value in a label instead of a comboBox?

[jshultz]

Thread1 told you pretty much exactly how to do it, you should really read the responses to your question.

[gggram2013]

Yea, when I try to put a label with a dataSource, display member or value member it doesn't work...

[jshultz]

You should elaborate on "it doesn't work..." when posting problems in a forum. What doesn't work? What error does it give you?

[gggram2013]

Well basically I don't know how to code it so as to display the value on a label instead of a comboBox. eg:
da.Fill(dt);
comboBox.DataSource = dt;
comboBox.DisplayMember = "InvPlusOne";
comboBox.ValueMember = "InvPlusOne";
I would want to put:
label.DataSource = dt;
label.DisplayMember = "InvPlusOne";
label.ValueMember = "InvPlusOne";
But that does not function since label doesn't have those properties.

Nevertheless, I'm gonna use a comboBox and try hiding it meanwhile, so thanks for ur assistance!

[jshultz]

Well, you could just read it from the dataset instead of setting the datasource.
Just change the datatable to a dataset and do roughly the following:


ds.Tables["yourtablename"].Rows[0]["InvPlusOne"].ToString()


Somethin like that should work, I believe (not tested).

[Thread1]

Well basically I don't know how to code it so as to display the value on a label instead of a comboBox. eg:
da.Fill(dt);
comboBox.DataSource = dt;
comboBox.DisplayMember = "InvPlusOne";
comboBox.ValueMember = "InvPlusOne";
I would want to put:
label.DataSource = dt;
label.DisplayMember = "InvPlusOne";
label.ValueMember = "InvPlusOne";
But that does not function since label doesn't have those properties.

Nevertheless, I'm gonna use a comboBox and try hiding it meanwhile, so thanks for ur assistance!

it is not always the case that you have to use databinding (using DataSource) when assigning values from database to a control. if you just want to display a value (scalar), you could assign it to the text/value property of the control. in your case...


label.Text = dt[0][0].ToString();

[gggram2013]

Hey it works Great now. Thanks for ur help Thread1, it was really helpful.
I put:

label.Text = dt.Rows[0]["InvPlusOne"].ToString();

Appreciate everyone's help!
George