Dynamic SQL Statement

TroRonTroRon
edited 2:07AM in FastReport .NET
Hi @all

In my Application i create SQL Statements dynamic.

With the help from AlexTZ i had createt the following Code to build the SQL Statement and transfer it to FastReport.NET:
Dim KritAnz, Krit, SQL, GblKrit, AblageTyp As String

On Error GoTo btnSpecialFilter

KritAnz = 5
Krit = ""

If ComboBox1.Text = "Gem?¤lde" Then AblageTyp = 1
If ComboBox1.Text = "Skulpturen" Then AblageTyp = 2
If ComboBox1.Text = "Stiche" Then AblageTyp = 3
If ComboBox1.Text = "Fotos" Then AblageTyp = 4
If ComboBox1.Text = "Dias" Then AblageTyp = 5
If ComboBox1.Text = "Zeichnungen" Then AblageTyp = 6

If Not ComboBox1.Text = vbNullString Then
Krit = Krit & " AND fld_ablagetyp = " & AblageTyp
End If

If Not txtRegistratur.Text = vbNullString Then
Krit = Krit & " AND fld_registratur LIKE '" & "*" & txtRegistratur.Text & "*'"
End If

If Not txtRegistratur.Text = vbNullString Then
Krit = Krit & " AND fld_stichworte LIKE '" & "*" & txtStichwort.Text & "*'"
End If

If Not txtRegistratur.Text = vbNullString Then
Krit = Krit & " AND fld_ersteller LIKE '" & "*" & txtErsteller.Text & "*'"
End If

If Not txtRegistratur.Text = vbNullString Then
Krit = Krit & " AND fld_jahr LIKE '" & "*" & txtJahr.Text & "*'"
End If

SQL = "SELECT * FROM db_hamapict "
If Krit <> "" Then
Krit = Mid(Krit, 5)
SQL = SQL & "WHERE " & Krit
If KritAnz Then
GblKrit = Krit
Else
GblKrit = ""
End If
End If

If InDebug = True Then
MessageBox.Show(SQL)
End If

FastReport.Utils.Res.LoadLocale("German.frl")
repPrintCatalogSpecialFilter.Load(variAppReports + "repPrintCatalogSpecialFilter.frx")
repPrintCatalogSpecialFilter.SetParameterValue("myConnString", variConnectionString)
Dim table As FastReport.Data.TableDataSource = DirectCast(repPrintCatalogSpecialFilter.GetDataSource("db_hamapict"), FastReport.Data.TableDataSource)
table.SelectCommand = SQL
repPrintCatalogSpecialFilter.Show()

Exit Sub

btnSpecialFilter:
MessageBox.Show("Der gew??nschte Report konnte nicht geladen werden. Bitte pr??fen Sie: " & variAppReports, "HamaPict.NET --> Reporting Problem", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

The Problem is know, that FastReport only execute the simple Statement like select * from db_hamapict

Example:

This should be executed:
select * from db_hamapict where fld_ablagetyp = 4 and fld_stichworte like 'elbe'

and should return 5 Records.

Fast Report returns 0 Records.

I'dont know why. When I test the SQL Statement direct in Access, all would be ok.

Ideas ?

Thank you.

Comments

  • AlexTZAlexTZ
    edited 2:07AM
    Hello,

    What if you set the SQL 
    select * from db_hamapict where fld_ablagetyp = 4 and fld_stichworte like 'elbe'
    directly in the report designer? Will this work?
  • TroRonTroRon
    edited 2:07AM
    Hi

    Sorry, no, that doesn't work.

    The same Problem, return Records 0.

    Strange !?
  • AlexTZAlexTZ
    edited 2:07AM
    So the problem is probably with SQL syntax. Don't you miss % symbol here? 
    fld_stichworte like 'elbe%'
  • TroRonTroRon
    edited 2:07AM
    Not realy, the Result is the same.

    When I test the SQL Statement directly in Access with % the Result is 0 Records.

    When I test this SQL Statement direct in Access:
    select * from db_hamapict where fld_ablagetyp = 4 and fld_stichworte like '*Elbe*'

    two Records are returned.

    I'm really confused now >
  • TroRonTroRon
    edited 2:07AM
    Hmm, for Me it looks like FastReports ignore the second Part of the SQL Statement.

    I've tried to Hardcode the Statements in my Application:
    select * from db_hamapict was correct executed

    select * from db_hamapict where fld_ablagetyp = 4 was correct exectuted

    select * from db_hamapict where fld_ablagetyp = 4 and fld_stichworte like '*Elbe*' could not be exectued, 0 Records
    select * from db_hamapict where fld_ablagetyp = 4 and fld_stichworte like 'Elbe%' could not be exectued, 0 Records
  • AlexTZAlexTZ
    edited 2:07AM
    FastReport.Net does not touch the SQL text in any way. The SQL is passed directly to OleDbDataAdapter. You can check this with simple code that uses standard OleDbConnection/OleDbDataAdapter:
      Dim table As New DataTable
  Dim connectionString As String = ""  ' <<< your connection string here
  Using conn As OleDbConnection = New OleDbConnection(connectionString)
    conn.Open
    Dim sql As String = ""   ' <<< your SQL statement here
    Using adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conn)
      adapter.Fill(table)
    End Using
  End Using
  MessageBox.Show(("Rows returned: " & table.Rows.Count.ToString))
  • TroRonTroRon
    edited 2:07AM
    Hi

    I'm sorry, you've got right.

    it's not select * from ....., it must be select distinct * from ..........

    Thank you so much.

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport .NETComment As ...

© Vanilla Keystone Theme 2024