Datagrid with VB 2005

mfahnestock

Hi all!

Aspiring VB programmer losing hair <And Sleep> over this problem. I
have spent several days researching through the various usenets and
resources to no avail, and so now I am taking the next step.

Situation:
I have a form (Form 1) with a datagrid that has a context menu for
adding updated records. When selecting the "Add Option" a second form
(Form 2) is displayed for data population. The user saves the
information and returns to Form 1. On the closing statement of Form 2,
it calls the Fill procedure on Form 1 to refresh the data, but it's not
working.

Here's the Datagrid Population Code:
************************************************
Private dsRooms As DataSet
Private tblRooms As DataTable
Private ceAdapter As SqlCeDataAdapter

'Opens connection and fills combo boxes with correct values
ceConn = New SqlCeConnection(CONNECTIONSTRING)
ceConn.Open()
Try
' Determines if Refresh is required by reviewing the
Local_rowguid
Dim sql As String

If Local_rowguid <> "" Then

'Build the new sql query sorting by Local_rowguid
sql = "SELECT Description, Damage_Class_Desc, Room_Name
"
sql = sql & "FROM Room_Disaster "
sql = sql & "INNER JOIN Damage_Classification ON
Room_Disaster.Damage_Class = Damage_Classification.Damage_Class "
sql = sql & "INNER JOIN Rooms ON Room_Disaster.Room_ID
= Rooms.Room_ID "
sql = sql & "WHERE (Local_rowguid = '" & Local_rowguid
& "')"

Else
sql = "SELECT Description, Damage_Class_Desc, Room_Name
"
sql = sql & "FROM Room_Disaster "
sql = sql & "INNER JOIN Damage_Classification ON
Room_Disaster.Damage_Class = Damage_Classification.Damage_Class "
sql = sql & "INNER JOIN Rooms ON Room_Disaster.Room_ID
= Rooms.Room_ID "
sql = sql & "WHERE (Local_rowguid = NULL)"
End If

ceAdapter = New SqlCeDataAdapter(sql, ceConn)
dsRooms = New DataSet
'If Local_rowguid <> "" Then
Me.dsRooms.Clear()
' End If

Me.ceAdapter.Fill(Me.dsRooms, "tblRooms")
Me.grdRoomDamage.DataSource = Nothing
Me.grdRoomDamage.DataSource = dsRooms.Tables(0)

'Formats Datagrid
With grdRoomDamage
.TableStyles.Clear()
.TableStyles.Add(myTableStyle(dsRooms))
.Font = New Font("Tahoma", 8.0, FontStyle.Regular)
End With
End Sub
****************************************
Couple nuances, this is a mobile device program. So I am using SQL CE
for most of the calls as it is a replicating database to an enterprise
SQL server. I am also using TableStyles to format the datagrid, I did
not include that code as I have been remming it out for testing
purposes and it hasn't made a difference.

Please help!

Mar 29 '06 #1

Cor Ligthert [MVP]

Mfahnestock,

I have spent some time looking at your code, you tell. It is not working.
What is not working, what are the current results?

Cor

<mf*********@verizon.net> schreef in bericht
news:11*********************@v46g2000cwv.googlegro ups.com...

Hi all!

Aspiring VB programmer losing hair <And Sleep> over this problem. I
have spent several days researching through the various usenets and
resources to no avail, and so now I am taking the next step.

Situation:
I have a form (Form 1) with a datagrid that has a context menu for
adding updated records. When selecting the "Add Option" a second form
(Form 2) is displayed for data population. The user saves the
information and returns to Form 1. On the closing statement of Form 2,
it calls the Fill procedure on Form 1 to refresh the data, but it's not
working.

Here's the Datagrid Population Code:
************************************************
Private dsRooms As DataSet
Private tblRooms As DataTable
Private ceAdapter As SqlCeDataAdapter

'Opens connection and fills combo boxes with correct values
ceConn = New SqlCeConnection(CONNECTIONSTRING)
ceConn.Open()
Try
' Determines if Refresh is required by reviewing the
Local_rowguid
Dim sql As String

If Local_rowguid <> "" Then

'Build the new sql query sorting by Local_rowguid
sql = "SELECT Description, Damage_Class_Desc, Room_Name
"
sql = sql & "FROM Room_Disaster "
sql = sql & "INNER JOIN Damage_Classification ON
Room_Disaster.Damage_Class = Damage_Classification.Damage_Class "
sql = sql & "INNER JOIN Rooms ON Room_Disaster.Room_ID
= Rooms.Room_ID "
sql = sql & "WHERE (Local_rowguid = '" & Local_rowguid
& "')"

Else
sql = "SELECT Description, Damage_Class_Desc, Room_Name
"
sql = sql & "FROM Room_Disaster "
sql = sql & "INNER JOIN Damage_Classification ON
Room_Disaster.Damage_Class = Damage_Classification.Damage_Class "
sql = sql & "INNER JOIN Rooms ON Room_Disaster.Room_ID
= Rooms.Room_ID "
sql = sql & "WHERE (Local_rowguid = NULL)"
End If

