C# Command Including Additional Variable

Question

I'm having trouble working a variable into an existing code fragment. I'm sure its something simple I'm missing, but I'm not getting anywhere.

This is the current code, which has no errors:

new SqlCommand(@"xp_cmdshell 'bcp ""SELECT tipReportingSummaryView.payGroupCode, employee.employeeFName + '' '' + employee.employeeLName AS employeeName, tipReportingSummaryView.employeeID, SUM(tipReportingSummaryView.netChargeTips) AS netChargeTips, tipReportingSummaryView.positionCode, rvc.rvcName, ''CHG TIPS'' AS tipType FROM tipReportingSummaryView INNER JOIN employee ON tipReportingSummaryView.employeeID = employee.employeeID INNER JOIN rvc ON tipReportingSummaryView.rvcID = rvc.rvcID WHERE (tipReportingSummaryView.businessDate >= (dateadd(day,(-15),datediff(day,(0),getdate())))) AND (tipReportingSummaryView.businessDate <= (dateadd)) AND (tipReportingSummaryView.payGroupCode = 1) GROUP BY tipReportingSummaryView.payGroupCode, employee.employeeFName + employee.employeeLName, tipReportingSummaryView.employeeID, tipReportingSummaryView.positionCode, rvc.rvcName, employee.employeeFName + '' '' + employee.employeeLName HAVING SUM(tipReportingSummaryView.netChargeTips) <> 0"" queryout " + filePath + " -U sa -P password -c -t ,'", conn))

I want (tipReportingSummaryView.payGroupCode = ) to be equal to the value of the payGroup variable, so something like this:

new SqlCommand(@"xp_cmdshell 'bcp ""SELECT tipReportingSummaryView.payGroupCode, employee.employeeFName + '' '' + employee.employeeLName AS employeeName, tipReportingSummaryView.employeeID, SUM(tipReportingSummaryView.netChargeTips) AS netChargeTips, tipReportingSummaryView.positionCode, rvc.rvcName, ''CHG TIPS'' AS tipType FROM tipReportingSummaryView INNER JOIN employee ON tipReportingSummaryView.employeeID = employee.employeeID INNER JOIN rvc ON tipReportingSummaryView.rvcID = rvc.rvcID WHERE (tipReportingSummaryView.businessDate >= (dateadd(day,(-15),datediff(day,(0),getdate())))) AND (tipReportingSummaryView.businessDate <= (dateadd)) AND (tipReportingSummaryView.payGroupCode = " + payGroup + ") GROUP BY tipReportingSummaryView.payGroupCode, employee.employeeFName + employee.employeeLName, tipReportingSummaryView.employeeID, tipReportingSummaryView.positionCode, rvc.rvcName, employee.employeeFName + '' '' + employee.employeeLName HAVING SUM(tipReportingSummaryView.netChargeTips) <> 0"" queryout " + filePath + " -U sa -P password -c -t ,'", conn))

Summary Version

Current:

(tipReportingSummaryView.payGroupCode =1)

Desired:

(tipReportingSummaryView.payGroupCode = " + payGroup + ")

By adding " + payGroup + ", I get all kinds of syntax errors on that line, so its obviously causing it to bomb out.

Any suggestions are welcome.

I think payGroup is a local variable, and he wants its value in the string. — crashmstr, Apr 15 '13 at 19:43
You might want to post the specific syntax error that you get. — crashmstr, Apr 15 '13 at 19:44
@crashmstr - Yeah, that figures (removed former comment to prevent any confusion) — VisualMelon, Apr 15 '13 at 19:44
You should probably use SqlParameters. You won't have to worry about wrapping things in quotes then, plus it prevents SqlInjection. — PeteGO, Apr 15 '13 at 19:48
@PeteGO: yes. More specifically SqlCommand has a Parameters, which is an SqlParameterCollection. You refer to the parameters like this in the SQl command text "@param1". And add them like this: SqlCommand cmd = new SqlCommand("... where something=@param1 ..."); cmd.Parameters.AddWithValue("@param1", paramvalue); — Csaba Toth, Apr 15 '13 at 19:54
I'll look at the SqlParameters. It is probably the better way to do this, but I wasn't familiar with it. Using the string literals as outlined in the original snippet, adding @ in front of the string allows it to compile. — mkswanson, Apr 15 '13 at 20:03

score 0 · Accepted Answer · edited May 23 '17 at 11:50

You need to place another @ before the second string literal - else the double quotes will fail to compile (it will think it's two literals back to back) - as below.

Isolated: " + payGroup + @"

new SqlCommand(@"xp_cmdshell 'bcp ""SELECT tipReportingSummaryView.payGroupCode, employee.employeeFName + '' '' + employee.employeeLName AS employeeName, tipReportingSummaryView.employeeID, SUM(tipReportingSummaryView.netChargeTips) AS netChargeTips, tipReportingSummaryView.positionCode, rvc.rvcName, ''CHG TIPS'' AS tipType FROM tipReportingSummaryView INNER JOIN employee ON tipReportingSummaryView.employeeID = employee.employeeID INNER JOIN rvc ON tipReportingSummaryView.rvcID = rvc.rvcID WHERE (tipReportingSummaryView.businessDate >= (dateadd(day,(-15),datediff(day,(0),getdate())))) AND (tipReportingSummaryView.businessDate <= (dateadd)) AND (tipReportingSummaryView.payGroupCode = " + payGroup + @") GROUP BY tipReportingSummaryView.payGroupCode, employee.employeeFName + employee.employeeLName, tipReportingSummaryView.employeeID, tipReportingSummaryView.positionCode, rvc.rvcName, employee.employeeFName + '' '' + employee.employeeLName HAVING SUM(tipReportingSummaryView.netChargeTips) <> 0"" queryout " + filePath + @" -U sa -P password -c -t ,'", conn))

You should probably put one after the filePath concatenation also.

See "Verbatim Strings" here: MSDN Page On Strings (sorry, couldn't find a page specifically for them)

Edit: similar to this question here

Thanks! That is what was confusing me since it wasn't requiring anything different with the filePath concatenation. @ before the string fixed it. — mkswanson, Apr 15 '13 at 20:02

C# Command Including Additional Variable

1 Answers1