none
Parameter comparison in OLEDB RRS feed

  • Question

  • Hey guys, i want to be able to do something like

    SELECT  *
    FROM    Table1 
    LEFT JOIN
    Table2 ON Table1.attribute3 = Table2.attribute2
    WHERE   (
    (Table1.attribute1=@parameter1 OR @parameter1=0)
    AND
    (Table1.attribute2=@parameter2 OR @parameter2='')
    AND
    (Table2.attribute2=@parameter3 OR @parameter3='')
    )
    

    using VB2010 so i can fill some search even when the parameter is not given.

    How can I achieve this?

    I just want to have the exact equivalent of this Access Query:

    SELECT *
    FROM Table1 INNER JOIN Table2 ON Table1.foreign_key=Table2.attribute1
    WHERE (((Table1.attribute1)=[parameter1?] OR [parameter1?] IS Null) AND ((Table2.attribute1)=[parameter2?] OR ([parameter2?] Is Null)));
    
    
    Thanks!

     

    Monday, May 9, 2011 2:39 PM

Answers

  • I'm assuming the syntax of your SQL statement is correct:

        Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Test Files\db1 XP.mdb")
        AccessConnection.Open()
        Dim AccessCommand As New OleDbCommand("SELECT *
    FROM Table1 INNER JOIN Table2 ON Table1.foreign_key=Table2.attribute1
    WHERE (((Table1.attribute1)=[?] OR [?] IS Null) AND ((Table2.attribute1)=[?] OR ([?] Is Null)))
    ", AccessConnection)
        AccessCommand.Parameters.AddWithValue("Param1", Param1Value)
        AccessCommand.Parameters.AddWithValue("Param2", Param1Value)
        AccessCommand.Parameters.AddWithValue("Param3", Param2Value)
        AccessCommand.Parameters.AddWithValue("Param4", Param2Value)
        Dim AccessDataReader As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
        While AccessDataReader.Read()
          Console.WriteLine(AccessDataReader.Item("Field1").ToString)
          Console.WriteLine(AccessDataReader.Item("Field2").ToString)
          Console.WriteLine(AccessDataReader.Item("Field3").ToString)
          '...
        End While
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by LECHIP Tuesday, May 10, 2011 12:18 PM
    Monday, May 9, 2011 3:51 PM

All replies

  • I'm assuming the syntax of your SQL statement is correct:

        Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Test Files\db1 XP.mdb")
        AccessConnection.Open()
        Dim AccessCommand As New OleDbCommand("SELECT *
    FROM Table1 INNER JOIN Table2 ON Table1.foreign_key=Table2.attribute1
    WHERE (((Table1.attribute1)=[?] OR [?] IS Null) AND ((Table2.attribute1)=[?] OR ([?] Is Null)))
    ", AccessConnection)
        AccessCommand.Parameters.AddWithValue("Param1", Param1Value)
        AccessCommand.Parameters.AddWithValue("Param2", Param1Value)
        AccessCommand.Parameters.AddWithValue("Param3", Param2Value)
        AccessCommand.Parameters.AddWithValue("Param4", Param2Value)
        Dim AccessDataReader As OleDbDataReader = AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
        While AccessDataReader.Read()
          Console.WriteLine(AccessDataReader.Item("Field1").ToString)
          Console.WriteLine(AccessDataReader.Item("Field2").ToString)
          Console.WriteLine(AccessDataReader.Item("Field3").ToString)
          '...
        End While
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by LECHIP Tuesday, May 10, 2011 12:18 PM
    Monday, May 9, 2011 3:51 PM
  • Hey thnx ofr the reply!

    I ended up using the parameters of the TypedDataset with a Query similar of what you suggested. Turns out that my query was right.

    Thanks a lot!

    Tuesday, May 10, 2011 12:18 PM