asked on

c#.NET Create Dynamic SQL statement from info in a URL OnClick

I have two pages:
The first page displays an image, the image is a map of london, I have used an image map to cut up the districts on the map. The idea is that a user clicks a district and go to a new page (page two) containing the areas within that district.
I have a mssql database with the info for the districts/areas setup and working fine.
My current code and SQL query that is performed when a user clicks a district is:

<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{

SqlConnection myConnection = new SqlConnection("server=localhost;" +
"database=ldc;Trusted_Connection=Yes");

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
" MapLocations where district='camden'", myConnection);

DataSet ds = new DataSet();
myCommand.Fill(ds);

MyRepeater.DataSource = ds;
MyRepeater.DataBind();
}
</script>

I want to change the SQL query so that it populates district= with the name of the district the user clicked on the previous page.

I tried something along the line of:

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
" MapLocations where district=Response.Write([id])", myConnection);

Where id would be passed in the URL such as map.aspx?id=OxfordCircus

all the data in the dbase is vchar.

But it fails....is there some easy way of doing this so i dont have to create 30 separate aspx files containing 30 unique sql queries to reflect the 30 different districts in london...?

praneetha

change it !Page.IsPostback..

ad when u r passing the id in the url..it is accessed

using Request.QueryString["id"].toString()....now you shoul dbe able to use it

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
" MapLocations where district="+Request.querystring["id"].ToString(), myConnection);

<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
if(!Page.ispostback) //change that to not postback
{

SqlConnection myConnection = new SqlConnection("server=localhost;" +
"database=ldc;Trusted_Connection=Yes");

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
" MapLocations where district='camden'", myConnection);

DataSet ds = new DataSet();
myCommand.Fill(ds);

MyRepeater.DataSource = ds;
MyRepeater.DataBind();
}
}
</script>

tomasX2

string id = whatever your id is... // don´t think you can get it from response.write.....
string select = "SELECT * FROM MapLocations where district='" + id + "'";
SqlDataAdapter myCommand = new SqlDataAdapter(select , myConnection);

ASKER CERTIFIED SOLUTION

tomasX2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HoomanJamshidiNHE

ASKER

thanks man....that saves me tons of time.