... but the question was different from the actual topic of the thread and I have marked it as answered. So to get the best help, I am going to repost the followup question here.
I have a page that uses a dropdownlist box to populate address fields from a database. If I change one of the text boxes, say Street, and hit the update button, the changes are not being saved back to the database. I am not sure what is causing this problem
but maybe someone else can see what it is where I am blind.
I am going to include the entire pages content just so that you all can see everything I am doing in case I did something wrong elsewhere that is affecting it.
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections
Partial Class Customers_Addresses
Inherits System.Web.UI.Page
Protected Sub Cancel_Click(sender As Object, e As System.EventArgs) Handles Cancel.Click
Response.Redirect("~/Customers/Account.aspx")
End Sub
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
txtAddressName.Focus()
End Sub
Protected Sub AddAddress_Click(sender As Object, e As System.EventArgs) Handles AddAddress.Click
If Not (Session("Account") > 0) Then
' Make sure the session is not timed out
AddAddress.CausesValidation = False
Response.Redirect("~/Customers/Login.aspx")
' Has the Address Name been used?
ElseIf VerifyAddressName() Then
lblError.Text = "This Address Name Is Already In Use."
lblError.Visible = True
Else
' Add the New Address
UpdateAddressName()
' Redirect User To The Accounts Page
Session("Status") = "Address"
Response.Redirect("~/Customers/Account.aspx")
End If
End Sub
Protected Function AddressChecked() As Boolean
'DETERMINE IF ANY ADDRESS HAS PREFERRED CHECKED
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("SELECT [CustomerID] FROM [ADDRESSES] WHERE (([CustomerID] = @CustomerID) AND ([Preferred] = @Preferred))", con)
cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account"))
cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = True
Dim ds As DataSet
Dim dt As DataTable
Dim da As SqlDataAdapter
Dim customerID As Integer
Try
' Try to open the connection.
con.Open()
da = New SqlDataAdapter(cmd)
ds = New DataSet()
dt = New DataTable()
da.Fill(ds)
dt = ds.Tables(0)
For Each dr As DataRow In dt.Rows
If Not dr Is Nothing Then
customerID = dr("CustomerID")
End If
Next
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
' Is there another checked address?
If customerID = Nothing Then
' There are no other checked addresses
Return False
Else
' There is another checked address
Return True
End If
End Function
Protected Sub UncheckAddress()
' UNCHECK THE CURRENTLY CHECKED ADDRESS
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("UPDATE [ADDRESSES] SET [Preferred] = @Preferred WHERE [CustomerID] = @CustomerID", con)
cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = False
cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account"))
' Insert the Record
Try
' Try to open the connection.
con.Open()
cmd.ExecuteNonQuery()
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
End Sub
Protected Sub UpdateAddressName()
' SEND VARIABLES TO THE SQL DATABASE TO CREATE A NEW ADDRESS
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO [ADDRESSES](CustomerID, AddressName, AddressType, Preferred, Street, AptNumber, City, State, ZipCode) VALUES (@CustomerID, @AddressName, @AddressType, @Preferred, @Street, @AptNumber, @City, @State, @ZipCode)", con)
cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account"))
cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text
cmd.Parameters.Add("@AddressType", Data.SqlDbType.NVarChar).Value = AddressTypeDropDown.SelectedItem.Value
' If Preferred is checked
If Preferred.Checked = True Then
' Check to see if any other addresses are checked
If AddressChecked() = True Then
' Uncheck old address
UncheckAddress()
End If
' Continue to update the data
End If
' Declare a temporary storage variable for AptNumber
Dim tmpAptNumber As String
tmpAptNumber = txtAptNumber.Text
cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = (If(Preferred.Checked, True, False))
cmd.Parameters.Add("@Street", Data.SqlDbType.NVarChar).Value = txtStreet.Text
cmd.Parameters.Add("@AptNumber", Data.SqlDbType.NVarChar).Value = (If(tmpAptNumber = Nothing, "", tmpAptNumber))
cmd.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = txtCity.Text
cmd.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = StateDropDown.SelectedItem.Value
cmd.Parameters.Add("@ZipCode", Data.SqlDbType.NVarChar).Value = txtZipCode.Text
' Insert the Record
Try
' Try to open the connection.
con.Open()
cmd.ExecuteNonQuery()
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
End Sub
Protected Function VerifyAddressName() As Boolean
'DETERMINE IF ADDRESS NAME HAS BEEN USED BY THIS CUSTOMER
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("SELECT [AddressName] FROM [ADDRESSES] WHERE ([AddressName] = @AddressName)", con)
cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text
Dim ds As DataSet
Dim dt As DataTable
Dim da As SqlDataAdapter
Dim strAddressName As String
Try
' Try to open the connection.
con.Open()
da = New SqlDataAdapter(cmd)
ds = New DataSet()
dt = New DataTable()
da.Fill(ds)
dt = ds.Tables(0)
For Each dr As DataRow In dt.Rows
If Not dr Is Nothing Then
strAddressName = dr("AddressName")
End If
Next
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
' Has this Address Name been used?
If strAddressName = Nothing Then
' The Name has not been used
Return False
Else
' The Name has been used
Return True
End If
End Function
Protected Sub UpdateAddress_Click(sender As Object, e As System.EventArgs) Handles UpdateAddress.Click
If Not (Session("Account") > 0) Then
UpdateAddress.CausesValidation = False
Response.Redirect("~/Customers/Login.aspx")
Else
' Update the database with the modified Address
UpdateModifiedAddress()
' Redirect User To The Accounts Page
Session("Status") = "AddressMod"
Response.Redirect("~/Customers/Account.aspx")
End If
End Sub
Protected Sub UpdateModifiedAddress()
' UPDATE THE DATABASE WITH THE NEWLY MODIFIED ADDRESS
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("UPDATE [ADDRESSES] SET [AddressName] = @AddressName, [AddressType] = @AddressType, [Preferred] = @Preferred, [Street] = @Street, [AptNumber] = @AptNumber, [City] = @City, [State] = @State, [ZipCode] = @ZipCode WHERE [CustomerID] = @CustomerID", con)
cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account"))
cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text
cmd.Parameters.Add("@AddressType", Data.SqlDbType.NVarChar).Value = AddressTypeDropDown.SelectedItem.Value
cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = (If(Preferred.Checked, True, False))
cmd.Parameters.Add("@Street", Data.SqlDbType.NVarChar).Value = txtStreet.Text
' Declare a temporary storage variable for AptNumber
Dim tmpAptNum As String
tmpAptNum = txtAptNumber.Text
If (tmpAptNum = Nothing) Or (tmpAptNum = "") Then
tmpAptNum = ""
Else
tmpAptNum = txtAptNumber.Text
End If
cmd.Parameters.Add("@AptNumber", Data.SqlDbType.NVarChar).Value = tmpAptNum
cmd.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = txtCity.Text
cmd.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = StateDropDown.SelectedItem.Value
cmd.Parameters.Add("@ZipCode", Data.SqlDbType.NVarChar).Value = txtZipCode.Text
' Insert the Record
Try
' Try to open the connection.
con.Open()
cmd.ExecuteNonQuery()
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
End Sub
Protected Sub AddressDropDown_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles AddressDropDown.SelectedIndexChanged
' Send the values of the selected Address Name to the appropriate fields
PopulateFields()
lblError.Visible = False
End Sub
Protected Sub PopulateFields()
'POPULATE THE FIELDS WITH THE CURRENT SELECTED ADDRESS NAME
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("SELECT [AddressName], [AddressType], [Preferred], [Street], [AptNumber], [City], [State], [ZipCode] FROM [ADDRESSES] WHERE (([CustomerID] = @CustomerID) AND ([AddressName] = @AddressName))", con)
cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account"))
cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = AddressDropDown.SelectedItem.Value
Dim ds As DataSet
Dim dt As DataTable
Dim da As SqlDataAdapter
Try
' Try to open the connection.
con.Open()
da = New SqlDataAdapter(cmd)
ds = New DataSet()
dt = New DataTable()
da.Fill(ds)
dt = ds.Tables(0)
For Each dr As DataRow In dt.Rows
If Not dr Is Nothing Then
txtAddressName.Text = dr("AddressName")
AddressTypeDropDown.SelectedValue = dr("AddressType")
Preferred.Checked = dr("Preferred")
txtStreet.Text = dr("Street")
txtAptNumber.Text = dr("AptNumber")
txtCity.Text = dr("City")
StateDropDown.SelectedValue = dr("State")
txtZipCode.Text = dr("ZipCode")
End If
Next
Catch err As Exception
' Handle an error by displaying the information.
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
End Try
End Sub
End Class
I am getting the following exception when the database connection is opened and the query is executed.
Violation of PRIMARY KEY constraint 'PK_ADDRESSES'. Cannot insert duplicate key in object 'dbo.ADDRESSES'. The duplicate key value is (1, Home).
The statement has been terminated.
Not sure why it is a violation. I have CustomerID and AddressName as primary keys in the ADDRESSES table, but I am trying to update an already existing record, not add a new one. I can't figure out why it would kick out the update.
Just found out that when Visual Studio sees an Update query containing a primary key it turns the Update into an Insert, which caused the record to not update.
I removed the primary key AddressName and it works now. But I am not sure how to allow the user to change the AddressName now since it's a primary key. =/
Marked as answer by 78coolbreeze on Nov 26, 2012 12:44 AM
But I am not sure how to allow the user to change the AddressName now since it's a primary key. =/
Add another column in your address table named Address_ID. Make sure this column is integer. Make this column as primary key instead of AddressName. Then you can change the AddressName column anytime.
The reason I had AddressName as part of the primary key along with CustomerID is to prevent the customer from having multiple nick names for their address that were the same. I also use this approach for my credit card table.
I did however find a slight work around. If I first delete the record and then do an insert based on the vaules of the textboxes it will update the record. However, it only works on data that is not linked to a credit card. So now I am stuck once again
because customers can't update addresses that have credit cards.
In order to try and finish the project on time, I am just removing the ability to update and delete addresses and cards. But if you want to take a look at my relational diagram I will include it below.
78coolbreeze
Member
3 Points
27 Posts
Update Database Records Not Working...
Nov 25, 2012 10:16 PM|LINK
I had this question in this thread...
http://forums.asp.net/t/1861148.aspx/2/10?Postback+Is+Not+Working+Correctly+With+Databound+DropDownList+
... but the question was different from the actual topic of the thread and I have marked it as answered. So to get the best help, I am going to repost the followup question here.
I have a page that uses a dropdownlist box to populate address fields from a database. If I change one of the text boxes, say Street, and hit the update button, the changes are not being saved back to the database. I am not sure what is causing this problem but maybe someone else can see what it is where I am blind.
I am going to include the entire pages content just so that you all can see everything I am doing in case I did something wrong elsewhere that is affecting it.
ASP.NET Code
<%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Addresses.aspx.vb" Inherits="Customers_Addresses" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <br /> <br /> <br /> <asp:Panel ID="Panel1" runat="server" DefaultButton="AddAddress"> <table align="center" cellpadding="2" cellspacing="0" class="style1" style="width: 60%"> <tr> <td style="text-align: center; font-family: 'Arial Black'; text-decoration: underline;" class="style30" colspan="3"> Manage Addresses</td> </tr> <tr> <td style="text-align: right; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:SqlDataSource ID="AddressDropDownDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [AddressName] FROM [ADDRESSES] WHERE ([CustomerID] = @CustomerID) ORDER BY [AddressName]"> <SelectParameters> <asp:SessionParameter Name="CustomerID" SessionField="Account" Type="Decimal" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="TypeDropDownDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [AddressType] FROM [ADDRESSTYPE]"> </asp:SqlDataSource> <asp:SqlDataSource ID="StateDropDownDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [State] FROM [STATE] ORDER BY [State]"></asp:SqlDataSource> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> </td> </tr> <tr> <td class="style30" colspan="3" style="text-align: center; font-family: 'Arial Black';"> Select An Address To Edit Or Add A New Address</td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> </td> </tr> <tr> <td class="style30" colspan="3" style="text-align: center; font-family: 'Arial Black';"> <asp:DropDownList ID="AddressDropDown" runat="server" AppendDataBoundItems="True" DataSourceID="AddressDropDownDataSource" DataTextField="AddressName" DataValueField="AddressName" Height="24px" Width="200px" AutoPostBack="True"> <asp:ListItem Selected="True" Value="-1">Add A New Address...</asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> Address Name:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:TextBox ID="txtAddressName" runat="server" TextMode="SingleLine" Width="200px" MaxLength="50"></asp:TextBox> </td> </tr> <tr> <td class="style30" colspan="3" style="text-align: center; font-family: 'Arial Black';"> <asp:Label ID="lblError" runat="server" Font-Names="Arial Black" ForeColor="Red" Visible="False"></asp:Label> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtAddressName" Display="Dynamic" ErrorMessage="Address Name Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtAddressName" Display="Dynamic" ErrorMessage="Address Name Can Only Contain Letters, Numbers, Apostrophes and Dashes And Must Be Between 3 And 50 Characters." Font-Names="Arial" ForeColor="Red" ValidationExpression="^[\s0-9a-zA-Z'\-]{3,50}$"></asp:RegularExpressionValidator> </td> </tr> <tr> <td class="style30" style="width: 232px; text-align: right; font-family: 'Arial Black'; height: 26px;"> Address Type:</td> <td class="style27" style="height: 26px"> </td> <td style="text-align: left; height: 26px;"> <asp:DropDownList ID="AddressTypeDropDown" runat="server" DataSourceID="TypeDropDownDataSource" DataTextField="AddressType" DataValueField="AddressType" Width="200px" AppendDataBoundItems="True"> <asp:ListItem Selected="True" Value="-1">Select Address Type...</asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="AddressTypeDropDown" Display="Dynamic" ErrorMessage="Address Type Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True" InitialValue="-1"></asp:RequiredFieldValidator> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> Preferred Shipping:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:CheckBox ID="Preferred" runat="server" /> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> Street:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:TextBox ID="txtStreet" runat="server" Width="200px" MaxLength="50"></asp:TextBox> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtStreet" Display="Dynamic" ErrorMessage="Street Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="txtStreet" Display="Dynamic" ErrorMessage="Street Name Can Only Contain Letters, Numbers, Periods, Apostrophes and Dashes And Must Be Between 5 And 50 Characters." Font-Names="Arial" ForeColor="Red" ValidationExpression="^[\s0-9a-zA-Z'\-\.]{5,50}$"></asp:RegularExpressionValidator> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> Apt Number <span class="style29">(Not Required)</span>:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:TextBox ID="txtAptNumber" runat="server" Width="100px" MaxLength="10"></asp:TextBox> </td> </tr> <tr> <td class="style30" colspan="3" style="text-align: center; font-family: 'Arial Black';"> <asp:RegularExpressionValidator ID="RegularExpressionValidator5" runat="server" ControlToValidate="txtAptNumber" Display="Dynamic" ErrorMessage="Apartment Number Can Only Contain Letters, Numbers And Dashes And Must Be Between 0 And 10 Characters." Font-Names="Arial" ForeColor="Red" ValidationExpression="^[\s0-9a-zA-Z\-]{0,10}$"></asp:RegularExpressionValidator> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> City:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:TextBox ID="txtCity" runat="server" Width="200px" MaxLength="50"></asp:TextBox> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="txtCity" Display="Dynamic" ErrorMessage="City Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server" ControlToValidate="txtCity" Display="Dynamic" ErrorMessage="City Can Only Contain Letters, Periods, Apostrophes And Dashes And Must Be Between 2 And 50 Characters." Font-Names="Arial" ForeColor="Red" ValidationExpression="^[a-zA-Z'\.\-]{2,50}$"></asp:RegularExpressionValidator> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> State:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:DropDownList ID="StateDropDown" runat="server" DataSourceID="StateDropDownDataSource" DataTextField="State" DataValueField="State" AppendDataBoundItems="True"> <asp:ListItem Selected="True" Value="-1">Select State...</asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td class="style30" colspan="3" style="text-align: center; font-family: 'Arial Black';"> <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="StateDropDown" Display="Dynamic" ErrorMessage="State Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True" InitialValue="-1"></asp:RequiredFieldValidator> </td> </tr> <tr> <td class="style30" style="width: 232px; text-align: right; font-family: 'Arial Black';"> Zip Code:</td> <td class="style27"> </td> <td style="text-align: left"> <asp:TextBox ID="txtZipCode" runat="server" Width="100px" MaxLength="10"></asp:TextBox> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="txtZipCode" Display="Dynamic" ErrorMessage="Zip Code Is Required." Font-Names="Arial" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="RegularExpressionValidator4" runat="server" ControlToValidate="txtZipCode" Display="Dynamic" ErrorMessage="Zip Code Can Only Contain Numbers And Dashes And Must Be Between 5 And 10 Characters." Font-Names="Arial" ForeColor="Red" ValidationExpression="^[0-9\-]{5,10}$"></asp:RegularExpressionValidator> </td> </tr> <tr> <td style="text-align: center; font-family: 'Arial Black';" class="style30" colspan="3"> <asp:Button ID="AddAddress" runat="server" Text="Add Address" Font-Names="Arial" /> <asp:Button ID="UpdateAddress" runat="server" Font-Names="Arial" Text="Update Address" /> <asp:Button ID="Cancel" runat="server" Text="Cancel" CausesValidation="False" /> </td> </tr> <tr> <td style="width: 232px; text-align: right; font-family: 'Arial Black';" class="style30"> </td> <td class="style27"> </td> <td style="text-align: left"> </td> </tr> </table> <br /> <br /> <br /> </asp:Panel> </asp:Content>Visual Basic Codebehind
Imports System.Web.Configuration Imports System.Data.SqlClient Imports System.Data Imports System.Collections Partial Class Customers_Addresses Inherits System.Web.UI.Page Protected Sub Cancel_Click(sender As Object, e As System.EventArgs) Handles Cancel.Click Response.Redirect("~/Customers/Account.aspx") End Sub Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load txtAddressName.Focus() End Sub Protected Sub AddAddress_Click(sender As Object, e As System.EventArgs) Handles AddAddress.Click If Not (Session("Account") > 0) Then ' Make sure the session is not timed out AddAddress.CausesValidation = False Response.Redirect("~/Customers/Login.aspx") ' Has the Address Name been used? ElseIf VerifyAddressName() Then lblError.Text = "This Address Name Is Already In Use." lblError.Visible = True Else ' Add the New Address UpdateAddressName() ' Redirect User To The Accounts Page Session("Status") = "Address" Response.Redirect("~/Customers/Account.aspx") End If End Sub Protected Function AddressChecked() As Boolean 'DETERMINE IF ANY ADDRESS HAS PREFERRED CHECKED ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("SELECT [CustomerID] FROM [ADDRESSES] WHERE (([CustomerID] = @CustomerID) AND ([Preferred] = @Preferred))", con) cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account")) cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = True Dim ds As DataSet Dim dt As DataTable Dim da As SqlDataAdapter Dim customerID As Integer Try ' Try to open the connection. con.Open() da = New SqlDataAdapter(cmd) ds = New DataSet() dt = New DataTable() da.Fill(ds) dt = ds.Tables(0) For Each dr As DataRow In dt.Rows If Not dr Is Nothing Then customerID = dr("CustomerID") End If Next Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try ' Is there another checked address? If customerID = Nothing Then ' There are no other checked addresses Return False Else ' There is another checked address Return True End If End Function Protected Sub UncheckAddress() ' UNCHECK THE CURRENTLY CHECKED ADDRESS ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("UPDATE [ADDRESSES] SET [Preferred] = @Preferred WHERE [CustomerID] = @CustomerID", con) cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = False cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account")) ' Insert the Record Try ' Try to open the connection. con.Open() cmd.ExecuteNonQuery() Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try End Sub Protected Sub UpdateAddressName() ' SEND VARIABLES TO THE SQL DATABASE TO CREATE A NEW ADDRESS ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("INSERT INTO [ADDRESSES](CustomerID, AddressName, AddressType, Preferred, Street, AptNumber, City, State, ZipCode) VALUES (@CustomerID, @AddressName, @AddressType, @Preferred, @Street, @AptNumber, @City, @State, @ZipCode)", con) cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account")) cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text cmd.Parameters.Add("@AddressType", Data.SqlDbType.NVarChar).Value = AddressTypeDropDown.SelectedItem.Value ' If Preferred is checked If Preferred.Checked = True Then ' Check to see if any other addresses are checked If AddressChecked() = True Then ' Uncheck old address UncheckAddress() End If ' Continue to update the data End If ' Declare a temporary storage variable for AptNumber Dim tmpAptNumber As String tmpAptNumber = txtAptNumber.Text cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = (If(Preferred.Checked, True, False)) cmd.Parameters.Add("@Street", Data.SqlDbType.NVarChar).Value = txtStreet.Text cmd.Parameters.Add("@AptNumber", Data.SqlDbType.NVarChar).Value = (If(tmpAptNumber = Nothing, "", tmpAptNumber)) cmd.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = txtCity.Text cmd.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = StateDropDown.SelectedItem.Value cmd.Parameters.Add("@ZipCode", Data.SqlDbType.NVarChar).Value = txtZipCode.Text ' Insert the Record Try ' Try to open the connection. con.Open() cmd.ExecuteNonQuery() Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try End Sub Protected Function VerifyAddressName() As Boolean 'DETERMINE IF ADDRESS NAME HAS BEEN USED BY THIS CUSTOMER ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("SELECT [AddressName] FROM [ADDRESSES] WHERE ([AddressName] = @AddressName)", con) cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text Dim ds As DataSet Dim dt As DataTable Dim da As SqlDataAdapter Dim strAddressName As String Try ' Try to open the connection. con.Open() da = New SqlDataAdapter(cmd) ds = New DataSet() dt = New DataTable() da.Fill(ds) dt = ds.Tables(0) For Each dr As DataRow In dt.Rows If Not dr Is Nothing Then strAddressName = dr("AddressName") End If Next Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try ' Has this Address Name been used? If strAddressName = Nothing Then ' The Name has not been used Return False Else ' The Name has been used Return True End If End Function Protected Sub UpdateAddress_Click(sender As Object, e As System.EventArgs) Handles UpdateAddress.Click If Not (Session("Account") > 0) Then UpdateAddress.CausesValidation = False Response.Redirect("~/Customers/Login.aspx") Else ' Update the database with the modified Address UpdateModifiedAddress() ' Redirect User To The Accounts Page Session("Status") = "AddressMod" Response.Redirect("~/Customers/Account.aspx") End If End Sub Protected Sub UpdateModifiedAddress() ' UPDATE THE DATABASE WITH THE NEWLY MODIFIED ADDRESS ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("UPDATE [ADDRESSES] SET [AddressName] = @AddressName, [AddressType] = @AddressType, [Preferred] = @Preferred, [Street] = @Street, [AptNumber] = @AptNumber, [City] = @City, [State] = @State, [ZipCode] = @ZipCode WHERE [CustomerID] = @CustomerID", con) cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account")) cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = txtAddressName.Text cmd.Parameters.Add("@AddressType", Data.SqlDbType.NVarChar).Value = AddressTypeDropDown.SelectedItem.Value cmd.Parameters.Add("@Preferred", Data.SqlDbType.Bit).Value = (If(Preferred.Checked, True, False)) cmd.Parameters.Add("@Street", Data.SqlDbType.NVarChar).Value = txtStreet.Text ' Declare a temporary storage variable for AptNumber Dim tmpAptNum As String tmpAptNum = txtAptNumber.Text If (tmpAptNum = Nothing) Or (tmpAptNum = "") Then tmpAptNum = "" Else tmpAptNum = txtAptNumber.Text End If cmd.Parameters.Add("@AptNumber", Data.SqlDbType.NVarChar).Value = tmpAptNum cmd.Parameters.Add("@City", Data.SqlDbType.NVarChar).Value = txtCity.Text cmd.Parameters.Add("@State", Data.SqlDbType.NVarChar).Value = StateDropDown.SelectedItem.Value cmd.Parameters.Add("@ZipCode", Data.SqlDbType.NVarChar).Value = txtZipCode.Text ' Insert the Record Try ' Try to open the connection. con.Open() cmd.ExecuteNonQuery() Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try End Sub Protected Sub AddressDropDown_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles AddressDropDown.SelectedIndexChanged ' Send the values of the selected Address Name to the appropriate fields PopulateFields() lblError.Visible = False End Sub Protected Sub PopulateFields() 'POPULATE THE FIELDS WITH THE CURRENT SELECTED ADDRESS NAME ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("SELECT [AddressName], [AddressType], [Preferred], [Street], [AptNumber], [City], [State], [ZipCode] FROM [ADDRESSES] WHERE (([CustomerID] = @CustomerID) AND ([AddressName] = @AddressName))", con) cmd.Parameters.Add("@CustomerID", Data.SqlDbType.Int).Value = CInt(Session("Account")) cmd.Parameters.Add("@AddressName", Data.SqlDbType.NVarChar).Value = AddressDropDown.SelectedItem.Value Dim ds As DataSet Dim dt As DataTable Dim da As SqlDataAdapter Try ' Try to open the connection. con.Open() da = New SqlDataAdapter(cmd) ds = New DataSet() dt = New DataTable() da.Fill(ds) dt = ds.Tables(0) For Each dr As DataRow In dt.Rows If Not dr Is Nothing Then txtAddressName.Text = dr("AddressName") AddressTypeDropDown.SelectedValue = dr("AddressType") Preferred.Checked = dr("Preferred") txtStreet.Text = dr("Street") txtAptNumber.Text = dr("AptNumber") txtCity.Text = dr("City") StateDropDown.SelectedValue = dr("State") txtZipCode.Text = dr("ZipCode") End If Next Catch err As Exception ' Handle an error by displaying the information. Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() End Try End Sub End Class78coolbreeze
Member
3 Points
27 Posts
Re: Update Database Records Not Working...
Nov 26, 2012 12:28 AM|LINK
I am getting the following exception when the database connection is opened and the query is executed.
Not sure why it is a violation. I have CustomerID and AddressName as primary keys in the ADDRESSES table, but I am trying to update an already existing record, not add a new one. I can't figure out why it would kick out the update.
78coolbreeze
Member
3 Points
27 Posts
Re: Update Database Records Not Working...
Nov 26, 2012 12:44 AM|LINK
Just found out that when Visual Studio sees an Update query containing a primary key it turns the Update into an Insert, which caused the record to not update.
I removed the primary key AddressName and it works now. But I am not sure how to allow the user to change the AddressName now since it's a primary key. =/
rajish
Member
424 Points
89 Posts
Re: Update Database Records Not Working...
Nov 27, 2012 08:25 AM|LINK
nice work
GaurangNaik
Contributor
2095 Points
499 Posts
Re: Update Database Records Not Working...
Nov 27, 2012 08:37 AM|LINK
Hi Again,
Add another column in your address table named Address_ID. Make sure this column is integer. Make this column as primary key instead of AddressName. Then you can change the AddressName column anytime.
Hope this helps.
GauranG
78coolbreeze
Member
3 Points
27 Posts
Re: Update Database Records Not Working...
Nov 27, 2012 02:39 PM|LINK
The reason I had AddressName as part of the primary key along with CustomerID is to prevent the customer from having multiple nick names for their address that were the same. I also use this approach for my credit card table.
I did however find a slight work around. If I first delete the record and then do an insert based on the vaules of the textboxes it will update the record. However, it only works on data that is not linked to a credit card. So now I am stuck once again because customers can't update addresses that have credit cards.
In order to try and finish the project on time, I am just removing the ability to update and delete addresses and cards. But if you want to take a look at my relational diagram I will include it below.