Tuesday, March 20, 2012

Custom SelectCommand

If I hard code the select statement everything works fine.

<asp:SqlDataSourceID="SqlDataSourceZip"runat="server"ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"

SelectCommand="SELECT * FROM [Station] WHERE ([ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528')"></asp:SqlDataSource>

Want to use a label control to return data, but can't find anything that works.

<asp:LabelID="zipLabel"runat="server"></asp:Label>

[ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528'

something like this, but it don't work.

SelectCommand="SELECT * FROM [Station] WHERE (<%=zipLabel.Text%>)"></asp:SqlDataSource>

Like this:

SelectCommand="SELECT * FROM [Station] WHERE [ZipCode]=@.ZipCode">

<SelectParameters>

<asp:ControlParameterControlID="zipLabel"Name="ZipCode"Type="String"PropertyName="Text"/>

</SelectParameters>

But it is common to use a dropdowlist to hold these zipcodes. In this case, you can try something like this:

<SelectParameters>

<asp:ControlParameterControlID="zipDropDownList"Name="ZipCode"Type="String"PropertyName="SelectedValue"/>

</SelectParameters>

|||

Thanks for the response. This is what I got...

Incorrect syntax near '='.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '='.

I'm using a web service and getting zipcodes within X mile radius

Here is zipLabel:

ZipCodes g =newZipCodes();

AuthenticationHeader authHeader =newAuthenticationHeader();

authHeader.SessionID ="AuthNumberHere";

g.AuthenticationHeaderValue = authHeader;

zipLabel.Text ="";

errorLabel.Text ="";

ZipCodeDistances[] zipCodes = g.GetZipCodesWithin(postalCodeTextBox.Text,Convert.ToInt32(radiusTextBox.Text));for (int i = 0; i < zipCodes.Length; i++)

{

ZipCodeDistances zipCode = zipCodes[i];

zipLabel.Text = zipLabel.Text + (zipCode.ZipCode +"' OR [ZipCode] = '");

}

zipLabel.Text ="[ZipCode] = '" + zipLabel.Text + postalCodeTextBox.Text +"'";

So, postal code 02155 with a 3 mile radius displays this in zipLabel.Text

[ZipCode] = '02153' OR [ZipCode] = '02156' OR [ZipCode] = '02144' OR [ZipCode] = '02140' OR [ZipCode] = '02145' OR [ZipCode] = '02474' OR [ZipCode] = '01890' OR [ZipCode] = '02143' OR [ZipCode] = '02155'

Thanks,


|||

You may need to work with array type in your custom select. You need have customized solution for array type operation in SQL Server.

Here is a link for you to read for a through review on this issue http://www.sommarskog.se/arrays-in-sql-2005.html.

And also another one with a split function you can use:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=334436&SiteID=1

No comments:

Post a Comment