how to use dynamic strings separated by comma in sql query?

Question

hello i build a webservice in visual studio 2010. i get some id's which are saved in a string looks like this:

string room_ids="5,11,99,42";

they are separated by comma. i created a foreach loop to split the ids and from the comma and use them in my sql query until the ids are finished. but it doesn't work. i get an error it says:

Error converting data type nvarchar to numeric

here is my code, thanks in advance for your help!

internal static List<RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
{       
    List<RAUM> strasseObject = new List<RAUM>();
    string[] allegebaude = GEBAEUDE_ID.Split(new char[] { ',' });

    foreach (string gebaudeid in allegebaude)
    {
        Trace.WriteLine("SIND JETZT DRINNE");
        Trace.WriteLine(gebaudeid);

        using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
        using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) AND STADT_ID = ISNULL(@Stadt_ID, STADT_ID) AND GEBAEUDE_ID = ISNULL(@gebaudeid,GEBAEUDE_ID ) AND REGION_ID = ISNULL(@Region_ID, REGION_ID)", con))
        {
            con.Open();
            if (!StringExtensions.IsNullOrWhiteSpace(RAUMKLASSE_ID))
                cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
            else
                cmd.Parameters.AddWithValue("@Raumklasse_ID", DBNull.Value);

            if (!StringExtensions.IsNullOrWhiteSpace(STADT_ID))
                cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
            else
                cmd.Parameters.AddWithValue("@Stadt_ID", DBNull.Value);

            if (!StringExtensions.IsNullOrWhiteSpace(GEBAEUDE_ID))
                cmd.Parameters.AddWithValue("@gebaudeid", GEBAEUDE_ID);
            else
                cmd.Parameters.AddWithValue("@gebaudeid", DBNull.Value);

            if (!StringExtensions.IsNullOrWhiteSpace(REGION_ID))
                cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
            else
                cmd.Parameters.AddWithValue("@Region_ID", DBNull.Value);



            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    if (rdr["BEZEICHNUNG"] != DBNull.Value && rdr["ID"] != DBNull.Value)
                    {
                        strasseObject.Add(new RAUM()
                        {
                            RaumName = rdr["BEZEICHNUNG"].ToString(),
                            RaumID = rdr["ID"].ToString()

                        });
                    }
                }
            }

        }
    }
    return strasseObject;
}

Tom Chantler · Accepted Answer · 2012-06-19 11:13:55Z

2

If you already have the IDs in a comma-separated string (called IDstring) then you can just do something like this:

sqlQuery = "SELECT Columns FROM table WHERE ID IN (" + IDstring + ")";

In your specific case, don't split the original string (GEBAEUDE_ID) but use it as it is:

   // Don't use a foreach loop any more
   string gebaudeIdSection = " AND GEBAEUDE_ID IN (" + GEBAEUDE_ID + ") ";
   if (string.IsNullOrEmpty(GEBAUDE_ID)) { gebaudeIdSection = ""; } // if there are no ids, let's remove that part of the query completely.
   using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
   using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) AND STADT_ID = ISNULL(@Stadt_ID, STADT_ID)" + gebaudeIdSection + " AND REGION_ID = ISNULL(@Region_ID, REGION_ID)", con))
   { // The rest of the code is the same as before...

edited Jun 19, 2012 at 11:13

answered Jun 19, 2012 at 7:22

Tom Chantler

15k4 gold badges50 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eray Geveci Over a year ago

thanks for your help, i have a little problem, i can't set the gebaudeid to empty, because my string is whith []. how can i solve this?

Tom Chantler Over a year ago

I'm not sure I understand. You don't do anything with gebaudeid. You need to take the original comma-separated string GEBAUDE_ID that you passed into the function and just use that without splitting it. Remove the bit of code that splits the string into an array.

Eray Geveci Over a year ago

it works but when i let the gebaude id empty i get an error: Incorrect syntax near ')'.

Tom Chantler Over a year ago

I have tweaked the code to completely omit that part of the query if there are no ids.

Eray Geveci Over a year ago

thank you it works, i tried your solution for the other ids,too. but it seems like difficult. because of the "AND" in your string. when i just give 1 parameter the "AND" in the string creates an error. i don't know how to fix that

davioooh · Accepted Answer · 2012-06-19 07:26:57Z

1

First of all I think you have to correct:

cmd.Parameters.AddWithValue("@gebaudeid", GEBAEUDE_ID);

with:

cmd.Parameters.AddWithValue("@gebaudeid", gebaudeid);

Then, try to convert the ids into integers ( for example, using Convert.ToInt32(gebaudeid) ) and not to pass them as strings in AddWithValue method.

answered Jun 19, 2012 at 7:26

davioooh

24.8k47 gold badges172 silver badges262 bronze badges

Comments

Thousand · Accepted Answer · 2012-06-19 07:28:56Z

1

try this:

if (!StringExtensions.IsNullOrWhiteSpace(gebaudeid))
            cmd.Parameters.AddWithValue("@gebaudeid", Convert.ToInt32(gebaudeid));

you should also pass the right parameter to your AddWithValue statement. You are iterating over the list of your ID's, that list being allegebaude. So you have to pass the gebaudeid parameter to your statement, instead of what you're doing now.

edited Jun 19, 2012 at 7:28

answered Jun 19, 2012 at 7:22

Thousand

6,6484 gold badges41 silver badges46 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:21:15Z

0

You can't implicitly treat a comma separated list of values; instead you'll need to create a table values function to split the list of values into a table structure and then use this structure as you would normally.

Have a look at this question: INSERT INTO TABLE from comma separated varchar-list for a sample function.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jun 19, 2012 at 7:22

Dave

3,6311 gold badge24 silver badges25 bronze badges

Collectives™ on Stack Overflow

how to use dynamic strings separated by comma in sql query?

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related