Empty child database and masterID problem

[Neo Geshel]

Greetings.

I have a serious problem. I have multiple sets of tables, several of
which are chained more than two tables deep. That is, I have a parent, a
child, and a great-grandchild table.

Currently, I am allowing the parent to be edited by itself. In order to
get to a child table, a user needs to select a specific parent table ID.
I have set this up using panels, and putting a drop-down list in the
first panel, with its contents drawn from the parent table. When the
user selects a parenttable.name, the correct parenttable.ID is sent to
the next iteration of the page, where it is used as the
childtable.parentID reference to populate the datagrid.

My main problem is when there are no child table entries for that parent
table ID. Without any entries, the "add entry" value for
childtable.parentID cannot be filled, because nothing is being drawn
from the database.

Below is the code that I have tried to implement so far:

Sub ddlSelectRegion_Click(sender As Object, e As EventArgs)
Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings( "strConn"))
Dim myCmd as New OleDbCommand("SELECT * FROM tblCarClubInfo WHERE
[CarClubRegionID] = " & iRegionID & " ORDER BY [ID]", myConn)
myConn.Open()
dgCarClubInfo.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnectio n)
Dim ltrRegionID as Literal
ltrRegionID.Text = ddlSelectRegion.SelectedValue
dgCarClubInfo.DataBind()
myConn.Close()
End Sub

On the submission of the drop-down list's button, the selected value
gets extracted, and used in the SQL statement. However, if it is empty,
I need to pass that value on to the correct column in the datagrid. I
have tried to use a literal (<asp:literal /> - it uses no HTML), but the
fourth to last line always throws an error (Object reference not set to
an instance of an object). In order to populate the literal, this line
must work. That way, I can populate the "add" row's "ParentID" field
with the literal, so any additions will have the ParentID it requires.

Another problem is that I need to make this literal population fire only
when the database doesn't return any results. I have tried to find the
ASP.NET equivelents to the old rs.EOF rs.BOF methods, without success.
Any suggestions?

On another note, are there any tutorials that can show how I can have a
parent/child/grandchild/greatgrandchild datagrid (or close enough) with
every table being editable, deletable, and addable from within the same
datagrid? That is, you have "edit", "delete" and "add" buttons for every
layer of the datagrid.

TIA
....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Phillip Williams]

Greetings Neo,

1- You should write a condition for if
ddlSelectRegion.SelectedItem.Value.Equals ("") then write a different sql
statement

2- The reason you get “Object reference not set to an instance of an object�
is that you have not used the "New" keyword in instantiating the Literal
control
Dim ltrRegionID As New Literal

3- A simple way to create your expandable datagrid, is to use the
ItemDataBound event of the datagrid. This event would provide you a handle
to the datagrid item which is the analogy of a table row that contains cells.
You then loop through the cells collection of the DataGridItem, save their
values in a temporary array then rewrite an HTMtable that contains a child
datagrid bound to a child dataset. Something that uses the following code
components:

Public Class testGrid
Inherits System.Web.UI.WebControls.DataGrid

Private Sub testGrid_ItemDataBound(ByVal sender As Object, ByVal e As
DataGridItemEventArgs) Handles MyBase.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or _
e.Item.ItemType = ListItemType.AlternatingItem Then
BuildChildLayout(e.Item)
End If

End Sub
Private Sub BuildChildLayout(ByVal item As DataGridItem)
Dim row As DataGridItem = item
'loop through the row.Cells collection, and save their content in an
array
'remove all row.Cells
Dim newCell As New TableCell
row.Cells.Add(newCell)
Dim t As New Table
newCell.Controls.Add(t)
'now you have a Table (t) where you can re-write the values that you
saved above
'then add a new row, create a datagrid in it and bind it to the
child data
End Sub
End Class

--
WEBSWAPP Development Inc.
http://www.webswapp.com
"Neo Geshel" wrote:

Greetings.

I have a serious problem. I have multiple sets of tables, several of
which are chained more than two tables deep. That is, I have a parent, a
child, and a great-grandchild table.

