Solved

Best way to skip duplicate records when updateng table in vb.net and sql

Posted on 2015-01-23
3
138 Views
Last Modified: 2015-02-24
Hi I have a table made up like
machineID
UserNumber
VMode
IMode
CDateTime
WCode
ValidEntry

there are 3 fields that make the unique key which are
machineID
UserNumber
CDateTime

most times there are about 3000-5000 logs to download and I put these into a table. I used to fill the table first before trying the insert in a try catch block so that is would not add duplicates but takes too long. So then thought lets just get the data and fill a table then update the database. It downloads all the logs in seconds but having trouble when trying to use the tableadapter to save it.

this is what I was using
INSERT INTO CLog
                         (machineID, UserNumber, VMode , IMode , CDateTime, WorkCode, ValidEntry)
SELECT        @machineID AS Expr1, @UserNumber AS Expr2, @VMode AS Expr3, @IMode AS Expr4, @CDateTime AS Expr5, @WCode AS Expr6, @ValidEntry AS Expr8
WHERE        (NOT EXISTS
                             (SELECT        machineID, UserNumber, CDateTime
                               FROM            ClockInLog AS ClockInLog_1
                               WHERE        (machineID= @machineID) AND (UserNumber= @UserNumber) AND (CDateTime= @CDateTime)))

Open in new window



when the table adapter tries to do the update I get the error
The parameterized query '(@machineIDvarchar(1024),@UserNumber varchar(1024),@VMode' expects the parameter '@machineID, which was not supplied.

I don't get why I am getting this error if I try it in the dataset query editor it works fine.

And does vb.net build a big query and send it in one go or open the connection and loop through the insert statements?
0
Comment
Question by:taz8020
3 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
Comment Utility
An INSERT statement does it all in one go (set based).

I'm not sure why it doesn't work for you. It might be that you need to wrap all varchar values with single quotes
e.g.
SELECT        CHAR(39)+@machineID +CHAR(39), CHAR(39)+@UserNumber+CHAR(39), @VMode, @IMode, @CDateTime, @WCode, @ValidEntry

I'm not sure what datatypes all your other columns are, so I've only added CHAR(39) =single quote around the first couple.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now