Problems with Parameters and SQLDataSource

G

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?
 
N

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
 
G

Guest

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

--
DRH


[email protected] said:
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?
Click to expand...
Click to expand...
 
N

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


[email protected] said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SqlDataSource, Eval, and Label Text 2
problem parameters in sqldatasource 1
sqlDataSource + Store Procedure + pameters IF 2
Modify SqlDataSource parameters before select 5
Problems passing info to next page 3
GridView-FormView (Master/Detail) Control not working... 1
Problem with 2 SqlDataSource 2
How store sqlDataSource SELECT results in dataset? 1

Top