ceAdapter = New SqlCeDataAdapter(sql, ceConn)
dsRooms = New DataSet
'If Local_rowguid <> "" Then
Me.dsRooms.Clear()
' End If

Me.ceAdapter.Fill(Me.dsRooms, "tblRooms")
Me.grdRoomDamage.DataSource = Nothing
Me.grdRoomDamage.DataSource = dsRooms.Tables(0)

'Formats Datagrid
With grdRoomDamage
.TableStyles.Clear()
.TableStyles.Add(myTableStyle(dsRooms))
.Font = New Font("Tahoma", 8.0, FontStyle.Regular)
End With
End Sub
****************************************
Couple nuances, this is a mobile device program. So I am using SQL CE
for most of the calls as it is a replicating database to an enterprise
SQL server. I am also using TableStyles to format the datagrid, I did
not include that code as I have been remming it out for testing
purposes and it hasn't made a difference.

Please help!

Mar 29 '06 #2

mfahnestock

Cor ---

The datagrid on Form 1 starts by populating nothing, which is correct.
The TableStyle is applied successfully. The issue is that when Form2
is populated and saved. The user returns to Form1. I make a call to
the function defined above, but the datagrid shows NO results. It
should haveat least 1 record from what was saved in Form2. Instead,
the datagrid stays blank without any data. I want it to refresh and
show the data.

Mark

Mar 29 '06 #3

Cor Ligthert [MVP]

Hi,

And you are sure that it is 100% right updated in the DataBase?

Cor
<mf*********@verizon.net> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

Cor ---

The datagrid on Form 1 starts by populating nothing, which is correct.
The TableStyle is applied successfully. The issue is that when Form2
is populated and saved. The user returns to Form1. I make a call to
the function defined above, but the datagrid shows NO results. It
should haveat least 1 record from what was saved in Form2. Instead,
the datagrid stays blank without any data. I want it to refresh and
show the data.

Mark

Mar 29 '06 #4

mfahnestock

Cor ---

Validated it. Form1 posts correctly to Table1 with the PK. Form2
posts correctly to Table2 with a FK matched to the PK.
If I run the SQL statement created, it correctly retrieves the data.

Mark

Mar 29 '06 #5

Cor Ligthert [MVP]

Mark,

Sorry that I ask so much, however you said you had broken your hair yourself
already over it.

I don't see it as well not. However, what do you mean with this sentence.

The user saves the
information and returns to Form 1. On the closing statement of Form 2,
it calls the Fill procedure on Form 1 to refresh the data, but it's not
working.

Howe do you do that. Is that in Form1 or in Form2

Cor

Mar 29 '06 #6

mfahnestock

Not a problem at all, as you are helping me out... Let me walk through
the process.

1. The process starts with the user selecting the option for data
entry on the Local form. The form loads blank as it will become a New
Record in the system. I am calling this Local Form (Form1).
2. The Local Form contains 3 tabs with some textboxes, combos, etc.
The last tab has a datagrid. The datagrid is linked to a dataset that
joins several tables (Room_Disaster, Damage_Classification, and Rooms).
Because it is a new record, the dataset is blank as there are no
values as of yet since the incident is brand new.
3. A context menu has been placed on the datagrid with the option of
'Add'. If the user chooses to Add some detailed information, it will
pop the Room_Disaster screen, which I am calling Form2. This allows
the user to entry room specific information. Programmatically on save,
I am pushing an INSERT into the database Room_Disaster table with the
detailed information. The Local_rowguid, which is the Primary Key from
the Local Table, is a global variable that is passed to Room_Disaster.
This allows me to associate each Detailed Room_Disaster to the
corresponding Local Incident.
4. The user closes Room_Disaster (Form2) and I have a call to refill
the dataset to refresh the datagrid on the Local form.

Basically the code on Form2 reads:

Private Sub cmdReturnLocal_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdReturnLocal.Click
Call frmDataEntryLocal.Fill_Datagrid() ** FILL DATAGRID IS THE
CALL TO REFRESH THE DATAGRID **
Me.Close()
End Sub

5. From the backend, I can see the data in Room_Disaster, I can see
the data in Local, I can see the matching keys, so I know the saves are
performing successfully. The kicker is the darn datagrid in the Local
form (Form1) does not refresh to show that a record was now added.

I have tried clearing the datagrid, tieing the datasource of the grid
to nothing and then reassociating, but all to know avail. Hope that
helps narrow things a bit Cor. Needless to say, programmatically, it
should work. Well, at least in my opinion ;)

Mar 29 '06 #7

mfahnestock

Not a problem at all, as you are helping me out... Let me walk through
the process.

