"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:uW******** *****@TK2MSFTNG P15.phx.gbl...
Specify the DataTextField and DataValueField of the dropdown...
DropDownList1.D ataSource =
objCommand.Exec uteReader(Comma ndBehavior.Clos eConnection)
DropDownList1.D ataTextfield = "Title"
DropDownList1.D ataValuefield = "TitleId" 'that won't work beceause you
don't have a TitleId, so you could just reuse Title..but that might not be
unique...
DropDownList1.D ataBind()
anyways, having Data access directly in your page_load is a sign that you
don't have a business or data layer ;) I know ur just playing around ...
Karl
That worked. I had never tried binding data to a dropdownlist before (as you
may have figured out).
I do have a TitleID field within the database that is the PK and an Identity
type so auto increments for me. I didn't see any reason to who the PK on the
webpage anywhere though.
I'm not sure what you mean by Business or Data layer.
My project was to try to add data into related tables successfully. All my
work in the past has been adding data into one table, which has been fairly
easy to do. It's adding data to two related tables at the same time that I
hadn't tried and wasn't sure how to successfully do it. I am running into a
duplicate value problem now.
My Tables:
[T-Master]
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
[T-Title]
TitleID INT PRIMARY KEY IDENTITY NOT NULL,
EmpID INT,
CONSTRAINT EmpID_FK FOREIGN KEY(EmpID)
REFERENCES [T-Master](EmpID)
[T-TitleList]
TitleID INT PRIMARY KEY IDENTITY NOT NULL,
Title VARCHAR(200)
The TitleList table is just a table to popluate the DropDownList.
The relationship between T-Master and T-Title is a one to many so an
Employee can have more than one Title.
I understand that I can't have a duplicate employee within the T-Master
table with the same EmpID.
In my code (show below), I can do the insert into both tables just fine and
it appears to work successfully. But, if I try to submit again with the same
employee but different Title, I get the Error:
Violation of PRIMARY KEY constraint 'PK__T-Master__440B1D6 1'. Cannot insert
duplicate key in object 'T-Master'.
The statement has been terminated.
I do understand what's happening here. So, how would I modify my code so
that I could add another Title to the Employee's title within the T-Title
table without violating the key constraint in T-Master?
Here's my Code:
=============== =============== ======
Imports System.Data
Imports System.Data.Sql Client
Partial Class insert1
Inherits System.Web.UI.P age
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
lblFirstName.Te xt = "First Name"
lblLastName.Tex t = "Last Name"
lblEmployeeID.T ext = "Employee ID"
lblTitle.Text = "Employee Title"
Dim strSQL, strConnection As String
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
strConnection = "server=sqldev; database=test;U ser
ID=sa;Password= password"
strSQL = "Select Title FROM [T-TitleList]"
objConnection = New SqlConnection(s trConnection)
objConnection.O pen()
objCommand = New SqlCommand(strS QL, objConnection)
ddlTitle.DataSo urce =
objCommand.Exec uteReader(Comma ndBehavior.Clos eConnection)
ddlTitle.DataTe xtField = "Title"
ddlTitle.DataBi nd()
objConnection.C lose()
End Sub
Protected Sub btnSubmit_Click (ByVal sender As Object, ByVal e As
System.EventArg s) Handles btnSubmit.Click
Dim strConnection, strSQL1, strSQL2, strSQL3 As String
Dim objConnection As SqlConnection
Dim objCommand1, objCommand2, objCommand3 As SqlCommand
strConnection = "server=sqldev; database=test;U ser
ID=sa;Password= password"
strSQL1 = "INSERT INTO [T-Master] (EmpID, FirstName, LastName)
VALUES ('" & _
txtEmployeeID.T ext & "','" & txtFirstName.Te xt & "','" &
_
txtLastName.Tex t & "')"
strSQL2 = "INSERT INTO [T-Title] (EmpID, Title) VALUES ('" & _
txtEmployeeID.T ext & "','" &
ddlTitle.Select edValue.ToStrin g & "')"
strSQL3 = "Select * From View1"
objConnection = New SqlConnection(s trConnection)
objConnection.O pen()
objCommand1 = New SqlCommand(strS QL1, objConnection)
objCommand2 = New SqlCommand(strS QL2, objConnection)
objCommand3 = New SqlCommand(strS QL3, objConnection)
objCommand1.Exe cuteNonQuery()
objCommand2.Exe cuteNonQuery()
dgList1.DataSou rce =
objCommand3.Exe cuteReader(Comm andBehavior.Clo seConnection)
dgList1.DataBin d()
objConnection.C lose()
End Sub
End Class
=============== =============== ======
And here's the web form, if it matters:
=============== =============== ======
<%@ Page Language="VB" Debug="true" AutoEventWireup ="false"
CodeFile="inser t1.aspx.vb" Inherits="inser t1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body style="backgrou nd-color:Black;">
<form id="form1" runat="server">
<table border="1" bordercolor=whi te>
<tr>
<td valign=top style="width: 155px"><div>
<asp:Label ID="lblFirstNam e" runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua "></asp:Label><br />
<asp:TextBox ID="txtFirstNam e" runat="server" BackColor="Silv er"
Font-Names="Arial Narrow" ForeColor="Navy "></asp:TextBox><br />
<br />
<asp:Label ID="lblLastName " runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua "></asp:Label><br />
<asp:TextBox ID="txtLastName " runat="server" BackColor="Silv er"
Font-Names="Arial Narrow" ForeColor="Navy "></asp:TextBox><br />
<br />
<asp:Label ID="lblEmployee ID" runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua "></asp:Label><br />
<asp:TextBox ID="txtEmployee ID" runat="server" BackColor="Silv er"
Font-Names="Arial Narrow" ForeColor="Navy "></asp:TextBox><br />
<br />
<asp:Label ID="lblTitle" runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua "></asp:Label><br />
<asp:DropDownLi st ID="ddlTitle" runat="server" Width="384px"
BackColor="Silv er" Font-Names="Arial Narrow" ForeColor="Navy ">
</asp:DropDownLis t><br />
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit Info"
Width="152px" BackColor="Blac k" BorderColor="Aq ua" BorderStyle="So lid"
Font-Bold="True" Font-Names="Monotype Corsiva" Font-Size="Large"
ForeColor="Lime " />
</div></td>
<td valign=top><asp :DataGrid ID="dgList1" runat=server
PagerStyle-BorderColor="Si lver" PagerStyle-ForeColor="Whit e"
HeaderStyle-BackColor="Navy " HeaderStyle-Font-Bold="True"
HeaderStyle-ForeColor="Whit e" ForeColor="Whit e" >
<PagerStyle BorderColor="Si lver" ForeColor="Whit e" />
<HeaderStyle BackColor="Navy " Font-Bold="True" ForeColor="Whit e" />
</asp:DataGrid></td>
</tr>
</table>
</form>
</body>
</html>
=============== =============== ======
I am using Visual Studio 2005 Beta 2 with .NET Framework 2 Beta 2.
Thanks,
Jim
