.NET Mixer: May 2010

Sunday, May 30, 2010

Insert bulk data into oracle using SQLLoader in C#

Please visit my new Web Site https://coderstechzone.com

Sometimes we need to insert a large volume of data into the Oracle database. Sometimes data comes from comma seperated csv file or from a comma seperated text file. By reading this article you can insert data from both source to oracle. There are mainly two ways to insert bulk data into Oracle:

1. Using Bindarray
2. Using SqlLoader

Here in this article i will explain how one can use to insert bulk data from text file or csv file into oracle
using SQLLoader.

To do that please follow the below steps:

1. Create a folder in your c: drive and named it WorkingFolder.
2. Create a controller file in the WorkingFolder using below code:

LOAD DATA
INFILE DataOut.txt
BADFILE dataFile.bad
APPEND INTO TABLE SASN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(CDR_Type,Rec_Type)

And name this file by "Ctrl.txt".
3. Now create a text file which contins data like below:

1,2
3,4
5,6

And name this file "DataOut.txt".
4. Now create a sample table in oracle like below:

5. Now from your c# project run the below sample code:

Process proc = new Process();
            string myCommand = @"CMD.EXE";
            proc.StartInfo = new ProcessStartInfo(myCommand);
            
            //Set up arguments for CMD.EXE
            proc.StartInfo.Arguments = @"/c SQLLDR User/Password@Schema CONTROL=C:\WorkingFolder\Ctrl.txt";

            proc.StartInfo.RedirectStandardOutput = true;

            proc.StartInfo.RedirectStandardError = true;

            proc.StartInfo.UseShellExecute = false;

            proc.StartInfo.WorkingDirectory = @"c:\WorkingFolder\";
            proc.Start();
            proc.WaitForExit();

            if (proc.ExitCode == 0) // Successfully completed
                MessageBox.Show("Successfully Inserted");
            
            else
                MessageBox.Show(proc.StandardError.ReadToEnd());

6. Now run the above code & hope you will get below output:

Sunday, May 16, 2010

How to read hidden field data in GridView Asp.net C#

Please visit my new Web Site https://coderstechzone.com

In some cases developers need to collect more data instead of datakeynames. In that cases developers use hidden field to retain those important data. Here in this article i wil show how one can use hidden field to store some important data & how can read those. For simplicity here i use a product table & use ID in a hidden field to read them from server side SelectedIndexChanged event. The product table looks like below:

Now add a page in your project & also add a GridView like below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridView_Hidden.aspx.cs" Inherits="GridView_Hidden" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>How to read GridView Hiden Field Data</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
         <HeaderStyle BackColor="Red" Font-Bold="True" ForeColor="White" />
         <RowStyle BackColor="LightGray" />
         <AlternatingRowStyle BackColor="LightGray" />

         <Columns>
             <asp:CommandField ShowSelectButton="True" />

             <asp:TemplateField HeaderText="Product Name">
             <ItemTemplate>
             <asp:HiddenField runat="server" ID="HiddenField1" Value='<%#Eval("ID")%>'></asp:HiddenField>
             <asp:Label runat="server" ID="Label2" Text ='<%#Eval("Name")%>'></asp:Label>
             </ItemTemplate>
             </asp:TemplateField>

             <asp:BoundField DataField="Description" HeaderText="Description" />
             <asp:BoundField DataField="Color" HeaderText="Color" />
             <asp:BoundField DataField="Size" HeaderText="Size" />


         </Columns>
            <SelectedRowStyle BackColor="Blue" ForeColor="White" />
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Now under SelectedIndexChanged event write the below code to read hidden field value within gridview template column:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = clsDBUtility.GetDataTable("SELECT * FROM PRODUCT");
            GridView1.DataSource = dt;
            GridView1.DataBind();
            Cache["Data"] = dt;
        }

    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sValue = ((HiddenField)GridView1.SelectedRow.Cells[1].FindControl("HiddenField1")).Value;
        Response.Write("Product Id=" + sValue);
    }

Now run the project & will get below like output:

Hope now you can read & use the hidden field vlaue from within a GridView.

Thursday, May 13, 2010

Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

Please visit my new Web Site https://coderstechzone.com

When you google this error you will get a lots of solution. But no one help me to resolve my problem. So i am working on this issue to find out my solution & finally i got a simple problem which i want to share with you. My situation is i have a link server (SQL Server 2008) with my working Sql server 2005. I have created a table in the link server means Sql server 2008 which is given below:

And i wrote a sample stored procedure with a dynamic SQL to produce this error like below:

CREATE PROCEDURE SP_Test
@Table_Tail AS VARCHAR(20)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @STRSQL AS VARCHAR(5000)

 SET @STRSQL = 'DELETE FROM [xxxxx\SQL01].[RA_CTL_SUMMARY].dbo.TBL_IBSPhase2 WHERE EntryDate='''+@Table_Tail+''' 
 AND Prefix=''NOKIA'' '
 EXEC (@STRSQL) 

END

The problem is when i want to run or execute this query i will get the below error:
Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Solution:

In this case the solution is simple. Just change the datatype size of column Prefix MAX to a fixed size will resolved my problem. Means in my scenario i have declared the datatype of Prefix column from VARCHAR(MAX) to VARCHAR(5000).

In your case this may not be the situation so keep googling & try other solutions. This is one of the solution only which i did not get from google.

Tuesday, May 11, 2010

Set alternate table row color using JQuery

Please visit my new Web Site https://coderstechzone.com

In many cases we need to represent data in tabular format so that user can easily read data in straightforward way. It will be more helpful for users if we can give or set different row color for alternate rows of the table to distinguish one row from another. To do that i have googled and found some examples. But each example will not consider the header row of the table. I need to color alternate rows except header. So i tried to write a JQuery method which will color alternate rows of a table except considering header row and i want to share with you.

To do the example first create the table and definitely give an id for header rows to distinguish header row in JQuery method like below:

<table>
    <caption>Article History</caption>
    <thead><tr id="Header"><th>Article</th><th>Published</th></tr></thead>
    <tr><td>An introduction to JQuery</td><td>May 01, 2010</td></tr>
    <tr><td>How to set TextBox Value using JQuery</td><td>May 03, 2010</td></tr>    
    <tr><td>How to color Alternate Row</td><td>May 05, 2010</td></tr>    
    <tr><td>How to declare Variable in JQuery</td><td>May 07, 2010</td></tr>    
    <tr><td>What is JQuery $ Symbol</td><td>May 09, 2010</td></tr>        
    </table>

And the JQuery method will be:

<script language="javascript">
            $(document).ready(function() {
                $("caption").css("text-align", "left");
                $('#Header').css("background-color", "Red");
                // You can also add css like:
                $('#Header').addClass('abc');
                $("tr:not(\'#Header\'):even").css("background-color", "Blue");
                $("tr:not(\'#Header\'):odd").css("background-color", "Green");
            });
    </script>

Here i showed how you can apply direct CSS as well as cssclass using JQuery.

The code is self explanatory except filter option. The filters “even” and “odd” can be used in jQuery for selecting odd or even index of the elements.

Now run the page and hope you will get the below interface:

Hope it will help you.

How to get set read TextBox value using JQuery

Please visit my new Web Site https://coderstechzone.com

As we knew that using javascript to read or get or set textbox value is easy but due to JQuery notation novice developers always make mistakes to read textbox value. Here in this article i will show you how one can get set read textbox value or textbox text using JQuery.

To run the example first add a page in your project. Then add three textboxes and one command button to do sum like below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="jQuery1.aspx.cs" Inherits="jQuery1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>How to read TextBox value using JQuery</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox runat="server" ID="txtFirst"></asp:TextBox>
        <asp:TextBox runat="server" ID="txtSecond"></asp:TextBox>
        <asp:Button runat="server" ID="btn" Text="+" />
        <asp:TextBox runat="server" ID="txtSum"></asp:TextBox>
    </div>
    </form>
</body>
</html>

Now add the below JQuery script which will read two number from txtFirst & txtSecond and set the result into third TextBox:

<script language="javascript">
            $(document).ready(function() {          
                $('#btn').click(function() {
                    
                    // Read First number & 2nd Number from TextBox
                    var $nFirstNum = parseInt($('#txtFirst').val())
                    var $nSecondNum = parseInt($('#txtSecond').val())
                    var $nResult = $nFirstNum+$nSecondNum
                    
                    // Set the result into txtSum TextBox
                    $('#txtSum').val($nResult)
                    return false;
                });
            });
    </script>

Now run the page. Hope you will get below interface:

Hope now you can read or write value to a TextBox using JQuery easily.

Monday, May 10, 2010

How to declare JQuery variable & What is JQuery $ sign symbol

Please visit my new Web Site https://coderstechzone.com

In my first article i have discussed on how to start JQuery in your asp.net application. Here in this article i will explain how one can declare simple JQuery variable as well as object type Jquery variable also i will explain what does it mean by Jquery $ sign.

At first What is variable:

In programming, a variable is a value that can change, depending on conditions or on information passed to the program. Typically, a program consists of instructions that tell the computer what to do and data that the program uses when it is running. The data consists of constants or fixed values that never change and variable values (which are usually initialized to "0" or some default value because the actual values will be supplied by a program's user).

Now declaring a variable in JQuery:
Declarinag a variable in JQuery almost like javascript declaration. See the below example which will use simple variables:

<script language="javascript">
            $(document).ready(function() {          
            var a=10;
            var b=20;
            var c=a+b;
            alert(c);
            });
    </script>

Now what is Jquery $ sign:
To initialize an object JQuery uses $ symbol. You can use this symbol to pass selector, function, string etc to execute or implement some functionality. You can replace the symbol $ by jquery word which will give you same result. Basically its a notation you have to use to pass a selector.

So the best way of declaring a variable in JQuery based on my first example is:

<script language="javascript">
            $(document).ready(function() {          
            var $a=10;
            var $b=20;
            var $c=$a+$b;
            alert($c);
            });
    </script>

Object Declaration:
JavaScript supports Object concept very well. We can declare object variable same as javascript. You can create an object using the object literal in JQuery as follows:

<script language="javascript">
        $(document).ready(function() {          
            var Developer = {
               $Name: "Shawpnendu",
               $Experience: 6
            };
            
            //You can write and read properties of an object using the dot notation as follows:
            
            // Read Properties
            
            alert(Developer.$Name)
            
            // Write or set properties
            Developer.$Name="Bikash"
            alert(Developer.$Name)            
        });
    </script>

Hope this will help you.

Introduction on using JQuery in Asp.net application

Please visit my new Web Site https://coderstechzone.com

In a single line jQuery is a fast, lightweight client side library that is CSS3 compliant and supports almost all browsers. The jQuery framework is extensible and very nicely handles DOM manipulations, CSS, AJAX, Events and Animations. By using JQuery you can dramatically reduce your client side code. Jquery code is completely separate from your HTML code. Around the world many JQuery gurus develops handy plugins for you which you can use to boost up user experience while browsing your application. You can easily integrate JQuery with your AJAX application as well as Microsft start to provide intelligence on writing JQuery from VS 2008.

OK now start.

To Start JQuery first visit the JQuery site and download latest JS file.

Now create a project. Add a folder named Script and add the JQuery js file in this folder.

Now open your Default.aspx page and include the JQuery library just after the Title tag within Head section in your page like below:

<script src="Script/jquery.js" type="text/javascript"></script>

Now your application is ready to use JQuery. Ok first we try to write Hello World ! as usual. For this modify your default.aspx page Head tag HTML markup like below:

<head runat="server">
    <title>An introduction to JQuery</title>
    <script src="Script/jquery.js" type="text/javascript"></script>

    <script type="text/javascript"> 

        $(document).ready(function() { 
                alert('Hello World !');
            });
    </script>    
</head>

Now run the project & you will get below output:

Another example:

Add a button control in your page & run the page & click on the button. You will get Hello World ! message again. To do that modify your default.aspx page Head tag & Body tag HTML markup like below:

<head runat="server">
    <title>Untitled Page</title>
    <script src="Script/jquery.js" type="text/javascript"></script>
    
    <script language="javascript">
            $(document).ready(function() {          
                $('#btn').click(function() {
                    alert('Hello World !');
                });
            });
    </script>    
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button runat="server" ID="btn" Text="Click Me" />
    </div>
    </form>
</body>

The output will be:

If you look at the button control HTML you wouldn't get any extra code to implement this click example like javascript. This is the beauty of JQuery which tell us that "Separate javascript code from HTML mark-up".

Now read the documentation from jquery site to enrich yourself and keep in mind that JavaScript is a language whereas jQuery is a library written using JavaScript.

Sunday, May 9, 2010

Master page image path problem in asp.net C#

Please visit my new Web Site https://coderstechzone.com

As you knew that master page uses absolute image URL which cause problem to reference an image inside other folder. The problem occurs due to below reasons:

1. Master page is in the root folder
2. Images under another root folder
3. Aspx pages under another root folder

As a result you will get "Image not found" or "Image file path not exist" error.

Scenarios like below will help you to understand the problem:

To set the master page image path to reference an image based on above scenario you may write a code like:

<img alt="" src="Images/netmixerfinal.gif"/>

Which will creating the problem. The solution is simple and you may follow the below code to resolve the problem:

<img alt="" src="<%= Page.ResolveUrl("~")%>Images/netmixerfinal.gif"/>

If you not yet understand the problem then continue to read to run an example.

First add a master page then add a folder named images also add a folder named CRMModule. Now put an image under images folder plus add an aspx page(must include previously defined master page) under CRMModule folder.

Now modify the masterpage code like below:

<%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Master page Image path problem</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <img alt="" src="<%= Page.ResolveUrl("~")%>Images/netmixerfinal.gif"/>
        <hr />
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>

Now add a label in the container page and write something on it.
Now run the project and you will get something like below:

Hope now referencing image path from master page has been resolved.

TreView control to display hierarchical data or group break down data or relational data in Asp.Net

Please visit my new Web Site https://coderstechzone.com

In most of the cases developers need to display Hierarchical data or group breakdown using TreeView control in Asp.Net. Most of the ways you can follow to display such type of group data or relational data or hierarchical data such as gridview, repeater control but if something related to navigational issues then I think TreeView control is the perfect control to display Hierarchical or group or relational data. To do this example please create the below three tables with sample data in your database first to run my example code:

Now add an aspx page in your project.
Modify the HTML Matkup code by below code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Hierarchical_TreeView.aspx.cs" Inherits="Hierarchical_TreeView" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>TreeView to display hierarchical or group break down data</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label runat="server" ID="lbl" Font-Bold="true">TreeView to display hierarchical data</asp:Label>
        <hr />
        <asp:TreeView ID="TreeView1" runat="server" ShowLines="true">
        </asp:TreeView>
    </div>
    </form>
</body>
</html>

Now go to the page_load event and write the below code:

using System;
using System.Data;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Hierarchical_TreeView : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string ConnString = WebConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
            using (SqlConnection Conn = new SqlConnection(ConnString))
            {
                string SSQLDepartment = "Select * from Department";
                string SSQLSubject = "Select * from Subject";
                string SSQLTeacher = "Select * from Teacher";
                string SFullSQL = SSQLDepartment + ";" + SSQLSubject + ";" + SSQLTeacher;             

                DataSet dsFullData = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(SFullSQL, Conn);
                da.Fill(dsFullData);
                dsFullData.Tables[0].TableName = "Department";
                dsFullData.Tables[1].TableName = "Subject";
                dsFullData.Tables[2].TableName = "Teacher";

                DataRelation Department_Subject = new DataRelation("DeptSub", dsFullData.Tables["Department"].Columns["ID"], dsFullData.Tables["Subject"].Columns["DeptID"]);
                dsFullData.Relations.Add(Department_Subject);

                DataRelation Subject_Teacher = new DataRelation("SubTech", dsFullData.Tables["Subject"].Columns["ID"], dsFullData.Tables["Teacher"].Columns["SubID"]);
                dsFullData.Relations.Add(Subject_Teacher);

                foreach (DataRow oDepartment in dsFullData.Tables["Department"].Rows)
                {
                    TreeNode NodeDepartment = new TreeNode();
                    NodeDepartment.Text = oDepartment["Name"].ToString();
                    NodeDepartment.Value = oDepartment["ID"].ToString();
                    TreeView1.Nodes.Add(NodeDepartment);

                    foreach (DataRow oSubject in oDepartment.GetChildRows("DeptSub"))
                    {
                        TreeNode NodeSubject = new TreeNode();
                        NodeSubject.Text = oSubject["Name"].ToString();
                        NodeSubject.Value = oSubject["ID"].ToString();
                        NodeDepartment.ChildNodes.Add(NodeSubject);

                        foreach (DataRow oTeacher in oSubject.GetChildRows("SubTech"))
                        {
                            TreeNode NodeTeacher = new TreeNode();
                            NodeTeacher.Text = oTeacher["Name"].ToString();
                            NodeTeacher.Value = oTeacher["ID"].ToString();
                            NodeSubject.ChildNodes.Add(NodeTeacher);
                        }
                    }
                }
            }
        }
    }
}

Now run the project and hope you will get below interface:

Hope now you can represent hierarchical data or group break down data or relational data using asp.net TreeView control.

Tuesday, May 4, 2010

80040E14 - MS Access Syntax Error message

Please visit my new Web Site https://coderstechzone.com

You may face the "Error: 80040E14 - MS Access Syntax Error message" when you want to execute or run an Insert, Update, Delete or even Select statement. There were several types of error you may encounter like below:

1. Syntax error in FROM clause
2. Syntax error in INSERT INTO statement
3. Syntax error in UPDATE statement
4. Syntax error (missing operator) - caused by ' mark
5. Syntax error (missing operator) - incorrect delimiters
6. Syntax error - division by zero error
7. Mismatched CommandTypeEnum value

The above error may comes for below reasons:
1. Using a Reserved Word for a field name or for a table name.
2. Spaces between field name or table name
3. Attempting to insert single quote within a string/text value
4. Incorrect syntax or typing mistake

Resolution:

Using a Reserved Word for a field name or for a table name:
If you are using any reserve word of MS-Access as a field name or table name then you encounter the error. The simple solution is wrap up the field name or table name by third bracket like:

Select [name], [password], [email] From [Agents]

Spaces between field name or table name:
If you are using any spaces within a field name or table name then you encounter the error. The simple solution is wrap up the field name or table name by third bracket like:

Select [name], [password], [email address] From [Agents]

Attempting to insert single quote within a string/text value:

If you want to insert a text or string value which contains single quotation like 'What's in your mind?' within the value then you encounter an error. Because the single quote telling MS-Access that the string value has ended, and anything afterwards should be treated as legitimate SQL. The simple solution is use string.Replace() method to double any single quotes will resolve your problem. Paramterised queries also the best solution in this regard.

Incorrect syntax or typing mistake:
Try to learn MS-ACCESS SQL syntax will resolve your problem.

Want To Search More?

Newer Posts Older Posts Home



Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash

.NET Mixer

Sunday, May 30, 2010

Sunday, May 16, 2010

Thursday, May 13, 2010

Tuesday, May 11, 2010

Monday, May 10, 2010

Sunday, May 9, 2010

Tuesday, May 4, 2010

Blog Archive