Using Parameters

Using Parameters

Postby thomas holschen » Tue 26 Sep 2006 09:43

Hi there,

I've got a Problem Using Parameters in VB.Net with CRLab Dataprovider 2.50.21.0

Everytime I use the GUI to create a TableAdapter VS crashes.
Here is what I've done:

Step 1 -> Create a Table

CREATE TABLE ngp_artikel
(
idartikel serial NOT NULL,
artikelnummer varchar(15) NOT NULL,
artikelbezeichnung varchar(50) NOT NULL,
CONSTRAINT ngp_artikel_pkey PRIMARY KEY (idartikel)
)

Step 2 -> Establish a Connection via Serverexplorer

Step 3 -> Add a Dataset

Step 4 -> Add a TableAdapter

Image

Step 5 -> Use SQLStatements

Image

Step 6 -> Start Build SQLQuery

Image

Step 7 -> Add the Table to the QueryBuilder

Image

Step 8 -> Select the Desired Columns

Image

Step 9 -> Add a Parameter to the Query

Image

Step 10 -> End of Query Design Process

Image

Step 11 -> Add Desired Methods to the Table Adapter

Image

Step 12 -> Take a look at the Result:

Image
Image

What's going wrong here?

Thanks for Help! :D

Thomas

P.S. Column IDArtikel is Return as a DateTime Column... but it is defined as Int32.
thomas holschen
 
Posts: 8
Joined: Fri 08 Sep 2006 10:56

Postby thomas holschen » Tue 26 Sep 2006 12:58

Hi Alexey,

This is what is going wrong:

Image

I just want to use the provider to build the Table Adapter in the Dataset, crashing Visual Studio is not what i want when I press the Finish Button on the following Form.

Image


Thomas

EDIT: post is Useless cause previous post was deleted by admin.
Please Ignore this Post.
Last edited by thomas holschen on Tue 28 Nov 2006 16:18, edited 1 time in total.
thomas holschen
 
Posts: 8
Joined: Fri 08 Sep 2006 10:56

Postby Alexey » Tue 26 Sep 2006 13:57

Just checked this again... and reproduced the crash. Terrific.
We will investigate this problem. You will be informed about resolution as soon as possible.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

A quick fix for the parameters

Postby New_Dood » Fri 06 Oct 2006 05:05

In your select statement
Select idartikal etc.
from ngp etc.
where (artikelnummer = :param1)


change the where to the actual table name and define it in your fill statement in code

where (artikelnummer = :artikelnummer)

in the code in your form

Example:
Form1 code...
Private Sub SimpleButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton1.Click
Try
BindingNavigatorAddNewItem.PerformClick()
Me.WorkorderTableAdapter.FillBy(Me.PostgresDataSet.workorder, TextEdit1.Text)
Catch ex As Exception
MsgBox("Workorder # cannot be duplicated", MsgBoxStyle.Exclamation)
End Try
End Sub
Me.WorkorderTableAdapter.FillBy(Me.PostgresDataSet.workorder, TextEdit1.Text)


This is where you define the argument parameter not in the select.
The select statement must use a table name (postgres does not support named parametrs)

Hope this helps[/img]
New_Dood
 
Posts: 1
Joined: Fri 06 Oct 2006 04:52

Postby thomas holschen » Fri 06 Oct 2006 08:15

Hi Allen,

Thanks for your tip.

I've got a little Problem with it. I can define my Adapter but when I click "Finish" in the last Dialog this Message prevents me from using the Adapter.
It is not generated.

Image

What's going wrong?

Thanks for your help anyway.

Thomas
thomas holschen
 
Posts: 8
Joined: Fri 08 Sep 2006 10:56

Postby Alexey » Fri 06 Oct 2006 10:10

thomas holschen, please wait a bit. I hope, we will fix this problem for you.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby thomas holschen » Fri 20 Oct 2006 13:10

Here is a little Workaround.

I used a PG Function to get my select statement to work.
Just replace the "ngp_artikel" Characters with your Tablename if you want to test it or use the Create Statement of the ngp_artikel table in the first post.

Thomas

Code: Select all
CREATE OR REPLACE FUNCTION hela_select_ngp_artikel(pm_Limit int, pm_offset int, pm_Order varchar(255), pm_Where varchar(254)) RETURNS SETOF ngp_artikel AS $$
DECLARE

