Special characters in Mysql fields

You should use the following syntax to create a CSV file in the format expected by Microsoft Excel:

... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

However fields with carriage returns may break the CSV as MySQL will automatically close a field when the \r\n line break is found. To work around this, replace all \r\n breaks with \n. The field does not close on \n breaks and it will be read into a single cell in Excel. You can do this in the same SQL statement, for example:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I also found that null values could break the CSV. These can be handled in a similar way:

SELECT IFNULL(possible_null_field, "") FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Note: this replaces NULL values with an empty string which is technically not the same thing but it will give you an empty cell in Excel instead of breaking the CSV structure and shifting the following cells to the left.

Option Strict Discrepancy

I have run into programs where I have made a change and when I recompile them, I get a message similar to "Option Strict On disallows conversion from x to y".
The problem is the program has always been this way, and now the Option Strict On is getting invoked at some point that it had not been invoked in the past. I tried setting the Option Strict Off in the Project Properties. I also added an OPTION STRICT OFF to the top of the program. But the compiler was still using the "OPTION STRICT ON" directive.

The first time I ran into the problem, I just fixed to program to work with Option Strict ON. But then I ran into similar program fragments and decided to try and figure out where the problem was.
I found that the programName.aspx.designer.vb file had an OPTION STRICT ON which took precedence over the Source File OPTION STRICT OFF line. Change the programName.aspx.designer.vb to OPTION STRICT OFF and the program compiles as it always has.
Now I don't know how the designer is getting changed, but at least I know where to look when this occurs in the future.

Setting OutputCache in Web.Config

<outputCache enableOutputCache="true|false" 
             enableFragmentCache="true|false" 
             sendCacheControlHeader="true|false" 
             omitVaryStar="true|false"
             defaultProvider="AspNetInternalProvider">
</outputCache>

Using Request.InputStream multiple times in .Net program

I wanted to use the Request.Inputstream for multiple things in a program, but when I went to use it the second time, nothing was returned. I found you need to set the position parm to zero, before using the Request.InputStream on subsequent calls.

Example:

dim inXML as string
inXML=Request.InputStream

dim inXML2 as string
Request.Inputstream.Position=0
inXML2 =Request.InputStream

The second variable will contain the same thing as the first.

Some ideas on retrieving data from a DataReader

Retrieving Values from a DataReader

By Nannette Thacker

There are several ways to retrieve values from a DataReader. 

The first method is by using the index and looping through the fields in order as retrieved within the Select statement. Starting with the 0 index, you may use the GetValue() Function. 

The following code snippets are in VB. A C# snippet is provided at the bottom. 

reader = objCommand.ExecuteReader()
While reader.Read()

If Not reader.GetValue(0) Is DBNull.Value Then _
    lastname = reader.GetValue(0)
If Not reader.GetValue(1) Is DBNull.Value Then _
    firstname = reader.GetValue(1)

End While
reader.Close()
However, this makes the code harder to read, and if a field is added or removed from the query, it could break your code. So let's look at how to retrieve the values by field names. 

If your reader is using an OleDbDataReader, we use the reader.Item function. 

If Not reader.Item("lastname") Is DBNull.Value Then _
    lastname = reader.Item("lastname")
If Not reader.Item("firstname") Is DBNull.Value Then _
    firstname = reader.Item("firstname")
If your reader is using a SqlDataReader, we use the reader.GetItem function. 

If Not reader.GetItem("lastname") Is DBNull.Value Then _
    lastname = reader.GetItem("lastname")
If Not reader.GetItem("firstname") Is DBNull.Value Then _
    firstname = reader.GetItem("firstname")
Here is a C# example: 

if (reader["lastname"] != DBNull.Value)
    lastname = reader["lastname"].ToString();
A reader of my column, Zac, suggests that reader("lastname") instead of reader.Item("lastname") will also work. 

He is correct. For the DataReader class implementation, Item is the default property and may be left out. You may access an indexer by using the () operator on the base class object or you may access an indexer by using the Item property. 

