Problems with Parameters and SQLDataSource

[Guest]

Hi, I'm having problems to get the value of a Querystring into the
SelectCommand of a SQLDataSource, here's my code:

----------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%'
+ @nombre + '%'">

<SelectParameters>
<asp:QueryStringParameter Name="nombre"
QueryStringField="nombre" type="string" DefaultValue=""/>
</SelectParameters>

</asp:SqlDataSource>

-----------------------------------------------
Here is the URL that call that code....

http://localhost:8103/MySQL_Test/default2.aspx?nombre=daniel

------------------------------------------------

The SelectCommand is not working right, is like the @nombre weren't
returning any value even if I set a default value

I have tried this commands with no result...
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' + @nombre + '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' & @nombre & '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre=@nombre

If I set the querystring value directly into the SelectCommand the query
return the expected result:
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%daniel%'

What's wrong with my code?

 

[neilmcguigan]

in the aspx (this example is for the northwind sample database):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
SelectCommand="SELECT * FROM [Customers] WHERE
([ContactName] LIKE @ContactName)"
OnSelecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:QueryStringParameter Name="ContactName"
QueryStringField="Name" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

in the codebehind (this is C#):

protected void SqlDataSource1_Selecting(object sender,
SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@ContactName"].Value = "%" +
e.Command.Parameters["@ContactName"].Value + "%";
}

what you want to do is handle the selecting event, and wrap the
parameter value in the % wildcards

also, don't use dynamic SQL

 

[Guest]

My DB in MySQL, is that a reason for my problem?

--
DRH

in the aspx (this example is for the northwind sample database):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
SelectCommand="SELECT * FROM [Customers] WHERE
([ContactName] LIKE @ContactName)"
OnSelecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:QueryStringParameter Name="ContactName"
QueryStringField="Name" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

in the codebehind (this is C#):

protected void SqlDataSource1_Selecting(object sender,
SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@ContactName"].Value = "%" +
e.Command.Parameters["@ContactName"].Value + "%";
}

what you want to do is handle the selecting event, and wrap the
parameter value in the % wildcards

also, don't use dynamic SQL

Hi, I'm having problems to get the value of a Querystring into the
SelectCommand of a SQLDataSource, here's my code:

----------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%'
+ @nombre + '%'">

<SelectParameters>
<asp:QueryStringParameter Name="nombre"
QueryStringField="nombre" type="string" DefaultValue=""/>
</SelectParameters>

</asp:SqlDataSource>

-----------------------------------------------
Here is the URL that call that code....

http://localhost:8103/MySQL_Test/default2.aspx?nombre=daniel

------------------------------------------------

The SelectCommand is not working right, is like the @nombre weren't
returning any value even if I set a default value

I have tried this commands with no result...
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' + @nombre + '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' & @nombre & '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre=@nombre

If I set the querystring value directly into the SelectCommand the query
return the expected result:
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%daniel%'

What's wrong with my code?

 

[neilmcguigan]

yes it is

1. sqldatasource is for ms sql server

2. parameter declarations different in mysql

My DB in MySQL, is that a reason for my problem?

--
DRH

in the aspx (this example is for the northwind sample database):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
SelectCommand="SELECT * FROM [Customers] WHERE
([ContactName] LIKE @ContactName)"
OnSelecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:QueryStringParameter Name="ContactName"
QueryStringField="Name" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

in the codebehind (this is C#):

protected void SqlDataSource1_Selecting(object sender,
SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@ContactName"].Value = "%" +
e.Command.Parameters["@ContactName"].Value + "%";
}

what you want to do is handle the selecting event, and wrap the
parameter value in the % wildcards

also, don't use dynamic SQL

Hi, I'm having problems to get the value of a Querystring into the
SelectCommand of a SQLDataSource, here's my code:

----------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%'
+ @nombre + '%'">

<SelectParameters>
<asp:QueryStringParameter Name="nombre"
QueryStringField="nombre" type="string" DefaultValue=""/>
</SelectParameters>

</asp:SqlDataSource>

-----------------------------------------------
Here is the URL that call that code....

http://localhost:8103/MySQL_Test/default2.aspx?nombre=daniel

------------------------------------------------

The SelectCommand is not working right, is like the @nombre weren't
returning any value even if I set a default value

I have tried this commands with no result...
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' + @nombre + '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%' & @nombre & '%'

-SELECT cedula,nombre,tipo FROM clientes WHERE nombre=@nombre

If I set the querystring value directly into the SelectCommand the query
return the expected result:
-SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE '%daniel%'

What's wrong with my code?

 

[davecove]

Problems with Parameters and SQLDataSource and MySQL

Neil's code will work for MySQL if done this way:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>"
SelectCommand="SELECT * FROM [Customers] WHERE
([ContactName] LIKE ?)"
OnSelecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:QueryStringParameter Name="ContactName"
QueryStringField="Name" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

in the codebehind (this is C#):

protected void SqlDataSource1_Selecting(object sender,
SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters[0].Value = "%" +
e.Command.Parameters[0].Value + "%";
}


The differences are the '?' in the SelectCommand and the use of index value rather than parameter name in the codebehind function.

MySQL uses ?s in the SelectCommand rather than named parameters like '@ContactName'. Those ?s are matched up in order with the list of SelectParameters... 1st '?' with 1st SelectParameter, 2nd '?' with 2nd SelectParameter, etc.

Since the parameter names are not used, the 0-based index number has to be used in the codebehind, thus e.Command.Parameters[0].Value returns the 1st SelectParameter, e.Command.Parameters[1].Value returns the 2nd SelectParameter, etc.

Hope that helps someone,
Dave

 

[bluemoon]

What you can do is this......
----------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

Session("nombre") = "%" & Request.Querystring("nombre") & "%"

End Sub

----------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT cedula,nombre,tipo FROM clientes WHERE nombre LIKE @nombre">

<SelectParameters>
<asp:SessionParameter Name="nombre" SessionField="nombre" />
</SelectParameters>

</asp:SqlDataSource>
-----------------------------------------------

 

[roadhawk]

:captain: Dave u're awsome it works !! after hours of seeking ...:wave:

The differences are the '?' in the SelectCommand and the use of index value rather than parameter name in the codebehind function.

MySQL uses ?s in the SelectCommand rather than named parameters like '@ContactName'. Those ?s are matched up in order with the list of SelectParameters... 1st '?' with 1st SelectParameter, 2nd '?' with 2nd SelectParameter, etc.

Since the parameter names are not used, the 0-based index number has to be used in the codebehind, thus e.Command.Parameters[0].Value returns the 1st SelectParameter, e.Command.Parameters[1].Value returns the 2nd SelectParameter, etc.

Hope that helps someone,
Dave