Using Parameters

thomas holschen · Post by **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

Step 5 -> Use SQLStatements

Step 6 -> Start Build SQLQuery

Step 7 -> Add the Table to the QueryBuilder

Step 8 -> Select the Desired Columns

Step 9 -> Add a Parameter to the Query

Step 10 -> End of Query Design Process

Step 11 -> Add Desired Methods to the Table Adapter

Step 12 -> Take a look at the Result:

What's going wrong here?

Thanks for Help!

Thomas

P.S. Column IDArtikel is Return as a DateTime Column... but it is defined as Int32.

thomas holschen · Post by **thomas holschen** » Tue 26 Sep 2006 12:58

Hi Alexey,

This is what is going wrong:

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.

Thomas

EDIT: post is Useless cause previous post was deleted by admin.
Please Ignore this Post.

Alexey · Post by **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.

New_Dood · Post by **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]

thomas holschen · Post by **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.

What's going wrong?

Thanks for your help anyway.

Thomas

Alexey · Post by **Alexey** » Fri 06 Oct 2006 10:10

thomas holschen, please wait a bit. I hope, we will fix this problem for you.

thomas holschen · Post by **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 · Post by **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:

Thomas

quietfulness · Post by **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..

Alexey · Post by **Alexey** » Thu 30 Nov 2006 08:42

Thomas, try to set parameter name as "artikelnummer"
i.e.

Code: Select all

where (artikelnummer = :artikelnummer)

Devart Forums

Using Parameters

Using Parameters

A quick fix for the parameters

testing Provider 2.50.22.0