how to write a proper query for (Insert values to columns where values not exist)?

Question

0.00/5 (No votes)

See more:

Dear Pros out there,

Question:
how to query an insert statement with where values to be insert do not exist in table columns?

error faced
incorrect syntax near WHERE.

i know my query was wrong but who can correct me please~!!!

VB

Dim sdwEnrollNumber As String = ""

Dim insert1 As String = "INSERT INTO emptbl (userid, namex, fingerindex, tmpData, priviledge, password) VALUES (@userid, @namex, @fingerindex, @tmpData, @priviledge, @password) WHERE userid <> @userid"
                    cmd.Parameters.AddWithValue("@userid", sdwEnrollNumber)
                    
                    If connection.State = ConnectionState.Closed Then
                        connection.Open()
                    End If
                    cmd.ExecuteNonQuery()
                    connection.Close()
                    loadout()

Posted 21-Nov-13 20:56pm

donaldliaw87

Updated 21-Nov-13 21:01pm

v2

Add a Solution

2 solutions

Solution 1

There is no WHERE on INSERT statements, unless you are inserting values from a result of a SELECT. If you want to check if that ID is already used, you need to do a SELECT statement first. In my opinion, unless of course if it's one of your requirements, for primary key values, you should let the database handle the uniqueness of the ID instead of checking if it is already used every time you are inserting a value.

Posted 21-Nov-13 21:05pm

walterhevedeich

Comments

donaldliaw87 22-Nov-13 3:12am

thx for your opinion, ok insert don't have where clause, i got it.

> the id i already set to uniqe key
> i think i have to do a select statement to do compare, because i download and insert the id from certain machines

walterhevedeich 22-Nov-13 3:22am

I personally prefer letting the database generate the ID. That way, you don't need to be inserting an additional "userid" column and worrying if it's unique or not. If you need to return the id generated, then you need to use a stored procedure for that.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

donaldliaw87 · Accepted Answer · 2013-11-21T21:34:00

simply code simply get what i want, i know this was very bad logic but at least it works

VB

axCZKEM1.SSR_GetAllUserInfo(iMachineNumber, sdwEnrollNumber, sName, sPassword, iPrivilege, bEnabled) = True

Dim sdwEnrollNumber As String = ""  
Dim sele As String = "SELECT * FROM emptbl WHERE useid = '" & sdwEnrollNumber & "'"
                    sele = True
                    If sele = False Then
                        Dim insert1 As String = "INSERT INTO emptbl (userid, namex, fingerindex, tmpData, priviledge, password) VALUES (@userid, @namex, @fingerindex, @tmpData, @priviledge, @password)"
                        Dim cmd As New SqlCommand(insert1, connection)
                        cmd.Parameters.AddWithValue("@userid", sdwEnrollNumber)
                        cmd.Parameters.AddWithValue("@namex", sName)
                        cmd.Parameters.AddWithValue("@fingerindex", idwFingerIndex)
                        cmd.Parameters.AddWithValue("@tmpData", sTmpData)
                        cmd.Parameters.AddWithValue("@priviledge", iPrivilege)
                        cmd.Parameters.AddWithValue("@password", sPassword)
                        If connection.State = ConnectionState.Closed Then
                            connection.Open()
                        End If
                        cmd.ExecuteNonQuery()
                        connection.Close()
                        loadout()

                    End If