/*
   Sucht in der ngp_Artikel-Tabelle nach allen Datensätzen die der Optionalen Where-Bedingung entsprechen,
   sortiert diese ggf. nach der optionalen Orderbedingung und Limitiert die Ausgabe auf die in der
   Limit-Bedingung festgelegte Datensatzanzahl. Der Startdatensatz der Ausgabe kann mit der Offset-Bedingung
   nach hinten verlagert werden.
*/

rec RECORD; /* Puffer für Record*/
func_cmd varchar(4000); /* Puffer für Query-Statement */
ipm_Where varchar(255); /* Puffer für Where-Bedingung*/
ipm_Order varchar(255); /* Puffer für Order-Bedingung*/
ipm_Limit integer; /* Puffer für Limit-Bedingung*/
ipm_Offset integer; /* Puffer für Offset-Bedingung*/
ipm_TableName varchar(255); /* Puffer für TabellenNamen*/
BEGIN

/* Tabelllennamen setzen */
ipm_TableName:='ngp_artikel';

/* Wichtig da der Core-Lab Provider diese Funktion nur mit NULL initalisiert */
/* Prüfen ob Wherebedingung = NULL */
if pm_Where is null  then
   ipm_Where:='';
else
   ipm_Where:=pm_Where;
end if;
/* Prüfen ob Orderbedingung = NULL */
if pm_Order is null  then
   ipm_Order:='';
else
   ipm_Order:=pm_Order;
end if;
/* Prüfen ob Limitbedingung = NULL */
if pm_Limit is null  then
   ipm_Limit:=0;
else
   ipm_Limit:=pm_Limit;
end if;

/* Prüfen ob Offsetbedingung = NULL */
if pm_offset is null  then
   ipm_Offset:=0;
else
   ipm_Offset:=pm_offset;
end if;

/* Anhand Parameterwerte Query Zusammenbauen*/

if ipm_Where = '' then  /* Keine Wherebedingung*/
   if ipm_Order = '' then /* Keine Orderbedingung*/
      if ipm_Limit = 0 then /* Keine Limitbedingung*/
         func_cmd := 'SELECT * FROM ' || ipm_TableName ;
      else
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' limit ' || ipm_Limit || ' offset ' || ipm_Offset;
      end if;
   else
      if ipm_Limit = 0 then
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' order by ' || ipm_Order;
      else
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' order by ' || ipm_Order || ' limit ' || ipm_Limit || ' offset ' || ipm_Offset;
      end if;
   end if;
else
   if ipm_Order = '' then /* Keine Orderbedingung*/
      if ipm_Limit = 0 then /* Keine Limitbedingung*/
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' where ' || ipm_Where  ;
         
      else
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' where ' || ipm_Where || ' limit ' || ipm_Limit || ' offset ' || ipm_Offset;
      end if;
   else
      if ipm_Limit = 0 then
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' where ' || ipm_Where || ' order by ' || ipm_Order ;
      else
         func_cmd := 'SELECT * FROM ' || ipm_TableName || ' where ' || ipm_Where || ' order by ' || ipm_Order || ' limit ' || ipm_Limit || ' offset ' || ipm_Offset;
      end if;
   end if;
end if;
/* Ausführen der Query und Rückgabe der Wert*/
FOR rec IN execute func_cmd Loop
   RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;
thomas holschen
 
Posts: 8
Joined: Fri 08 Sep 2006 10:56

testing Provider 2.50.22.0

Postby thomas holschen » Tue 28 Nov 2006 16:16

HI Alexey,

I've tried the Version 2.50.22.0.

VS stoped crashing, but the Table Adapter is not generated successfully.
Result is still:

Image

Thomas
thomas holschen
 
Posts: 8
Joined: Fri 08 Sep 2006 10:56

Postby quietfulness » Wed 29 Nov 2006 05:04

Yeah.. When creating parameters in tableadapter wizard, VS wont crash but still it prompts that the parameters are not created successfully.

But after you generate it. Just go to the tableadapter query you generated with parameters, right click it and select PREVIEW DATA.

You can then test your parameters there by filling in the values, I tested it also during code on forms and it works.

You can't preview your parameterized queries in tableadapter wizard though. But at least we can use parameters now.

I hope that this bug will be fixed on later versions.. :D
quietfulness
 
Posts: 18
Joined: Sat 12 Aug 2006 06:37

Postby Alexey » Thu 30 Nov 2006 08:42

Thomas, try to set parameter name as "artikelnummer"
i.e.
Code: Select all
where (artikelnummer = :artikelnummer)
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL