Monday, January 24, 2011

Bulk Insert Stored Procedure not returning 0 row(s) affected (Error)

Lately,

A while ago, a friend of mine sent me a CSV file whose fields are separated by comma. I followed a link from http://www.sqlteam.com in creating a stored procedure via BULK INSERT. This complex stored procedure inserts rows from a csv file to a database table with a field defined as primary key (identity seed option set).

After changing the script from the website and executed the SP below:
1:  use clientdb  
2:  go  
3:  EXEC Customer_Import 'D:\Csharp progs\Orders\Client.csv', 2  

The message returned in the result pane was: 0 row(s) affected. Wow, how could this happend where in fact, the csv has 50 rows? I've changed other options to the SP but got no luck. The solution i ran into was to re-open the CSV file and save as another file. Ex. (from Client to Client1). So, when i executed the SP above using the other file, it performed perfectly.
 use clientdb  
 go  
 EXEC Customer_Import 'D:\Csharp progs\Orders\Client1.csv', 2  

So, I guess there must be a problem with the original file, like corrupt data or incorrect data format.

Saturday, January 22, 2011

Printing Checkboxes in Crystal Reports.NET

Source: Crystal Reports


To display a checkbox on the report, create a new formula field and insert the following formula.
Code:
  If {Field} = True Then  
       'Display the checkbox of your choice here  
       Formula = Chr(254)  
  Else  
       'Display empty checkbox  
       Formula = Chr(168)  
  End If 

Thursday, January 20, 2011

Create a database schema in SQL Server

Source: http://msdn.microsoft.com/en-us/library/dd207005.aspx

In recent versions of sql server specifically 2005 and 2008, the database called Adventure works seems to be a good example with lots of tables to play with.

When I tried to open the database, the tables have prefix or have namespace before it.
Example: HumanResources.EmployeeAddress

In the previous example called northwind and pubs,table names were just one word.

So, to create a schema, here's how to do it:
1:  create schema <schema_name>  
2:  go  
3:  alter schema <schema_name>  
4:  transfer <dbo.yourtable>   
5:  go  
6:  -- show table  
7:  select * from schema_name.tablename  

Monday, January 17, 2011

Ambigous column name in TSQL Query

If you encounter this error in your TSQL query embedded in C#, you might want to check the table it is being joined. It might be that some field in the other table has the same fieldname.

Example:

Orders - status varchar(90)
OrdersSubForm - status varchar(30)

Just make sure, you create a prefix of the table name plus the dot and append it in your field. Just like the example below, for the status field.
1:  select   
2:   Orders.status,  
3:   OrderNum  
4:  from Orders  
5:  right outer join OrderSubForm  
6:  on OrderSubForm.ID = Orders.ID  
7:  where (your condition here)  

Saturday, January 15, 2011

Sort datatable in .NET

Sorting a data table before binding to a data control like gridview or datalist is provided by microsoft through it's predefined methods which is defaultview.sort.
Example:
Code:
  //C#  
  dt1.DefaultView.Sort = "Name Desc";  
  //VB.NET  
  dt1.DefaultView.Sort = "Name Desc" 
Where dt1 is your datatable. Or you could create a dataview object as provided by MSDN.
Source: MSDN

Tuesday, January 11, 2011

PopupControlExtender GetProxyForCurrentPopup not showing in Intellisense

I was wondering why when I type PopupControlExtender. nothing happens. Supposedly, GetProxyForCurrentPopup method will show.
The solution was so simple, I forgot to include the namespace of AjaxControlsToolkit.
Code:
  //add namespace AjaxToolkit  
  using AjaxControlToolkit;  
  //call commit method  
  PopupControlExtender pce =   PopupControlExtender.GetProxyForCurrentPopup(this);    
  pce.Commit("hi");

Monday, January 10, 2011

JavaScript OOP using Prototype

