Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-23
3
Medium Priority
?
186 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 2000 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 49

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

926 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