Asked by:
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 UsingWednesday, 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