Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Typed dataset with dynamic where clause

Posted on 2014-03-21
9
Medium Priority
?
471 Views
Last Modified: 2014-03-26
I have an old ASP1.0 website that presnts the small amount of items we have in our libary.
It works ok but needs an update and I intended to use a Typed Dataset so that i could attach GridView for search and DetailsView via an ObjectDataSource.

But the problem I have is that the search allows for AND / OR / NOT for each term.

Building the SQL string the way it is done now is easy enough as it uses IF  statements (in run-at-server code) to place the AND.OR.NOT , but I cant determine the bets way to attach this to a typed dataset.

have looked a bit at overloading the Partial classes to intercept the Bind but got myself lost.
Tried a stored procedure to no avail but didnt try too hard i guess. the old search page.
0
Comment
Question by:Robberbaron (robr)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39944933
Can you post the code that configures and binds your typed dataset?

Normally you would bind to the DefaultView of the datatable and apply filtering using the Filter property of the view.
0
 
LVL 13

Expert Comment

by:Jitendra Patil
ID: 39945022
can you provide a .aspx and .cs code here?

According to your problem as what i suppose, if you use if and else statements to build your where clause it will be help you get you the desired result.

there must be id's assigned to each and every dropdownlist so you can check it code behind and create a dynamic query which can be assigned to your sqldatasource too.

hope this helps.
0
 
LVL 32

Author Comment

by:Robberbaron (robr)
ID: 39946651
this is old asp1 code that creates the sql query.
SQLquery="SELECT * " & _
         "FROM Technical L " & _
         "WHERE L.Id > 0 "

if Request("CallNo") <> "" then
SQLquery=SQLquery & Request("AndCall") & _
     " L.CallNumber LIKE '%" & Request("CallNo") & "%' "
end if

if Request("Title") <> "" then
SQLquery=SQLquery & Request("AndTitle") & _
     " L.Title LIKE '%" & Request("Title") & "%' "
end if

if Request("Author") <> "" then
SQLquery=SQLquery & Request("AndAuthor") & _
     " L.Author LIKE '%" & Request("Author") & "%' "
end if

'the keywords1 was supplied
if Request("KeyWords1") <> "" then
	SQLquery=SQLquery & Request("AndWords1") & " ( "
	MyString = Request("KeyWords1")
	MyArray = Split(MyString," ",-1,1)
	' MyArray(0) contains "VBScript". ' MyArray(1) contains "is". ' MyArray(2) contains "fun!".
	msg=0
	SQLquery=SQLquery & _
	    "L.Subject LIKE '%" & MyArray(msg) & "%' " 
	msg=msg+1
	do while msg<=ubound(myarray)
	  SQLquery=SQLquery & " AND ) " & _
	    "(L.subject LIKE '%" & MyArray(msg) & "%' " 
	  msg=msg+1
	loop 
	SQLQuery=sqlquery & " ) "
end if

if Request("KeyWords2") <> "" then
	SQLquery=SQLquery & Request("AndWords2") & " ( "
	MyString = Request("KeyWords2")
	MyArray = Split(MyString," ",-1,1)
	' MyArray(0) contains "VBScript". ' MyArray(1) contains "is". ' MyArray(2) contains "fun!".
	msg=0
	SQLquery=SQLquery & _
	    "L.Subject LIKE '%" & MyArray(msg) & "%' " 
	msg=msg+1
	do while msg<=ubound(myarray)
	  SQLquery=SQLquery & " AND ) " & _
	    "(L.Subject LIKE '%" & MyArray(msg) & "%' " 
	  msg=msg+1
	loop 
	SQLQuery=sqlquery & " ) "
end if

'complete the query
	SQLquery=SQLquery & "ORDER BY L.Title ASC;"

Open in new window


but i want to bind to a typed dataset to make it easy to attach gridview.

