Solved

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

Posted on 2015-01-23
3
145 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:
Simon earned 500 total points
ID: 40567009
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 46

Expert Comment

by:Martin Liss
ID: 40627754
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

862 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

21 Experts available now in Live!

Get 1:1 Help Now