Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedures

Author  Topic 

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 04:11:09
can you pls help me with a stored procedure,i have to dropdownlist i want to first select the first dropdown them the information that belongs to that name i select be shown in the second dropdownlist,
like i have provinces if i select province in the first dropdown, the city that belongs to that province will be shown on the second dropdownlist.Thanks

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-09-10 : 04:13:10
what's the problem you are facing ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

afrika
Master Smack Fu Yak Hacker

2706 Posts
Posted - 2008-09-10 : 04:20:45
Thats not a stored procedure's funcntion.

You should look into ajax, and its relatively easy to use in visual studio. Though you will need to create a recordset to retrieve your data. To get that done, you could create a data connection class.
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 04:30:16
How can i do it because i tryed a lot but it not working,it only take the last record

quote:
Originally posted by afrika

Thats not a stored procedure's funcntion.

You should look into ajax, and its relatively easy to use in visual studio. Though you will need to create a recordset to retrieve your data. To get that done, you could create a data connection class.
Go to Top of Page

afrika
Master Smack Fu Yak Hacker

2706 Posts
Posted - 2008-09-10 : 04:33:52
What frontend are you using to display ? .net ?
If thats the case, this post should have posted in the .net forum.

post your script here ?
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 04:37:12
is that when i try to retrieve the information that related to the dropdown i selected it only give me the information of the last record in my dropdownlist
quote:
Originally posted by khtan

what's the problem you are facing ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

afrika
Master Smack Fu Yak Hacker

2706 Posts
Posted - 2008-09-10 : 04:39:59
quote:
Originally posted by Mpilo

is that when i try to retrieve the information that related to the dropdown i selected it only give me the information of the last record in my dropdownlist
quote:
Originally posted by khtan

what's the problem you are facing ?

KH
[spoiler]Time is always against us[/spoiler]




Post your script here
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 04:43:02
i'm using .net and for database i use sqlserver , i think we can solve this with stored procedure here is my stored procedure
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[cityselects]
@CityName varchar(30)=''
As

declare @Cities varchar(30)
if exists (select ProvinceDataName from ProvinceData)

set @Cities=''
select @Cities=ProvinceDataName from ProvinceData
begin
select CityName from City,provincedata
where City.provincedatano=provincedata.provincedatano and provincedata.provincedataname=@Cities
End

but it take the max(provincedataname)
quote:
Originally posted by afrika

What frontend are you using to display ? .net ?
If thats the case, this post should have posted in the .net forum.

post your script here ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-09-10 : 04:47:05
quote:
if exists (select ProvinceDataName from ProvinceData)

set @Cities=''
select @Cities=ProvinceDataName from ProvinceData

what are you trying to do with these statements ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 04:50:22
trying to check the selected item
so i can continue to select the city that belongs to that province that is seleted

quote:
Originally posted by khtan

quote:
if exists (select ProvinceDataName from ProvinceData)

set @Cities=''
select @Cities=ProvinceDataName from ProvinceData

what are you trying to do with these statements ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-09-10 : 04:50:35
quote:
Originally posted by Mpilo

can you pls help me with a stored procedure,i have to dropdownlist i want to first select the first dropdown them the information that belongs to that name i select be shown in the second dropdownlist,
like i have provinces if i select province in the first dropdown, the city that belongs to that province will be shown on the second dropdownlist.Thanks

maybe this is what you want ? You pass in the selected provice name from 1st drop down into the Stored Procedure and it returns the cityname ?

ALTER PROCEDURE [dbo].[cityselects]
	@province varchar(30)=''
AS
BEGIN
	SELECT 	CityName 
	FROM 	City INNER JOIN provincedata
		ON	City.provincedatano = provincedata.provincedatano 
	WHERE	provincedata.provincedataname = @province
END


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

afrika
Master Smack Fu Yak Hacker

2706 Posts
Posted - 2008-09-10 : 04:51:02
quote:
Originally posted by Mpilo


but it take the max(provincedataname)


So what are you trying to sort. And what are you trying to display in your front end. I advice you post your .net script that interacts with this as well. As I said ajax could do the trick easily
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-09-10 : 04:54:19
<<
select @Cities=ProvinceDataName from ProvinceData
>>

If there are more than one ProvinceDataName, only the last returned would be assigned
Follow khtan's query

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-09-10 : 04:56:59
This link will provide you some insight. http://msdn.microsoft.com/en-us/library/aa581792.aspx
Also, when you are trying to populate second dropdown on index change of fisrt (province), you have to use SP mentioned by khtan and pass province selected to it.
1. Populate Province dropdown
2. On index change of province dropdown, pass selected value to SP (khtan).
3. Fill city dropdown with data returned from above SP.

Hope this helps. also search for "master detail .net" on google.
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 05:06:52
i tried this but now it doesn't output anything in the dropdown

quote:
Originally posted by khtan

quote:
Originally posted by Mpilo

can you pls help me with a stored procedure,i have to dropdownlist i want to first select the first dropdown them the information that belongs to that name i select be shown in the second dropdownlist,
like i have provinces if i select province in the first dropdown, the city that belongs to that province will be shown on the second dropdownlist.Thanks

maybe this is what you want ? You pass in the selected provice name from 1st drop down into the Stored Procedure and it returns the cityname ?

ALTER PROCEDURE [dbo].[cityselects]
	@province varchar(30)=''
AS
BEGIN
	SELECT 	CityName 
	FROM 	City INNER JOIN provincedata
		ON	City.provincedatano = provincedata.provincedatano 
	WHERE	provincedata.provincedataname = @province
END


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-09-10 : 05:10:38
How did you execute the procedure?
Did you supply parameter value?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 05:15:36
Maybe the problem with this is that i cal this stored procudure in the ddlCity_Onload.maybe it retrieve before i select.is this can be problem?
quote:
Originally posted by khtan

quote:
Originally posted by Mpilo

can you pls help me with a stored procedure,i have to dropdownlist i want to first select the first dropdown them the information that belongs to that name i select be shown in the second dropdownlist,
like i have provinces if i select province in the first dropdown, the city that belongs to that province will be shown on the second dropdownlist.Thanks

maybe this is what you want ? You pass in the selected provice name from 1st drop down into the Stored Procedure and it returns the cityname ?

ALTER PROCEDURE [dbo].[cityselects]
	@province varchar(30)=''
AS
BEGIN
	SELECT 	CityName 
	FROM 	City INNER JOIN provincedata
		ON	City.provincedatano = provincedata.provincedatano 
	WHERE	provincedata.provincedataname = @province
END


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

afrika
Master Smack Fu Yak Hacker

2706 Posts
Posted - 2008-09-10 : 05:18:45
I would advice you paste your .net page here
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 05:19:09
Yes i did all that.
quote:
Originally posted by madhivanan

How did you execute the procedure?
Did you supply parameter value?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 05:23:08
Okey i'm using three tier so here is my Data access layer:
public DataTable SelectCity(string CityName)
{
SqlParameter p;
this.Connections();
DataTable dtcity = new DataTable();

cmd = new SqlCommand("cityselects", connection);
cmd.CommandType = CommandType.StoredProcedure;

p = new SqlParameter("@CityName", SqlDbType.VarChar);
p.Direction = ParameterDirection.Input;
p.Value = CityName;
cmd.Parameters.Add(p);
adapter = new SqlDataAdapter(cmd);
dtcity.Clear();
adapter.Fill(dtcity);
cmd.ExecuteNonQuery();
this.Closeconnection();

return dtcity;
}
Business logic:
public DataTable CitySelect()
{
dtCity.Clear();
return dtCity = conCity.SelectCity(CityName);
}

and my presentation layer :
protected void ddlCityName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{

if ((Convert.ToString(ddlCityName.SelectedItem)) == ("Add New City"))
{
Session["provincename"] = "City";
Response.Redirect("AddName.aspx");
province = 1;


}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

quote:
Originally posted by afrika

I would advice you paste your .net page here
Go to Top of Page

Mpilo
Yak Posting Veteran

52 Posts
Posted - 2008-09-10 : 05:26:17
No i paste a wrong code for Presentation layer here is the correct one:
protected void ddlCityName_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
dtProv = CBLL.CitySelect();
int j;
bool found = false;
ddlCityName.Items.Add(new ListItem("--Select City--", ""));

while (found == false)
{
found = true;
for (j = 0; j < dtProv.Rows.Count; j++)
{

ddlCityName.Items.Add(new ListItem(dtProv.Rows[j]["CityName"].ToString()));
}
}

ddlCityName.Items.Add(new ListItem("Add New City"));

}
}
quote:
Originally posted by afrika

I would advice you paste your .net page here
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site