Please verify if I have this correct sql tables to vs 2008

jjeff123
jjeff123 used Ask the Experts™
on
Hello, I am creating a website in which users can update index information. I am a newb.

 The data is stored on 2 tables in sql2000.
One table has the index key, name, display name.
The 2nd table has Index Key,Index return key, Return, Return date.

In vs2008 I want to have to pages with the first grid view with 3 colums: Index name, Date as of, and options(hyper link to second page)
2nd page would have gridview with just that specified index: date list, returns, options(edit/delete link)

I am a little stuck on these:

I believe since i have data of both tables on the first gridset I need to merge the tables in a data set. With a sql command to give the most recent date to populate on the grid?

Second question what commands do I use for the second page to update the returns onto the sql database?

Thanks for you help !!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008

Commented:
As a visual kind of person, I always struggle to understand words.  How are you binding the GridView?  Do you have SqlDataSource, or some other ADO.NET construct?

Author

Commented:
Posted below is an example of the code I am looking at manipulating. I believe I have to inner join the two tables, but I am not sure on the code for that.

table one
index key ,  name

Table 2
Index key ,Date
I want the most recent date to populate on the grid.

Private Sub dgrRiskfree()
        Dim genlAdapt As SqlDataAdapter
        Dim genlSet As DataSet
        Dim tblName As String
        Dim tblSect As String

        Dim dt As DataTable
        Dim dr As DataRow

        tblName = "Table Name"
        tblSect = "SELECT * " & _
                  "FROM [" & tblName & "] " & _
                  "ORDER BY [Month] DESC"

        myConnection.Open()
        genlSet = New DataSet("genlSet")
        genlAdapt = New SqlDataAdapter(tblSect, myConnection)
        genlAdapt.Fill(genlSet, tblName)
        myConnection.Close()

        dt = genlSet.Tables(tblName)

        DataGrid1.DataSource = dt
        DataGrid1.DataBind()
    End Sub


Thanks for your assistance!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Well...

the SQL would be something like :

Select  t1.name, t1.display_name, max(t2.return_date) as return_date
from table1 t1
inner join table2 t2 on t2.index_key = t1.index_key
group by t1.name, t1.display_name
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2008

Commented:
Since you are using ADO.NET, are you calling 'dgrRiskfree' in the Page_Load event handler?  Are you checking for Not Page.IsPostBack, or calling it every time?  Do you need for this data to be updateable?

Author

Commented:
Yes, the dgrRiskfree would be in the Page_Load, this would be page one.  I will have a 3rd column in the grid that will have a hyper link to a second page with the Index the user has choosen and that page will be updatable. Do you agree with mark_wills SQL statement?  
Most Valuable Expert 2012
Top Expert 2008

Commented:
If you need an updateable query, then I would not go with the joins, but get 2 DataTables into a DataSet, and add a DataRelation to the DataSet between the two tables.

Author

Commented:
The updateable gridview will be n the second page. The first gridview has both tables inner joined an a hyperlink to the second page gridview. I need do as you say on the seond gridview and add the 2 tables into a DataSet, add a DataRelation. How do I code the hyperlink on the first gridview to pull only a specific index selected on the send gridview?  
Most Valuable Expert 2012
Top Expert 2008

Commented:
"How do I code the hyperlink on the first gridview to pull only a specific index selected on the send gridview?"
I don't understand that question--"to pull only a specific index".  That sounds like DataNavigateUrlFields, DataNavigateUrlFormatString, and a QueryString value.

Example:
<asp:HyperLinkField DataNavigateUrlFields="Whs-num,Dept,trx-date"
DataNavigateUrlFormatString="~/gl.aspx?whs={0}&dept={1}"
DataTextField="Amount" HeaderText="Sku Value"
NavigateUrl="~/MemberPages/Accounting/glbalictrxtrans.aspx"
DataTextFormatString="{0:c}" >
<ItemStyle HorizontalAlign="Right" />
<FooterStyle HorizontalAlign="Right" />
</asp:HyperLinkField>

Open in new window

Author

Commented:
Yes, the DataNavigateUrlFields, DataNavigateUrlFormatString, and a QueryString value would work fine. I want to pass the Index Key to the second page gridview in order for the updateable gridview to populate with the selected Index's information.  Here the usere would have the option to change data.

Most Valuable Expert 2012
Top Expert 2008
Commented:
DataNavigateUrlFields would have the Index column, and the DataNavigateUrlFormatString would have the URL and QueryString options that you can use to pass to the 2nd web page.  Then, on the 2nd page, grab the arguments passed from Request.QueryString["key"] to use as an argument to pull data from the table.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
TLO, My understanding of the problem is typical (?) of a master detail type thing. First "page" has the master grid (built from the aforementioned query) where the user can select or highlight a row where one of the columns is a hyperlink (or equivelant) to fire up a second page with additional details pertaining to the selected row. The first page is really a selection screen to get into the second page.  The second page can be editted or manipluated however needed.

jjeff123, if my understanding is correct, please have a look at the asp.net tutorial : http://www.asp.net/Learn/data-access/tutorial-09-vb.aspx

It bascialyy shows you how to set up the master on one page and then links to the details on a second page (see pictures below).
asp-net-tutorial-9-Master-Detail.jpg
Most Valuable Expert 2012
Top Expert 2008

Commented:
Yes, Mark, that is what I was showing details for--how to configure the HyperLink to pull up the 2nd page, and pass key information as a QueryString value.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
ummmm.... hadn't refreshed this page for over an hour, so, didnt see some of those previous comments. Sorry about that...

Author

Commented:
This information provided is perfect. I am glad you guys are here for us newbs. Thanks!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Very happy to have been of some assistance, though believe the credit really goes to TheLearnedOne's impeccable knowledge. And I do apologise for being out of synch back there...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial