Need Help with SQL Update Parameters

Question

User-1577525901 posted

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim x As String
        Dim y As String
Dim r As String
        Dim V1 As String
        Dim V2 As String

     


        Try
                Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\Traceability\Traceability.accdb")

                Using cmd As New OleDbCommand("SELECT Status FROM tblPalletRecords WHERE Palletnumber = @x ", conn)
                    cmd.Parameters.AddWithValue("@x", txbPalletNumber.Text)
                    conn.Open()
                    V1 = CStr(cmd.ExecuteScalar())
                    conn.Close()
                    MsgBox(V1)

                End Using

                If V1 = "In Stock" Then
                    Using cmd2 As New OleDbCommand("UPDATE tblPalletRecords SET OrderNumber = @z, ShipmentNumber = @r WHERE PalletNumber = @x", conn)
                        cmd2.CommandText = "UPDATE tblPalletRecords SET OrderNumber = @z, ShipmentNumber = @r WHERE PalletNumber = @x "
                        cmd2.Parameters.AddWithValue("@z", txbOrderNumber.Text)
                        cmd2.Parameters.AddWithValue("@r", txbShipmentNumber.Text)
                        cmd2.Parameters.AddWithValue("@x", txbPalletNumber.Text)
                        conn.Open()
                        cmd2.ExecuteNonQuery()
                        conn.Close()
                    End Using
                    Using cmd3 As New OleDbCommand("SELECT Status FROM tblPalletRecords WHERE Palletnumber = @x", conn)
                        cmd3.Parameters.AddWithValue("@x", txbPalletNumber.Text)
                        conn.Open()
                        V2 = CStr(cmd3.ExecuteScalar())
                        conn.Close()
                    End Using
                    Response.Write("<script type=""text/javascript"">alert(""The Status to " & x & " has Changed to " & V2 & """);</script")
                Else
                    Response.Write("<script type=""text/javascript"">alert(""The Pallet is not In Stock to Ship"");</script")
                End If
            End Using
            Catch ex As Exception
                'Error handling
            End Try

            txbSearch.Text = txbPalletNumber.Text
        GridView1.DataBind()
    End Sub
End Class

As it stands this code above works.  I am having issues with additional parameters.  See below, I added my txbCustomer to the Parameters, but it's not updating the database now.  The big difference between txbCustomer and the rest of the textboxes is that its letters not numebrs. Maybe this is my issue? How do i fix it?   For Instance: 

If V1 = "In Stock" Then
 Using cmd2 As New OleDbCommand("UPDATE tblPalletRecords SET OrderNumber = @z, ShipmentNumber = @r, Customer = @y WHERE PalletNumber = @x", conn)
 cmd2.CommandText = "UPDATE tblPalletRecords SET OrderNumber = @z, ShipmentNumber = @r, Customer = @y WHERE PalletNumber = @x "
 cmd2.Parameters.AddWithValue("@z", txbOrderNumber.Text)
 cmd2.Parameters.AddWithValue("@r", txbShipmentNumber.Text)
 cmd2.Parameters.AddWithValue("@x", txbPalletNumber.Text)
 cmd2.Parameters.AddWithValue("@y", txbCustomer.Text)
 conn.Open()
 cmd2.ExecuteNonQuery()
 conn.Close()
 End Using

Answer 1

User283571144 posted

Hi ChronoTrigger,

According to your codes, I couldn't directly find why the ADO.NET doesn't work.

Could you please tell me the Customer column's data type?

Does your code show any error message?

If you could post more details information, it will be more easily for us to find the reason.

Best Regards,

Brando

Answer 2

User-1134632663 posted
CREATE PROC [dbo].[Sp_GridCrud]
(
@EmpId int=0,@FirstName varchar(50)=Null,@LastName varchar(50)=Null,@PhoneNumber nvarchar(15)=Null,
@EmailAddress nvarchar(50)=Null,@Salary decimal=Null,@Event varchar(10)
)
AS
BEGIN
IF(@Event='Select')
BEGIN
SELECT * FROM Employee ORDER BY FirstName ASC;
END

ELSE IF(@Event='Add')
BEGIN
INSERT INTO Employee (FirstName,LastName,PhoneNumber,EmailAddress,Salary,CreatedDate) VALUES(@FirstName,@LastName,@PhoneNumber,@EmailAddress,@Salary,GETDATE());
END

ELSE IF(@Event='Update')
BEGIN
UPDATE Employee SET FirstName=@FirstName,LastName=@LastName,PhoneNumber=@PhoneNumber,EmailAddress=@EmailAddress,Salary=@Salary where Id=@EmpId;
END

ELSE
BEGIN
DELETE FROM Employee WHERE Id=@EmpId;
END
END

GO


For more details visit my below article

https://www.c-sharpcorner.com/article/crud-using-stored-procedure-in-asp-net-gridview-real-time/