Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Master-Details

Status
Not open for further replies.
Caden

Caden

Programmer
Dec 9, 2001
101
CA
So, this is a bit of an update on an older post, but i've made progress and i'm stuck again.

Here's an addition.

So I found the master-details relationship, and currently, I have this...

Click on the details button on the master page and this is the code on the details page...

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter

Dim SelectCmd As String = "select positiontitle, shift, openings" _
& "from jobs"

MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("PubsString"))
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 11))
MyCommand.SelectCommand.Parameters("@Id").Value = Request.QueryString("id")

DS = new DataSet()
Try
MyCommand.Fill(DS, "jobs")
MyDataGrid.DataSource = DS.Tables("jobs").DefaultView
MyDataGrid.DataBind()
Catch
End Try

End Sub

</script>
<html>
<head>
</head>
<body style="FONT: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Working with Master-Detail Relationships</font>
</h3>
<h4><font face="Verdana">Details for Job </font><%=Request.QueryString("id")%>
</h4>
<ASP:DataGrid id="MyDataGrid" runat="server" HeaderStyle-BackColor="#aaaadd" Font-Size="8pt" Font-Name="Verdana" CellPadding="3" BorderColor="Black" BackColor="#CCCCFF" Width="595px" Font-Names="Verdana">
<HeaderStyle backcolor="#AAAADD"></HeaderStyle>
</ASP:DataGrid>
</form>
</body>
</html>



Now it isn't working right, it is catching the querystring, so the ID is right, it'll tell me...

"Details for job (whatever it is the ID is)"
but then I get nothing, so the datagrid isn't catching the information, but I dont' know what's wrong...

Any help would be great, thanks.
Caden
 
Sort by date Sort by votes
Whatever happened with the ODBC mdb datasource post?

As for above, You dont have a WHERE Clause in your sql statement. Its best to have your id be an integer, and an identity column. No need for parameters on this one.

For master details, you need to set the datakey field in the html code of the datagrid, then use that when you bind your details grid. I can post working master-detail datagrid code if you want.

It will save you grief in the future if you start all your code in the page load with the postback check, and call your code from there...

Code: 
Sub Page_Load(Sender As Object, E As EventArgs)
   If Not Page.IsPostBack Then
      BindData()
   End If
End Sub

Sub BindData()   
        Dim SelectCmd As String = "SELECT positiontitle, shift, openings FROM jobs WHERE JobID='" & Request.QueryString("id") & "'"
    
        Dim MyConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("PubsString"))
        Dim MyCommand As New SqlDataAdapter(SelectCmd, MyConnection)
    
        Dim DS As new DataSet()
    Try
        MyCommand.Fill(DS)
          MyDataGrid.DataSource = DS
          MyDataGrid.DataBind()
    Catch ex As Exception
        message.Text = ex.ToString()
    Finally
        MyConnection.Close()
    End Try
End Sub
 
Upvote 0 Downvote
Here is the HTML code of my Datagrid.

I'll add the WHERE clause, but if you don't mind, toss up that working code of the Master-Details.

That problem you were helping me out with earlier I fixed, i'm an idiot, and it was something tiny, really, very annoying.

Thanks for the help so far.
Caden




<asp:DataGrid id="entries" runat="server" OnDeleteCommand="entries_delete" AutoGenerateColumns="False" OnEditCommand="entries_Edit" OnCancelCommand="entries_Cancel" OnUpdateCommand="entries_Update" Height="100px" Font-Names="Arial,Helvetica,sans-serif" Font-Size="8pt" Font-Bold="True" CellPadding="1" CellSpacing="2" BorderColor="Black">
<HeaderStyle font-size="10pt" forecolor="White" backcolor="Black"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" HeaderText="Admin" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="ID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="positiontitle" HeaderText="Position Title"></asp:BoundColumn>
<asp:BoundColumn DataField="shift" HeaderText="Shift"></asp:BoundColumn>
<asp:BoundColumn DataField="openings" HeaderText="Openings"></asp:BoundColumn>
<asp:BoundColumn DataField="startpay" HeaderText="Start Pay"></asp:BoundColumn>
<asp:BoundColumn DataField="toppay" HeaderText="Top Dept. Pay"></asp:BoundColumn>
<asp:BoundColumn DataField="longdesc" HeaderText="Long Desc"></asp:BoundColumn>
<asp:BoundColumn DataField="date" ReadOnly="True" HeaderText="Date" DataFormatString="{0:g}"></asp:BoundColumn>
<asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>
<asp:HyperLinkColumn Text="Details" DataNavigateUrlField="id" DataNavigateUrlFormatString="details.aspx?id={0}"></asp:HyperLinkColumn>
</Columns>
</asp:DataGrid>


 
Upvote 0 Downvote
This actually has 2 datagrids on same page with the master-details... in some columns i have links to open other pages, setting the querystrings, like your example.

