Problems with Parameters and SQLDataSource

[Daniel R. H.]

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/def...?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?

--
DRH

[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

Daniel R. H. wrote:

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/def...?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?

--
DRH

[Daniel R. H.]

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

--
DRH
"ne**********@gmail.com" wrote:

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

Daniel R. H. wrote:

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/def...?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?

--
DRH

[neilmcguigan]

yes it is

1. sqldatasource is for ms sql server

2. parameter declarations different in mysql

Daniel R. H. wrote:

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

--
DRH
"ne**********@gmail.com" wrote:

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

Daniel R. H. wrote:

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/def...?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?
>
--
DRH