.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> VB.Net >> Post New QuestionBookmark and Share Subscribe to Forum

Excel To Sql Server Using Vb.net

Posted By: Naresh     Posted Date: June 03, 2010    Points:2   Category :VB.Net
Hi,

I want to import data from excel to sql server using vb.net coading.Iam using Visual Studio 2005.

After a button click i need to select a particular excel file .
and after another button click i need to send the selected excel file to sql server to a particular database.



Thanks
Naresh

Responses
Author: Lalij Mer             
Posted Date: June 04, 2010     Points: 5   

Show here step by step Guide


Step-1
Create a sample table:
create table emp
(
sno int identity,
fname nvarchar(20),
lname nvarchar(20),
mobnum nvarchar(15),
city nvarchar(20),
state nvarchar(30),
zip int
)


Step-2
Take a Web page and name it as Excel2sql.aspx (Following is the code contained in the Excel2sql.aspx page).
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel2Sql.aspx.cs" 
Inherits="_Default" %>

<!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 id="Head1" runat="server">
<title>:: Importing Data From Excel Sheet to SQL Server ::</title>
<style type="text/css">
.style1
{
width: 50%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

<table align="center" class="style1">
<tr>
<td align="center">
<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">
Insert Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">
View Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">
Delete Data</asp:LinkButton>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>

</tr>
<tr>
<td colspan="9" align="center">
<asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
</td>

</tr>
</table>

</div>
</form>
</body>
</html>

Step-3
Here is the vb.net code in the Excel2sql.aspx.cs file:
Imports System
Imports System.Collections
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb 'This namespace is mainly used for dealing with
'Excel sheet data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Xml.Linq
Public partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

End Sub

protected void insertdata_Click(Object sender, EventArgs e)
{
OleDbConnection oconn = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("example.xls") + "
Dim Properties As Extended = Excel 8.0") 'OledbConnection and
' connectionstring to connect to the Excel Sheet
Try
'After connecting to the Excel sheet here we are selecting the data
'using select statement from the Excel sheet
Dim ocmd As OleDbCommand = New OleDbCommand("select * from [Sheet1$]",oconn)
Dim 'Here [Sheet1$] is the name of the sheet As oconn.Open()
'in the Excel file where the data is present
Dim odr As OleDbDataReader = ocmd.ExecuteReader()
Dim fname As String = ""
Dim lname As String = ""
Dim mobnum As String = ""
Dim city As String = ""
Dim state As String = ""
Dim zip As String = ""
While odr.Read()
fname = valid(odr, 0)'Here we are calling the valid method
lname = valid(odr, 1)
mobnum = valid(odr, 2)
city = valid(odr, 3)
state = valid(odr, 4)
zip = valid(odr, 5)
'Here using this method we are inserting the data into the database
insertdataintosql(fname, lname, mobnum, city, state, zip)
End While
oconn.Close()
Catch ee As DataException
lblmsg.Text = ee.Message
lblmsg.ForeColor = System.Drawing.Color.Red
Finally
lblmsg.Text = "Data Inserted Sucessfully"
lblmsg.ForeColor = System.Drawing.Color.Green
End Try
End Class
{
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

End Sub

Protected Sub insertdata_Click(ByVal sender As Object, ByVal e As EventArgs)
OleDbConnection oconn = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("example.xls") + "
Dim Properties As Extended = Excel 8.0") 'OledbConnection and
' connectionstring to connect to the Excel Sheet
Try
'After connecting to the Excel sheet here we are selecting the data
'using select statement from the Excel sheet
Dim ocmd As OleDbCommand = New OleDbCommand("select * from [Sheet1$]",oconn)
Dim 'Here [Sheet1$] is the name of the sheet As oconn.Open()
'in the Excel file where the data is present
Dim odr As OleDbDataReader = ocmd.ExecuteReader()
Dim fname As String = ""
Dim lname As String = ""
Dim mobnum As String = ""
Dim city As String = ""
Dim state As String = ""
Dim zip As String = ""
While odr.Read()
fname = valid(odr, 0)'Here we are calling the valid method
lname = valid(odr, 1)
mobnum = valid(odr, 2)
city = valid(odr, 3)
state = valid(odr, 4)
zip = valid(odr, 5)
'Here using this method we are inserting the data into the database
insertdataintosql(fname, lname, mobnum, city, state, zip)
End While
oconn.Close()
Catch ee As DataException
lblmsg.Text = ee.Message
lblmsg.ForeColor = System.Drawing.Color.Red
Finally
lblmsg.Text = "Data Inserted Sucessfully"
lblmsg.ForeColor = System.Drawing.Color.Green
End Try
End Sub

Step-4
'This valid method is mainly used to check where the null values are 
'contained in the Excel Sheet and replacing them with zero
Protected String valid(OleDbDataReader Property stval)'if any columns are () As myreader,int
End Property
Protected viewdata_Click(Object Property e)'Code to View () As sender,EventArgs
End Property
Public insertdataintosql(String Property lname,() As fname,string
End Property
String mobnum,string city,string Function zip)() As state,string'inserting data into the Sql Server
Dim conn As SqlConnection = New SqlConnection("Data Source = .\\sqlexpress
AttachDbFileName=|DataDirectory|exceltosql.mdfTrusted_Connection=yes")
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "insert Function emp(ByValfname, ByVallname, ByValmobnum, ByValcity, ByValstate, ByValzip) As into
Dim conn As SqlConnection = New SqlConnection("Data Source = .\\sqlexpress
AttachDbFileName=|DataDirectory|exceltosql.mdfTrusted_Connection=yes")
Try
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "delete from emp"
cmd.CommandType = CommandType.Text
conn.Open()
cmd.ExecuteScalar()
conn.Close()
Catch de1 As DataException
lblmsg.Text = de1.Message
lblmsg.ForeColor = System.Drawing.Color.Red
Finally
lblmsg.Text = "Data Deleted Sucessfully"
lblmsg.ForeColor = System.Drawing.Color.Red
End Try
End Function
}



Please Check my answer if you help....
Thank You...

Lalit.


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend