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

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?
LVL 3
taz8020Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SimonConnect With a Mentor Commented:
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
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.