Can add new data to DataGridView but can't save it!

[teddysnips]

WINDOWS FORM>
We are migrating an app from VS2003 using DataGrid to VS2005 using
DataGridView. Naturally, all our DataGrid helper classes are now
redundant. This is more or less my first exposure to this technology
- I've spent the past five years on ASP.NET

I want to create a very simple builder form (one that allows the user
to modify tables that hold look-up data for the main application - for
example, my current builder is "Location Type", and contains values
such as "School", "Post Office", "Community Hall" etc.)

The new, VS2k5 form is required to have the same look and feel as the
previous version. It's got "New", "Save" and "Delete" buttons.

The form has a BindingSource object and this is set to a DataTable at
runtime by executing a stored procedure:

Me.bsLocationType.DataSource = GetGridData()
......
Public Function GetGridData() As DataTable

' Get dataview based on criteria
Dim ds As DataSet =
SqlHelper.ExecuteDataset(DBConnection.ConnectionString,

CommandType.StoredProcedure, "stpGetLocationType")
' Return data
Return ds.Tables(0)

End Function
......
The SQL in the SPROC is:

SELECT
LocTypeID,
LocTypeCode AS [Type Code],
LocTypeDescription AS [Location Description]
FROM
tblLocationType
ORDER BY
LocTypeDescription
......
If the user presses "New" I can persuade the DataGridView to add a new
row, and move the focus to that row (I do this by adding a row to the
underlying DataTable) though I'm struggling to get the focus to the
first editable cell in the row.

Dim dt As DataTable = Me.bsLocationType.DataSource
Dim row As DataRow = dt.NewRow()
dt.Rows.Add(row)
Dim rowIndex As Integer = Math.Max(0, bsLocationType.Count)
dgRecords.CurrentCell = dgRecords.Rows(rowIndex - 1).Cells(1)

The user can then enter data to the new row.

BUT.

BUT.

No matter what I try, I can't seem to get the data to save! I'm sure
I could roll my own function - something along the lines of either a)
generating some in-line SQL - e.g.

Dim strSQL As String = "INSERT INTO LocationType (Code, Description)
VALUES (" & dgRecords.Rows(rowIndex - 1).Cells(1).Value & ", " &
dgRecords.Rows(rowIndex - 1).Cells(2).Value & ")"

or b) using the row values as parameters to an INSERT stored
procedure.

and run either of these against the database. Bit clunky though, and
sort of takes away the point of DataBinding, to me at any rate. If
the user tabs or moves away from the "new" row the data persists in
the grid, but if I close the form, the data disappears. In other
words, the DataTable to which the DataViewGrid has the data, but it's
not being written back to the Database.

Thoughts?

Edward

 

[Cor Ligthert[MVP]]

Teddy,

The windowform datagrid (as well as the aspnet datagrid) are still in
VS2005, it is not direct in the toolbox, however, with not much more than a
simple click in the dialog to add, will add it to that toolbox.

The change that it ever disappears from dotNet is in my idea low, breaking
changes of the existing code is a very important isue not to be done for the
VB develloping team.

Cor

WINDOWS FORM>
We are migrating an app from VS2003 using DataGrid to VS2005 using
DataGridView. Naturally, all our DataGrid helper classes are now
redundant. This is more or less my first exposure to this technology
- I've spent the past five years on ASP.NET

I want to create a very simple builder form (one that allows the user
to modify tables that hold look-up data for the main application - for
example, my current builder is "Location Type", and contains values
such as "School", "Post Office", "Community Hall" etc.)

The new, VS2k5 form is required to have the same look and feel as the
previous version. It's got "New", "Save" and "Delete" buttons.

The form has a BindingSource object and this is set to a DataTable at
runtime by executing a stored procedure:

Me.bsLocationType.DataSource = GetGridData()
.....
Public Function GetGridData() As DataTable

' Get dataview based on criteria
Dim ds As DataSet =
SqlHelper.ExecuteDataset(DBConnection.ConnectionString,

CommandType.StoredProcedure, "stpGetLocationType")
' Return data
Return ds.Tables(0)

End Function
.....
The SQL in the SPROC is:

SELECT
LocTypeID,
LocTypeCode AS [Type Code],
LocTypeDescription AS [Location Description]
FROM
tblLocationType
ORDER BY
LocTypeDescription
.....
If the user presses "New" I can persuade the DataGridView to add a new
row, and move the focus to that row (I do this by adding a row to the
underlying DataTable) though I'm struggling to get the focus to the
first editable cell in the row.

Dim dt As DataTable = Me.bsLocationType.DataSource
Dim row As DataRow = dt.NewRow()
dt.Rows.Add(row)
Dim rowIndex As Integer = Math.Max(0, bsLocationType.Count)
dgRecords.CurrentCell = dgRecords.Rows(rowIndex - 1).Cells(1)

The user can then enter data to the new row.

BUT.

BUT.

No matter what I try, I can't seem to get the data to save! I'm sure
I could roll my own function - something along the lines of either a)
generating some in-line SQL - e.g.

Dim strSQL As String = "INSERT INTO LocationType (Code, Description)
VALUES (" & dgRecords.Rows(rowIndex - 1).Cells(1).Value & ", " &
dgRecords.Rows(rowIndex - 1).Cells(2).Value & ")"

or b) using the row values as parameters to an INSERT stored
procedure.

and run either of these against the database. Bit clunky though, and
sort of takes away the point of DataBinding, to me at any rate. If
the user tabs or moves away from the "new" row the data persists in
the grid, but if I close the form, the data disappears. In other
words, the DataTable to which the DataViewGrid has the data, but it's
not being written back to the Database.

Thoughts?

Edward

 

[teddysnips]

Teddy,

The windowform datagrid (as well as the aspnet datagrid) are still in
VS2005, it is not direct in the toolbox, however, with not much more than a
simple click in the dialog to add, will add it to that toolbox.

The change that it ever disappears from dotNet is in my idea low, breaking
changes of the existing code is a very important isue not to be done for the
VB develloping team.

Cor

Thank you for taking the time to respond. However, you are responding
to a question that I didn't ask - the decision to replace the DataGrid
with the DataGridView has already been taken. There is a very good
reason for this - in terms of our application the DataGrid has a
killer bug that has never been resolved.

So, once again, how do I do the save on the DataGridView please?

Edward

 

[Kevin Spencer]

DataSet and DataTable are by nature disconnected. It is not a given that a
DataTable or a DataSet will have a database behind them. Therefore, you must
update the database from the DataSet or DataTable yourself.

--
HTH,

Kevin Spencer
Microsoft MVP

DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

WINDOWS FORM>
We are migrating an app from VS2003 using DataGrid to VS2005 using
DataGridView. Naturally, all our DataGrid helper classes are now
redundant. This is more or less my first exposure to this technology
- I've spent the past five years on ASP.NET

I want to create a very simple builder form (one that allows the user
to modify tables that hold look-up data for the main application - for
example, my current builder is "Location Type", and contains values
such as "School", "Post Office", "Community Hall" etc.)

The new, VS2k5 form is required to have the same look and feel as the
previous version. It's got "New", "Save" and "Delete" buttons.

The form has a BindingSource object and this is set to a DataTable at
runtime by executing a stored procedure:

Me.bsLocationType.DataSource = GetGridData()
.....
Public Function GetGridData() As DataTable

' Get dataview based on criteria
Dim ds As DataSet =
SqlHelper.ExecuteDataset(DBConnection.ConnectionString,

CommandType.StoredProcedure, "stpGetLocationType")
' Return data
Return ds.Tables(0)

End Function
.....
The SQL in the SPROC is:

SELECT
LocTypeID,
LocTypeCode AS [Type Code],
LocTypeDescription AS [Location Description]
FROM
tblLocationType
ORDER BY
LocTypeDescription
.....
If the user presses "New" I can persuade the DataGridView to add a new
row, and move the focus to that row (I do this by adding a row to the
underlying DataTable) though I'm struggling to get the focus to the
first editable cell in the row.

Dim dt As DataTable = Me.bsLocationType.DataSource
Dim row As DataRow = dt.NewRow()
dt.Rows.Add(row)
Dim rowIndex As Integer = Math.Max(0, bsLocationType.Count)
dgRecords.CurrentCell = dgRecords.Rows(rowIndex - 1).Cells(1)

The user can then enter data to the new row.

BUT.

BUT.

No matter what I try, I can't seem to get the data to save! I'm sure
I could roll my own function - something along the lines of either a)
generating some in-line SQL - e.g.

Dim strSQL As String = "INSERT INTO LocationType (Code, Description)
VALUES (" & dgRecords.Rows(rowIndex - 1).Cells(1).Value & ", " &
dgRecords.Rows(rowIndex - 1).Cells(2).Value & ")"

or b) using the row values as parameters to an INSERT stored
procedure.

and run either of these against the database. Bit clunky though, and
sort of takes away the point of DataBinding, to me at any rate. If
the user tabs or moves away from the "new" row the data persists in
the grid, but if I close the form, the data disappears. In other
words, the DataTable to which the DataViewGrid has the data, but it's
not being written back to the Database.

Thoughts?

Edward

 

[Cor Ligthert[MVP]]

Teddy,

Normally the same as with the datagrid as it is only about one table. The
datagridview has some more and some less possibilities, it is not a
replacement. A datagridview holds only tables by instance but can show more
types of datacollections. A datagrid holds complete datasets, but can show
less types of datacollections.

You are talking as if it is a replacement. Which is not, it is an addition.

Cor

 

[noregrets62]

I believe that Teddy decided to replace the datagrid with a
datagridview in his code. I suspect that Teddy is not saying that the
control is a replacement for the datagrid.

So, forgetting about the datagrid and there is a connection, is there
a way to save from the datagridview?

Please, anyone except for COR, answer this question?

Given that a datagridview has been binded to a dataset, Is there a
solution to his question?

 

[KeithLiebenberg]

Update the underlying data of a datagridview

The DataGridView wants you to move off the current record to get it to update its bound table. Then you need to call the TableAdapters Update Command.

'This forces the DataGridView to update its Table
MyGridsBindingSource.CurrencyManager.Position = MyGridsBindingSource.CurrencyManager.Position + 1

'This update the database
MYGridsTableAdapter.Update(MyDataSet.MyTable)

Keith