Help!! Can someone please check my code?

[Guest]

I have two tables, tblPost - (Cols PostID, TopicID, UserID, Question, PostMsg
and PostDT) and tblTopic - (Cols TopicID, Topic). I am trying to get
tblPost.Question and tblPost.PostDT into DataGrid1 when the DropDownList1
(which consists of Topic) is clicked.

I have the following code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim cnn as New SqlClient.SqlConnection("data source=SILVER; initial
catalog=RidingForEveryone; integrated security=SSPI;")
Dim da as SqlDataAdapter = New SqlDataAdapter
Dim ds as DataSet = New DataSet()
da.SelectCommand = New SqlCommand("spFillTopicddl")
da.SelectCommand.Connection = cnn
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.Fill(ds, "tTopic")
DropDownList1.DataSource = ds
DropDownList1.DataMember = "tTopic"
DropDownList1.DataTextField = "Topic"
DropDownList1.DataBind()
End If
End Sub

Sub FillPost()
Dim cnn as New SqlClient.SqlConnection("data source=SILVER; initial
catalog=RidingForEveryone; integrated security=SSPI;")
Dim da as SqlDataAdapter = New SqlDataAdapter
Dim myCommand as SqlCommand = New SqlCommand("Select Question, PostDT From
tblPost inner join tblTopic on tblTopic.TopicID = tblPost.TopicID Where
tblTopic.TopicID=" & DropDownList1.SelectedIndex.ToString, cnn)
Dim ds as New DataSet()
da.SelectCommand = myCommand
da.Fill(ds, "tPost")
DataGrid1.DataSource = ds
DataGrid1.DataMember = "tPost"
DataGrid1.DataBind()
End Sub

Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal
e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
FillPost()
End Sub

The DropDownList is filled when the page is loaded. When I click on an
item, however, the DataGrid is displayed and nothing is in it, no matter what
item I select in the DropDownList. Visual Studio doesn't complain about a
thing . . . it just merely returns nothing.

Any help anyone can give me will be greatly appreciated!

 

[Guest]

It looks like you are referring to the the SelectedIndex rather than
SelectedValue or SelectedItem.Text when you build your dynamic sql.

 

[Guest]

Thanks for your response!

I tried using SelectedIndex after I tried both of the other. The
SelectedValue and Selected Item both return the following error:
"System.Web.HttpException: The IListSource does not contain a data source
named 'Post'"

There is another case where I use the datagrid to produce results in a
textbox when someone clicks on an item in the grid. I did create that
functionality by using a component which includes a different dataset. Do
you suppose VS does not allow mixing creating datasets via their wizards and
creating datasets in code? Am I going to have to start from square one?

What is the IListSource?

 

[Guest]

One thing I didn't mention is that you are not setting the DataValueField on
your dropdown. Based on your dynamic query, you will need the TopicID in
order get the right results.

DropDownList1.DataTextField = "Topic"
DropDownList1.DataValueField = "TopicID"

I am not sure why the error reports that "Post" is missing rather than
"tPost", but when you set the DataGrid1.DataMember to "tPost" it is checking
the DataSet for that table. I am not sure if the tPost table exists if there
are no results, but either way, since there is only one table in the DataSet,
I probably wouldn't specify it.

 

[Guest]

Thanks again for your response, Jed.

I took out the "DropDownList1.DataValueField="TopicID" because it gave me
the following error:
System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does
not contain a property with the name TopicID.

After putting it back in and seeing the error again, the DataRowView I
believe it is referring to is the DataView I used to get the display in a
textbox after an item on the datagrid is clicked.

I think I'm going to have to start over with everything. I'm confused. I
have seen so many ways of doing these things (eg via drag and drop & wizards,
using a component, via code, etc.)! I would guess it would make sense that
whatever way I go, I shouldn't try to mix the different methods. I received
a book when I purchased VS .NET that has quite a few examples of using a
component for datasets. They only showed how to do it with one table mainly,
but did have one example of two tables. For this project, I really have four
tables. I tried to get one dataset in one component I could access for
everything via dataviews and VS did not cooperate. It did work beautifully,
however, if I just created the component with one dataset (consisting of one
table), filled the datagrid and and used a dataview to show the item clicked
in the grid in a textbox. Thought I would simply be able to incorporate that
functionality into the rest of the page, but apparently it isn't that easy.
Guess I'll just go back to coding the whole thing.

Do you have any favorite way of coding filling a dropdownlist that when the
item is selected displays in a datagrid; that datagrid being able to be
clicked to display an individual message in a textbox? I have 13 books on
..Net and I swear each one of them uses different ways to do parts of this.
I'm having trouble putting everything together.

Thanks again for responding!

 

[Guest]

Yeah. I agree that it is risky to mix Wizard code with custom code. I do
everything manually. I use the Wilson.ORMapper http://www.ormapper.net most
of the time for my datalayer interaction so I can use strongly typed objects
rather than DataSets.

The "problem" with dot net, as you have indicated, is the plethora of app
architecture options.

See if the following code works. I am not sure what else is going on, but
it should work. I usually use C# so hopefully I didn't misstype anything.
You should probably create a class with shared methods for stuff like getting
the connection string (e.g., from the config file.)

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
TopicDropDownList_Init()
End If
End Sub

Private Sub TopicDropDownList_Init()
Dim cnn as New SqlClient.SqlConnection("data source=SILVER; initial
catalog=RidingForEveryone; integrated security=SSPI;")
Dim da as SqlDataAdapter = New SqlDataAdapter
Dim ds as DataSet = New DataSet()
'Make sure this SP returns Topic and TopicID
da.SelectCommand = New SqlCommand("spFillTopicddl")
da.SelectCommand.Connection = cnn
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.Fill(ds)
DropDownList1.DataSource = ds
DropDownList1.DataTextField = "Topic"
DropDownList1.DataValueField = "TopicID"
DropDownList1.DataBind()
End Sub


Private Sub FillPost(ByVal topicID AS string)
Dim cnn as New SqlClient.SqlConnection("data source=SILVER; initial
catalog=RidingForEveryone; integrated security=SSPI;")
Dim da as SqlDataAdapter = New SqlDataAdapter
Dim myCommand as SqlCommand = New SqlCommand("SELECT Question, PostDT
FROM" &
" tblPost INNER JOIN tblTopic ON tblTopic.TopicID = tblPost.TopicID"
&
" WHERE tblTopic.TopicID=" & topicID, cnn)
Dim ds as New DataSet()
da.SelectCommand = myCommand
da.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub

 

[Guest]

Many, many thanks for your response. It worked with only one minor
adjustment. For some reason I had trouble with FillPost. I finally just put
in language for a stored procedure instead of using text for the query. I
think it had to do with the fact that TopicID is an integer. Now my next
challenge is to try to get the click mechanism on the DG to display results
in a textbox.

I very briefly looked at the ORMapper and am not sure how it would be
implemented for Web pages. I did just glance at it though. What is the
learning curve associated with using it?

Thanks again!

Sandy

 

[Guest]

I would be surprised if the TopicID integer vs. string was an issue since
there were no quotes around it in the dynamic sql. Maybe the missing ";" at
the end. I don't know.

Anyway, I am glad you have it working.

There is a bit of a learning curve associated with the Wilson.ORMapper , but
it was designed to be easy. There are other ormappers that are more
complicated. If you are just starting, it might be better to stick with
DataSets and DataReaders since most of the examples on the web are going to
use those.

Once you get to the point that you want strongly-typed objects and are
separating the data layer from the interface, I highly recommend it. It has
significantly reduced the amount of effort and time it takes for me to
interact with persistent data. There are CodeSmith templates that will
generate the entire datalayer. You might have to write a couple custom
stored procedures to handle some special situations, but other than that it's
done.

 

[Guest]

Again, thanks. I definitely will be trying the ORMapper at a later date.

I really appreciate your responses and your time!!

Sandy