1. The process starts with the user selecting the option for data
entry on the Local form. The form loads blank as it will become a New
Record in the system. I am calling this Local Form (Form1).
2. The Local Form contains 3 tabs with some textboxes, combos, etc.
The last tab has a datagrid. The datagrid is linked to a dataset that
joins several tables (Room_Disaster, Damage_Classification, and Rooms).
Because it is a new record, the dataset is blank as there are no
values as of yet since the incident is brand new.
3. A context menu has been placed on the datagrid with the option of
'Add'. If the user chooses to Add some detailed information, it will
pop the Room_Disaster screen, which I am calling Form2. This allows
the user to entry room specific information. Programmatically on save,
I am pushing an INSERT into the database Room_Disaster table with the
detailed information. The Local_rowguid, which is the Primary Key from
the Local Table, is a global variable that is passed to Room_Disaster.
This allows me to associate each Detailed Room_Disaster to the
corresponding Local Incident.
4. The user closes Room_Disaster (Form2) and I have a call to refill
the dataset to refresh the datagrid on the Local form.

Basically the code on Form2 reads:

Private Sub cmdReturnLocal_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdReturnLocal.Click
Call frmDataEntryLocal.Fill_Datagrid() ** FILL DATAGRID IS THE
CALL TO REFRESH THE DATAGRID **
Me.Close()
End Sub

5. From the backend, I can see the data in Room_Disaster, I can see
the data in Local, I can see the matching keys, so I know the saves are
performing successfully. The kicker is the darn datagrid in the Local
form (Form1) does not refresh to show that a record was now added.

I have tried clearing the datagrid, tieing the datasource of the grid
to nothing and then reassociating, but all to know avail. Hope that
helps narrow things a bit Cor. Needless to say, programmatically, it
should work. Well, at least in my opinion ;)

Mar 29 '06 #8

Cor Ligthert [MVP]

Mark,

I see in what you tell no reason why yo don't use a showdialog.
And just refill the dataset in form1 as that form2 closes?

dim frm as new form2
What i want to give to frm2
frm2.ThePublicVariables = whatever
frm2.showdialog(me)
What I need in form1 from form2

whatINeed = frm2.OtherPublicVariables
frm2.dispose 'dispose is prefered in this case

thedataset = new dataset 'I thought there was a bug in the clear and this
does the same
da.fill(the dataset)
dg.datasource = the dataset
dg.show (if needed I assume not)

However do you use a showdialog already and if not why not?

Cor
<mf*********@verizon.net> schreef in bericht
news:11**********************@e56g2000cwe.googlegr oups.com...

Not a problem at all, as you are helping me out... Let me walk through
the process.

1. The process starts with the user selecting the option for data
entry on the Local form. The form loads blank as it will become a New
Record in the system. I am calling this Local Form (Form1).
2. The Local Form contains 3 tabs with some textboxes, combos, etc.
The last tab has a datagrid. The datagrid is linked to a dataset that
joins several tables (Room_Disaster, Damage_Classification, and Rooms).
Because it is a new record, the dataset is blank as there are no
values as of yet since the incident is brand new.
3. A context menu has been placed on the datagrid with the option of
'Add'. If the user chooses to Add some detailed information, it will
pop the Room_Disaster screen, which I am calling Form2. This allows
the user to entry room specific information. Programmatically on save,
I am pushing an INSERT into the database Room_Disaster table with the
detailed information. The Local_rowguid, which is the Primary Key from
the Local Table, is a global variable that is passed to Room_Disaster.
This allows me to associate each Detailed Room_Disaster to the
corresponding Local Incident.
4. The user closes Room_Disaster (Form2) and I have a call to refill
the dataset to refresh the datagrid on the Local form.

Basically the code on Form2 reads:

Private Sub cmdReturnLocal_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdReturnLocal.Click
Call frmDataEntryLocal.Fill_Datagrid() ** FILL DATAGRID IS THE
CALL TO REFRESH THE DATAGRID **
Me.Close()
End Sub

5. From the backend, I can see the data in Room_Disaster, I can see
the data in Local, I can see the matching keys, so I know the saves are
performing successfully. The kicker is the darn datagrid in the Local
form (Form1) does not refresh to show that a record was now added.

I have tried clearing the datagrid, tieing the datasource of the grid
to nothing and then reassociating, but all to know avail. Hope that
helps narrow things a bit Cor. Needless to say, programmatically, it
should work. Well, at least in my opinion ;)

Mar 29 '06 #9

mfahnestock

Cor ---

Great idea! I just glossed over .ShowDialog and was using .Show.
Didn't even think of it. By keeping the control on the same form, it
fixed the problem. everything is working fine now. Thank you for all
of your assistance!

The code ended up looking like this as I had to create a new adapter as
well.

ceAdapter = New SqlCeDataAdapter(sql, ceConn)
dsRooms = New DataSet
ceAdapter.Fill(dsRooms, "tblRooms")
grdRoomDamage.DataSource = dsRooms.Tables(0)

'Formats Datagrid
With grdRoomDamage
.TableStyles.Clear()
.TableStyles.Add(myTableStyle(dsRooms))
.Font = New Font("Tahoma", 8.0, FontStyle.Regular)
End With

Mar 29 '06 #10