Dynamic SQL Statement

TroRon

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.

AlexTZ

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?

TroRon

Hi

Sorry, no, that doesn't work.

The same Problem, return Records 0.

Strange !?

AlexTZ

So the problem is probably with SQL syntax. Don't you miss % symbol here?

fld_stichworte like 'elbe%'

TroRon

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

TroRon

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

AlexTZ

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))

TroRon

Hi

I'm sorry, you've got right.

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

Thank you so much.