Using Parameters
-
- Posts: 8
- Joined: Fri 08 Sep 2006 10:56
Using Parameters
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.
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.
-
- Posts: 8
- Joined: Fri 08 Sep 2006 10:56
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.
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.
Last edited by thomas holschen on Tue 28 Nov 2006 16:18, edited 1 time in total.
A quick fix for the parameters
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]
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]
-
- Posts: 8
- Joined: Fri 08 Sep 2006 10:56
-
- Posts: 8
- Joined: Fri 08 Sep 2006 10:56
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
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;
-
- Posts: 8
- Joined: Fri 08 Sep 2006 10:56
testing Provider 2.50.22.0
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
I've tried the Version 2.50.22.0.
VS stoped crashing, but the Table Adapter is not generated successfully.
Result is still:
Thomas
-
- Posts: 18
- Joined: Sat 12 Aug 2006 06:37
- Contact:
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..
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..
Thomas, try to set parameter name as "artikelnummer"
i.e.
i.e.
Code: Select all
where (artikelnummer = :artikelnummer)