Another reader of my column, Marneus, pointed out the GetOrdinal option as well. He said "there is a performance hit, each time you use the reader("lastname"), it has to check the meta datas to get the column number." 

Readers may refer to this document on the GetOrdinal() 

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx 

Please note this comment on efficiency: 

"Because ordinal-based lookups are more efficient than named lookups, it is inefficient to call GetOrdinal within a loop. Save time by calling GetOrdinal once and assigning the results to an integer variable for use within the loop." 

From the article, here is the usage: 

' Call GetOrdinal and assign value to variable.
Dim customerID As Integer = reader.GetOrdinal("CustomerID")

' Use variable with GetString inside of loop.
While reader.Read()
    Console.WriteLine("CustomerID={0}", reader.GetString(customerID))
End While
Another reader, AndrewSeven, pointed out the cost for hitting the reader twice for each field. He suggests: 

object val = reader["lastname"];
if (val != DBNull.Value)
{
   lastname = val;
}
And also comments "If your concern is readability, you should probably encapsulate the null test so you can write: 

lastname = GetValueFromReader(reader,"lastname");"
Travis responded with this suggestion: "Just do this, it handles nulls nicely unlike .ToString():" 

lastname = Convert.ToString(reader["lastname"]);
So I tested his theory by creating columns that would not allow null values, and imported columns with null values. In VB it is written like so: 

lastname = Convert.ToString(reader("lastname"))
firstname = Convert.ToString(reader("firstname"))
That is much cleaner! Thanks everyone for your great input! 

May your dreams be in ASP.net!

Nannette Thacker

Iterating through a dictionary in VB .Net

For Each myKey As KeyValuePair(Of String, String) In dicDataValues
    Dim sColumn As String
    Dim sValue As String
    sColumn = myKey.Key
    sValue = myKey.Value.ToString
    Dim dr As DataRow
    dr = dt.NewRow
    dr("ColumnName") = sColumn
    dr("Value") = sValue
    dt.Rows.Add(dr)

Next

Creating case insensitive Dictionary in VB .net

To create a dictionary in VB .Net use the following:

Dim dicValues As New Dictionary(Of String, String) StringComparer.CurrentCultureIgnoreCase)


Normally the dictionary is case sensitive.

Navicat SQL storage location on PC

Here is the physical location that Navicat 9.1.8 stores the Query files:

c:\Program Files (x86)\PremiumSoft\Navicat MySQL\*databasename*\

*databasename* represents the name of each data base, so there will be a directory per database
where there are queries defined


I also found another directory that houses sql files.

\users\user\My Documents\Navicat\MySQL\servers\ServerName\DatabaseName

I don't know why there are two locations, unless an earlier version of Navicat created these files in one spot and now creates them in the new one.

But look around in these places if you can't find the files you are expecting

Publishing a solution to a site

When publishing a solution to Production, you must take care to update the web config files, as probably you now have the test data base connection strings in the Production system.

Adding programs to Production solution

When adding programs to a Production solution from the development server, not only do you have to copy the files to the proper directory, but you must add them to the solution in Visual Studio.
If not, when your program tries to load the program, you will get a run time error indicating that the resource could not be loaded. This is because it has NOT been compiled and is NOT in the DLL file.

Finding Control on A Page Using Javascript

Here the URL because the Javascript does not seem to appear.

http://www.krissteele.net/blogdetails.aspx?id=92

Enable/Disable All Buttons in a GridView using JavaScript
 9/9/2008      ASP.net      (2)

Sometimes you might find yourself in a situation where you have a GridView that has a number of buttons that you need to enable or disable all at once and don't want to have to force a post back on your users in order to loop through them all. Thankfully with a little client side magic (JavaScript), it's very easy to do just that. 

