Need Help with SQL Update Parameters RSS

• • Reply
  ChronoTrigge...

  None

  0 Points

  1 Post

  Need Help with SQL Update Parameters

  Mar 28, 2018 06:31 PM|ChronoTrigger|LINK

  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
  
  

• • Reply
  Brando ZWZ

  Star

  8311 Points

  2502 Posts

  Re: Need Help with SQL Update Parameters

  Mar 29, 2018 05:38 AM|Brando ZWZ|LINK

  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

• • Reply
  Satyaprakash...

  Member

  383 Points

  31 Posts

  Re: Need Help with SQL Update Parameters

  Apr 05, 2018 12:02 PM|Satyaprakash Samantaray|LINK

  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/