Currently, I am allowing the parent to be edited by itself. In order to
get to a child table, a user needs to select a specific parent table ID.
I have set this up using panels, and putting a drop-down list in the
first panel, with its contents drawn from the parent table. When the
user selects a parenttable.name, the correct parenttable.ID is sent to
the next iteration of the page, where it is used as the
childtable.parentID reference to populate the datagrid.

My main problem is when there are no child table entries for that parent
table ID. Without any entries, the "add entry" value for
childtable.parentID cannot be filled, because nothing is being drawn
from the database.

Below is the code that I have tried to implement so far:

Sub ddlSelectRegion_Click(sender As Object, e As EventArgs)
Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings( "strConn"))
Dim myCmd as New OleDbCommand("SELECT * FROM tblCarClubInfo WHERE
[CarClubRegionID] = " & iRegionID & " ORDER BY [ID]", myConn)
myConn.Open()
dgCarClubInfo.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnectio n)
Dim ltrRegionID as Literal
ltrRegionID.Text = ddlSelectRegion.SelectedValue
dgCarClubInfo.DataBind()
myConn.Close()
End Sub

On the submission of the drop-down list's button, the selected value
gets extracted, and used in the SQL statement. However, if it is empty,
I need to pass that value on to the correct column in the datagrid. I
have tried to use a literal (<asp:literal /> - it uses no HTML), but the
fourth to last line always throws an error (Object reference not set to
an instance of an object). In order to populate the literal, this line
must work. That way, I can populate the "add" row's "ParentID" field
with the literal, so any additions will have the ParentID it requires.

Another problem is that I need to make this literal population fire only
when the database doesn't return any results. I have tried to find the
ASP.NET equivelents to the old rs.EOF rs.BOF methods, without success.
Any suggestions?

On another note, are there any tutorials that can show how I can have a
parent/child/grandchild/greatgrandchild datagrid (or close enough) with
every table being editable, deletable, and addable from within the same
datagrid? That is, you have "edit", "delete" and "add" buttons for every
layer of the datagrid.

TIA
....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Neo Geshel]

Phillip Williams wrote:

Greetings Neo,

1- You should write a condition for if
ddlSelectRegion.SelectedItem.Value.Equals ("") then write a different sql
statement
What I need to know is whether a returned dataset from the database is
empty or not. Would this work?:

If dgCarClubInfo.DataSource = "" Then 'no data is returned, so...
Dim ltrRegionID as New Literal 'set the literal
ltrRegionID.Text = ddlSelectRegion.SelectedValue 'and populate it
Else
dgCarClubInfo.DataBind() 'otherwise if data is returned, dont set
the literal, and just populate the datagrid.
End If
2- The reason you get “Object reference not set to an instance of an object�
is that you have not used the "New" keyword in instantiating the Literal
control
Dim ltrRegionID As New Literal
Strangely enough, this works. Don't know why (since six lines above it
is another DIM that wouldn't work like that...).
3- A simple way to create your expandable datagrid, is to use the
ItemDataBound event of the datagrid. This event would provide you a handle
to the datagrid item which is the analogy of a table row that contains cells.
You then loop through the cells collection of the DataGridItem, save their
values in a temporary array then rewrite an HTMtable that contains a child
datagrid bound to a child dataset. Something that uses the following code
components:

Public Class testGrid
Inherits System.Web.UI.WebControls.DataGrid

Private Sub testGrid_ItemDataBound(ByVal sender As Object, ByVal e As
DataGridItemEventArgs) Handles MyBase.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or _
e.Item.ItemType = ListItemType.AlternatingItem Then
BuildChildLayout(e.Item)
End If

End Sub
Private Sub BuildChildLayout(ByVal item As DataGridItem)
Dim row As DataGridItem = item
'loop through the row.Cells collection, and save their content in an
array
'remove all row.Cells
Dim newCell As New TableCell
row.Cells.Add(newCell)
Dim t As New Table
newCell.Controls.Add(t)
'now you have a Table (t) where you can re-write the values that you
saved above
'then add a new row, create a datagrid in it and bind it to the
child data
End Sub