When a GridView renders on the page, it is simply an HTML table (if you don't believe me, view the source of a page with a GridView yourself) and you can traverse it just like you would any other HTML table using JavaScript. 

Below is code to do a basic version of this in order to enable or disable all buttons in a GridView. First the code gets an instance of the GridView using the ClientID of the GridView (to avoid ID mangling) then create a collection of all input controls in the GridView. Next it loops through each input control and checks the type (this is necessary because input controls include not only submit buttons but also things like textboxes). Finally we either enable or disable the submit button. 


script type="text/javascript" language="javascript"
function disableAllButtons() 
{
    var gridViewID = "<%=gvTest.ClientID %>";
    var gridView = document.getElementById(gridViewID);
    var gridViewControls = gridView.getElementsByTagName("input");

    for (i = 0; i < gridViewControls.length; i++) 
    {
        // if this input type is button, disable
        if (gridViewControls[i].type == "submit") 
        {
            gridViewControls[i].disabled = true;
        }
    }
}

function enableAllButtons() {
    var gridViewID = "<%=gvTest.ClientID %>";
    var gridView = document.getElementById(gridViewID);
    var gridViewControls = gridView.getElementsByTagName("input");

    for (i = 0; i < gridViewControls.length; i++) {
        // if this input type is button, disable
        if (gridViewControls[i].type == "submit") 
        {
            gridViewControls[i].disabled = false;
        }
    }
}
/script>
By changing the value in the getElementsByTagName, it is easy to get things like all the dropdown boxes in a GridView. You can even grab TR and TD elements and walk through each cell of your GridView. I just showed you a very basic example of this, but with a little fine-tuning you can control most any aspect of your GridView. 

I found this particular code useful to include along with the AJAX Animation Control Extender. OnLoading of pages, I can disable all controls on the page to prevent users from clicking something before the page is ready or while a save is occurring and then use the OnLoad event to re-enable all the buttons. 

I haven’t verified this to be sure, but I believe you can port this code over exactly for DataGrids and probably any other Grid or List control. As long as they render as HTML tables (DataGrids do for sure), it should work. 

Click here for a demo of this code in action. This code is simply using a GridView with data from a previous demo I did along with a template column that includes a button in each row. 

-->       

    I modified the above to disable all the input capable fields, so that when the user presses a button and expects some action, all the other buttons are disabled so that, if the system is unresponsive, they will not keep hitting buttons, queuing up more unwanted actions.

        function ChangeAllButtonStates(enableState)
        {
            //var buttonArray = document.getElementsByName("btnComment");
            var buttonArray = document.getElementsByTagName("input");

            for (var a = 0; a < buttonArray.length; a++)
            {
                var myType = buttonArray[a].type;
                var myButton = buttonArray[a];
                var myName = myButton.Name;
                
                if (myType == "submit" || myType == "button")
                {
                    buttonArray[a].disabled = enableState;
                }
            }
        }

Using UpdatePanel

I had to modify a program where all the data was loaded from the client side using XML and tables. The things I needed to add had already been done in another system and was implemented using the server side code. So, I had three options.
1) Manipulate the new additions from the client side, which would mean I had to start from scratch for that code, since it was server side code.
2) I could move the old stuff to the server side, which would necessitate creating server side code.
3) Leaving the old stuff alone and adding the new stuff on the server side. I chose this option. But it presented some challenges of its own, name managing the state when adding the new fields on postback.
I looked for various options and finally determined to use the UpdatePanel. This would allow me to only update the section of the screen I needed to and the remaining portion of the screen would not change.
Basically, you wrap you controls in the ContentTemplate option of the UpdatePanel and define the triggers that will cause the update for the portion of the screen we are updating.
Another work around involved how the original program functioned. The client code would execute the load mechanism to populate the screen when the program loaded. And because everything else was handled on the client side, it was only invoked 1 time. However, once you start calling server side, this code gets executed on each postback. So, I had to move this code to a function and use the client script manager to invoke this code only on program load.

Asp .Net UpdatePanel Info

http://ajax.net-tutorials.com/controls/updatepanel-control/

