Needs Help with SQL on an ASP.Net page

Look athttp://www.explorencmountains.com/City2.aspx?CityID=32&CountyID=7. Look at how Atlantic Properties and Buck Mountain Estats show up twice. They are supposed to just show up once. They are listed under multiple categories. I only want them to show up once. The problem is with subcategoryid.

Here is the code I had to use for my SQL (Please look at the bolded part:

<code>

mySQL = "Select distinct listingtype, businessname, advertiser.advertiserid,advertisersubcategory.subcategoryidFrom Advertiser, AdvertiserSubCategory, SubCategories, County, City"
mySQL = mySQL & " where Advertiser.AdvertiserID = AdvertiserSubCategory.AdvertiserID"
mySQL = mySQL & " and AdvertiserSubCategory.SubCategoryID = SubCategories.SubCategoryID"
mySQL = mySQL & " and Advertiser.CountyID=County.CountyID"
mySQL = mySQL & " and Advertiser.CityID = City.CityID"
mySQL = mySQL & " and Advertiser.CountyID = '" & strCountyID & "'"
mySQL = mySQL & " and AdvertiserSubCategory.SubCategoryID IN (13,26)"
mySQL = mySQL & " and Approve =1"
mySQL = mySQL & " Order By ListingType, BusinessName, advertiser.advertiserid"

</code>

Look at the bolded part below. If I remove advertisersubcategory.subcategoryid from the above SQL Statement, I will get an error message. How do I work around this? I would like to remove advertisersubcategory.subcategoryid as a distinct field. But I would like to somehow have the SQL still retrieve the subcategoryid for the records. Does anyone have any idea how I can solve this problem?

<code>

<%# IIF(Databinder.Eval(Container.DataItem, "ListingType") ="Featured","<img src='./Images/Featuredlisting.gif' border='0'>", "")%> <font face="Arial, Helvetica, sans-serif" color="#FFFFFF" size=2><a href='<%# "DisplayAdvertiser.aspx?ID=" & DataBinder.Eval (Container.DataItem, "AdvertiserID") & "&CatID=" & DataBinder.Eval (Container.DataItem, "SubCategoryID") %>'>
<%# Databinder.Eval(Container.DataItem, "BusinessName") %>

</code>

You need to use ADO.NET parameterized query instead of what you have now and in SQL you can use a UNION to remove duplicates. In the second link the UNIONs are at the end one more thing run a search for UNION in SQL Server BOL (books online) for details because it comes with fixed requirements. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/
http://msdn2.microsoft.com/en-us/library/ms187731.aspx

|||

I really don't have a clue. I looked at some union statements in a book, but it didn't help. I tried the following sql statement:

Select distinct listingtype, businessname, advertiserid From Advertiser
Union
select distinct subcategoryid from AdvertiserSubCategory
Union
select distinct subcategoryid from SubCategories
Union
select distinct countyid from county
Union
select distinct cityid from City
where CountyID = 4
and SubCategoryID IN (13,26)
and Approve =1
Order By ListingType, BusinessName, advertiser.advertiserid

I got the following error message: Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SubCategoryID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'SubCategoryID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Approve'.

I really would like to get someone to actually help me to fix original code. Can someone please help?

|||

You should have tried the parameterized query first because your current query will open you up to SQL injection attacks and UNION performs implict distinct so your query is redundant and you must have the same data type facing the same direction as I told you to read up in the BOL(books online). Hope this helps.

SELECT LastName, FirstName
FROM EmployeeOne
UNION
SELECT LastName, FirstName
FROM EmployeeTwo
UNION
SELECT LastName, FirstName
FROM EmployeeThree ;
GO

|||

I am assuming you did not see the samples in the insert of the MSDN magazine so click on any of the figure inserts for many options like the one below.

private void GetOutputValues()
{
string sConnString =
"Server=(local);Database=Northwind;Integrated Security=True;";
string sProc = "prGet_Customer";
using (SqlConnection oCn = new SqlConnection(sConnString))
{
using (SqlCommand oCmd = new SqlCommand(sProc, oCn))
{
oCn.Open();
oCmd.CommandType = CommandType.StoredProcedure;

oCmd.Parameters.Add("@.sCustomerID", SqlDbType.NChar, 5);
oCmd.Parameters["@.sCustomerID"].Value = "ALFKI";

oCmd.Parameters.Add("@.sCompanyName", SqlDbType.NVarChar, 40);
oCmd.Parameters["@.sCompanyName"].Direction =
ParameterDirection.Output;

oCmd.Parameters.Add("@.sContactName", SqlDbType.NVarChar, 30);
oCmd.Parameters["@.sContactName"].Direction =
ParameterDirection.Output;

oCmd.Parameters.Add("@.sCity", SqlDbType.NVarChar, 15);
oCmd.Parameters["@.sCity"].Direction =
ParameterDirection.Output;

oCmd.ExecuteNonQuery();
oCn.Close();

string sCompanyName =
oCmd.Parameters["@.sCompanyName"].Value.ToString();
string sContacName =
oCmd.Parameters["@.sContactName"].Value.ToString();
string sCity = oCmd.Parameters["@.sCity"].Value.ToString();
}
}
}

|||

I am still clueless.

I tried SELECT ListingType, BusinessName, AdvertiserID
FROM Advertiser
UNION
SELECT subcategoryid
FROM AdvertiserSubCategory

GO

and it didn't work. Subcategoryid is not a field in Advertiser's table at all.

Here's the error message I am getting: All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

|||

SELECT ListingType, BusinessName, AdvertiserID
FROM Advertiser
WHERE SubcategoryID IN
(
SELECT subcategoryid
FROM AdvertiserSubCategory
WHERE SubcategoryID = AdvertiserID)

Here is a subquery for you try it, if you did not get the result try SQL prompt it is like VS intelisense for management studio but you still need to rewrite the other part in parameters,try the link below for SQL injection attacks. Hope this helps.

http://weblogs.asp.net/scottgu/

http://www.red-gate.com/products/SQL_Prompt/index.htm

|||

I really appreciate your help. I'm pretty lost. I really need help in rewriting

mySQL = "Select distinct listingtype, businessname, advertiser.advertiserid,advertisersubcategory.subcategoryidFrom Advertiser, AdvertiserSubCategory, SubCategories, County, City"
mySQL = mySQL & " where Advertiser.AdvertiserID = AdvertiserSubCategory.AdvertiserID"
mySQL = mySQL & " and AdvertiserSubCategory.SubCategoryID = SubCategories.SubCategoryID"
mySQL = mySQL & " and Advertiser.CountyID=County.CountyID"
mySQL = mySQL & " and Advertiser.CityID = City.CityID"
mySQL = mySQL & " and Advertiser.CountyID = '" & strCountyID & "'"
mySQL = mySQL & " and AdvertiserSubCategory.SubCategoryID IN (13,26)"
mySQL = mySQL & " and Approve =1"
mySQL = mySQL & " Order By ListingType, BusinessName, advertiser.advertiserid"

I really don't know how to. I am concerned about injection.