Parameters.Addwithvalue with spaces

Question

I feel like this is really simple but after googling for days I cant figure it out.

I'm trying to select from an SQL DB, and use variables in the select statement. But Parameters.Addwithvalue(); won't work for a table column that has a space in it. In SQL i need to add [] but it won't work in C#.

This for example does work, as it should, pretty straight forward

cmd.Parameters.AddWithValue(@"fullName", string.Format("{0}", "%" + userName.TrimStart() + "%"));

But this won't work

cmd.Parameters.AddWithValue(@"glboal_country_Travel", string.Format("{0}{1}", "[" + glboal_country.TrimStart(), " Travel]"));

I have tried diffrent variations but nothing works, nor does Parameters.Add(...) and no i can't change the DB column names for

Any help is appreciated, thank you.

Full SQL command

@"select [AD ID], DISPLAYNAME, Categorisation, @glboal_country from [...db].[dbo].[...View] where [AD ID] = @AD_ID or DISPLAYNAME like @fullName"

Hi @TheITGuy, can you attach the complete SQLCommand and also possibly the sample database records as you mentioned. Thank you. — Yong Shun, Apr 25 '21 at 08:48
(SQL) Identifiers cannot be passed as parameters. Parameters are for values (literals). And as a side note: ["AddWithValue is Evil"](https://www.dbdelta.com/addwithvalue-is-evil/). — sticky bit, Apr 25 '21 at 08:49
@YongShun I updated the full sql command, and the records or simple records, moistly nvarchar — JoelE, Apr 25 '21 at 08:58
@TheMixy that's the things, I don't get an error just that column doesn't come back, I do get Display name, AD ID etc just not global_country — JoelE, Apr 25 '21 at 09:00
TheITGuys, may I know reason why you set `@glboal_country` param in `SELECT` statement. And your `glboal_country_Travel` in SQLCommand Parameter is not matched with `@glboal_country` in SQL. — Yong Shun, Apr 25 '21 at 09:07
Does this answer your question? [SQL Server LIKE containing bracket characters](https://stackoverflow.com/questions/3661125/sql-server-like-containing-bracket-characters) — Charlieface, Apr 25 '21 at 09:17
@Charlieface No. for some reason that only works in the second part of the query ```...where @pram like @param```, not in the ```select @column``` name part. — JoelE, Apr 25 '21 at 09:45
I get an error that it's an invalid column, but i get the error when trying to display it not when executing the SQL query — JoelE, Apr 25 '21 at 10:24
Its hard to help if you dont share exact error messages. Also where and how are you trying to "display it"? — TheMixy, Apr 25 '21 at 19:12
@TheMixy I'm not really getting an error message. I am trying to fill a DataTable and instead of filling the results from the DB i get the variable instead. so I get all of the results back but the ```@glboal_country``` gives me the variable, that variable is column name in the DB which can change depending on selections. I get an error when trying to display it, the error is that the column doesn't exist in the DataTable which makes sense so the issue is with filling the DataTable. — JoelE, Apr 26 '21 at 08:09
Aha, I dont think you can do it this way. See here: https://stackoverflow.com/questions/30454747/how-to-set-a-column-name-in-sql-query-as-parameter#30455085 — TheMixy, Apr 26 '21 at 08:17
@TheMixy the only other way i found it to work it like ```new SqlCommand(string.Format(@"select [AD ID], DISPLAYNAME, {0} Labor] from [DB].[dbo].[View] where [AD ID] = @param1 or DISPLAYNAME like @Param2", "[" + glboal_country.TrimStart())))``` from a security point of view, is this okay? — JoelE, Apr 27 '21 at 06:16
Concatenating sql commands is not good practice because of potential sql injection. But, in your case it depends on who provides the variable for {0}. If it's provided directly by user input, you could have security problems... the only alternative is then to restructure your table (which I would do) — TheMixy, Apr 27 '21 at 07:05

Parameters.Addwithvalue with spaces

0 Answers0