locked
Need Help with SQL Update Parameters RRS feed

  • 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

    Wednesday, March 28, 2018 6:31 PM

All replies

  • 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

    Thursday, March 29, 2018 5:38 AM
  • 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/
    Thursday, April 5, 2018 12:02 PM