2

Working with a legacy SQL Server table, it shows an optional col named [Group], which if omitted from code completely, the insert command functions just fine. The problem is related to that specific col.

What I have: 

daCust.InsertCommand = new SqlCommand("insert into dbo.TimeRouteTest ([Group], Route, Step_1) values (@Group, @Route, @Step_1)", con);

daCust.InsertCommand.Parameters.Add(new SqlParameter("[Group]", SqlDbType.Int, 4, "[Group]"));
daCust.InsertCommand.Parameters.Add(new SqlParameter("Route", SqlDbType.NVarChar, 100, "Route"));
daCust.InsertCommand.Parameters.Add(new SqlParameter("Step_1", SqlDbType.NVarChar, 50, "Step_1"));

then 

DataRow newRow = ds.Tables[0].NewRow();
newRow["[Group]"] = Convert.ToInt32(myGroup);
newRow["Route"] = mroute;
newRow["Step_1"] = mStep_1;

Note: I pre-process this data to make sure it's clean, I strip single quotes, etc. mroute and mStep_1 are just local variable holding text data passed from another function. myGroup is also a local var that is passed a value from a webservice. Then I follow up with:

ds.Tables[0].Rows.Add(newRow);
daCust.Update(ds);

If I comment out the [group] line, the command works fine. Any help on why this fails and how I can fix it? I know "group" is a reserved keyword, but adding the brackets is supposed to work to make it usable, or so I thought. The funny thing is, I can query this table, and get the value from the [group] col from other rows, so I know the col is truly there. Just unsure why I can't find it on insert...

Edit: if I use this code, it works fine:

daCust.InsertCommand = new SqlCommand("insert into dbo.TimeRouteTest (Route, Step_1) values (@Route, @Step_1)", con);

daCust.InsertCommand.Parameters.Add(new SqlParameter("Route", SqlDbType.NVarChar, 100, "Route"));
daCust.InsertCommand.Parameters.Add(new SqlParameter("Step_1", SqlDbType.NVarChar, 50, "Step_1"));

DataRow newRow = ds.Tables[0].NewRow();
newRow["Route"] = mroute;
newRow["Step_1"] = mStep_1;

ds.Tables[0].Rows.Add(newRow);

daCust.Update(ds);

Which pretty much isolates the issue to that specific col named [Group] and no combination of brackets or otherwise that I could think to try will make it work. Doing a select * from TimeRouteTest and then pulling the value from dr[2] does work, which is the index position of that col. Other than scrapping the table and using a better col name, how to fix? Curious note, looking at table definition in Visual Studio, it shows that col name as [Group], but if i look at data in table, it shows just Group as header/label in results...

Edit:

The line:

daCust.InsertCommand.Parameters.Add(new SqlParameter("[Group]", SqlDbType.Int, 4, "[Group]"));

and 

daCust.InsertCommand.Parameters.Add(new SqlParameter("@Group", SqlDbType.Int, 4, "[Group]"));

and 

daCust.InsertCommand.Parameters.Add(new SqlParameter("@[Group]", SqlDbType.Int, 4, "[Group]"));

all fail with error:

System.ArguementException: Column '[Group]' does not belong to table Table.

Edit: found (syntax) error. After smashing my head against wall for 3 hours, the following code actually works:

 daCust.InsertCommand = new SqlCommand("insert into dbo.TimeRouteTest ([Group], Route, Step_1) values (@Group, @Route, @Step_1)", con);

 daCust.InsertCommand.Parameters.Add(new SqlParameter("Group", SqlDbType.Int, 4, "Group"));
 daCust.InsertCommand.Parameters.Add(new SqlParameter("Route", SqlDbType.NVarChar, 100, "Route"));
 daCust.InsertCommand.Parameters.Add(new SqlParameter("Step_1", SqlDbType.NVarChar, 50, "Step_1"));

 DataRow newRow = ds.Tables[0].NewRow();
 newRow["Group"] = Convert.ToInt32(myGroup);
 newRow["Route"] = mroute;
 newRow["Step_1"] = mStep_1;

Turns out the only place you need brackets is the insert statement when declaring the col name, adding them anywhere else will cause various errors. My best guess is that Visual Studio causes partial confusion when it encounters a keyword col name by automatically showing brackets on col name so when exporting table definition, the brackets appear to be part of the name when in fact they are not.

The brackets are only required when using certain SQL statements and the col name is a reserved SQL Server keyword. Aggravating to say the least!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KenB
  • 21
  • 2
  • should parameter name be `@Group` here: `new SqlParameter("@Group", ...`? also do you need to specify last parameter `"[Group]"` for `insert` command at all? – ASh Mar 25 '18 at 07:23
  • Tried @Group in SqlParameter line, doesn't work. Technically no, if I omit the [Group] Parameter completely, the code works. It's not marked as required (null allowed) for insert, but practically speaking I do need a value passed into table for that col. – KenB Mar 25 '18 at 07:42
  • the 4th parameter, where you write "[Group]" isn't value. try `daCust.InsertCommand.Parameters.Add(new SqlParameter("@Group", SqlDbType.Int, 4));` – ASh Mar 25 '18 at 07:48
  • Gives same error if use that line. – KenB Mar 25 '18 at 07:51
  • The column is called `Group`. You put brackets around it only when it's a part of a query and not anywhere else, because only in the context of a query you might need to make the SQL keyword `GROUP` a non-keyword. In particular, you use `newRow["Group"]` and `@Group`. You should not "strip single quotes, etc" either, as doing so suggests you don't really understand what you are doing and what is the difference between a parametrized command and [concatenated SQL](https://stackoverflow.com/q/332365/11683). – GSerg Mar 25 '18 at 08:24
  • You are partially correct GSerg, the col name is Group, not [Group] as the table definition showed. However I do strip single quotes on some of the the parameters because it's passed from external source and those variables are used to dynamically build the select and insert commands. I've only used a very brief example here that doesn't reflect my full code. The real insert function handles user authentication, data validation and about 20 values of various nature. I was able to strip it down to 3 simple values for brevity and convenience here. – KenB Mar 25 '18 at 08:53
  • Stripping single quotes is not enough for building dynamic SQL either (see e.g. [1](https://stackoverflow.com/q/5520840/11683), [2](https://stackoverflow.com/q/139199/11683), [3](https://stackoverflow.com/q/8506574/11683)). For MS SQL Server, you should use `quotename`. – GSerg Mar 25 '18 at 09:48

1 Answers1

0

Turns out the only place you need brackets is the insert statement when declaring the col name, adding them anywhere else will cause various errors. My best guess is that Visual Studio causes partial confusion when it encounters a keyword col name by automatically showing brackets on col name so when exporting table definition, the brackets appear to be part of the name when in fact they are not. The brackets are only required when using certain sql statements and the col name is a reserved sql keyword. Correct code is shown at bottom of question. Thank you GSerg for verifying. My apologies if this was an amateur mistake, it had me stumped for awhile.

KenB
  • 21
  • 2