String concatenating for Dataset.compute method

[Guest]

I am trying to use the compute method on a dataset that uses a concatenated string as the filter. Here is my code

Dim myTable As DataTabl
myTable = CheckHrsVacDataSet1.Tables("PYCheckHistory"
Dim str1 As String = "((Employee = '
Dim str2 As String = "') AND (CheckDate >= '
Dim str3 As String = "'))
VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)", (String.Concat(str1, EmployeeID, str2, fiscalYrDate, str3))).ToStrin

The code works fine but I get an error when my str1 picks up employees with an apostrophe in their name (ie: O'neal). The compute method thinks that the O' is the end of the string. Does anyone know how I can code around this

Thank

 

[Jay B. Harlow [MVP - Outlook]]

JC,
You need to "quote" the quoted string.

The easiest way is to use String.Replace, something like:

VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)",
String.Concat(str1, EmployeeID.Replace("'", "''"), str2, fiscalYrDate,
str3))

Note String.Concat & String.Replace return strings, there is no need to call
ToString on the return value.

Hope this helps
Jay

I am trying to use the compute method on a dataset that uses a

concatenated string as the filter. Here is my code:

Dim myTable As DataTable
myTable = CheckHrsVacDataSet1.Tables("PYCheckHistory")
Dim str1 As String = "((Employee = '"
Dim str2 As String = "') AND (CheckDate >= '"
Dim str3 As String = "'))"
VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)",

(String.Concat(str1, EmployeeID, str2, fiscalYrDate, str3))).ToString

The code works fine but I get an error when my str1 picks up employees

with an apostrophe in their name (ie: O'neal). The compute method thinks
that the O' is the end of the string. Does anyone know how I can code around
this?

 

[mwazir]

Have you tried replacing one apostrophe with two?

EmployeeID = EmployeeID.Replace(Chr(39), Chr(39) & Chr(39))
VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)",
(String.Concat(str1, EmployeeID, str2, fiscalYrDate, str3))).ToString

HTH

--
Wazir

I am trying to use the compute method on a dataset that uses a

concatenated string as the filter. Here is my code:

Dim myTable As DataTable
myTable = CheckHrsVacDataSet1.Tables("PYCheckHistory")
Dim str1 As String = "((Employee = '"
Dim str2 As String = "') AND (CheckDate >= '"
Dim str3 As String = "'))"
VacHrsThisYr.Text = myTable.Compute("Sum(TotalVacationHrs)",

(String.Concat(str1, EmployeeID, str2, fiscalYrDate, str3))).ToString

The code works fine but I get an error when my str1 picks up employees

with an apostrophe in their name (ie: O'neal). The compute method thinks
that the O' is the end of the string. Does anyone know how I can code around
this?

 

[Guest]

thanks jay, u got it