try to start writing codebehind pages too, it will help organize a little better. You can have 2 similar aspx pages calling the same codebehind page down the road.

This has a search feature too that will find data selected from a drop down list. The dg select command will launch the binding of the detail grid, but on page load, sets the details of the first item in dg

vendors.aspx
Code: 
<%@ Page language="VB" debug="true" inherits="ven_cb" src="vendors.aspx.vb"%>
<HTML>
<HEAD>
	<TITLE>Vendors</TITLE>
	<link rel=stylesheet type="text/css" href="css\page.css">
</HEAD>
<FORM RUNAT=Server>
    <div class=head>Vendors</div>
    <hr>
    <table border=0 cellspacing=0 cellpadding=0 width=98% class=hyper><tr>
    	<td align=left width=40%><a href="venDetails.aspx?venid=new">Add New Vendor</a>&nbsp;&nbsp;<a href="po.aspx">Purchase Orders</a></td>
    	<td align=right width=60%><asp:DropDownList runat=server id=venDDL DataValueField="VendorID" DataTextField="VendorName" onSelectedIndexChanged=findPO autoPostBack=True />
    	&nbsp;<asp:button id="resetBtn" runat="server" text="Reset" onClick="reset_grid" />
    	</td></tr>
    </table>
    <hr>
    <div align=right><asp:Label id=message runat=server class=messages enableviewstate=false /></div>
	<asp:Datagrid id=venMaster runat=server autogeneratecolumns=False width=98% OnPageIndexChanged="po_Page" PageSize="6" AllowPaging="true" OnSelectedIndexChanged="po_Select" DataKeyField="VendorID" class=dg CellSpacing=1 CellPadding=2 BackColor=White ForeColor=Black GridLines=None>
		<HeaderStyle backcolor="#4A3C8C" cssclass=dgHead></HeaderStyle>
        <SelectedItemStyle forecolor="White" backcolor="#9471DE"></SelectedItemStyle>
        <PagerStyle horizontalalign="Right" backcolor="#C6C3C6" mode="NumericPages" Position="TopAndBottom"></PagerStyle>
        <ItemStyle backcolor="#DEDFDE"></ItemStyle>
        <columns>
        	<asp:buttoncolumn text="History" commandname="Select" ItemStyle-Font-Size="smaller" ItemStyle-Width=45 />
            <asp:HyperLinkColumn Text="Details" ItemStyle-Width=45 ItemStyle-VerticalAlign="Middle" DataNavigateUrlField="VendorID" DataNavigateUrlFormatString="venDetails.aspx?venid={0}" Target="_blank" />
            <asp:BoundColumn DataField="VendorName" HeaderText="Vendor Name" ItemStyle-Wrap=False ItemStyle-VerticalAlign=Middle />
            <asp:BoundColumn DataField="VendorAcct" HeaderText="Acct #" ItemStyle-Wrap=False ItemStyle-VerticalAlign=Middle ItemStyle-Width=75 />
	        <asp:BoundColumn DataField="VendorContactName" HeaderText="Contact" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Middle" />
	        <asp:BoundColumn DataField="VendorPhone" HeaderText="Phone" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Middle" ItemStyle-Width=100 />
            <asp:BoundColumn DataField="VenEmail1" HeaderText="Email" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Middle" ItemStyle-Width=100 />
            <asp:HyperLinkColumn DataTextField="VenWeb" ItemStyle-Wrap=True HeaderText="Web URL" ItemStyle-VerticalAlign="Middle" DataNavigateUrlField="VenWeb" Target="_blank" />
    	</columns>
    </asp:DataGrid><br>
      	<asp:datagrid id=poHist runat=server width="98%" CellSpacing=1 GridLines=None CellPadding=3 BackColor=White ForeColor=Black EnableViewState=False AutoGenerateColumns=False class=dg>
         	<HeaderStyle backcolor="#4A3C8C" cssClass=dgHead />
            <ItemStyle backcolor="#DEDFDE" />
                <columns>
                	<asp:HyperLinkColumn DataTextField="PONum" ItemStyle-Wrap="False" HeaderText="PO #" ItemStyle-Width=50 ItemStyle-VerticalAlign="Middle" DataNavigateUrlField="PONum" DataNavigateUrlFormatString="poDetails.aspx?PONum={0}" Target="_blank" />
	        		<asp:BoundColumn DataField="POTotal" HeaderText="PO Total" ItemStyle-Wrap=False ItemStyle-VerticalAlign=Middle ItemStyle-Width=75 DataFormatString="{0:C}" />
	        		<asp:BoundColumn DataField="POStatus" HeaderText="Status" ItemStyle-Wrap="False" ItemStyle-VerticalAlign="Middle" ItemStyle-Width=150 />
            		<asp:BoundColumn DataField="PODesc" HeaderText="Details" ItemStyle-Wrap="True" ItemStyle-VerticalAlign="Middle" />
                </Columns>
        	</asp:datagrid>