doPostBack Javascript and .Net implications

Here is an article about calling the dopostback routine in your javascript code.
However, if you only have button controls, the dopostback is not included in your javascript and the eventarguments are not defined.
Provider

Monday, June 16, 2008
Understanding the JavaScript __doPostBack Function
In this article we going to understand the the functionality of __doPostBack function of JavaScript.

_doPostBack Function
Let us take a look at the function.
Listing 1 - _The __doPostBack function


function __doPostBack(eventTarget, eventArgument) {
if (!theForm.onsubmit || (theForm.onsubmit() != false)) {
theForm.__EVENTTARGET.value = eventTarget;
theForm.__EVENTARGUMENT.value = eventArgument;
theForm.submit();
}
}
Description
The __doPostBack function takes two arguments, eventTarget and eventArgument. The eventTarget contains the ID of the control that causes the postback and the eventArgument contains any additional data associated with the control. Note that the two hidden fields, “__EVENTTARGET” and “__EVENTARGUMENT,” are automatically declared. The value of the eventTarget and eventArgument are stored in the hidden fields. The two hidden variables can be accessed from the code behind using the forms/params collection.
Using the hidden variables you can also find the ID of the control which causes the postback. All you need to do is to retrieve the value of the __EVENTTARGET from the form parameter collection. Take a look at the code below.
Listing 2 – Getting the _EVENTTARGET hidden field
protected void Page_Load(object sender, EventArgs e)
{
string controlName = Request.Params.Get("__EVENTTARGET");
}
Description
For this code to work you need to add any web server control on the form except for Button and ImageButton control (Discuss it later). Let us add the DropDownList control and set the AutoPostBack property to true and populate the DropDownList with some dummy data. Now, run the page and view the source of the page.
You will see the following line of code.
Listing 3 – DropDownList calling __doPostBack function

The onchange event of the DropDownList calls the __doPostBack function. The ID of the control, “DropDownList1,” is also passed to the _doPostBack function and stored in the _EVENTTARGET hidden field. In the Page_Load I fetch the value of the _EVENTTARGET variable which in this case is the ID of the DropDownList. This way we can find out that which control caused the postback.
What about Buttons and ImageButtons?
You might be wondering about the POSTBACK triggered by the Buttons and the ImageButtons. Well, let us see the code generated by the Buttons.
Listing 4 – Code generated by the Button server control

As demonstrated in the code above, the Button control does not call the __doPostBack function. Because of this, the _EVENTTARGET will always be empty. However, you can find out the ID of the Button by looping through the form controls collection. Take a look at the code below.
Listing 5 – Finding the Button control in the form collection
foreach (string str in Request.Form)
{
Control c = Page.FindControl(str);
if (c is Button)
{
control = c;
break;
}
}
Description
In the code above I iterated through the controls on the page. If the control is of type Button then the loop breaks and the control is returned back to the user.
Passing Arguments
If you look closely at the __doPostBack function you will notice that the second argument is called the eventArgument. You can allow controls to pass arguments to the doPostBack function. Check out the code below.
Listing 6 – Passing arguments to the __doPostBack function


string passedArgument = Request.Params.Get("__EVENTARGUMENT");
Description
The “Button2” when clicked fires the DoPostBack function which in turn calls the __doPostBack. The __doPostBack function contains two arguments, eventTarget and eventArgument. The eventTarget is “Button2” and the eventArgument is “My Argument.” Later, in the C# code behind, I have accessed the eventArgument using the Request.Params collection. The passedArgument variable will contain the value “My Argument.”
Thanks to aspalliance.com
Posted by Spanco at 2:19 PM
Labels: Javascript

Formatting Source Program Code in BlogSpot

Use the <pre> tags for Source Code.

Crystal Reports - various input paramaters

Here is a way to have multiple input parameters specified.
In each of the below scenarios, if no parm is specified for the field, its value is set to true. Otherwise the actual condition is evaluated returning the appropriate true or false. Then all of the parms are "ANDED" together to get whether a record is selected or not.

