fedeteka fedeteka - 1 month ago 6
Vb.net Question

Where clause not working on a SQLite Join request

I need all the unique values of ParteB field on ActPartB table matching the same IDNumber with userActTable but only for the condition of some userIDNumber (on userActTable)

The full sql string with the "WHERE"

sql = "SELECT DISTINCT ParteB FROM ActParteB INNER JOIN userAct ON IDNumber = IDNumber WHERE userAct.userIDNumber = ? order by ParteB asc"


Then I add

cmdConnection.Parameters.AddWithValue("@userIDNumber", NumberOfActiveuser)


The program run with no debugging error but it shows all the unique fields of ActPartB no matter the value of userIDNumber. The value os userIDNumber is fine because.

Is just that the WHERE is "ignored"

This

sql = "SELECT DISTINCT ParteB FROM ActParteB INNER JOIN userAct ON IDNumber = IDNumber WHERE userAct.userIDNumber = ? order by ParteB asc"


Produces the same ouput of this

sql = "SELECT DISTINCT ParteB FROM ActParteB INNER JOIN userAct ON IDNumber = IDNumber order by ParteB asc"


What is wrong with the Where clause?

Full code here:

Using conn As New SQLiteConnection(SQLiteConnStr)

Try
conn.Open()

Dim cmdConnection As SQLiteCommand = New SQLiteCommand(sql, conn)

Dim sql = "SELECT DISTINCT ParteB FROM ActParteB INNER JOIN userAct ON IDNumber = IDNumber WHERE userAct.userIDNumber = ? order by ParteB asc"

cmdConnection.Parameters.AddWithValue("@userIDNumber", NumberOfActiveUser)

Dim readerParteB As SQLiteDataReader = cmdConnection.ExecuteReader()

ParteBComboBox.Items.Clear()

Try
While (readerParteB.Read())
ParteBComboBox.Items.Add(readerParteB("ParteB"))
End While
Catch ex As Exception
MsgBox(ex.ToString())
End Try

Catch ex As Exception
MsgBox(ex.ToString())
End Try

End Using

CL. CL.
Answer
Dim cmdConnection As SQLiteCommand = New SQLiteCommand(sql, conn)

Dim sql = "..."

Your command does not work correctly because it uses a different SQL string (the value of some other sql variable that you declared in some previous part of the code).

Furthermore, as mentioned by Jinx88909, you must add the table names to the join to specify which IDNumber column you mean (when in doubt, the database will of course choose the wrong one):

... JOIN userAct ON userAct.IDNumber = ActParteB.IDNumber ...
                    ^^^^^^^^           ^^^^^^^^^^

Alternatively, when the column names are the same, better use USING, which automatically gets the column from both tables:

... JOIN userAct USING (IDNumber) ...