delimitered list through sql server

.net , asp.net , db Libya
  • 12 years ago
    Hello

    I have a table in SQL server which looks like this:

    service_id
    service_name
    service_keywords

    The data in service_keywords is delimitered with a colon eg. website,stuff,testing,words

    Now I have a webform which allows people to search (via a textbox). When they enter a word it should check all the words in the service_keywords columns and return the id's that have a match (same method as LIKE in sql statements, eg site should also match the keyword website).

    Can I do this in .NET or do I have to do this via a stored procedure or is there any other option available? Can't seem to figure this out.

    Thanks in advance.
  • 12 years ago

    hi Kevin,

    Try to split your string stored in service_keywords.

    - Fetch the string.

    - create an string array.... like:  string[] arrkeywords;

    - then split the string fetched earlier.....like : arrkeywords = servicekeywords.Split(new string[]{","}, StringSplitOptions.None);

    Then you can compare each array element with the input word.

    Regards,

    Royal

  • 12 years ago

    Hi,

    This is a simple database query, I dont know SQL server syntax, so this is oracle syntax that you can edit:

    [CODE]

    using oraConn as oracleconnection = new oracleconnection(configurationmanager.connectionstrings("db").connectionstring)

    using oraCmd as oraclecommand = new oraclecommand("select * from table where service_keywords like (:QUERY)", oraconn)

    oraCmd.connection.open

    oraCmd.parameters.addwithvalue("QUERY", "%" & txtQuery.text & "%")

    dim oraDread as oracledatareader = nothing

    oraDread = oraCmd.Executereader

    if oraDread isnot nothing andalso oradread.hasrows

    do while oraDread.read

    ' your code to do the stuff with the results goes here

    loop

    oraDread.close

    end if

    oracmd.connection.close

    end using

    end using

    [/CODE]

    That should see you right.  If you want the results in a dataset, use a dataadapter and call fill on it passing in the dataset.

    Regards

    Simon C

  • 12 years ago
    HI You can simply much Just follow the following DIM CON AS NEW SQLconnection("cOnNeCtIoN sTING ") DIM DA AS NEW DATASET DIM DA AS NEW SQLDATAADAPTER("SELECT [ServiceID] from Tablename Where servicekeywords like '%' + @k + '%' ",CON) DA.SelectCommand.Parameters.AddWithValue("@k",TEXTBOX1.TEXT) DA.FILL(DS) Thanks my Regards .. Eng : Ahmed Alrefaie

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Memory is like an orgasm. It's a lot better if you don't have to fake it.” - Seymour Cray