Read Excel files data and insert in to SQL Table

Create “Book1.xls” file in that add Header like “ProductID” and “vcSerialNo”

Imports System.IO

Imports System.Data.OleDb

Private Sub GetFile()

 

Dim conString As String = ""

Dim strFileType As String = Path.GetExtension(txtpath.Text).ToLower()

Dim xlspath As String = txtpath.Text

'Connection String to Excel Workbook

If strFileType.Trim() = ".xls" Then

conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlspath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

ElseIf strFileType.Trim() = ".xlsx" Then

conString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""", xlspath)

End If

Dim oledbConnection As New OleDb.OleDbConnection(conString)

Try

Dim query As String = "SELECT [ProductID],[vcSerialNo] FROM [Sheet1$]"

If oledbConnection.State = ConnectionState.Closed Then

oledbConnection.Open()

End If

Dim cmd As New OleDbCommand(query, oledbConnection)

Dim ds As New DataSet()

Dim adptr As New OleDbDataAdapter(cmd)

adptr.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

Dim i As Integer

For i = 0 To ds.Tables(0).Rows.Count - 1

Dim ProductID = ds.Tables(0).Rows(i)(0).ToString()

      Dim vcSerialNo = ds.Tables(0).Rows(i)(1).ToString()

Dim sqlConnection As New SqlClient.SqlConnection

      Dim sqlCommand As New SqlClient.SqlCommand()

Try

sqlConnection.ConnectionString = "Data Source=" + txtDBSource.Text + ";Initial Catalog=" + txtDBName.Text + ";User Id=" + txtUserName.Text + ";Password=" + txtPW.Text + ";Integrated Security=False;Connect Timeout=30;"

If sqlConnection.State = ConnectionState.Closed Then

      sqlConnection.Open()

End If

      sqlCommand.Connection = sqlConnection

sqlCommand = New SqlClient.SqlCommand("INSERT INTO tProdSerialNoInventory(lProdId, vcSerialNo, bIsAvailable, dtUpdDT, lUpdUserId) VALUES ('" & ProductID & "','" & vcSerialNo & "', 1, '" & Date.Now() & "', 0)", sqlConnection)

sqlCommand.ExecuteNonQuery()

Catch ex As Exception

Finally

If sqlConnection.State = ConnectionState.Closed Then

            sqlConnection.Close()

End If

End Try

Next

Catch ex As Exception

MessageBox.Show("Reocrds not found")

Finally

oledbConnection.Close()

End Try

End Sub

 

Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

If txtDBSource.Text = "" Or txtDBName.Text = "" Or txtUserName.Text = "" Or txtPW.Text = "" Then

            MessageBox.Show("Check Configuration")

            Return

Else

Dim MyFolderBrowser As New System.Windows.Forms.OpenFileDialog

      MyFolderBrowser.Title = "Select the ExcelSheet"

      MyFolderBrowser.Filter = "All files (*.*)|*.*xls"

      If MyFolderBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then

                txtpath.Text = MyFolderBrowser.FileName

                GetFile()

      End If

End If

End Sub

 

Demo Download

Read More

Client side HTML encoding

<!-- Add JS Before build -->
<script src="JS/jquery-1.6.2.min.js" type="text/javascript"></script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <script type="text/javascript" >

        function validateTxt() {
            var div = document.getElementById("Div1");

            var str = "&lt;h1&gt; Hello.. &lt;/h1&gt;&lt;br /&gt; &lt;b&gt;&lt;i&gt; Chirag Here :) &lt;/i&gt;&lt;/b&gt;";

            str = this.replaceAll(str, "&amp;", "&");
            str = this.replaceAll(str, "&lt;", "<");
            str = this.replaceAll(str, "&gt;", ">");
            str = this.replaceAll(str, "&nbsp;", " ");
            var str4Div = str;
            div.innerHTML = str4Div;
        }

        function replaceAll(str, subStr, newStr) {
            var offset = 0;
            var index = str.indexOf(subStr);
            while (index != -1) {

                 str = str.substr(0, index) + newStr + str.substr(index + subStr.length);
                 offset = index + newStr.length;
                index = str.indexOf(subStr, offset);
            }
            return str;
         }

        $(document).ready(function() {
            validateTxt();
        });

    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div id="Div1" onload="javascript:validateTxt();" >
    </div>
    </form>
</body>
</html>

OutPut::

Demo Download
 

Read More

XML banner fader in jQuery

Here’s the XML code.

<!--?xml version="1.0" encoding="utf-8"?-->
<gallery fade="500" ontime="5000">
    <picture src="banners/banner1.jpg" href="http://www.google.com" target="_blank"></picture>
    <picture src="banners/banner2.jpg" href="http://www.yahoo.com" target="_blank"></picture>
    <picture src="banners/banner3.jpg" href="http://www.bing.com" target="_blank"></picture></gallery> 

The HTML code is very simple

<!-- Add JQuery Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>

<div id="banner"> 
           <a id="banner_href" href="#">
           <img id="banner_image" src="banners/banner1.jpg"  alt="Banner Image" ></a>
 </div> 

 JavaScript.jd Document

$(document).ready(function(){
    xmldata=new Array();         //initialise array for XML data
    fade=0;                                //time taken for transitions
    ontime=0;                           //time between transitions
    changenum=0;                    //current banner
    changetot=0;                      //total banners

    $("#banner").fadeTo(1,0);        //fade out the fallback banner

    $.ajax({                                            //get the XML data
              url: "banners.xml",                //URL to get the data from
              success: function(data) {      //if we succeed, set everything up. We don't expect failure.
                  $(data).find("picture").each(function()
                  {
                    xmldata.push($(this));                //add item into array
                 });
                 changetot=xmldata.length;                                             //get total length
                  ontime=eval($(data).find("gallery").attr("ontime"));    //get fade time
                 fade=eval($(data).find("gallery").attr("fade"));             //get time between fades
                 change_banner(changenum);                                         //change banner for first banner
                 }
        });
});

function change_banner(){
    data=xmldata[changenum];              //get current banner XML object
    img=$(data).attr("src");                    //retrieve variables
    href=$(data).attr("href");
    target=$(data).attr("target");

    $("#banner_href").attr("href",href);        //change variables on HTML
    $("#banner_href").attr("target",target);
    $("#banner_image").attr("src",img);

    //increment banner value, this is executed BEFORE the callback as we already have the variables.
    changenum++;
    if(changenum>=changetot)                //check for banner value bounds
        changenum=0;   

    cache = document.createElement('img');            //create an image
    //pre-cache next image (means the browser will load it instantly)            cache.src = $(xmldata[changenum]).attr("src");
   //fade banner in, then wait, then fade out and call back to this function. 
   $("#banner").fadeTo(fade,1).fadeTo(ontime,1).fadeTo(fade,0,change_banner);    
}

Read More