Register and join over 40,000 other developers!
Recent Topics
-
Tekken 7
IsrealJones - Mar 22 2021 07:21 AM
-
The Game You Are Waiting For?
WendellHarper - Dec 06 2020 01:21 PM
-
Quora and Reddit Backlinks
WendellHarper - Dec 06 2020 01:14 PM
-
Delete account
pindo - Jul 23 2020 01:33 AM
-
Print specific values from dictionary with a specific key name
Siten0308 - Jun 20 2019 01:43 PM
Recent Blog Entries
Recent Status Updates
Popular Tags
- networking
- Managed C++
- stream
- console
- database
- authentication
- Visual Basic 4 / 5 / 6
- session
- Connection
- asp.net
- import
- syntax
- hardware
- html5
- array
- mysql
- java
- php
- c++
- string
- C#
- html
- loop
- timer
- jquery
- ajax
- javascript
- programming
- android
- css
- assembly
- c
- form
- vb.net
- xml
- linked list
- login
- encryption
- pseudocode
- calculator
- sql
- python
- setup
- help
- game
- combobox
- binary
- hello world
- grid
- innerHTML

Importing Excel File To Microsoft Sql Server Using C#
Started by Frapps, May 02 2012 07:50 AM
sql server sql server microsoft impor excel file import c# csharp
1 reply to this topic
#1
Posted 02 May 2012 - 07:50 AM
what is worng to my code?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace HbI
{
public partial class Hb1 : Form
{
public Hb1()
{
InitializeComponent();
}
private void Hb1_Load(object sender, EventArgs e)
{
}
private void BrowseBtn_Click(object sender, EventArgs e)
{
OpenFileDialog file1 = new OpenFileDialog();
file1.ShowDialog();
BrowseTxtbx.Text = file1.FileName.ToString();
}
private void ImportBtn_Click(object sender, EventArgs e)
{
try
{
if (BrowseTxtbx.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "")
MessageBox.Show("enter Valid Details");
OleDbConnection con = new OleDbConnection(@"PRovider=Microsoft.Jet.OLEDB.4.0;Data Source='" + BrowseTxtbx.Text + "';Extend Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbCommand cmd = new OleDbCommand("SELECT * from [SHeet1$]", con);
con.Open();
OleDbDataReader reader1 = cmd.ExecuteReader();
int b = 0, i = 0;
b = reader1.VisibleFieldCount;
string str = reader1.GetName(i) + "nvarchar(50)";
i++;
while ( i < b )
{
str += "," + reader1.GetName(i) + "nvarchar(50)";
i++;
}
SqlConnection con1 = new SqlConnection(@"uid='" + textBox2.Text + "';pwd='" + textBox5.Text + "';initial catalog='" + textBox3.Text + "'data source=.\\sqlexpress");
con1.Open();
SqlCommand cmd1 = new SqlCommand("create table" + textBox4.Text + "(" + str + ")", con1);
cmd1.ExecuteNonQuery();
while (reader1.Read())
{
SqlConnection con2 = new SqlConnection(@"uid='" + textBox2.Text + "';pwd='" + textBox4.Text + "';initial catalog='" + textBox3.Text + "';data source=.\\sqlexpress");
con2.Open();
string str1;
int j = 0;
if (reader1.IsDBNull(j))
{
str1 = "'" + " " + "'";
}
else
{
str1 = "'" + reader1.GetString(j).ToString() + "'";
}
j++;
while ( j < b )
{
if (reader1.IsDBNull(j))
str1 += "," + "'" + " " + "'";
else
{
str1 += "," + "," + reader1.GetString(j).ToString() + "'";
}
j++;
}
SqlCommand cmd2 = new SqlCommand("inser into " + textBox4.Text + "values(" + str1 + ")", con2);
cmd2.ExecuteNonQuery();
con2.Close();
}
con.Close();
MessageBox.Show("Successfully Completed");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ExitBtn_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
I attached the picture of my preferred output and my error. please help me. thanks guys
I posted these topic 3 times. im sorry mods i dont know how to delete my previous topics about this. i'm newbie here. i'm sorry.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace HbI
{
public partial class Hb1 : Form
{
public Hb1()
{
InitializeComponent();
}
private void Hb1_Load(object sender, EventArgs e)
{
}
private void BrowseBtn_Click(object sender, EventArgs e)
{
OpenFileDialog file1 = new OpenFileDialog();
file1.ShowDialog();
BrowseTxtbx.Text = file1.FileName.ToString();
}
private void ImportBtn_Click(object sender, EventArgs e)
{
try
{
if (BrowseTxtbx.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "")
MessageBox.Show("enter Valid Details");
OleDbConnection con = new OleDbConnection(@"PRovider=Microsoft.Jet.OLEDB.4.0;Data Source='" + BrowseTxtbx.Text + "';Extend Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbCommand cmd = new OleDbCommand("SELECT * from [SHeet1$]", con);
con.Open();
OleDbDataReader reader1 = cmd.ExecuteReader();
int b = 0, i = 0;
b = reader1.VisibleFieldCount;
string str = reader1.GetName(i) + "nvarchar(50)";
i++;
while ( i < b )
{
str += "," + reader1.GetName(i) + "nvarchar(50)";
i++;
}
SqlConnection con1 = new SqlConnection(@"uid='" + textBox2.Text + "';pwd='" + textBox5.Text + "';initial catalog='" + textBox3.Text + "'data source=.\\sqlexpress");
con1.Open();
SqlCommand cmd1 = new SqlCommand("create table" + textBox4.Text + "(" + str + ")", con1);
cmd1.ExecuteNonQuery();
while (reader1.Read())
{
SqlConnection con2 = new SqlConnection(@"uid='" + textBox2.Text + "';pwd='" + textBox4.Text + "';initial catalog='" + textBox3.Text + "';data source=.\\sqlexpress");
con2.Open();
string str1;
int j = 0;
if (reader1.IsDBNull(j))
{
str1 = "'" + " " + "'";
}
else
{
str1 = "'" + reader1.GetString(j).ToString() + "'";
}
j++;
while ( j < b )
{
if (reader1.IsDBNull(j))
str1 += "," + "'" + " " + "'";
else
{
str1 += "," + "," + reader1.GetString(j).ToString() + "'";
}
j++;
}
SqlCommand cmd2 = new SqlCommand("inser into " + textBox4.Text + "values(" + str1 + ")", con2);
cmd2.ExecuteNonQuery();
con2.Close();
}
con.Close();
MessageBox.Show("Successfully Completed");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ExitBtn_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
I attached the picture of my preferred output and my error. please help me. thanks guys
I posted these topic 3 times. im sorry mods i dont know how to delete my previous topics about this. i'm newbie here. i'm sorry.
#2
Posted 02 May 2012 - 08:04 PM
Ok, can you copy the error message here please ?
when working with office documents many things can happen
but for starters i see a couple of things :
>> new SqlCommand("create table" + textBox4.Text + "(" + str + ")", con1);
you are not checking textBox4.Text + "(" + str + ")" for improper characters, in fact i think you cant use "(" in a table name
>> reader1.GetName(i) + "nvarchar(50)";
you need to :
a. check for improper characters , not all characters are allowed for field names
b. give it an space in between name and nvarchar, like this : " nvarchar(50)";
maybe you should try to exec the create table statement only until it works right, and then come back for some more advise, deal ?
in any case, you should log how the sql statement for create table looks like and test if it works on sql-analizer,
by the way, you might want to exec an drop table statement before creating a new one, cause if the table already exists you will
get a sql error.
use a form with a multilined textbox if you want to have a log on how the statements are getting made
when working with office documents many things can happen
but for starters i see a couple of things :
>> new SqlCommand("create table" + textBox4.Text + "(" + str + ")", con1);
you are not checking textBox4.Text + "(" + str + ")" for improper characters, in fact i think you cant use "(" in a table name
>> reader1.GetName(i) + "nvarchar(50)";
you need to :
a. check for improper characters , not all characters are allowed for field names
b. give it an space in between name and nvarchar, like this : " nvarchar(50)";
maybe you should try to exec the create table statement only until it works right, and then come back for some more advise, deal ?
in any case, you should log how the sql statement for create table looks like and test if it works on sql-analizer,
by the way, you might want to exec an drop table statement before creating a new one, cause if the table already exists you will
get a sql error.
use a form with a multilined textbox if you want to have a log on how the statements are getting made
Also tagged with one or more of these keywords: sql, server, sql server, microsoft, impor excel file, import, c#, csharp
Language Forums →
C# →
How to make code run differently depending on the platform it is running on?Started by xarzu, 05 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
How do I set a breakpoint in an attached process in visual studioStarted by xarzu, 04 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
Do you use obfuscation?Started by Fernando, 22 Jan 2019 ![]() |
|
![]() |
||
General Forums →
Game Development →
Include a specific error, task, problem, or question in your titleStarted by Killuah, 04 Jul 2018 ![]() |
|
![]() |
||
General Forums →
Everything Else →
Hosting and Registrars →
Rackend Switzerland Offshore Server at low rates with quality supportStarted by Rackend, 14 Feb 2018 ![]() |
|
![]() |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download