In JavaScript, each Object can inherit properties from another object, called it's prototype. When evaluating an expression to retrieve a property, JavaScript first looks to see if the property is defined directly in the object. If it is not, it then looks at the object's prototype to see if the property is defined there. This continues up the prototype chain until reaching the root prototype. Each object is associated with a prototype which comes from the constructor function from which it is created. (Source: http://mckoss.com/jscript/object.htm)

Here's an example i created:
1:  <html>  
2:  <head>  
3:  <script type="text/javascript" language="javascript">  
4:  function NumberManip()  
5:  {  
6:   var result = 0;  
7:  }  
8:  NumberManip.prototype.add = function(num1, num2) {  
9:   this.result = num1 + num2;  
10:  }  
11:  NumberManip.prototype.subtract = function(num1, num2) {  
12:   this.result = num1 - num2;  
13:  }  
14:  //define toString(), similar to C# overriding ToString() function  
15:  NumberManip.prototype.toString = function()  
16:  {  
17:   return this.result;  
18:  }  
19:  function UseOOPScript()  
20:  {  
21:   var onj = new NumberManip();  
22:   onj.add(5,5);  
23:   var onj1 = new NumberManip();  
24:   onj1.subtract(6,3);  
25:   alert("Result of addition: " + onj + " Subtraction: " + onj1 + "");  
26:  }  
27:  </script>  
28:  </head>  
29:  <body>  
30:  <div>  
31:    <input type="submit" onclick="UseOOPScript()" value="Submit" />  
32:  </div>  
33:  </body>  
34:  </html>  

Based from the code above, we created two objects onj and onj1.
After that, each object references to a method of NumberManip.
The NumberManip acts
as a constructor when you declared onj and onj1 objects.

Inner joins in TSQL

Source: TSQL Programming Reference:

An inner join applies two logical query processing phases—it applies a Cartesian product between the two input tables like a cross join, and then it filters rows based on a predicate that you specify. Like cross joins, inner joins have two standard syntaxes: ANSI SQL-92 and ANSI SQL-89.

Using the ANSI SQL-92 syntax, you specify the INNER JOIN keywords between the table names. The INNER keyword is optional because an inner join is the default, so you can specify the JOIN keyword alone. You specify the predicate that is used to filter rows in a designated clause called ON. This predicate is also known as the join condition.

Here are two basic implementations of inner join scripts i created, one with the inner keyword, and the other w/o the inner keyword.


1:  select distinct l.LoanNum, (c.CusLastName + ', ' + c.CusFirstName + ' '  
2:     + c.CusMiddleInit) as Name, Status, DateGranted, AmountGranted  
3:            from loanmaster l  
4:            join customer c on c.CustomerID = l.CustomerID  
5:            where l.loannum = 4  
6:  select distinct l.LoanNum, (c.CusLastName + ', ' + c.CusFirstName + ' '  
7:     + c.CusMiddleInit) as Name, Status, DateGranted, AmountGranted  
8:            from loanmaster l  
9:            inner join customer c on c.CustomerID = l.CustomerID  
10:            where l.loannum = 4  

Friday, January 7, 2011

GridView rowupdating event not getting new values from controls

I have a code in rowupdating event which get's values from textbox controls during edit mode. However, when update command button is clicked, the values from the controls were cleared or null. So, I checked the page load event, and found out that I have a code which rebinds the grid when it is not post back since the update link button will trigger postback.
Here is the page load code:
Code:
if(!string.IsNullOrEmpty(Request.QueryString["LoanNum"]))  
 {  
  ShowToControls(Convert.ToInt32(Request.QueryString["LoanNum"]));  
  BindGridDetails(Convert.ToInt32(Request.QueryString["LoanNum"]));  
 }  
So, the modified pageload code should add a IsPostback condition so that the binding of gridview will not be executed.
Here's the modified script:
Code:
if(!string.IsNullOrEmpty(Request.QueryString["LoanNum"])  
  && !Page.IsPostBack)  
 {  
  ShowToControls(Convert.ToInt32(Request.QueryString["LoanNum"]));  
  BindGridDetails(Convert.ToInt32(Request.QueryString["LoanNum"]));  
 }  

Thursday, January 6, 2011

Online Syntax Highlighter for code posts

As I reviewed my blog, the code snippets seems to be a mess and unpresentable. So I did some googling earlier and found some online syntax highlighter that may come in handy.

http://tohtml.com/jScript/
http://quickhighlighter.com/

Wednesday, January 5, 2011

Suffix to cast numeric literal numbers to decimal value

Usually, i do decimal.parse(numeric value) to parse numbers to decimal. But there's a shortcut way to do this. Just append a suffix m or M to the numeric value.
Code:
decimal balanceBank = 50000.5m;  
decimal balanceBank = 50000.5M; 
Source: MSDN

Monday, January 3, 2011

URL Rewritting Resolve URL in my external CSS and JavaScript Files

I have applied url rewritting from scott: URL Rewriting Tutorial to make my url's SEO friendly.
However, my css and javascript files are created internally (w/ in the page itself). When i tried separating my css and .js files, I usually encountered javascript errors such as:

1. GetCustomerDetail is undefined [based from Mozilla Firebug]
2. Microsoft JS runtime error: object required [Internet explorer]

I tried googling for hours about object required and have'nt found an answer. As i read again scott's article, the last paragraph was a hint regarding how to resolve CSS and image external links.

The solution I came up with was using Control.ResolveUrl() so that my external javascript and css files will work.
1:  src='<%= Page.ResolveUrl("yourjsfile")%>'  
2:  href='<%= Page.ResolveUrl("yourcssfile")%>'  
A good resource, but haven't tested yet. Westwind Tutorial
FROM MSDN: MSDN

Set focus an asp.net control using Page setfocus method

Most of the time, i usually have a javascript function to set focus a control during pageload. The control.focus() most of the time does not work. But in an asp.net Page control, there is a handy method to focus.
Code:
Page.Setfocus(Control.ClientID); 
where Control could be a textbox or button.

Saturday, January 1, 2011

Highlight GridView row using javascript

Here's the code:
Code:
  if(e.row.rowtype == DataControlRowType.DataRow)  
  {  
     e.Row.Attributes.Add("onclick","document.getElementById('"   
    + e.Row.ClientID + "')  
     .style.backgroundColor = 'green';");  
  }  

Structures in C#

Before migrating to .NET, i used to code a lot in C++/C.. One of the topics in data structures was structures .C# also has struct in its arsenal.

A structure is similar to a class, but is a value type, rather than a reference type. Structures are declared using the keyword struct and are syntactically similar to classes. Here is the general form of a struct:
Code:
struct name : interfaces
{
   // member declarations
}
The name of the structure is specified by name.
Structures cannot inherit other structures or classes, or be used as a base for other structures or classes. (Of course, like all C# types, structures do inherit object.) However, a structure can implement one or more interfaces. These are specified after the structure name using a comma-separated list. Like classes, structure members include methods, fields, indexers, properties, operator methods, and events. Structures can also define constructors, but not destructors. However, you cannot define a default (parameterless) constructor for a structure. The reason for this is that a default constructor is automatically defined for all structures, and this default constructor can’t be changed. The default constructor initializes the fields of a structure to their default values. Since structures do not support inheritance, structure members cannot be specified as abstract, virtual, or protected. (FROM: C# COMPLETE REFERENCE)
So, to give an example, here is a struct that computes the area of a rectangle:
Code:
public struct MyStruct  
  {  
  public int height;  
  public int width;  
  public MyStruct(int h, int w)  
  {  
  this.height = h;  
  this.width = w;  
  }  
  public override string ToString()  
  {  
  return (width * height).ToString();  
  }  
  }  
  And here is the test program:  
  static void Main(string[] args)  
  {  
  MyStruct s;  
  s.width = 5;  
  s.height = 5;  
  //declare s1 similar to instantiation of objects  
  MyStruct s1 = new MyStruct(9, 7);  
  Console.WriteLine("Area of first rectangle is: {0} second  
  is: {1}", s.ToString(),s1.ToString());  
  //array of structs  
  MyStruct[] s2 = new MyStruct[3];  
  s2[0].height = 8;  
  s2[0].width = 4;  
  s2[1].height = 10;  
  s2[1].width = 10;  
  s2[2].height = 12;  
  s2[2].width = 2;  
  Console.WriteLine("\n");  
  foreach (var @struct in s2)  
  {  
  Console.WriteLine("Area of the three rectangle are: {0}",  
  @struct.ToString());  
  }  
  } 
As you can see from the test program, you can declare a struct object w/o specifying a new keyword. You could also declare a struct object using the new keyword, provided that you supply values to it's constructor. The third code also let's you create an array of structures.