SqlDataSource & parameters is crashing

[JonC]

Hi,

I'm adding a SqlDataSource to an ASP.NET application using CoreLab.PostgreSql as the provider. This works absolutely fine as long as I don't use parameters in the select query. When I add a parameter and attempt to test the query, it comes back saying it's missing parameters, while if I just attempt to "Finish" and close the window, VS 2005 will crash and ask if I wish to restart.

I'm using VS2005 (C# language) and version CoreLab.PostgreSql 2.5.21.0 (Standard).

Any suggestions?

Cheers,

Jon.

[Alexey]

Please specify your query.

[JonC]

It's:-

select * from tb_person where addressline7 like :pc

Cheers,

Jon.

[JonC]

Just tried changing that to:-

select * from tb_person where addressline7 like @pc

and that seems to not crash...

The :parameter format works with other .NET providers, so is this just a pg.net thing?

Cheers,

Jon.

[JonC]

The @parameter style gets interpreted as an actual column name in the query; so in fact, this doesn't work.

So my work around at the moment is not to set the SelectCommand at design time, but generate it at run-time and then add parameters as normal.

Is there something more elegant?

Ta,

Jon.

[Alexey]

I cannot reproduce any error. Please provide me with detailed scenario on how to reproduce the problem.

[JonC]

Hi Alexey,

Sorry for the delay.

The steps I'm doing are:-

1) Create New ASP.NET website (C#)
2) Switch to design view
3) Drag a SqlDataSource to the page
4) Click the smart-glyph and 'configure data source'
5) Select New Connection
6) Change the data source to PostgreSQL Database (PostgreSQLDirect . NET)
7) Fill in suitable username/password/database
Click test connection to check above
9) Move onto "Configure the Select Statement"
10) Select "Specify a custom SQL statement or stored procedure"
11) Click Next
12) Enter a select query with a parameter; "select * from some_table where somecol like :someval" for instance.
13) Click next
14) Do test query; this fails saying the 'someval' parameter is missing.
15) VS 2005 will sometimes crash here, but always seems to crash when 'Finish' is clicked...

Steps 14 & 15 don't cause a crash when another ADO.NET provider is selected (such as Access/SQL Server).

Hope that helps,

Jon.

[Alexey]

We have reproduced the problem with parameter (without any crash actually). Now we are investigating it. You will be notified on results as soon as possible.

[Alexey]

We are not able to fix this issue right now, because this would lead to implementing our own SqlDataSource component. This possibility is being considering. For now standard SqlDataSource component doesn't support our provider (System.Web.UI.Design.WebControls.SqlDataSourceDesigner.DeriveParameters).
As a work-around you may use Selecting event of that component, e.g.:

Code: Select all

  protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {

    MySqlCommandBuilder.DeriveParameters((MySqlCommand)e.Command);
    e.Command.Parameters[0].Value = "%";
  }