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
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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
None
0 Points
1 Post
Need Help with SQL Update Parameters
Mar 28, 2018 06:31 PM|ChronoTrigger|LINK
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:
Star
9831 Points
3120 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
Member
433 Points
31 Posts
Re: Need Help with SQL Update Parameters
Apr 05, 2018 12:02 PM|Satyaprakash Samantaray|LINK
(
@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/