if i attach to SQLDatasource, it appears  i can change the QueryText dynamically from the click event of the Button, which i cant from ObjectDateSource. (I have previously only used ObjectDataSource ).

using sqlsource is not an issue cause we use SQLExpress.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Accepted Solution

by:
Jitendra Patil earned 2000 total points
ID: 39947273
yes on the button click you can generate a dynamic sql query with required where clause and assign it to Datasource also, if you need to have sorting in gridview you can keep the dynamic sql in viewstate and use it to rebind the gridview.

may be the below link can help you out
Using Parameters with the SqlDataSource Control
hope this helps.
0
 
LVL 32

Author Comment

by:Robberbaron (robr)
ID: 39947406
getting there. I used the hint from jitendra to change to a SQLDatasource and then can modify the select SQL statement.

works but now my issue is that Pagination of the GridView fails; it displays the first page correctly but clicking the second or further page link does not use the modified query it seems.

  protected void butUpdate_Click(object sender, EventArgs e)
    {
        string mySQL = "";
        mySQL = sdsLib.SelectCommand;
        string[] splitAND = new string[]{"AND"};
        string[] queryPart = mySQL.Split(splitAND, StringSplitOptions.RemoveEmptyEntries);

        //part zero should be constant. the items and Table name WHERE
        mySQL = queryPart[0];
        //query part 1 is CodeType
        mySQL += UpdateQueryPart(queryPart[1], ddAndCodeType.Text, sdsLib.SelectParameters[1].DefaultValue);

        //query part 2 is CodeNumber
        mySQL += UpdateQueryPart(queryPart[2], ddAndNumber.Text, sdsLib.SelectParameters[2].DefaultValue);

        //query part 3 is Description & SOrt clause
        //split off the sort clause
        int i = queryPart[3].IndexOf(" ORDER");
        string mySortClause = queryPart[3].Substring(i);
        queryPart[3] = queryPart[3].Substring(0, i - 1);

        mySQL += UpdateQueryPart(queryPart[3], ddAndWords1.Text, sdsLib.SelectParameters[3].DefaultValue);

        //now add in the assetplus only if specified
        if (ddlAssetPlus.SelectedValue != "Either")
        {
            string myPart = " ([AssetPLUS] = @AssetPLUS) ";

            mySQL += UpdateQueryPart(myPart, ddAndAssetPlus.Text, "0");
            try
            {
                //remove if exists
                sdsLib.SelectParameters.Remove(sdsLib.SelectParameters["AssetPlus"]);
            }
            finally
            {
                sdsLib.SelectParameters.Add("AssetPlus", DbType.Boolean, ddlAssetPlus.SelectedValue );
            }                         
        }

        mySQL += ")" + mySortClause;

        sdsLib.SelectCommand = mySQL;
        //gvResults.DataSource = sdsLib;

Open in new window

0
 
LVL 32

Author Comment

by:Robberbaron (robr)
ID: 39947415
looks like i have fixed pagination by saving to ViewState and retrieving in the Page_Changing event.
 
    protected void gvResults_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        sdsLib.SelectCommand = ViewState["currentSQL"].ToString();

    }

Open in new window

0
 
LVL 13

Assisted Solution

by:Jitendra Patil
Jitendra Patil earned 2000 total points
ID: 39955562
i think you will need to rebind the grid for paging puropse as below,
protected void gvResults_PageIndexChanging(object sender, GridViewPageEventArgs e)
       {
           sdsLib.SelectCommand = ViewState["currentSQL"].ToString();
           grResults.PageIndex = e.NewPageIndex;
           //again re assign the datasource select command and rebind the grid.
       }

if you need more assistance, feel free to ask, let me know if the issue is solved or not.

hope this helps.
0
 
LVL 32

Author Closing Comment

by:Robberbaron (robr)
ID: 39955701
the hint to use SQLDatasource instead of ObjectDataSource was what i needed.
worked out the viewstate bit myself, but it is a necessary part of the answer.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question