I have used stored procedures to insert and select but for some reason I can
not get this code to update records. Please help I must have made a dumb
mistake
STORED PROCEDURE
CREATE Procedure OrdersAddDetail
( @fname nvarchar(50),
@lnametxt nvarchar(50),
@add1 nvarchar(50),
@add2 nvarchar(50),
@city nvarchar(50),
@state nvarchar(50),
@zip nvarchar(50),
@phone nvarchar(50),
@bfname nvarchar(50),
@blname nvarchar(50),
@badd1 nvarchar(50),
@badd2 nvarchar(50),
@bcity nvarchar(50),
@bstate nvarchar(50),
@bzip nvarchar(50),
@bphone nvarchar(50),
@CustomerID int
)
AS
UPDATE Customers
SET
fname = @fname,
lname = @lnametxt,
add1 = @add1,
add2 = @add2,
city = @city,
state = @state,
zip = @zip,
phone = @phone,
bfname = @bfname,
blname = @blname,
badd1 = @badd1,
badd2 = @badd2,
bcity = @bcity,
bstate = @bstate,
bzip = @bzip,
bphone = @bphone
WHERE
CustomerID = @CustomerID
GO
SUB
Public Sub UpdateAdd(ByVal customerID As Integer, ByVal fnametxt As String,
ByVal lname As String, ByVal add1 As String, ByVal add2 As String, ByVal city
As String, ByVal state As String, ByVal zip As String, ByVal phone As String,
ByVal bfname As String, ByVal blname As String, ByVal badd1 As String, ByVal
badd2 As String, ByVal bcity As String, ByVal bstate As String, ByVal bzip As
String, ByVal bphone As String)
' Create Instance of Connection and Command Object
Dim myConnection2 As SqlConnection = New
SqlConnection(ConfigurationManager.AppSettings("Co nnectionString"))
Dim myCommand2 As SqlCommand = New SqlCommand("OrdersAddDetail",
myConnection2)
' Mark the Command as a SPROC
myCommand2.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterCustomerID As SqlParameter = New
SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Value = CInt(customerID)
myCommand2.Parameters.Add(parameterCustomerID)
'Dim parameterOrderID As SqlParameter = New
SqlParameter("@OrderID", SqlDbType.Int, 4)
'parameterOrderID.Direction = ParameterDirection.Output
'myCommand2.Parameters.Add(parameterOrderID)
Dim parameterfname As SqlParameter = New SqlParameter("@fname",
SqlDbType.NVarChar, 50)
parameterfname.Value = fnametxt
myCommand2.Parameters.Add(parameterfname)
Dim parameterlname As SqlParameter = New
SqlParameter("@lnametxt", SqlDbType.NVarChar, 50)
parameterlname.Value = lname
myCommand2.Parameters.Add(parameterlname)
Dim parameteradd1 As SqlParameter = New SqlParameter("@add1",
SqlDbType.NVarChar, 50)
parameteradd1.Value = add1
myCommand2.Parameters.Add(parameteradd1)
Dim parameteradd2 As SqlParameter = New SqlParameter("@add2",
SqlDbType.NVarChar, 50)
parameteradd2.Value = add2
myCommand2.Parameters.Add(parameteradd2)
Dim parametercity As SqlParameter = New SqlParameter("@city",
SqlDbType.NVarChar, 50)
parametercity.Value = city
myCommand2.Parameters.Add(parametercity)
Dim parameterstate As SqlParameter = New SqlParameter("@state",
SqlDbType.Char, 10)
parameterstate.Value = state
myCommand2.Parameters.Add(parameterstate)
Dim parameterzip As SqlParameter = New SqlParameter("@zip",
SqlDbType.NChar, 18)
parameterzip.Value = zip
myCommand2.Parameters.Add(parameterzip)
Dim parameterphone As SqlParameter = New SqlParameter("@phone",
SqlDbType.NChar, 18)
parameterphone.Value = phone
myCommand2.Parameters.Add(parameterphone)
Dim parameterbfname As SqlParameter = New
SqlParameter("@bfname", SqlDbType.NVarChar, 50)
parameterbfname.Value = bfname
myCommand2.Parameters.Add(parameterbfname)
Dim parameterblname As SqlParameter = New
SqlParameter("@blname", SqlDbType.NVarChar, 50)
parameterblname.Value = blname
myCommand2.Parameters.Add(parameterblname)
Dim parameterbadd1 As SqlParameter = New SqlParameter("@badd1",
SqlDbType.NVarChar, 50)
parameterbadd1.Value = badd1
myCommand2.Parameters.Add(parameterbadd1)
Dim parameterbadd2 As SqlParameter = New SqlParameter("@badd2",
SqlDbType.NVarChar, 50)
parameterbadd2.Value = badd2
myCommand2.Parameters.Add(parameterbadd2)
Dim parameterbcity As SqlParameter = New SqlParameter("@bcity",
SqlDbType.NVarChar, 50)
parameterbcity.Value = bcity
myCommand2.Parameters.Add(parameterbcity)
Dim parameterbstate As SqlParameter = New
SqlParameter("@bstate", SqlDbType.Char, 10)
parameterbstate.Value = bstate
myCommand2.Parameters.Add(parameterbstate)
'
Dim parameterbzip As SqlParameter = New SqlParameter("@bzip",
SqlDbType.NChar, 18)
parameterbzip.Value = bzip
myCommand2.Parameters.Add(parameterbzip)
Dim parameterbphone As SqlParameter = New
SqlParameter("@bphone", SqlDbType.NVarChar, 50)
parameterbphone.Value = bphone
myCommand2.Parameters.Add(parameterbphone)
' Open the connection and execute the Command
myConnection2.Open()
myCommand2.ExecuteNonQuery()
myConnection2.Close()
CODE IN ASPX PG
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs)
Dim cart As Commerce.ShoppingCartDB = New Commerce.ShoppingCartDB()
' Calculate end-user's shopping cart ID
Dim cartId As String = cart.GetShoppingCartId()
' Calculate end-user's customerID
' Dim CustomerID As Integer = CInt(Request.Params("CustomerID"))
'Dim customerId As String = User.Identity.Name
' Update the address detail into customers record
' Dim orderadd As Commerce.OrdersDB = New Commerce.OrdersDB()
' Dim CustomerId As Integer = orderadd.PlaceOrderAdd(CustomerId,
fname.Text, lname.Text, add1.Text, add2.Text, city.Text, state.Text,
zip.Text, phone.Text, bfname.Text, blname.Text, badd1.Text, badd2.Text,
bcity.Text, bstate.Text, bzip.Text, bphone.Text)
UpdateCustomerDatabase()
Dim CustomerID As Integer = CInt(Request.Params("CustomerID"))
'Update labels to reflect the fact that the order has taken place
Header.Text = "Check Out Complete!"
'Message.Text = "<b>Your Order Number Is: </b>" & orderId
End Sub
Sub UpdateCustomerDatabase()
Dim Customer As Commerce.OrdersDB = New Commerce.OrdersDB()
' Obtain current user's Customer ID
Dim CustomerID As String = CInt(Request.Params("CustomerID"))
' Obtain references to row's controls
Dim fnametxt As TextBox = CType(FindControl("fname"), TextBox)
Dim lname As TextBox = CType(FindControl("lname"), TextBox)
Dim add1 As TextBox = CType(FindControl("add1"), TextBox)
Dim add2 As TextBox = CType(FindControl("add2"), TextBox)
Dim city As TextBox = CType(FindControl("city"), TextBox)
Dim state As TextBox = CType(FindControl("state"), TextBox)
Dim zip As TextBox = CType(FindControl("zip"), TextBox)
Dim phone As TextBox = CType(FindControl("phone"), TextBox)
Dim bfname As TextBox = CType(FindControl("bfname"), TextBox)
Dim blname As TextBox = CType(FindControl("blname"), TextBox)
Dim badd1 As TextBox = CType(FindControl("badd1"), TextBox)
Dim badd2 As TextBox = CType(FindControl("badd2"), TextBox)
Dim bcity As TextBox = CType(FindControl("bcity"), TextBox)
Dim bstate As TextBox = CType(FindControl("bstate"), TextBox)
Dim bzip As TextBox = CType(FindControl("bzip"), TextBox)
Dim bphone As TextBox = CType(FindControl("bphone"), TextBox)
Dim fname = fnametxt.Text
Customer.UpdateAdd(CInt(CustomerID), fname, lname.Text, add1.Text,
add2.Text, city.Text, state.Text, zip.Text, phone.Text, bfname.Text,
blname.Text, badd1.Text, badd2.Text, bcity.Text, bstate.Text, bzip.Text,
bphone.Text)
Response.Redirect("CheckOutpayment.aspx?CustomerID =" & CustomerID)
End Sub
--
t
