I have an insert statement in my VB code that updates about 20 fields in sql. I pass through todays date and a set of values.
This occurs on a button click event. My problem is if the button is clicked more than once the data is duplicated in the sql table. Is there anyway for it to overwrite the current data if the date is the same?
Below is an example of the code
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
conn.Open()
Using selectCommand As SqlCommand = conn.CreateCommand
With selectCommand
.CommandType = CommandType.Text
.CommandText = "INSERT INTO dbo.MorningReport(--Fields--) " + "VALUES--@fields--)"
.Parameters.Add("@field1", SqlDbType.DateTime).Value = Date.Now
.Parameters.Add("@field2", SqlDbType.Int).Value = getavgNT()
This can be a little tricky, and it depends on the specifications of your system. Am I to understand that there will only ever be one record for any given date?
If so, there is first one thing you will need to consider. DateTime.Now returns the current date and time on the system. You will need to ensure that you are only using the date as the selector, and either ignoring the time, or zeroing it out.
That said, you might have to perform a SELECT statement first to see if a record exists. Once you know, you can perform a basic if/else to perform UPDATE if it exists or INSERT if it does not.
"Dream as if you'll live forever, live as if you'll die today." --James Dean
if you are updating part of the primary key you will need to delete and reinsert the record, if you are simply updating specific columns then you can do an update if the record already exists.
I would switch from in-line SQL to calling a Stored Procedure ...
s1lentb0b
Member
5 Points
48 Posts
Insert into SQL
Jan 04, 2013 03:58 PM|LINK
Hi
I have an insert statement in my VB code that updates about 20 fields in sql. I pass through todays date and a set of values.
This occurs on a button click event. My problem is if the button is clicked more than once the data is duplicated in the sql table. Is there anyway for it to overwrite the current data if the date is the same?
Below is an example of the code
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn.Open() Using selectCommand As SqlCommand = conn.CreateCommand With selectCommand .CommandType = CommandType.Text .CommandText = "INSERT INTO dbo.MorningReport(--Fields--) " + "VALUES--@fields--)" .Parameters.Add("@field1", SqlDbType.DateTime).Value = Date.Now .Parameters.Add("@field2", SqlDbType.Int).Value = getavgNT()AceCorban
Star
12318 Points
2269 Posts
Re: Insert into SQL
Jan 04, 2013 04:08 PM|LINK
This can be a little tricky, and it depends on the specifications of your system. Am I to understand that there will only ever be one record for any given date?
If so, there is first one thing you will need to consider. DateTime.Now returns the current date and time on the system. You will need to ensure that you are only using the date as the selector, and either ignoring the time, or zeroing it out.
That said, you might have to perform a SELECT statement first to see if a record exists. Once you know, you can perform a basic if/else to perform UPDATE if it exists or INSERT if it does not.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Insert into SQL
Jan 04, 2013 04:19 PM|LINK
Make your insert query resemble this:
insert into TheTable
(field1, field2, etc)
select value1, value2, etc
where not exists
(subquery to check for existing records)
AceCorban
Star
12318 Points
2269 Posts
Re: Insert into SQL
Jan 04, 2013 04:22 PM|LINK
That's actually a neat WHERE clause, I'll have to remember that one in the future. Though I believe he wants it to update if it does exist.
aabruzzese
Contributor
2806 Points
759 Posts
Re: Insert into SQL
Jan 04, 2013 05:47 PM|LINK
if you are updating part of the primary key you will need to delete and reinsert the record, if you are simply updating specific columns then you can do an update if the record already exists.
I would switch from in-line SQL to calling a Stored Procedure ...
Here is an example:
CREATE PROCEDURE dbo.Savecategories
(
@category_id int,
@category_name varchar(50),
@category_image varchar(100)
)
AS
--Date Developer Init Rev
declare @returnCode int
select @returnCode = 0
UPDATE [categories]
SET
[category_id] = @category_id,
[category_name] = @category_name,
[category_image] = @category_image
WHERE
[category_id] = @category_id
if (@@rowcount > 0)
BEGIN
GOTO OnExit
END
if (@@ERROR <> 0)
BEGIN
select @returnCode = @@ERROR
GOTO OnExit
END
INSERT INTO [categories]
(
[category_id],
[category_name],
[category_image]
)
VALUES
(
@category_id,
@category_name,
@category_image
)
OnExit:
RETURN @returnCode
GO
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Insert into SQL
Jan 05, 2013 04:01 AM|LINK
Hello，
What do you mean "duplicated records"？
oned_gk
All-Star
31475 Points
6428 Posts
Re: Insert into SQL
Jan 05, 2013 07:44 AM|LINK
Are you use a function for inserting?