Escaping quotes in XML

[richiemac]

Hi Not sure if this a question for XML, SQL or .Net forum but here goes. I have an XML file with a number of elements, one of which is,

select * from trainarrivals where ActiveFlag = 1 and DueTime between ('" + DateTime.Now.ToShortTimeString() + "') and ('" + (DateTime.Now.AddHours(1).ToShortTimeString()) + "')

I am using a C# application to read the file and connect to a MySql database. When I get this element the environment adds escape characters to the string so it looks like,

select * from trainarrivals where ActiveFlag = 1 and DueTime between ('\" + DateTime.Now.ToShortTimeString() + \"') and ('\" + (DateTime.Now.AddHours(1).ToShortTimeString()) + \"')

This in turn causes an exception in my code being thrown. My question is, how can I write the xml file so the ( " ) is treated as such or change the string when it is read? Thanks in advance and appologies if this is the wrong place!

[led mike]

richiemac wrote:

DateTime.Now.ToShortTimeString()

What are you going to do with that as a string? :confused: Maybe something like this will work better? select * from trainarrivals where ActiveFlag = 1 and DueTime between ('?') and ('?')

led mike

[richiemac]

Ok let me explain. The string is an sql query that is stored in an xml config file. The DateTime stuff is so i can check the database for records within a certain time frame. I am getting the string from the config file and trying to use concatenation to get the current time - the config will be called at different times and only wants values from the database for the next hour. If I hard code the string in the IDE, e.g. string sqlStr = "..."; then all works fine. The problem arises when I get the stored string from config. The reason I'm storing the string in config is because there are many different queries that depend on certain other aspects. The config thing needs to stay in place so I have to get it working this way (if possible). Hope this clears up the confusion. Rich

[led mike]

richiemac wrote:

Hope this clears up the confusion.

No it does not. If DateTime.Now.ToShortTimeString() is a TSQL statement then why do you have a "string" in a config file instead of a Stored Procedure?

led mike

[richiemac]

Config files are used because the app has to have the ability to read any appropriate config and use the query string. Users can therefore add new config files that will be read. At this time the query could be anything so I therefore wouldn't use stored procs. Plus using stored procs now would mean major changes that time doesn't allow for. The DateTime.Now.ToShortTimeString() is a .net System statement that I'm trying to concatenate into the query string that is read from the config. If I can't do it in this way is there anyway aroud this without having to make big changes?

[led mike]

richiemac wrote:

that I'm trying to concatenate into the query string that is read from the config.

Ok we are still having some confusion because in your first post ...

richiemac wrote:

I have an XML file with a number of elements, one of which is,

and you follow that with the SQL statement including the DateTime.Now.ToShortString() etc.... so.... now it seems that the DateTime.Now.ToShortString() is NOT in the XML data, is that true? If so then my first post offers an example of how you could deal with the problem. If this:

select * from trainarrivals where ActiveFlag = 1 and DueTime between ('@DTStart') and ('@DTEnd')

was in the XML then you could use a System.Data.SqlClient.SqlCommand giving it the string and then setting the parameters using DateTime.Now.XXXX() methods. see the documentation[^] for details. does that help?

led mike

[richiemac]

Cheers my friend! Got it working - eventually. Again thanks for the help. Rich