In order to debug SQL many people open new windows in Query Analyser or Management Studio trying to see where the errors come from and opening transactions and rolling them back and basically be miserable.
Yet, even from Microsoft SQL 2000 stored procedures had debug support. You would use Query Analyser, open Object Browser, right click a stored procedure and select Debug.
However, in SQL 2005 you can't do that anymore. Query Analyser is no longer available, the Management Studio doesn't have debug options and the SQL 2000 Query Analyser doesn't allow you to debug stored procedures on SQL 2005 servers. But there is support for SQL debugging in Visual Studio .NET, in the Professional and Team versions. Let me rephrase: If you have the Express or Standard editions you are out of luck. No SQL 2005 debugging for you. I did some queries on the web searching for third party sql debuggers, maybe something from Microsoft, like their Javascript Debugger (which works better than the in-built javascript debugging in Visual Studio, btw)
Well, you can! But you need to use the sp_OA* stored procedures and VBScript. Here is a link to the user defined function that allows you to regex in sql: Regular Expressions in T-SQL
You may get this error while trying to use the OLE Automation : Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
The solution I've found is use sp_configure to enable Ole Automation Procedures like this:
EXEC sp_configure 'show advanced options', 1 -- make them available reconfigure
EXEC sp_configure 'Ole Automation Procedures', 1 -- turn on OLE reconfigure
EXEC sp_configure -- to see the new value EXEC sp_configure 'show advanced options', 0 -- make them unavailable reconfigure
I've spent two hours today trying to understand why my application works. I mean, I was glad it worked, but, based on my understanding of the ASP.Net cycle, it shouldn't have.
Long story short: in Page_Load I was instantiating and adding to the Page Controls collection some controls. To my astonishment, the postback worked! With events and everything. Even worse, even if the controls were being created every time (independent of IsPostBack) and the data was always added from the database, the postback data was overwriting it!
As far as I knew, the postback data is being loaded before the Page_Load event. And when you change something in the Page_Load, it stays changed. But things are different with dynamically generated controls. My guess is that the Page_Load method is being executed for the page, then recursively for the child controls. If a control has not yet retrieved it's postback data, it tries again.
Yet, if you try to do the same in a button event or in Page_LoadComplete, it doesn't work.
So, if you dynamically add the same controls in the Page_Load in between postbacks, the postback data will be saved and the events will fire.
I've updated this page a few times, adding more optimizations, so get the last version.
I was asked to find a way to maintain the vertical scroll in a selection box (that is a select html element with a size bigger than 1). I checked to see what property was changing when I was scrolling the select and I noticed that scrollTop was the one. So I used
sel.scrollTop=value;
.
Well, this works fine in FireFox and apparently works fine in IE7, too. However, in Internet Explorer 7, if you click on the up/down arrows of the select scrollbar, the scroll resets to 0. Worst than that, in IE6 you can't even set the scrollTop property. You don't get an error, but it doesn't work.
One suggested solution for people that tried the same thing is to set the size attribute to the number of options, thus getting rid of the select scrollbar, and place the select in a div with fixed height and overflow auto. It will look like a select listbox, but the scroll will be from the div and easily changed. I didn't choose this solution, basically because I felt it was cheating.
So, I've applied another solution, one that changes the selected item so that the select element scrolls itself to a position as close to the desired vertical scroll position as possible. Then, I set the scrollTop property, so that it goes at that exact position in IE7 and FireFox. If one clicks the up/down scroll arrows in IE7, the scroll position resets to the one found by the selectedIndex, not 0. And it works in IE6, too.
Here is the code:
Warning! This will not work if the select is hidden by way of display=none or visibility=hidden. Also, for large selects, it will look funny scrolling through all the options. Optimizations can be applied, that try to find the correct selectedIndex or stop after the scroll position has been found (like in this example) or that search the best scrollTop match by dividing the options in two parts rather than taking them one by one, etc.
In MS Sql when you try to insert or update a field value or parameter that is bigger than the defined size you get String or binary data would be truncated and the query fails. In order to get rid of this, you can use SET ANSI_WARNINGS OFF. But, warning (not ANSI :) ), there are some drawbacks that you have to be aware of before using SET ANSI_WARNINGS OFF :
putting SET ANSI_WARNINGS OFF in a stored procedure will force a procedure recompilation each time, so it is better to set it before the execution of the stored procedure
this setting will disable the indexes, so queries might run slower
no error will be generated when a division by zero appears and a null value is returned (this might not sound as a drawback, but think that you don't always want to return null in some cases and you want at least to be aware of them)
What SET ANSI_WARNING does: - disables the warning message from aggregated functions when finding null values - returns null on divisions by zero and arithmetic overflows - values are truncated when the length of the data is bigger than the defined size
Warning: this is only a partially working solution due to some Javascript issues described (and solved) here.
A requirement I had was to maintain the scroll position of ListBoxes on PostBack. The only solution I could find was to get the scroll through Javascript (the scrollTop property of the select) and restore it on page load, however, that would have meant a lot of custom controls, not to mention lots of work, to which I am usually against.
So, I used a ControlAdapter! The ControlAdapter is something new to the NET 2.0 framework. The Control in 2.0 looks for a ControlAdapter and delegates the usual methods (like OnLoad,OnInit,Render,etc) to the adapter. You tell the site to use an adapter for a specific type of control and possibly a specific browser type (by using a browser file), and it uses that adapter for all of the controls of the selected type and also the ones inherited from them. To disallow the "adaptation" of your control, override ResolveAdapter to always return null.
Ok, the code!
Of course, you will ask me What is that ScriptManagerHelper? It's a little something that tries to get the ScriptManager class without having to reference the System.Web.Extensions library for Ajax. That means that if there is Ajax around, it will use ScriptManager.[method] and if it is not it will use ClientScript.[method]. To.Int(object) is obviously something that gets the integer value from a string.
There is another thing, at the beginning I've inherited this adapter from a WebControlAdapter, but it resulted in showing all the options in the select (all the items in the ListBox) with empty text. The value was set as well as the number of options. It might be because in WebControlAdapter the Render method looks like this:
While working on a Windows app that used Crystal Reports 9 with Visual Studio 2003 on .NET 1.1 I've stumbled upon a problem. For no apparent reason, the saving of a report took minutes rather than seconds, the .cs file associated with the report sometimes disappeared and randomly errors like the one below appeared:
Custom tool error: "Code generator 'ReportCodeGenerator' failed. Exception stack = System.IO.FileNotFoundException: File or assembly name CrystalDecisions.CrystalReports.Engine, or one of its dependencies, was not found. File name: "CrystalDecisions.CrystalReports.Engine" at CrystalDecisions.VSShell.CodeGen.ReportClassWriter..ctor(String filePath) at CrystalDecisions.VSShell.CodeGen.ReportCodeGenerator.GenerateCode(String inputFileName, String inputFileContent)
I've searched the net and found a lot about wrong versions, deploying, etc. But I wasn't deploying anything, for crying out loud! First I thought it was about installing the Crystal Reports X runtime. I removed it, but that didn't solve anything. What was I to do?
Well, a temporary solution was to backup the .cs file somewhere, as it seemed not to change. Then I would just copy it back and add it to the project and it would work, but again, the time consumed by saving the report was huge! And there were caching problems. Sometimes I would see the report without the last modification in the application.
After a while I realised that actually I was deploying something. The project also had an installer, with the Crystal Reports .msm files. I remembered that I did install the application once, in order to test the installation process. Then, of course, I did remove it from the installed applications. That was it! All I had to do was to reinstall the application, thus repairing some Crystal Reports files or configuration that the uninstall process previously messed up.
One of my projects involves translation of pages and I have built a general framework that not only translates, but also tries to preserve the capitalization of text. For example, for English to Italian translations, MORNING would be translated into MATTINA and morning into mattina. But I've stumbled into an issue with ACTIVITY.
You see, the Italian for activity is attività, which would be translated into the UTF8 of html to attivitÀ or attività. Well, then how would one translate ACTIVITY? Maybe ATTIVITÀ? No! Because, apparently, both IE and FireFox (and possibly any other browser) don't count à unless it is in lowercase, even if, as far as I know, HTML should be case insensitive.
So watch out! Use the numbering system, it is more compatible.
I was looking for an answer to the problem of a grid inside an update panel. You see, since the rows and cells of a DataGrid or a GridView are special controls that can't be put inside panels, only in specific parent controls like tables and rows, there is no way to update only a row or a cell of a grid. If the grid is big, it takes a long time to render it entirely, it takes the CPU to 100%, it even blocks the animation of gifs. That results in ugly Ajax.
So, my first thought was: is there a way to update only what has changed? As I was saying in a previous post, a Page is rendered as its HTML string the first time it is loaded and then each Ajax postback makes it render like a list of tokens. The token format is this:
length|type|id|content|
For example 100|updatePanel|UpdatePanel1|<inner HTML of panel of 100 bytes>|
What if I would to insert my own tokens, then? Could I, let's say, change the innerHTML of a control outside of the UpdatePanel? And the answer is YES!
There are 20 token types:
updatePanel
hiddenField
arrayDeclaration
scriptBlock
expando
onSubmit
asyncPostBackControlIDs
postBackControlIDs
updatePanelIDs
asyncPostBackTimeout
childUpdatePanelIDs
panelsToRefreshIDs
formAction
dataItem
dataItemJson
scriptDispose
pageRedirect
error
pageTitle
focus
Most are not interesting, but 4 of them are!
type:updatePanel If you add a token to the rendered page string that has the typeupdatePanel and the id is the UniqueID or ClientID of a control, the content will replace the innerHTML of that control, even if the control is not in an UpdatePanel.
type:hiddenField If you add a token to the rendered page string that has the typehiddenField and the id is the UniqueID or ClientID of a control, the content will replace the value html property of that control. You can use it on hidden fields, but also on any type of input or html element that has a value. If the control does not exist, a hidden input will be created with that id and then the value will be set. You could read that value after a normal PostBack, let's say.
type:expando If you add a token to the rendered page string that has the typeexpando a script will be executed in Javascript that looks like this:
id=content
Example: 5|expando|document.getElementById('TextBox1').style.backgroundColor|'red'| This will result in the change of the background color of the control with id TextBox1 to red.
type:focus If you add a token to the rendered page string that has the typefocus and the content is a ClientID, then the focus will be set to that control provided that the focus.js script has been loaded. This script is loaded when you use Page.SetFocus. So, in order to set the focus to a control using this method, you must use SetFocus in PageLoad on any control you would like.
Why not use SetFocus, then, and be done with it? Well, because this, as all the methods above work on ANY control in the page, not just the ones in the update panel.
Of course, that doesn't solve my initial problem, of speeding up the Ajax rendering of large grids. That's because, even if I would solve the ViewState issues and the quirks that are bound to appear, I still can't change the innerHTML property of tables or table rows, as it is a readonly property.
So where am I to use this? It's easy: first of all, put a button (and only a button) inside an UpdatePanel. Any click on that button will trigger an Ajax postback, but will send a minimal amount of data. Then, put outside the UpdatePanel a Panel. Now you can override the Render of the page and on every Ajax postback, add whatever HTML you want to that panel. If you want to do it from javascript, put the button in a div with style="display:none" and then trigger the button click whenever you want to cause the postback. I am certain that for large readonly grids, that is a way faster method than the putting the grid inside the updatepanel.
To do this the traditional Atlas way you would have had to declare a web service, and then to set a javascript onclick event on the button, that would have executed a WebService method that returned a string, and manually change the innerHTML of the panel.
However, a problem arises. What happens if you have a library written in VB and having indexed properties and you try to use it in a C# project? Well, it works, and the generated code is something like:
public override bool get_PropertyName(string index) { } public override bool set_PropertyName(string index) { }
Somehow, this compiles :) Anyway, the problem now is that you can't use the VB code that used to use the VB library if you convert it to C# like this. I haven't found any way to do this: VBLib(with indexed properties)+VBApplication(inheriting or overriding indexed properties) -> C#Lib(translated)+VBApplication(unchanged).
I wanted to create this Javascript fly that would... well... fly on the screen. So the first thing I did is create an empty html, put a script tag in it, add some init function to the body and then write the code. First problem: how to get maximum height and width of the page in both IE and Mozilla. I found a way, then I added a more complex html code, like a DOCTYPE. Well, amazingly (duh!) it didn't work. Finally, after trying several options, I found this code to be working in both browsers and in both doctypes (or lack of). Please report any issues with it, so I can fix it. Thank you.
function maxHeight() { var h=0; if (window.document.innerHeight>h) h=window.document.innerHeight; if (window.document.documentElement.clientHeight>h) h=window.document.documentElement.clientHeight; if (window.document.body.clientHeight>h) h=window.document.body.clientHeight; return h; } function maxWidth() { var w=0; if (window.document.innerWidth>w) w=window.document.innerWidth; if (window.document.documentElement.clientWidth>w) w=window.document.documentElement.clientWidth; if (window.document.body.clientWidth>w) w=window.document.body.clientWidth; return w; }
You want to create a report with Crystal Reports and you create that weird ADO.Net DataSet, then you add a Crystal Report, you select the tables you want from the created DataSet, then you drag the Id references to create the table links, you go through all that weird Report Expert, you add a Crystal ReportViewer to a Windows Form and you press F5! And you get "Query Engine Error".
Well, two main reasons for this are explained in this nice article: "Query Engine Error" With Crystal Reports .NET, but you just created the report, there is no way you changed the XSD or the name of the tables.
I tried a lot of things until I found out what was going on. You see, I had this tables that had an "Id" column and a many-to-many table that had "UserId" and "MenuId" columns. In order to link them, I did what I also did in the XSD, I dragged the Id (primary key of each table) to the UserId and MenuId columns. That was the problem! You have to do it the other way around, drag the Foreign Key columns to the Primary Key columns.
Hopefully, you would have read this article before wasting hours to find out what the hell is that error and where it comes from... It happened to me with Crystal Reports 9.0 and Visual Studio 2003.
Oh! And don't bother to link the tables in the DataSet XSD, since Crystal Reports seems oblivious to that.
I am working on this windows app that uses Crystal Reports. I've never used it before and I thought "Hey! I could learn something new". So I opened this .rpt file in Visual Studio 2003 and I got the most incomprehensible interface ever. I mean, I will have to invest some hours just to understand what the report interface is all about.
But anyway, I thought I would take an existing report and then just change something, like adding a new field. I went with the mouse on an empty space, right click, context menu, Insert... I could insert text. I could insert Special Fields (report data like number of the page, creation date and so on). I could insert Fields. Only that option was disabled! I've tried every option in the context menu, no avail.
Finally I've decided I am to dumb to figure it out, I went to man's best friend: Google! The answer found in an obscure forum was: - Go to View (in Visual Studio 2003) - click Other Windows - click Document Outline
Now a Field Explorer window is open and I can see all fields and drag them to the report. That was it! :-/
When one wants to replace a text, say, case insensitive, one uses the .NET Regex class. In order to make sure the text to be replaced is not interpreted as a Regex pattern, the Regex.Escape method is used. But what about the replacement string? What if you want to replace the text with "${0}", which in Regexian means "the entire matched string" ? You need to somehow escape the replace pattern.
I have no idea where to find this information on MSDN, although I am sure it is hidden somewhere in all that Regex labyrinth. Here is the link on MSDN: Substitutions
So here is the info: You only need escaping the dollar sign, so the code would look like this:
Regex reg=new Regex("Text to replace",RegexOptions.CaseInsensitive);
In order to debug SQL many people open new windows in Query Analyser or Management Studio trying to see where the errors come from and opening transactions and rolling them back and basically be miserable.