</form>
</html>

vendors.aspx.vb

Code: 
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HTMLControls

	Public Class ven_cb
	Inherits System.Web.UI.Page
	
	Public venMaster, poHist As DataGrid
	Public venDDL As DropDownList
	Public message As Label

	Dim ConnectionString As String = "server=(local);database=helpdesk;trusted_connection=true"

    Sub Page_Load(Sender As Object, E As EventArgs)
        If Not Page.IsPostBack Then
      		BindDDL()
      		BindMaster()
  		BindDetails()
        End If
    End Sub
    
    Sub BindMaster()
    	Dim CommandText As String
    	If ViewState("PONum") = "" Then
        	CommandText = "SELECT t1.* FROM tblVendors t1 ORDER BY VendorName ASC"
        Else If ViewState("PONum") <> "" Then
        	CommandText = "SELECT t1.* FROM tblVendors t1 WHERE t1.VendorID=" & ViewState("PONum") & " ORDER BY t1.VendorName ASC"
        End If
          
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
    
        Dim ds As New DataSet()
        myCommand.Fill(ds)
    	
        venMaster.DataSource = ds
        If ds.Tables(0).Rows.Count < 1 Then
        	message.text = "There are not any Vendors for the filter selected."
        	BindDetails()
        	Exit Sub
        End If
        venMaster.DataBind()
        myConnection.Close()
        venMaster.SelectedIndex = 0
    End Sub
    
    Sub BindDetails()
    	If venMaster.SelectedIndex <> -1 Then
	    	Dim poValue As String = CStr(venMaster.DataKeys(venMaster.SelectedIndex))
        	Dim CommandText As String = "SELECT t1.PONum, t1.POTotal, t1.PODesc, t2.POStatus FROM tblPO t1 LEFT JOIN tblPOStatus t2 ON t1.POLastStatus = t2.POStatusID WHERE VendorID='" & poValue & "' ORDER BY t1.PONum DESC"
    
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim myCommand As New SqlCommand(CommandText, myConnection)

			myConnection.Open()
    		poHist.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    	End If
        	poHist.DataBind()
    	End Sub
    
    Sub BindDDL()
    	Dim objConnection As New SqlConnection(ConnectionString)
    	Dim SelectFilter As String = "SELECT VendorID, VendorName FROM tblVendors"
		Dim objCommand As SqlCommand = New SqlCommand(SelectFilter, objConnection)
		Dim objAdapter As SqlDataAdapter = New SqlDataAdapter(objCommand)
		Dim objDataSet As DataSet = New DataSet()
				  		
  		objAdapter.Fill(objDataSet) 
			venDDL.DataSource = objDataSet
			venDDL.DataBind()
		objConnection.Close()
		venDDL.Items.Insert(0, new ListItem("Find By Vendor"))	
    End Sub
    
    Sub po_Select(Sender As Object, E As EventArgs)
        BindDetails()
    End Sub
    
    Sub po_Page(Sender As Object, E As DataGridPageChangedEventArgs)  
        venMaster.CurrentPageIndex = e.NewPageIndex
        BindMaster()
        BindDetails()
    End Sub
    
	Sub reset_grid(sender As Object, e As System.EventArgs)
		venDDL.SelectedIndex = 0
		venMaster.CurrentPageIndex = 0
		ViewState("PONum") = ""
		BindMaster()
		BindDetails()
	End Sub
	
	Sub findPO(sender As Object, e As System.EventArgs)
        	Dim strPO As String = venDDL.SelectedItem.Value
        	ViewState("PONum") = strPO
        	venMaster.CurrentPageIndex = 0
        BindMaster()
        BindDetails()
	End Sub
