Retrieving Data

[waelr]

to retrieve data im using this code:

SqlConnection1.Open()
Dim x As SqlClient.SqlDataReader
Dim s
SqlCommand1.CommandText = "Select sum(I_AMOUNT) from INVOICES where I_YEAR = '" & ComboBox6.Text & "'"
x = SqlCommand1.ExecuteReader()
While (x.Read())
s = x.GetValue(0)
End While
x.Close()
TextBox1.Text = s
SqlConnection1.Close()

this code allows me to retrieve only one row of data
what should i do in order to retrieve many rows and display them in a datagrid
is there something to use instead of x.getvalue(0)

[jpgonc]

I don't know if this works...

Put a breakpoint on while and see if it loops more than one time.
If it does, put a Count = Count+1 before the GetValue and then change s = x.GetValue(0), to s = x.GetValue(count)

NOT TESTED! See for yourself.

[jmcilhinney]

It probably does get more than one row of data but you're only taking a value from the very last row because you are overwriting the contents of the "s" variable each time through the loop.

[jpgonc]

Try this...

SqlConnection1.Open()
Dim x As SqlClient.SqlDataReader
Dim s
SqlCommand1.CommandText = "Select sum(I_AMOUNT) from INVOICES where I_YEAR = '" & ComboBox6.Text & "'"
x = SqlCommand1.ExecuteReader()
While (x.Read())
count=count+1
s = x.GetValue(count)
textbox1.text=s ' Set Multiline=TRUE in the object properties.... or make an array
End While
x.Close()
SqlConnection1.Close()

[waelr]

I need to display my data in a DataGrid

[Asgorath]

waelr its normal it only return one row your using the aggregate function SUM .

Regards
Jorge

[jpgonc]

LOL Asgorath, didn't noticed that...

Waelr, run your SQL and see how many records you get...lol.

[Asgorath]

Once you modified your sql query just use a SqlDataAdapter1 to fill a dataset and assign it to your datagrid datasouce property.
Regards
Jorge

[waelr]

LOL
i copied and pasted the code without reading
forget about the sum
the problem persists even if i select something else
i tried assignining a datasource to the datagrid after filling the dataset but i get a problem with a "+" sign on the top left of the grid which i need to press on to display the results

[mendhak]

Set the datasource of the datagrid to a particular table in the dataset, like so:

DataGrid1.DataSource = ds.Tables(0)