End Class

Yes, but are the child/grandchild/greatgrandchild tables insertable,
updateable and deletable? that is what I am after. I want editing
controls on not only the parent, but on all three of its lower
generations. I simply don't know how to do that.

...Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Phillip Williams]

1- If you want to check if the returned dataset is empty before you bind it
to a grid, I would use a DataSet instead of a DataReader:
Dim da As New OleDb.OleDbDataAdapter(myCommand)
Dim ds As New DataSet
da.Fill(ds, "RegionID")
If ds.Tables("RegionID").Rows.Count >0 Then
'do something
Else
'do something else
End If

2- The Dim statement for scalar types (e.g. String) do not need the New
keyword
3- The answer to your question is Yes. Every datagrid (child, grand-child)
is simply another class that handles all of the events that you program in
it.

--
WEBSWAPP Development Inc.
http://www.webswapp.com
"Neo Geshel" wrote:

Phillip Williams wrote:

Greetings Neo,

1- You should write a condition for if
ddlSelectRegion.SelectedItem.Value.Equals ("") then write a different sql
statement

What I need to know is whether a returned dataset from the database is
empty or not. Would this work?:

If dgCarClubInfo.DataSource = "" Then 'no data is returned, so...
Dim ltrRegionID as New Literal 'set the literal
ltrRegionID.Text = ddlSelectRegion.SelectedValue 'and populate it
Else
dgCarClubInfo.DataBind() 'otherwise if data is returned, dont set
the literal, and just populate the datagrid.
End If

2- The reason you get “Object reference not set to an instance of an object�
is that you have not used the "New" keyword in instantiating the Literal
control
Dim ltrRegionID As New Literal

Strangely enough, this works. Don't know why (since six lines above it
is another DIM that wouldn't work like that...).

3- A simple way to create your expandable datagrid, is to use the
ItemDataBound event of the datagrid. This event would provide you a handle
to the datagrid item which is the analogy of a table row that contains cells.
You then loop through the cells collection of the DataGridItem, save their
values in a temporary array then rewrite an HTMtable that contains a child
datagrid bound to a child dataset. Something that uses the following code
components:

Public Class testGrid
Inherits System.Web.UI.WebControls.DataGrid

Private Sub testGrid_ItemDataBound(ByVal sender As Object, ByVal e As
DataGridItemEventArgs) Handles MyBase.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or _
e.Item.ItemType = ListItemType.AlternatingItem Then
BuildChildLayout(e.Item)
End If

End Sub
Private Sub BuildChildLayout(ByVal item As DataGridItem)
Dim row As DataGridItem = item
'loop through the row.Cells collection, and save their content in an
array
'remove all row.Cells
Dim newCell As New TableCell
row.Cells.Add(newCell)
Dim t As New Table
newCell.Controls.Add(t)
'now you have a Table (t) where you can re-write the values that you
saved above
'then add a new row, create a datagrid in it and bind it to the
child data
End Sub
End Class

Yes, but are the child/grandchild/greatgrandchild tables insertable,
updateable and deletable? that is what I am after. I want editing
controls on not only the parent, but on all three of its lower
generations. I simply don't know how to do that.

...Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Neo Geshel]

Phillip Williams wrote:

1- If you want to check if the returned dataset is empty before you bind it
to a grid, I would use a DataSet instead of a DataReader:
Dim da As New OleDb.OleDbDataAdapter(myCommand)
Dim ds As New DataSet
da.Fill(ds, "RegionID")
If ds.Tables("RegionID").Rows.Count >0 Then
'do something
Else
'do something else
End If

2- The Dim statement for scalar types (e.g. String) do not need the New
keyword
3- The answer to your question is Yes. Every datagrid (child, grand-child)
is simply another class that handles all of the events that you program in
it.

I've also ran into a very strange issue. When I try this code:

Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue.ToString()
ltrRegionID.Text = iRegionID

I get the following error message:

Name 'ltrRegionID' is not declared

