Refresh DataGrid 1

[GerardMcL]

Hi,

I have a form with a DataGrid showing the contents of Table1.

Below this we have text boxes for entering Data into Table1, when I click the Save button I execute the INSERT statement

and then
DataGrid1.Refresh() - Doesnt show the new Information
Set up the datasource again - Doesnt show the new Info.

Any ideas people? I used to just refresh the adodc in VB6 but this is infuriating me as the small problems always do.

 

[GerardMcL]

Actually I have noticed a different problem.

I have changed code and the design of my form but these changes seem to be lost at Runtime.

I have added textboxes that dont show up.
I have changed format of DataGrids but the changes have not appeared.
It seems to be running the form design as it was a few days ago.

Anyone know why?
Thanks for the help.

 

[29021972]

Hi,
I had similar problems with the layout of some controls. To see the changes I had to restart Visual Studio (2003) everytime I changed the look of a control.
Because my computer was a big mess, and slower everyday, I did a complete reinstall. That solved the problem.

I am not saying you need to re-install Visual Studio, but I do wonder if anyone else had similar problemns.

Another possibility might be that you copied and pasted some controls. That does give some trouble. Especially if you copy paste forms.
'
About the datagrid, can you show some code?


MCAD(2004)

 

[GerardMcL]

I had a small runtime error that wasnt stopping it playing, however once I fixed that the changes popped up straight away - WEIRD!

cmd = New OleDbCommand("INSERT INTO Tools (ToolID, Description, Price, CompanyID, BoughtDate) VALUES ('" & varToolID & "', '" & varDesc & "', '" & varPrice & "', '" & varCompID & "', '" & varBDate & "')", cn)

cmd.ExecuteReader()
dgTools.Refresh()

But no joy with this.
This is how I filled the Datagrid:


Dim da As OleDbDataAdapter
Dim dt As DataTable
Dim strFILL As String
Dim a As Integer

strFILL = "SELECT s.StaffID As ID, s.Title + '. ' + s.FirstName + ' ' + s.LastName AS Staff, th.Location1, th.Location2, th.Date, th.Returned, th.ReturnDate FROM Staff s INNER JOIN ToolHire th ON s.StaffID = th.StaffID WHERE th.ToolID = '" & ToolCode & "' ORDER BY th.Date DESC;"

da = New OleDbDataAdapter(strFILL, cn)
dt = New DataTable
da.Fill(dt)
DGtoolhistory.DataSource = dt

 

[29021972]

I think only setting the datasource like this is not enough.

I assume we are talking about windows forms, not web forms.

Instead of

da.Fill(dt)
DGtoolhistory.DataSource = dt

Try:

da.Fill(dt,"Staff")
dgToolhistory.SetDataBinding(dt, "Staff")



MCAD(2004)

 

[GerardMcL]

This brings up an ADODB problem, and where you specify the source Table, I am selecting using a join so would have specify two tables.

 

[29021972]

The "Staff" can be any name. It is just the name that you give to the data that you put in your Dataset.
It could as well be:

da.Fill(dt,"Endoplasmatic_Reticulum")
dgToolhistory.SetDataBinding(dt, "Endoplasmatic_Reticulum")

a dataset can contain more than one "views"... the name has nothing to do with the name of your table.

What is the ADODB problem you're talking about?

MCAD(2004)

 

[jebenson]

DataSets and DataTables are disconnected from the datasource. That is, once you fill them, they no longer have a connection to the database. Any changes you make to the database will not appear in the DataTable...because it is disconnected. Thus, this code:

cmd = New OleDbCommand("INSERT INTO Tools (ToolID, Description, Price, CompanyID, BoughtDate) VALUES ('" & varToolID & "', '" & varDesc & "', '" & varPrice & "', '" & varCompID & "', '" & varBDate & "')", cn)

cmd.ExecuteReader()
dgTools.Refresh()

will not update the datagrid with your new data. (On a quick side note...if you decide to continue with this method, you should use cmd.ExecuteNonQuery() to run your INSERT SQL, not the cmd.ExecuteReader() method.) If you want your changes to appear in the DataGrid, make the changes to the data in your DataSet/DataTable, not directly to the database. Then you can save all the changes at once using the DataAdapter's Update method. This has the advantage of allowing users to cancel any changes, and all you the programmer have to do to handle this situation is...not save the changes.

Here's a quick example:

Dim da As OleDbDataAdapter
Dim dt As DataTable

'Assume a connection named 'cn' already exists
da = New OleDbDataAdapter("Select * from Tools", cn)
dt = New DataTable
da.Fill(dt)

DataGrid1.DataSource = dt

To add a new row to the DataTable:

Dim dr As DataRow

dr = dt.NewRow()

dr.Item("ToolID") = 2112
dr.Item("Description") = "Cool tool!"
dr.Item("Price") = "19.99"
dr.Item("CompanyID") = "999"
dr.Item("BoughtDate") = CType("8/2/2006", Date) 'use an actual date here...this is an example

dt.Rows.Add(dr)

The row you just added should show up in the DataGrid. However, it is not yet in the database. To save it to the database, you can do this:

dim cmd as OleDbCommand

cmd = New OleDbCommand("INSERT INTO Tools (ToolID, Description, Price, CompanyID, BoughtDate) VALUES (@ToolID, @Description, @Price, @CompanyID, @BoughtDate)", cn)

da.InsertCommand = cmd

With da.InsertCommand
.Parameters.Add("@ToolID", OleDbType.Int, 4, "ToolID")
.Parameters.Add("@Description", OleDbType.VarChar, 100, "Description")
.Parameters.Add("@Price", OleDbType.Currency, 4, "Price")
.Parameters.Add("@CompanyID", OleDbType.Int, 4, "CompanyID")
.Parameters.Add("@BoughtDate", OleDbType.Date, 4, "BoughtDate")
End With

Then, to actually insert the data:

da.Update(dt)



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira