Problems with Parameters and SQLDataSource

Discussion in 'ASP .Net' started by Guest, Jul 19, 2006.

  1. Guest

    Guest 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 [email protected]

    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?
     
    Guest, Jul 19, 2006
    #1
    1. Advertisements

  2. Guest

    neilmcguigan Guest

    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
     
    neilmcguigan, Jul 20, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

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

    --
    DRH


     
    Guest, Jul 20, 2006
    #3
  4. Guest

    neilmcguigan Guest

    yes it is

    1. sqldatasource is for ms sql server

    2. parameter declarations different in mysql


     
    neilmcguigan, Jul 20, 2006
    #4
  5. Guest

    davecove

    Joined:
    Jan 12, 2009
    Messages:
    1
    Likes Received:
    0
    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
     
    davecove, Jan 12, 2009
    #5
  6. Guest

    bluemoon

    Joined:
    Feb 6, 2009
    Messages:
    1
    Likes Received:
    0
    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>
    -----------------------------------------------
     
    bluemoon, Feb 6, 2009
    #6
  7. Guest

    roadhawk

    Joined:
    Dec 31, 2009
    Messages:
    1
    Likes Received:
    0
    :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
     
    roadhawk, Dec 31, 2009
    #7
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.