Wednesday, October 04, 2006

Gluten Free Website technicalities driving me nuts!

For those awaiting updated posts on my gluten free blog, I hope you have patience. I have gotten so wrapped up in getting the recipe-postings section on my book site done that I have not put much time aside for anything else. And, as with nearly all programming "fun" (from experience), there is always some technical challenge to get past.

I have been hit with some of the most annoying "bugs" (or "features" as the software makers would probably label them) this week, particularly in getting my Recipe data into SQL-Server 2005 from a custom Win32 GUI using ADO Dataset Command Text values to pass my recipe-HTML from the GUI to the SQL Server. The sticking point was hit when my existing recipe-text (which I had in HTML already) contained a bunch of embedded quotation marks -- double quotes (vs. single quotes) were especially problematic. It is not the first time I have had to save such data to SQL-Server, but for some reason the normal methods for properly pre-processing those quotes was not working. I ended up using *single* quotes in my HTML around style names, etc., even though I do not think the W3C would see that as meeting their standards. IE/Firefox still accepts the end-result, but I am still miffed that I can not get the double-quotes to save to the database properly.

More annoying is how the command-text I am sending from the GUI to the DB will execute just fine if I copy/paste the command-text-string (containing the double-quotes,etc) into SQL's Query Analyzer window and execute it directly (without the GUI getting involved in the matter, and no ADO layer in between). That tells me there is something more going on here.

Well, I may revisit this one if issues persist. For now, the single-quote workaround is in place, and I am sticking with it.

Next challenge: Dealing with shortcomings of SQL 2005's IDE! Argghh. And, DTS (which is now SSIS or such), is frustrating me when I try to copy objects to my web-host from a local DB. The tool is copying table structures/data over, but omitting all referential integrity, keys, indexes, etc., and being new to SSIS I have yet to discover an option to enable that (why it wouldn't be obvious, I do not know -- hopefully there IS just an option to switch as I want!).

1 comment:

Mike Eberhart said...

Although the "solution" to my SQL parameter passing is a bit overly complex for my liking, it does work.

Basically, the following (using a TADODataSet in Delphi), does the job (too bad this blog doesn't seem to INDENT anything I pasted into here):

with ObjectDataSet do
begin
Active := False; //close dataset
CommandText := 'uspTestProc_ManageInsertsUpdates_TEST';
Prepared := True;
CommandType := cmdStoredProc;

with Parameters do
begin
Clear;
CreateParameter('@iTestID', ftInteger, pdInput, 0, TestID);
CreateParameter('@sTestName', ftString, pdInput, 120, TestName);
CreateParameter('@sTestText', ftString, pdInput, SQLVarcharMaxConst, TestText);
end;

try
Screen.Cursor := crHourglass;
Active := True;
...