End Class
 
Upvote 0 Downvote
Problem with that is, it's too much too look at and try and fish out my answer from it...i'm running under the assumption i'm missing one or two small things to get this to work.

I know it is passing the querystring across, but it isn't displaying the record...why not?

Caden

P.S. thanks for the code though, i'll try to get it working it'll probably teach me a lot in the long run.
 
Upvote 0 Downvote
Did you do as i posted before? is your id field acutally an nvarchar? Why? Can you change it to an integer? if you do, remove the single quotes...

Dim SelectCmd As String = "SELECT positiontitle, shift, openings FROM jobs WHERE JobID=" & Request.QueryString("id")

the little thing your missing is the where statement and the proper format of the id field.

did you remove the parameters? did you put in the message.text catch exception? that will tell you what wrong.
set custom errors = off in your web.config and debug=true in your page declarations.
 
Upvote 0 Downvote
My ID field is an autonumbered field. I'm creating an access database through the Microsoft Web Matrix, and it seems to really really suck. This may be the first of many problems, but hey, right now, most of what I want is working and i'm new, so this is progress.

I have the WHERE clause put in, but that doesn't appear to be helping. It's either not binding the grid, or something else.

I haven't yet put in the message.text catch.

Just an update
 
Upvote 0 Downvote
Here's an Update on my progress, and i'm suck once again, but I think i'm real close...

I took a bit of a different approach, grabbed my big ASP.NET unleashed book...and took this approach...

<%@ Page Language="vb" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)

Dim dtrJob As SqlDataReader
Dim intID as Integer
Dim cmdSelect as SqlCommand



Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Joel's Work\"& _
"jobs.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.Connection = dbConnection


intID = Int32.Parse( Request.QueryString("id"))


Dim strSelect As String = "Select * From Jobs Where ID=@ID"
cmdSelect = New SqlCommand(strSelect, dbConnection)

cmdSelect.Parameters.Add( "@ID", ID)
dbConnection.Open
dtrJob = cmdSelect.ExecuteReader()

if dtrJob.Read Then
toppay.Text = dtrJob( "toppay" )

End If
End Sub

</script>
<html>
<head>
</head>
<body style="FONT: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Working with Master-Detail Relationships</font>
</h3>
<h4><font face="Verdana">Details for Job </font>
</h4>
<h4>&nbsp;
</h4>
<h4><asp:Label id="toppay" runat="server"></asp:Label><%=Request.QueryString("id")%>
</h4>
</form>
</body>
</html>



I'm getting an error on this line
cmdSelect = New SqlCommand(strSelect, dbConnection)

it is telling me that the "specified cast is not valid"

and i'm not quite sure what that means...

Thanks
Caden
 
Upvote 0 Downvote
I think it has to do with your namespaces. You import the sqlclient namespace but use the oledb namespace for some of your variables.

Since your using access you can use the sqlclient name space, you can only use the oledb name space. the sqlclient name space only works with Sql Server.

there is also no System.Data.IDb... that I am aware of, only sqlclient and oledb.

the error message is saying that you are trying to cast 1 variable as a different variable, and there is no implicit conversion.

Jason Meckley
Database Analyst
WITF
 
Upvote 0 Downvote
Ahhh, alright, that makes sense.

So now i'm trying to adjust it, i'm assuming using there is an access equivilent to "Sqldatareader"?
 
Upvote 0 Downvote
Final Update!

I decided to scrap the Access database and change it to a SQL database...and everything works!

Thanks to all of you
Caden
 
Upvote 0 Downvote
I have a master-detail connection, how i make the detail to be master for an other detail?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dvannoy
  • Locked
  • Question
Using two css files within a master page 1
Replies
4
Views
848
jbenson001
jbenson001
dvannoy
  • Locked
  • Question
Menu and Master Page
Replies
1
Views
219
dvannoy
dvannoy
dvannoy
  • Locked
  • Question
Main Content in Master Page
Replies
7
Views
396
jbenson001
jbenson001
wbodger
  • Locked
  • Question
ASP.Net master page and forms
Replies
3
Views
196
wbodger
wbodger
Halliarse
  • Locked
  • Question
Master Page and CSS creation 1
Replies
9
Views
263
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top