Join 131,734 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 2,224 people online right now. Registration is fast and FREE... Join Now!
Before I add a record to my DB using a webform I need to check the inputed serial number on the form against the DB to see if it exists. For example:
The user types in SN12345 in the 'serial' text box and 5 in the 'qty' textbox on the webform and then clicks on submit. Before the record is created I need to check the serial number field in the DB for 'SN12345' - if it does not exist then create the record. If it does exists then only update the QTY field by appending the value of QTY on the form to the value stored in the DB.
serialnumber is a PK
I need help coding the 'if exists bit'
Here is my code for creating a new record:
CODE
MyCmd = New SqlCommand("insert into tblstock(category,items,location,OrderNo,description,serialnumber,Qty,datereceived) values& #40;@category,@items,@location,@OrderNo,@description,@serialnumber,@Qty,@dateRec eived)", MyConn)
I don't know if theres a quicker way(probably is) but I would create a datatable based on records returned from a select statement based on that query kinda like
CODE
select * from tableName where serialNumber = "SN12345"
and if the datatable has any records then you know that it already exists and you can proceed from there
Here is what I would use if I were doing this, except I would have this in a stored procedure
sql
IF EXISTS(SELECT serialnumber FROM tblstock WHERE serialnumber = @serialnumber) BEGIN UPDATE tblstock SET Qty = (Qty + @Qty) WHERE serialnumber = @serialnumber) END ELSE BEGIN INSERT INTO tblstock(category,items,location,OrderNo,description,serialnumber,Qty,datereceiv ed) VALUES (@category,@items,@location,@OrderNo,@description,@serialnumber,@Qty,@dateRec) END
Thanks for your responce. I have tried the code in VB but it complains about the 'exists, set, begin' commands not being declared or incorrect. I have never created a stored procedure, I imagine this would be created in sql and how would it be called from vb? Is there any other way around this?
cheers!
QUOTE(PsychoCoder @ 13 Oct, 2008 - 05:40 PM)
Here is what I would use if I were doing this, except I would have this in a stored procedure
sql
IF EXISTS(SELECT serialnumber FROM tblstock WHERE serialnumber = @serialnumber) BEGIN UPDATE tblstock SET Qty = (Qty + @Qty) WHERE serialnumber = @serialnumber) END ELSE BEGIN INSERT INTO tblstock(category,items,location,OrderNo,description,serialnumber,Qty,datereceiv ed) VALUES (@category,@items,@location,@OrderNo,@description,@serialnumber,@Qty,@dateRec) END
I am not quite sure how to convert\integrate your code it into my sub.
Anyway, here is my code.
CODE
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As SqlConnection Dim MyCmd As SqlCommand Dim MyCmd1 As SqlCommand
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
MyConn = New SqlConnection(connectionstring) MyConn.Open()
' test to see if serial number exists
IF EXISTS(SELECT serialnumber FROM tblstock WHERE serialnumber = @serialnumber) BEGIN() UPDATE() tblstock() SET Qty = (Qty + @Qty) WHERE() serialnumber = @serialnumber) End
'If serial does exist and qty has been updated the sub should end. Else BEGIN()
'Insert data into Tblstock MyCmd = New SqlCommand("insert into tblstock(category,items,location,OrderNo,description,serialnumber,Qty,datereceived) values& #40;@category,@items,@location,@OrderNo,@description,@serialnumber,@Qty,@dateRec eived)", MyConn) 'MyCmd = New SqlCommand("insert into Tblstock(category,items,location,po,description,serialnumber) values(@category,items,location,po,description,serialnumber)", MyConn) MyCmd.Parameters.AddWithValue("@category", SqlDbType.VarChar).Value = RCBCategory.Text MyCmd.Parameters.AddWithValue("@items", SqlDbType.VarChar).Value = RCBItem.Text MyCmd.Parameters.AddWithValue("@location", SqlDbType.VarChar).Value = RCBLocation.Text MyCmd.Parameters.AddWithValue("@OrderNo", SqlDbType.VarChar).Value = RCBPO.Text MyCmd.Parameters.AddWithValue("@description", SqlDbType.VarChar).Value = RTBDescription.Text MyCmd.Parameters.AddWithValue("@serialnumber", SqlDbType.VarChar).Value = RTBCode.Text MyCmd.Parameters.AddWithValue("@Qty", SqlDbType.VarChar).Value = RTBQty.Text MyCmd.Parameters.AddWithValue("@Datereceived", SqlDbType.DateTime).Value = RDPDateReceived.SelectedDate
MyCmd1 = New SqlCommand("Update tblOrders SET OrderStatus =@OrderStatus where ID='" & RTBID.Text & "'", MyConn)
Ok, forget my last post! - I have been playing around with stored procedures, and wow!
Here is a working trial.
CODE
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: Check for duplicate serial -- ============================================= Alter PROCEDURE dbo.TblStock_DuplicateSerial -- Add the parameters for the stored procedure here @serialnumber char(20) = null, @Qty int = null AS --BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here -- SELECT @serialnumber, @Qty
If EXISTS(SELECT serialnumber, qty FROM tblstock WHERE serialnumber = @serialnumber) BEGIN Update tblstock set qty=(qty+@qty) where serialnumber = @serialnumber END
ELSE
BEGIN
select * from tblstock -- test to see if routine works
END
I call the procedure from a query window using:
CODE
use bits; go execute dbo.tblstock_duplicateserial @serialnumber ='718908575093', @qty ='2';
Question, how do I call this procedure from within my VB web app?
Having got my test fully working I wanted to return to implementing the stored procedure 'Psycho coder' generously posted above. I have added to my stored procedure but get a 'must declare scalar variable @category" I tried adding @Category char(20) =null just under @Qty but to no avail. Here is my stored procedure code:
CODE
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author: -- Create date: -- Description: Check for duplicate serial -- ============================================= ALTER PROCEDURE [dbo].[TblStock_DuplicateSerial] -- Add the parameters for the stored procedure here @serialnumber char(20) = null, @Qty int = null --@Category char(20) = null
AS --BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here -- SELECT @serialnumber, @Qty
If EXISTS(SELECT serialnumber, qty FROM tblstock WHERE serialnumber = @serialnumber) BEGIN Update tblstock set qty=(qty+@qty) where serialnumber = @serialnumber END
ELSE
BEGIN
INSERT INTO tblstock(category,items,location,OrderNo,description,serialnumber,Qty,datereceived) VALUES & #40;@category,@items,@location,@OrderNo,@description,@serialnumber,@Qty,@dateRec ) END
Thanks in advance!
This post has been edited by macman007: 15 Oct, 2008 - 03:51 AM
13 Oct, 2008 - 03:44 PM
)
| 