Populate array from a dataset ?

Can someone point me to an example of populating an array from a dataset ?

This .net stuff is killing me.What are you trying to do in more detail? What goes in each element of the array? What about multiple tables?

Not that I'm a Datset fan but you have a lot more functionality with your data in a dataset than an array.

Populate dropdownlist...

I have a problem populating a dropdownlist with data from a dataset. Here is the code:

string strConnection="user id=logger;password=logger;";
strConnection+="database=MVS;server=4647117000N26";
SqlConnection objSqlConnection = new SqlConnection(strConnection);
objSqlConnection.Open();
DataSet objDataSet = new DataSet();
string strOrderDetails = "SELECT DISTINCT [job_name] FROM [mvs_jobs]";
SqlCommand objOrdDetailCommand = new SqlCommand(strOrderDetails,objSqlConnection);

SqlDataAdapter objODAdapter = new SqlDataAdapter(objOrdDetailCommand);






objODAdapter.Fill(objDataSet);
DropDownList1.DataSource = objDataSet;
DropDownList1.DataBind();
objSqlConnection.Close();

I am not really sure what the problem is, the dropdownbox is populated with "System.Data.DataRowView"...!?!

anyone who can help?
kind regards
Henrik...maybe you need to set the datamember property for the drop down list...
Try this:

string strConnection="user id=logger;password=logger;";
strConnection+="database=MVS;server=4647117000N26";
SqlConnection objSqlConnection = new SqlConnection(strConnection);
objSqlConnection.Open();
DataSet objDataSet = new DataSet();
string strOrderDetails = "SELECT DISTINCT [job_name] FROM [mvs_jobs]";
SqlCommand objOrdDetailCommand = new SqlCommand(strOrderDetails,objSqlConnection);

SqlDataAdapter objODAdapter = new SqlDataAdapter(objOrdDetailCommand);






objODAdapter.Fill(objDataSet);
DropDownList1.DataSource = objDataSet;
DropDownList1.DataTextField = "job_name"
DropDownList1.DataBind();
objSqlConnection.Close();
Thanks, it worked! Now all I have to do is to parse that variable to a new aspx page as parameter and build a crystal report based on the value... piece of cake, eh :)

kind regards
Henrik
Sweden
if you say so.....(piece of cake)......(smile).
Now it is done, I added a reportviewer webcontrol to a new pagepage and used the parameters from the previous page as parameters to the report, then I linked that with a report I have made in Crystal Reports 9. And it WORKS!!!! (on my localhost). I am still having troubles running it in our test-server. I thnk some Crystal dll files haven't been properly registered or even distributed. Crystal Reports works in a funny way, it follows its own standards. And I had to bring out my reference bible on C# in order to understand just how to use that reportviewer control.

Now comes the tricky part, to make the report portal dynamic with logins, profiles, possibility to add new reports to profiles etc etc etc..........the list can be as long as your imagination goes :)

kind regards
Henrik
I might add here as it's the asp.net forum that it is highly advisable to use a datareader to populate in a webform. It's faster than a dataadapter.
I use datareader for any web work. The dataset used with a dataadapter is only of any real use in windows based application.
Hmm, IC. Well I am still learning ASP.NET and I use the same code as I use in my C# desktop programs. Perhaps you can show a small example? I use MS SQL server.

kind regards
Henrik

Populate GridView with DataSet

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView
sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
dSet = New DataSet()
dView = New DataView
sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView
dgUsers.DataSource = dView
dgUsers.DataBind()
The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:
Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'
pointing to this line
gView = dSet.Tables("Users").DefaultView
How do I populate the GridView with the DataSet?Your DataSet and DataView variables stay the same. You will simply set
the DataSource of the GridView to your existing dView variable.
Using what you started with...

> sqlDapter.Fill(dSet, "Users")
> dView = dSet.Tables("Users").DefaultView

