XML to store multiple SQL Queries

[prrm333]

I have a function that works to save in an xml file a query, userID and userPassword that is working correctly. I also have the ability on the form for the user to type in a query to use or save. This also includes the ability with a checkbox to add text for a date range.

When I check this checkbox, the following sql code is added to the text I already typed in:

where dmv_trans_dte >= '12/31/2009 1:08:12 PM' and dmv_trans_dte <= '12/31/2009 11:59:59 PM'

The actual code to create this is:

txtSqlQuery.Text = txtSqlQuery.Text + " where dmv_trans_dte >= '" + dateTimePicker1.Text +
" " + dateTimePicker3.Text + "' and dmv_trans_dte <= '" + dateTimePicker2.Text + " " + dateTimePicker4.Text + "'";

When I try and save this I get an error and it is on centered on the "<" in the query.

Any ideas what is going on, why does it accept the ">" character?

Thanks in advance.

 

[jmeckley]

angle brackets are special characters. research xml to find the various ways to work around special characters.

why are you saving queries to xml through, especially if they are going to contain a user's password? and why are you using injected sql rather than parameterized queries?

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[prrm333]

I thought it had to do with special characters, I'll look into it.

The program allows the user to look at specific SQL tables. But in this case the user can type in there own queries and save them to a file if they use them consistenly. I was only using that query as an example as it contains the ability for the user to select a start and stop date/time from a calendar control if they want to set a date range.

 

[jmeckley]

unless the user has full control of writing the query, you would not need to save the actual query with parameters, only a pointer to the query and the parameters for that instance. for example, name the query "select all orders within a date range" with the parameters of start and end date. then persist the name and 2 date to disk (database, xml, txt, whatever). the actual query is stored in the application.

if the users have full control over the sql to write whatever they want, then it sounds like your application is re-inventing the wheel, as most/all databases have some from of query writer and you can save a sql query to a text file.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[prrm333]

The reason for saving queries to an xml file is to allow the user to also save queries to other tables within the database that are not hardcoded into the program. At this point there are 3 grids I created that go to 3 different tables with queries built based on selections from available textboxes. However there may be a need to query another table on a rare occation while saving the query when accessing that table. Also I have allowed the user to save multiple queries for this purpose.

I have with your suggestion found the following that allows me to save the query where I replace the < or > characters so it no longer errors on the save command. I am now using the following code prior to saving the xml:

userSQL = Regex.Replace(userSQL, "<", "&lt;");
userSQL = Regex.Replace(userSQL, ">", "&gt;");

I don't know if it's the best way but it works.

Thanks.

 

[whosrdaddy]

No need for regex!
Use CDATA sections to prevent parsing of the xml content.

here's more info:

http://www.w3schools.com/xmL/xml_cdata.asp

Cheers,

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!