(
    if {?sDispositions} <> "" then
        {purchaseorders.sDisposition} in {?sDispositions}
    else
        true
)
and
(
    if {?sFrom} = "" then 
       true
    else
       Date({purchaseorders.dtLastUpdate}) >= Date({?sFrom})
)
and
(
    if {?sTo} = "" then 
       true
    else
       Date({purchaseorders.dtLastUpdate}) <= Date({?sTo})
)
and 
(
    if {?sServicers} = 0 then
        true
    else
        {purchaseorders.iServicer} in {?sServicers}
)

Change Datagrid view data alignment

To change the alignment for specific columns in a datagridview, use the following:


dgvSummary.DataSource = dtSum
With dgvSummary.Columns
.Item(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight
.Item(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight
End With

dgvSummary.Refresh()

Adding Columns To a DataTable and Retrieving Data From Another Table

I had an instance where I needed a couple of columns from a table that was not efficiently retrieved using a Join with MYSQL, because the Key field was not a key in the secondary file, necessitating a sequential read through the file for each of the primary records. So, I decided to read the inefficient table into a datatable, and then as I processed my main datatable, to find the appropriate rows and stuff its data into the primary table. This program needed to output a CSV file, so it made more sense to add the columns to the primary table than to add additional logic to output the extraneous fields.
Also, there was a field used only as a link between the tables and it should not be emitted on the output operations, so logic is added to bypass this field while creating the CSV column and data lines.
Only the last row added from the inefficient table was to be selected for the CSV file, so we checked to see if any records were selected and then took the first record.
By using a SQL join between the tables, it took nearly 20 minutes to run. Setting up and using the below method resulted in completion in just a few seconds. Quite the improvement.



Dim dcComment, dcCommentDate As DataColumn
dcComment = New DataColumn("Comment", GetType(String))
dcCommentDate = New DataColumn("CommentDate", GetType(Date))

' add the comment fields to the PO Data Table
dtPO.Columns.Add(dcComment)
dtPO.Columns.Add(dcCommentDate)

Dim CSVsb As New StringBuilder(200)

Dim fieldcount As Integer = dtPO.Columns.Count
Dim CertOrdinal As Integer
CertOrdinal = dtPO.Columns("Certificate").Ordinal

For i = 0 To fieldcount - 1
If i = CertOrdinal Then
Continue For
End If

If CSVsb.Length > 0 Then
CSVsb.Append(",")
End If

CSVsb.Append(Chr(34) & dtPO.Columns(i).ColumnName & Chr(34))
Next

mobjFileInfo = New FileInfo(strPath)
'mobjFileInfo.CreateText()
Dim fileWriteStream As StreamWriter = mobjFileInfo.AppendText()

Dim sSort As String
sSort = "CommentDate Desc"
fileWriteStream.WriteLine(CSVsb.ToString)

For Each myDataRow As DataRow In dtPO.Rows
CSVsb.Length = 0

'Retrieve Comments for this PO
Dim sCert As String
sCert = myDataRow("Certificate")
Dim sSelect As String
sSelect = String.Format("Certificate = {0}", sCert)
Dim foundRows As DataRow() = dtComments.Select(sSelect, sSort)

If foundRows.Length > 0 Then
Dim myCommentRow As DataRow
myCommentRow = foundRows(0)
myDataRow("Comment") = myCommentRow("Comment")
myDataRow("CommentDate") = myCommentRow("CommentDate")
End If

For i = 0 To fieldcount - 1
'do not output the Certificate, only used to retrieve Comments
If i = CertOrdinal Then
Continue For
End If

If CSVsb.Length > 0 Then
CSVsb.Append(",")
End If

CSVsb.Append(Chr(34))

If Not (IsDBNull(myDataRow.Item(i))) Then
Dim sValue As String
sValue = myDataRow.Item(i)
CSVsb.Append(sValue.Replace(Chr(34), ""))
End If

CSVsb.Append(Chr(34))

Next

fileWriteStream.WriteLine(CSVsb.ToString)

Next

Retrieving Last Inserted ID From MYSQL from C#

After inserting a record into the database, to retrieve the ID created on the insert, use the following code:

OdbcCommand1.CommandText="SELECT LAST_INSERT_ID()";

Int64 LastID;
LastID = Convert.ToInt64(OdbcCommand1.ExecuteScalar());

GridView Processing in ASP .NET

Look at DocView for handling gridview data in ASP .Net. The DataItem is not available on postback per an ASPNet Forum post. Either use the cell.text data or set up the datakeys items of the gridview.

WIndows 7 - ODBC32 Drivers Visual Studio Projects

To use the old ODBC 32 bit drivers in .net programs, you must change the CPU to x86 in the project you want to use these drivers.
Right click on the project.
Select Compile
At the bottom of the page, select the "Advanced Compile Options"
Select the Target CPU and select the x86 option

When you run the resulting code, the program will use the 32 bit drivers.

VB Constructor - Calling base constructor on overloaded new methods

You can have multiple ways to create a class, passing various parameters when you create a 'new' instance of the object. In the class constructor, you use the
Me.New() to call the base constructor, or any of the constructor signatures

public class MyClass
dim var as string
dim i as integer
dim sConnString as string

public sub New()
var = "Init"
end sub

public sub New(vInt as integer)
Me.New() ' calls base constructor
i=vInt
end sub

public sub New(vInt as integer, vConnectionString as string)
Me.New(vInt)
sConnString=vConnectionString)

end sub

ASP TextBox/Label Box Will Not Get as small as you want it

I was trying to get a small label aligned on my form, but no matter what I did, the label was much larger than what I had specified in the HTML. I tried various things but still could not get it to appear the correct size in Visual Studio, though it did look the correct size when I ran the code. I finally realized that I had named the field a large number of characters and the name was displayed on the design screen, as large as necessary to display the ID of the label. I changed the size so that the name was 3 characters and the label appeared the correct size. So watch out for the length of the ID's you use especially when trying to size a small field.

.net Formatting Strings - Fixed Width

I always forget the specific syntax for formatting strings into a specified width. When I google, I always get number and date formatting, but have to really look for the STRING format.

So here it is for my easy reference:)