> dgUsers.DataSource = dView
> dgUsers.DataBind()
Assuming the GridView is named gvUsers, make the last two lines...
gvUsers.DataSource = dView
gvUsers.DataBind()
The DataGrid and GridView both take a DataView as the DataSource.
Brennan Stehling
http://brennan.offwhite.net/blog/
rn5a@.rediffmail.com wrote:
> I was using a DataView to bind records from a DB table to a DataGrid
> using the following code:
> Dim sqlDapter As SqlDataAdapter
> Dim dSet As DataSet
> Dim dView As DataView
> sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
> dSet = New DataSet()
> dView = New DataView
> sqlDapter.Fill(dSet, "Users")
> dView = dSet.Tables("Users").DefaultView
> dgUsers.DataSource = dView
> dgUsers.DataBind()
> The above works fine but I did like to bind the DataGrid to a GridView
> instead of a DataView as the above code shows. What I did is deleted
> the DataView from the above code & added a GridView i.e. all the
> instances of the DataView were replaced with GridView i.e. changed the
> variable name 'dView' to 'gView' but I get this error:
> Value of type 'System.Data.DataView' cannot be converted to
> 'System.Web.UI.WebControls.GridView'
> pointing to this line
> gView = dSet.Tables("Users").DefaultView
> How do I populate the GridView with the DataSet?

Populate GridView with DataSet

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:

Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView

sqlDapter = New SqlDataAdapter(strSQL, sqlConn)

dSet = New DataSet()
dView = New DataView

sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView

dgUsers.DataSource = dView
dgUsers.DataBind()

The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:

Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'

pointing to this line

gView = dSet.Tables("Users").DefaultView

How do I populate the GridView with the DataSet?Your DataSet and DataView variables stay the same. You will simply set
the DataSource of the GridView to your existing dView variable.

Using what you started with...

Quote:

Originally Posted by

sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView

Quote:

Originally Posted by

dgUsers.DataSource = dView
dgUsers.DataBind()

Assuming the GridView is named gvUsers, make the last two lines...

gvUsers.DataSource = dView
gvUsers.DataBind()

The DataGrid and GridView both take a DataView as the DataSource.

Brennan Stehling
http://brennan.offwhite.net/blog/
rn5a@.rediffmail.com wrote:

Quote:

Originally Posted by

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:
>
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView
>
sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
>
dSet = New DataSet()
dView = New DataView
>
sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView
>
dgUsers.DataSource = dView
dgUsers.DataBind()
>
The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:
>
Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'
>
pointing to this line
>
gView = dSet.Tables("Users").DefaultView
>
How do I populate the GridView with the DataSet?

Populate Labels with DataSet in code

I have several Label controls in my page and a stored procedure that always return 1 row. I want to call the same stored procedure with different parameter. Can anybody who me how to do this?