Problem is, it is meant to be the ID of a asp:literal tag further down
on the page.

....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Neo Geshel]

Neo Geshel wrote:

I've also ran into a very strange issue. When I try this code:

Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue.ToString()
ltrRegionID.Text = iRegionID

I get the following error message:

Name 'ltrRegionID' is not declared

Problem is, it is meant to be the ID of a asp:literal tag further down
on the page.

...Geshel

But when I actually dim it:

Dim ltrRegionID as literal
ltrRegionID.Text = iRegionID

I get the following problem:

Object reference not set to an instance of an object.
Damned if I do, damned if I don't!!!!!

....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Neo Geshel]

Neo Geshel wrote:

Neo Geshel wrote:

I've also ran into a very strange issue. When I try this code:

Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue.ToString()
ltrRegionID.Text = iRegionID

I get the following error message:

Name 'ltrRegionID' is not declared

Problem is, it is meant to be the ID of a asp:literal tag further down
on the page.

...Geshel

But when I actually dim it:

Dim ltrRegionID as literal
ltrRegionID.Text = iRegionID

I get the following problem:

Object reference not set to an instance of an object.
Damned if I do, damned if I don't!!!!!

...Geshel

A bit of dicking around, and I've found the problem. Seems that a
dataset doesn't like a literal dropped in the middle of it. The literal,
in order to work, must exist outside the dataset.

So the question now is: how do I populate a table cell of the "add
record" footer of the dataset with a value pulled from the drop-down
list on the previous page? I need this, so that any record addition to
this child table will know which parent record to associate the new
record with.

Below is the code that I have come up with so far:

Sub ddlSelectRegion_Click(sender As Object, e As EventArgs)
Dim iRegionID as String
iRegionID = ddlSelectRegion.SelectedValue.ToString()
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings( "strConn"))
myConn.Open()
Dim myCmd as New OleDbCommand("SELECT Count(*) FROM tblCarClubInfo
WHERE [CarClubRegionID] = " & iRegionID, myConn)
If MyCmd.ExecuteScalar() <> 0 Then
myCmd = New OleDbCommand("SELECT * FROM tblCarClubInfo WHERE
[CarClubRegionID] = " & iRegionID & " ORDER BY [ID]", myConn)
dgCarClubInfo.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnectio n)
dgCarClubInfo.DataBind()
Else
myCmd = New OleDbCommand("SELECT * FROM tblCarClubInfo WHERE
[CarClubRegionID] = " & iRegionID & " ORDER BY [ID]", myConn)
dgCarClubInfo.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnectio n)
dgCarClubInfo.DataBind()
ltrRegionID.Text = iRegionID
End If
myConn.Close()
End Sub

Yes, the ELSE has a database call that I know will not return any
records, but I had to do this in order for the datagrid to actually be
built (otherwise it would not even appear, even as a single "add record"
line).

Now, what I would like to do, is have the RegionID cell of the "add
record" row in the DataGrid be populated by the above ELSE via another
route other than the DataSource (unless I can actually EDIT or ADD TO
the datasource dynamically after it's been called from the database!!!
That would be cool!) That way, the Region ID (which is not pulled from
the database, because there are no records returned) is still available
for the "add record" field for RegionID.

I have tried by using asp:literal, but this throws a bunch of errors, as
seen above, because it can't be inside of a datagrid. I have still
attached a value to a literal ID above (fourth line from bottom), but
I'm looking for another method.

Any help would be greatly appreciated.
....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************

[Neo Geshel]

Neo Geshel wrote:

A bit of dicking around, and I've found the problem. Seems that a
dataset doesn't like a literal dropped in the middle of it. The literal,
in order to work, must exist outside the dataset.

So the question now is: how do I populate a table cell of the "add
record" footer of the dataset with a value pulled from the drop-down
list on the previous page? I need this, so that any record addition to
this child table will know which parent record to associate the new
record with.

Whoops. I meant "datagrid", not "dataset". Sorry.

....Geshel
--
************************************************** ********************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
************************************************** ********************