String.Format("{0,20}", "String goes here");
String.Format("{0,-20}", "String goes here");

Using clientscript.registerstartupscript/registerclientscriptblock Gotchas

If you use the above methods to invoke functions already defined in the aspx page, then you need to be aware of where those functions are defined, because if you try to invoke the functions and they have not yet been created, then you will get a null object message.

Even if you use the Client.registerstartupscript which places your script just above the closing form tag, if the functions you are invoking are also defined in this block of code, your code will produce an error.

So make sure your functions are already defined relative to where your server side script code is placed within the web form.

The safest place to put these functions is in the header block.

Disable Caching

base target="_self" /
%@ OutputCache Duration="1" VaryByParam="*" %


put these statements before anything else in the head section


The outputcache duration=1 - sets caching for 1 second

How To Disable A Button in ASP.Net so that the click event is passed to Code Behind

here's how to get around the disable/asp.net even problem.

add a javascript function called function disableControlls(){} (or whatever u want to call it) then call that from the body event "onbeforeunload" so your body tag would look something like this:

<body MS_POSITIONING="GridLayout" onbeforeunload="disableControlls();"> the original values for the controls still post back, as well as events before the unload event, so when you disable the controls at that point there is no adverse effect.

Nathan Maffeo
Friday, December 03, 2004

Calling Modal Form Spawns new windows Fix Asp.Net

New windows were being opened when I was doing postbacks on programs invoked using window.showmodaldialog.

The solution is to add this code to the <Head> section of the page.


<base target=_self>

This solved the problem. No more extraneous windows!!!!