I have a function that contains:

 DataSet objDs =new DataSet();
 System.Data.SqlClient.SqlConnection myConnection =new System.Data.SqlClient.SqlConnection(
 System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
 System.Data.SqlClient.SqlDataAdapter cmd;
 cmd =new System.Data.SqlClient.SqlDataAdapter("sp_getCounter1", myConnection);
 cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
 cmd.SelectCommand.Parameters.Add(
new SqlParameter("@dotnet.itags.org.param1", mode));
 DataTable dtMasterListe =new DataTable("mytable");
 myConnection.Open();
 cmd.Fill(objDs);

With the above code it is easy to e.g bind to datagrid, but I want to achiveve the following Label1.Text = objDs.getstring(etc).

Any clues?

public string FunctionName(){using (SqlConnection cn =new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { SqlCommand cmd =new SqlCommand("StoredProcedureName", cn); cmd.CommandType = CommandType.StoredProcedure;// Add parameters if needed cn.Open();return (string)ExecuteScalar(cmd); }}void Page_Load(){ Label1.Text = FunctionName()}

This is what I usually do:

If you already sure the dataset only contains 1 table and 1 row only, then you can use the following to get the data you want:
Label1.Text = ds.Tables[0].Rows[0][0].ToString();

If you know the table name returned in the dataset, you can also pass in the tablename:
Label1.Text = ds.Tables["tablename"].Rows[0][0].ToString();

It will even be better if you can specify the column name:
Label1.Text = ds.Tables["tablename"].Rows[0]["Name"].ToString();

I usually check if the number of tables & rows > 0 then only get the value:
if ((ds.Tables.Count == 0) && (ds.Tables["tablename"].Rows.Count == 0))

Hope this helps. ^_^

Hi,

If you know that your stored procedure will return only one row all the time, then go for theExcecuteScalar method.

Works perfect!

Had to make a minor change:

int ret = (int)cmd.ExecuteScalar();
return ret.ToString();

to make ExecuteScalar to work.

Thanks.

Populate treeview from dataset

In a webform how do I populate a treeview from a dataset?
All the examples seem to use xml.
Thanks in AdvanceJust loop through the dataset and add items to the treeview programmatically.

There is no simple way like Treeview1.Data= YourDataSet, so you just have to do something like:

'I dont know the specific code for adding to a treeview, but it would be somthing like this
Dim tItem as TreeViewItem
For x = 0 to ds1.Rows.Count-1

tItem = New TreeViewItem
tItem.text = ds1.Rows(x).Item(0).ToSTring()
TreeView1.Items.Add(tItem)
Next

Populating a datagrid from two separate data sources

Hi all,

I have an interesting situation. I have a datagrid I'm currently populating
using a dataset but now I need to introduce some extra records into this.
I'm unsure of which method to use. Is it possible for me to populate (fill)
a dataset from another dataset and at the same time, make sure there are no
duplicates?

Regards
John.John,

Check out the Merge method of the DataSet class. It might just be what
you're after.

--
Carsten Thomsen
Enterprise Development with Visual Studio .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
"John" <a@.b.com> wrote in message
news:uITE8tpVDHA.384@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have an interesting situation. I have a datagrid I'm currently
populating
> using a dataset but now I need to introduce some extra records into this.
> I'm unsure of which method to use. Is it possible for me to populate
(fill)
> a dataset from another dataset and at the same time, make sure there are
no
> duplicates?
> Regards
> John.

Populating a Dataset

I've created a vb windows application and written the following function within a class (class1). I dragged a dataset onto my form and renamed it datBookInfo but when I run the code I don't get anything in the form.

Please tell me what I'm doing wrong.

 Public Overloads Function GetBookInfo() As DataSet
 Dim oPubConnection As SqlConnection
 Dim SConnString As String
 Dim osqlCommPubs As SqlCommand
 Dim osqlCommTitles As SqlCommand
 Dim oDataAdapterPubs As SqlDataAdapter
 Dim oDataAdapterTitles As SqlDataAdapter
 Try
 SConnString = "Data Source=(local);Initial Catalog=Pubs;" & _
 "User ID=sa;Password=;"
 oPubConnection = New SqlConnection(SConnString)
 oPubConnection.Open()
 'Create command to retrieve pub info
 osqlCommPubs = New SqlCommand
 osqlCommPubs.Connection = oPubConnection
 osqlCommPubs.CommandText = "Select Pub_ID, Pub_Name from Publishers"
 'Create Data Adapter for pub info
 oDataAdapterPubs = New SqlDataAdapter
 oDataAdapterPubs.SelectCommand = osqlCommPubs
 'Create command to retrieve title info
 osqlCommTitles = New SqlCommand
 osqlCommTitles.Connection = oPubConnection
 osqlCommPubs.CommandText = "select Pub_ID, title, price, ytd_sales from titles"
 'Create data adapter for title info
 oDataAdapterTitles = New SqlDataAdapter
 oDataAdapterTitles.SelectCommand = osqlCommTitles
 'Create and fill a data set
 Dim datBookInfo As DataSet = New DataSet
 oDataAdapterPubs.Fill(datBookInfo, "Publishers")
 oDataAdapterTitles.Fill(datBookInfo, "Titles")
 Return datBookInfo
 Catch ex As Exception
 Finally
 oPubConnection.Close()
 End Try
 End Function

check that if the dataset is populated when there is no PostBack.

If(!Page.IsPostBack)
{
 LoadData();
}

Hmm, dont see anything obvious...How are you doing your bind of the dataset to the grid?

Also, since this is a windows project and not a web project (assuming your "I've created a vb windows application" is correct) there will NOT be a page.isPostBack...

Thanks,
MajorCats

populating a dropdownlist with data from a multidimensional arrays

Hello,

I hope someone can help me. I have an array defined as a two dimensional array that was populated from a dataset. I want to take that array and populate a dropdownlist. The reason I created a two dimensional array was because in the first dimension I store the value of the dropdownlist and in the second dimension I store the text for the dropdownlist. The value is not just a column from the dataset but a string that is pieced together. Please take a look at the code below and tell me what I'm doing wrong. If someone can please help me. I'm really lost.

'This is were I populate the array
Dim objArray(,) As Object
ReDim objArray(intRowCnt - 1, intColCnt - 1)
 i = 0
 For Each dr In mDSet.Tables(0).Rows
 With mDSet.Tables.Item(0).Rows(i)
 If .Item("mbo_key").ToString.Length > 0 Then
 objArray(i, 0) = .Item(3).ToString.Trim & "|" & .Item(4).ToString.Trim
 objArray(i, 1) = .Item(1).ToString.Trim & " - " & .Item(2).ToString.Trim & " *"
 Else
 objArray(i, 0) = .Item("period_key").ToString
 objArray(i, 1) = .Item(1).ToString.Trim & " - " & .Item(2).ToString.Trim
 End If
 End With
 i += 1
 Next
With ddlDropDownList
 .DataSource = objArray
For i = 0 To intMBO
 ' this is were it's blowing up!!!!!
 .DataValueField = CStr(objArray(i, 0))
 .DataTextField = CStr(objArray(i, 1))
Next
 .DataBind()
 End With

Why again are you not binding it straight to the Dataset? You should be able to do this using a Dataset, even if it is a concatenation of certain columns (you may even be able to this at the DB level)

As for your problem...you may be able to fix it if instead of using a multidimensial array, use a arrayed class that you define, then you should be able to set the .datavalue/textfield to the class properties (yes use properties). It would look much cleaner too. Very simple class

Public Class Items
Public Property Text...
Public Property Value...
End Class

Hope this helps!
--Michael
I thank you for your help but I'm not sure what you mean. I created 2 Properties like you suggested but I get an error saying I can't convert an array to string when I try to assign it to the .DataValueField of the dropdownlist. Can you please show me an example because I'm really fustrated with this problem.

Also the reason I don't bind it to a dataset is because the value of an option changes depending on if a certain field is Null or not. I wasn't sure how to do this.

With selPeriod
 .DataValueField = mclsMBO.marstrValue
 .DataTextField = mclsMBO.marstrText
End With

I want to thank you for your help. You set a light off in my head and I ran with it. What I did was I added to Columns to the dataset and populated them with the info I needed it and then I binded those fields to the drop down list. Once again I want to say thanks.

Populating ComboBox From Dataset

Hi Guys,

I need to populate a combobox from a dataset returned from a webservice. I set the datasource property of the control to the web method but got an empty list at run time! What am I doing wrong? Any help on how to achieve this? Looping through a datareader solved the problem but I want to consume a dataset from a web service. I thought this would be a straight stuff but I'm having problems with it.After setting the datasource property you have to bind the data to your controll.

It seems you are missing that bit.
I can handle that with DropDownList control in ASP.Net but not with ComboBox in Windows Application! Could you kindly show how syntactically?
Are you getting an error?
What does it say?

Post your code so that we can see more clearly what has gone wrong
I am not getting any error message! I only get this wierd entry in the ComboBox. "System.Data.DataViewManagerListItemTypeDescriptor"

This is the code that I used.

Private Sub Users_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim wsDemo As New ng_app001_pc_stock.PCInventory
ComboBox1.datasource = wsDemo.getAllOSTypes
End Sub

The webmethod getAllOSTypes works well, at least I tested it with DataGrid control. What could be wrong?

Populating datagrid with columns with multiple tables.

Using ASP.NET (vb), I have two tables and joining on ID. I am having some issues populating my datagrid with a dataset.

Here is what I have so far:

Dim conPubs as SqlConnection
Dim dsSearch1 as DataSet
Dim dsSearch2 as DataSet
Dim adSearch1 as SqlDataAdapter
Dim adSearch2 as SqlDataAdapter
Dim iRecordsFound as Integer
Dim sSearch as String

conPubs = New SqlConnection(...)

sSearch = txtSearch.Text("'", "''")
If sSearch.Length > 0 Then
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "WHERE BRAND like '%" & sSearch & "' "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
Else
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
End If

adSearch1 = New SqlDataAdapter(sSQL1, conPubs)
adSearch2 = New SqlDataAdapter(sSQL2, conPubs)

dsSearch1 = New DataSet()
dsSearch2 = New DataSet()

adSearch1.Fill(dsSearch1, "t_products")
adSearch2.Fill(dsSearch2, "t_products")

Dim pk1(0) as DataColumn
Dim pk2(0) as DataColumn

pk1(0) = dsSearch1.Tables(0).Columns("PRD_ID")
dsSearch1.Tables(0).PrimaryKey = pk1

pk2(0) = dsSearch2.Tables(0).Columns("PRD_ID")
dsSearch2.Tables(0).PrimaryKey = pk2

dsSearch1.Merge(dsSearch2, false, MissingSchemaAction.Add)

conPubs.Open()
iRecordsFound = dsSearch1.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

datagridOutput.DataSource = dsSearch1
datagridOutput.DataBind()
conPubs.Close()


The error I get now is:
System.ArgumentNullException: 'column' argument cannot be null. Parameter name: column

Any help would be great or better way of doing this is welcome as well.
Thanks.Probably because sSQL2 isn't selecting PRD_ID.
Is there a reason your not joining the tables in your sql statment?
Yeah, your right. I needed the PRD_ID in the sSQL2. I don't get errors but my datagrid says I have over 6 pages (of 25) but I can only see 2.

You suggestion why not putting the sql all in one. I did try this way but could not get to work properly. This is how I would like to do it but couldn't get my code to work.

Any ideas?
Where you using a JOIN or just WHERE X=Y?
TPM - Using x = y.

Update: I tried again and worked so not sure what I did differently but not complaining. This is what I have:

Dim conPubs as SqlConnection
Dim cmdSearch as SqlCommand
Dim dsSearch as DataSet
Dim SadSearch as SqlDataAdapter
Dim sSQL as String
Dim iRecordsFound as Integer
Dim sSearch as String

'Open connection with connection object
conPubs = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MS_SQL_CONN"))

sSearch = txtSearch.Text.Replace("'", "''")
sSearch = txtSearch.Text.Replace(";", vbNullString)

'form sql
If sSearch.Length > 0 Then
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd "
sSQL &= "WHERE p.PRD_ID = pd.PRD_ID "
sSQL &= "AND BRAND Like '%" & sSearch & "%' OR PMODEL Like '%" & sSearch & "%' "
sSQL &= "ORDER BY BRAND, MODEL"
Else
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd WHERE p.PRD_ID = pd.PRD_ID ORDER BY BRAND"
End If

'To execute sql statement and provide active connection
SadSearch = New SqlDataAdapter(sSQL, conPubs)

'Create instance of dataset object
dsSearch = New DataSet()

'fill datagrid
SadSearch.Fill(dsSearch, "t_products")

conPubs.Open()
iRecordsFound = dsSearch.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

dgrdResults.DataSource = dsSearch
dgrdResults.DataBind()
conPubs.Close()

Do you see any advantage to using the JOIN?
Thanks