Asterisk in SQL

I'm trying to creat a search form and want to use the Like clause in my select statement so that a user can enter part of a word rather than the entire word. When I use this sql I get no results:

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '*ARR*'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

The problem is with the * . If I remove the * it works fine. If I use the code within Access rather than from my aspx code, it works fine. Is there a work around for this?

Hi,

% (percent) is the standard wildcard character. Access client does support *, but when you use it via OleDB (Jet provider) it also requires %.

Therefore put

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '%ARR%'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

|||That did it. Thanks!!!

Assigning the for xml query output to a variable

Hi,
I would like to generate an XML query using the 'for xml' clause and assign
that to a text variable for further manipulation.
for e.g., I would like to do some thing like this.
declare @.test text
Set @.test = Select * from test_table for xml auto
But this gives my the syntax error. Can anyone help capturing the xml output
of a query into a variable?
Regards,
ArunHi
Have you looked at the text datatype limitations. You can not use a lot of
the T-SQL string manipulation functions against it, so you can't really do
much with it once you have it.
You can do a SELECT ... INTO ... to write it to a temporary table, then
select it into your @.test variable. I have not tried it, but is a
suggestion.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arun" <kathir_arun@.hotmail.com> wrote in message
news:ePOh0u8QFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to generate an XML query using the 'for xml' clause and
> assign
> that to a text variable for further manipulation.
> for e.g., I would like to do some thing like this.
> declare @.test text
> Set @.test = Select * from test_table for xml auto
> But this gives my the syntax error. Can anyone help capturing the xml
> output
> of a query into a variable?
> Regards,
> Arun
>

ASP: SqlDataSource - Select Command

I am using <asp:SqlDataSourceIDand for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:

SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @.LastName) ORDER BY [LastName]DESC">

I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written...

I tried:

LastName LIKE '%" & LastName & "%'"

But I get an error????Any suggestions, please...

Thanks !!

Hey,

LastName like @.LastName

And you need to embed a % in the passed in parameter value.

|||

Hi and Thanks for getting back to me... however Im not sure that I understand your response... would really appreciate an 'example' or instruction on how to do what you indicate...

Thanks Again !!

|||

How are you assigning the value to the drop down? Because whatever value it uses is sent directly to the data source control. You need to modify the value sent. There are a couple of ways to do this. You could modify the textbox value and manually call the select() for the data source, but that would leave the % value in the textbox. Better would be to not use the ControlParameter parameter object, but to use Parameter. Tap into the Selecting event, and do this:

this.SqlDS.SelectParameters["LastName"].DefaultValue = "%" + this.txtLastName.Text + "%";

|||I took your lead...and I coded the 'LastName' value from the form search page, like this...

LastNamePresent ="%" & LastName.Text &"%"
and on the result page extracted the value from the QueryString and used it like this...

SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE [LastName] LIKE @.LastName ORDER BY [FirstName]ASC"
And OILA!!!!!
IT WOOOOOOOOOOOOOOOOOORKED !!!!

THANKS !!!!