Excel data containing ' throws error in select method

Help Activities
, ,
1

@Palaniyappan

Continuing the discussion from Quotes Escape Character:

select method

It throws error Syntax error: Missing operand after ‘21’ operator.

currQtrDT.Select(“[”+currQtrDT.Columns(0).ToString+“]='”+row(0).ToString+“'”)

2

Could you see if this will work:
currQtrDT.Select("["+currQtrDT.Columns(0).ToString+"]='"+row(0).ToString+"'")
Instead of
currQtrDT.Select("["+currQtrDT.Columns(0).ToString+"]=’"+row(0).ToString+"’")

3

I dont find any difference. Both looks same :slight_smile:

4

Are you allowed to change the month format from jan’21 to jan21

5

Yes by using regex we can do that but I wanted to get the data as it is.

6

Here it is :sweat_smile:

image
image703×130 6.89 KB

It sometimes breaks the syntax, so I wanted to rule it out first.

7

It is single quote right ?
I have also put single quote but looking different.

1 Like
8

Correct. I think for some reason the other quote used to give errors, but I tested it myself just now and it was not the issue.

The issue was that you are basically doing this:

currQtrDT.Select("[Month Year]='Jan'21'")

That extra single quote is basically messing up the syntax and the expression doesn’t know what it should consider.

However, this is typically resolved by escaping the character with an identical one. I gave it a try and it worked:

currQtrDT.Select("[Month Year]='Jan''21'")

So for your original expression it would be:

currQtrDT.Select("["+currQtrDT.Columns(0).ToString+"]='"+row(0).ToString.Replace("'","''")+"'")
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.