Filling a datagrid from a Stored Proc.

[SteveInBeloit]

Hello,
I am trying to fill two datagrids on my form from stored procedures. I have not done this before (datagrids).
I opened Server Exp. and dragged my stored proc onto the form. I can see in generated code this gave me my connection string, and the command set to me stored proc. It also added the parameters.

Now what I think I need to do is in the load of the form, set the parameters value, make a sqldataadapter, make a dataset, and bind a datagrid to the dataset. I think. I am unsure how to make the dataset and bind the datagrid to it.

I keep going through the examples, but they all deal with one particular table, not stored procs. I can't seem to make it work with the stored procs.

Any help is really appriciated.

Thanks,
Steve

 

[SteveInBeloit]

Here is a little more info, here is what I am down to, but still doesn't work, the datagrid is empty:

Me.SqlCommand1.Parameters("@ForkLift").Value = 1
Me.SqlCommand1.Parameters("@OpenClosed").Value = 1
Me.SqlCommand1.Parameters("@SortBy").Value = "tkpriority"
Me.SqlCommand1.Parameters("@AD").Value = "A"

Dim daGrid1 As New System.Data.SqlClient.SqlDataAdapter(Me.SqlCommand1)
Dim dsGrid1 As New DataSet

dsGrid1.Clear()
daGrid1.Fill(dsGrid1)

 

[SteveInBeloit]

I have made some progress.

Here is what I have:

Dim daGrid1 As New SqlDataAdapter(Me.SqlCommand1)
Dim dsGrid1 As New DataSet

dsGrid1.Clear()
daGrid1.Fill(dsGrid1, "top")
DataGrid1.DataSource = dsGrid1.Tables("top").DefaultView

It will display the returned rows in the datagrid on the form.

If I am doing these in a poor manner, please let me know.

Now that I have the datagrid populated, I see that every column returned by the stored proc is showing. I only want a subset of these fields to show. I also want to be able to click on a record, or button to the right of the record, and go to another form with information about that record. I must be seaching on the wrong topics because I cannot find that covered anywhere in datagrids.

Any help.

Steve

 

[SteveInBeloit]

Datagrid help

Hi,
I have filled two datagrids on the form from stored procs. The data grids contain every field that the procs returned. I only want some of the fields to show. How can I just show a subset of them.

Also, I want to be able to click on a button by each row, and have it send me to another form with information about that row. How can this be done?

Thanks

 

[aBsOlUt]

Are you going to use these columns later on? There are several possible solutions in your problem.

1) Update your stored procedure so it does not return these columns. If you are not going to use them then you do not need them anyway.

2) Remove the columns from your DataTable (or DataSet) manually. For example, dataSet.tables("top").Columns.Remove("<columnName>"). That is not a very good solution as you will not be able to update or retrieve data from these columns anymore.

3) Try using
dataSet.Tables("top").Columns("<columnName>").ColumnMapping = MappingType.Hidden

4) Manually hide the column from your dataGrid not your dataSet.
dGrid1.Columns("<columnName>").Visible = false

As for editing a record on a new form, you have to create a new form with all the editing details and then bind your textboxes to a DataRowView object. You can also do it manually but this will make it a bit harder to update stuff as everything the user types will not actually enter your dataSet. It gets a bit tricky then...take a look at some tutorials around the web.

 

[SteveInBeloit]

Thanks for the suggestions.

Will I use these columns. I will one of them, the primary key to that particular record. I want to click on this record, and have it go to another form passing the primary key, and in that form, I'll call a stored proc with the key.

I will try your suggestions of hiding them this morning.

I do need to find out how to click on a record and pass the value of the hidden column.

Thanks,
Steve

 

[SteveInBeloit]

I am trying to hide the columns on the datagrid:

dGrid1.Columns("<columnName>").Visible = false

.Columns is not a property of the datagrd. Did you mean a different one. I looked through them, but couldn't find something that was close. I would like to rename the column headings also, but can't get that.

I am trying to follow the walk throughs, but it isn't going well. I think maybe because I am filling the dataset at run time from a stored proc, and binding the datagrid at runtime? The example in the book don't really cover that.

Kind of struggling...

 

[vis781]

Columns is a property of the datagrid.

 

[SteveInBeloit]

I'm not argueing that it is not, I just can't find it:

++ generated Me.DataGrid1 = New System.Windows.Forms.DataGrid

Dim daGrid1 As New SqlDataAdapter(Me.SqlCommand1)
Dim dsGrid1 As New DataSet

dsGrid1.Clear()
daGrid1.Fill(dsGrid1, "top")

DataGrid1.SetDataBinding(dsGrid1, "top")

Now if I type:
DataGrid1.
Columns does not come up in the list. . .

I'm sure I am doing something wrong, but can't find it.

 

[vis781]

No, i take it back aBsOlUt was talking about a datatable not a datagrid which is what your datagrid is bound to.

 

[SteveInBeloit]

OK, that must explain it.
So is there any way to get what I am needing? Hide some columns, change the headings, and click on a record and grab the "hidden" primary key and pass it to another form?
Thanks

 

[aBsOlUt]

I am not quite sure if this has to do with the fact that you are using .NET 1.1 and VS 2003.

In .NET 2.0 the dataGrid object became a dataGridView object and I believe it actually has a Columns property...

Sorry for the confusion if thats the case...I am not in my computer currently so I cant check it out.

edit: That seems to be the case, .NET 2.0 with dataGridView includes a 'Columns' property that you can also specify an index or column name. You'd be better off with other solutions unless you upgrade to .NET 2.0.