Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jan 05, 2013 07:44 AM by oned_gk
Jan 04, 2013 03:58 PM|LINK
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)
Using selectCommand As SqlCommand = conn.CreateCommand
.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()
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.
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)
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.
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
--Date Developer Init Rev
declare @returnCode int
select @returnCode = 0
[category_id] = @category_id,
[category_name] = @category_name,
[category_image] = @category_image
[category_id] = @category_id
if (@@rowcount > 0)
if (@@ERROR <> 0)
select @returnCode = @@ERROR
INSERT INTO [categories]
Jan 05, 2013 04:01 AM|LINK
My problem is if the button is clicked more than once the data is duplicated in the sql table.
What do you mean "duplicated records"？
Jan 05, 2013 07:44 AM|LINK
Are you use a function for inserting?