Using a stored procedure to update information in textboxes

[andy2325]

I currently have a small database where I use stored procedures to update the information entered in textboxes below is the stored procedure:
*******************************************************
Create PROCEDURE dbo.U_Customer

(
@CustName char(10),
@Phone char(50),
@Billing char(50),
@Shipping char(50),
@Contact char(50),
@Fax char(50),
@State char(50),
@Zip char(50),
@CustID char(10)
)

AS
Update dtCustomer

SET

CustomerName = @CustName,
Phone = @Phone,
BillingAddress = @Billing,
ShippingAddress = @Shipping,
ContactName = @Contact,
Fax = @Fax,
State = @State,
Zip = @Zip

Where CustomerID = @CustID
RETURN
***********************************************
This works fine however I am trying to manually code it in an update click event. Can anybody tell me what I am doing wrong because it does not seem to update the database.
***********************************************
Try
Dim connStr As String = "Data Source= Drew\sqlexp;Initial Catalog=Status;Integrated Security=True"
Dim conn As New SqlConnection(connStr)
Dim cmdCommand As SqlCommand = New SqlCommand("U_Customer", conn)
cmdCommand.CommandType = CommandType.StoredProcedure
Dim strNum = txtID.Text
cmdCommand.Parameters.AddWithValue("@CustID", strNum)
cmdCommand.Parameters("@CustID").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@CustName", txtName.Text)
cmdCommand.Parameters("@CustName").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Phone", txtPhone.Text)
cmdCommand.Parameters("@Phone").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Billing", txtBilling.Text)
cmdCommand.Parameters("@Billing").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Shipping", txtShipping.Text)
cmdCommand.Parameters("@Shipping").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Contact", txtContact.Text)
cmdCommand.Parameters("@Contact").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Fax", txtFax.Text)
cmdCommand.Parameters("@Fax").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@State", txtState.Text)
cmdCommand.Parameters("@State").Direction = ParameterDirection.Input
cmdCommand.Parameters.AddWithValue("@Zip", txtZip.Text)
cmdCommand.Parameters("@Zip").Direction = ParameterDirection.Input

cmdCommand.Connection.Open()
cmdCommand.ExecuteNonQuery()
lblError.Text = strNum
cmdCommand.Connection.Close()

Catch ex As Exception
lblError.Text = "Your Record can not be updated at this time please try later" & ex.Message
End Try

 

[chrissie1]

It seems ok to me. Does it give any error?

Christiaan Baes
Belgium

My Blog

 

[andy2325]

no actually it does not throw any errors

 

[blounty]

try setting your parameters up as follows specifying datatypes.

Dim conn As New SqlConnection("Data Source= Drew\sqlexp;Initial Catalog=Status;Integrated Security=True")
            Dim cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = " U_Customer"
            cmd.Connection = conn
           Dim strNum = txtID.Text
            cmdCommand.Parameters.AddWithValue("@CustID", sqldbtype.char).value = strnum
            cmdCommand.Parameters.AddWithValue("@CustName", sqldbtype.char).value = txtname.text
            cmdCommand.Parameters.AddWithValue("@Phone", sqldbtype.char).value = txtPhone.Text
            cmdCommand.Parameters.AddWithValue("@Billing", sqldbtype.char).value = txtBilling.Text
            cmdCommand.Parameters.AddWithValue("@Shipping", sqldbtype.char).value = txtShipping.Text
            cmdCommand.Parameters.AddWithValue("@Contact", sqldbtype.char).value = txtContact.Text
            cmdCommand.Parameters.AddWithValue("@Fax", sqldbtype.char).value = txtFax.Text
            cmdCommand.Parameters.AddWithValue("@State", sqldbtype.char).value = txtState.Text
            cmdCommand.Parameters.AddWithValue("@Zip", sqldbtype.char).value = txtZip.Text
            cmdCommand.Connection.Open()
            cmdCommand.ExecuteNonQuery()
            lblError.Text = strNum
            cmdCommand.Connection.Close()

Alex

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
Rick Cook, The Wizardry Compiled