1 reply to this topic

[Frapps]

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.

[BlackRabbit]

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