What Objects To Use & How To Properly Bind

[Jim in Arizona]

Being new, i experiment a lot and usually get results I'm not hoping for. My
current problem is reading from a SQL 2000 table and populating an ASP
dropdownlist with a column from the table. The only thing displayed in the
DropDownList is:

System.Data.Common.DbDataRecord

So, under the Page_Load Event, here's my code:

Dim strSQL, strConnection As String
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand

strConnection = "server=sqldev;database=test;User ID=sa;Password=password"
strSQL = "Select Title FROM [T-TitleList]"

objConnection = New SqlConnection(strConnection)
objConnection.Open()

objCommand = New SqlCommand(strSQL, objConnection)

DropDownList1.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataBind()

objConnection.Close()


Thanks,
Jim

 

[Karl Seguin]

Specify the DataTextField and DataValueField of the dropdown...

DropDownList1.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataTextfield = "Title"
DropDownList1.DataValuefield = "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.DataBind()


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


--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[Jim in Arizona]

Specify the DataTextField and DataValueField of the dropdown...

DropDownList1.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataTextfield = "Title"
DropDownList1.DataValuefield = "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.DataBind()


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__440B1D61'. 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.SqlClient

Partial Class insert1
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
lblFirstName.Text = "First Name"
lblLastName.Text = "Last Name"
lblEmployeeID.Text = "Employee ID"
lblTitle.Text = "Employee Title"

Dim strSQL, strConnection As String
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand

strConnection = "server=sqldev;database=test;User
ID=sa;Password=password"
strSQL = "Select Title FROM [T-TitleList]"

objConnection = New SqlConnection(strConnection)
objConnection.Open()

objCommand = New SqlCommand(strSQL, objConnection)

ddlTitle.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
ddlTitle.DataTextField = "Title"
ddlTitle.DataBind()

objConnection.Close()

End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As
System.EventArgs) 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;User
ID=sa;Password=password"
strSQL1 = "INSERT INTO [T-Master] (EmpID, FirstName, LastName)
VALUES ('" & _
txtEmployeeID.Text & "','" & txtFirstName.Text & "','" &
_
txtLastName.Text & "')"

strSQL2 = "INSERT INTO [T-Title] (EmpID, Title) VALUES ('" & _
txtEmployeeID.Text & "','" &
ddlTitle.SelectedValue.ToString & "')"


strSQL3 = "Select * From View1"
objConnection = New SqlConnection(strConnection)
objConnection.Open()

objCommand1 = New SqlCommand(strSQL1, objConnection)
objCommand2 = New SqlCommand(strSQL2, objConnection)
objCommand3 = New SqlCommand(strSQL3, objConnection)
objCommand1.ExecuteNonQuery()
objCommand2.ExecuteNonQuery()

dgList1.DataSource =
objCommand3.ExecuteReader(CommandBehavior.CloseConnection)
dgList1.DataBind()

objConnection.Close()
End Sub
End Class

====================================

And here's the web form, if it matters:

====================================

<%@ Page Language="VB" Debug="true" AutoEventWireup="false"
CodeFile="insert1.aspx.vb" Inherits="insert1" %>

<!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>Untitled Page</title>
</head>
<body style="background-color:Black;">
<form id="form1" runat="server">
<table border="1" bordercolor=white>
<tr>
<td valign=top style="width: 155px"><div>
<asp:Label ID="lblFirstName" runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua"></asp:Label><br />
<asp:TextBox ID="txtFirstName" runat="server" BackColor="Silver"
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="Silver"
Font-Names="Arial Narrow" ForeColor="Navy"></asp:TextBox><br />
<br />
<asp:Label ID="lblEmployeeID" runat="server" Font-Names="Monotype
Corsiva" Font-Size="X-Large"
Text="Label" ForeColor="Aqua"></asp:Label><br />
<asp:TextBox ID="txtEmployeeID" runat="server" BackColor="Silver"
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 />
<aspropDownList ID="ddlTitle" runat="server" Width="384px"
BackColor="Silver" Font-Names="Arial Narrow" ForeColor="Navy">
</aspropDownList><br />
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit Info"
Width="152px" BackColor="Black" BorderColor="Aqua" BorderStyle="Solid"
Font-Bold="True" Font-Names="Monotype Corsiva" Font-Size="Large"
ForeColor="Lime" />
</div></td>
<td valign=top><aspataGrid ID="dgList1" runat=server
PagerStyle-BorderColor="Silver" PagerStyle-ForeColor="White"
HeaderStyle-BackColor="Navy" HeaderStyle-Font-Bold="True"
HeaderStyle-ForeColor="White" ForeColor="White" >
<PagerStyle BorderColor="Silver" ForeColor="White" />
<HeaderStyle BackColor="Navy" Font-Bold="True" ForeColor="White" />
</aspataGrid></td>
</tr>
</table>

</form>
</body>
</html>

====================================

I am using Visual Studio 2005 Beta 2 with .NET Framework 2 Beta 2.

Thanks,
Jim