importing some data form excell to database - C# Discussion Boards

12-Jul-11 2:33

Hi all I have a problem i am importing data from an excell spreadsheet to a sql 2008 database ,and i can do that in c# 3.5 getting all fields in the spreadsheet the same as the ones in the db..

I want to select now a few fields and insert them or rather import them to the database not all fields are the same here heres my code and i am getting this error
" The connection was not closed. The connection's current state is open."

i want only these 2 fields(DOC_TPL_CGY_TP_DESC ,USR ) from excell now to dbs the other two i will insert namually please help

private void btnDocumentCat_Click(object sender, EventArgs e)
{
string oconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\2010Projects\\Project1\\DocumentCatalogue\\bin\\Debug\\MyExcelFile.xls;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
OleDbConnection con = new OleDbConnection(oconn);

string filepath = @"C:\\2010Projects\\Project1\\DocumentCatalogue\\bin\\Debug\\MyExcelFile.xls";
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";

using (OleDbConnection cn = new OleDbConnection(ConnectionString))
{
cn.Open();
DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + WorkSheetName + "]", cn);

OleDbCommand OCM = new OleDbCommand("SELECT DOCUMENT NAME ,USER FROM [" + WorkSheetName + "]", con);
DataTable dt = new DataTable(WorkSheetName);

OleDbDataReader odr = OCM.ExecuteReader();

//OleDbDataReader URD = OCM.ExecuteReader();

string DOC_TPL_CGY_TP_DESC = string.Empty;
string DOC_TPL_CGY_TP_CODE = string.Empty;
int DOC_TPL_CGY_TP_ID = 0;
string USR = string.Empty;

while (odr.Read())
{
// DOC_TPL_CGY_TP_CODE = valid(odr, 0);
DOC_TPL_CGY_TP_DESC = valid(odr, 0);
USR = valid(odr, 1);

Insertdataintosql2(DOC_TPL_CGY_TP_CODE,DOC_TPL_CGY_TP_DESC,DOC_TPL_CGY_TP_ID, USR);
}

}
}

private void Insertdataintosql2(string DOC_TPL_CGY_TP_CODE, string DOC_TPL_CGY_TP_DESC, int DOC_TPL_CGY_TP_ID, string USR)
{
throw new NotImplementedException();
}

sipho

PIEBALDconsult12-Jul-11 3:16

PIEBALDconsult

12-Jul-11 3:16

That code sure is hard to read without PRE tags. Sigh | :sigh:

Which line is giving the Exception?

Why use @ and \\ ?

What are the DataTable dt and OleDbCommand ocmd used for?

Is the connection to the destination database open?

cpokazi12-Jul-11 3:27

12-Jul-11 3:27

oh yes i saw my connection was wrong...thanks but yes am still struggling i wanna select a few colomns from the excel sheet to the dbs but i aint sure how to do that if its not all columns. Smile | :)

PIEBALDconsult12-Jul-11 15:09

PIEBALDconsult

12-Jul-11 15:09

I just don't see what would give you that error.

Shameel12-Jul-11 3:48

Shameel

12-Jul-11 3:48

Why take all the pains to do it in C#, when you can do it in TSQL directly.

select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\myexcel.xls', 'select * from [Sheet1$]')

cpokazi12-Jul-11 4:04

12-Jul-11 4:04

OleDbCommand OCM = new OleDbCommand("SELECT DOCUMENT_NAME ,USER FROM [" + WorkSheetName + "]", cn);

i am using the above way because i only want thse two coloumns form my excel and import them to a dbs table but i still am getting an arror..please help
error "No value given for one or more required parameters."

Shameel12-Jul-11 4:37

Shameel

12-Jul-11 4:37

Did you name the columns in the spreadsheet? By default, the values in the first row are treated as column names.

cpokazi12-Jul-11 4:44

12-Jul-11 4:44

yes my spreadsheet coloumns are the ones in this line of code

OleDbCommand OCM = new OleDbCommand("SELECT [DOCUMENT_NAME] ,[USER] FROM [" + WorkSheetName + "]", cn);

OleDbDataReader odr = OCM.ExecuteReader();

when i build my app it can not go tru this statement gives me this error
"No value given for one or more required parameters." because what i am trying to do is get documentname and user name from excell then store it in a table but now i can get these ..

Hope i did answer you question

Shameel12-Jul-11 23:43

Shameel

12-Jul-11 23:43

Ensure that the WorkSheetName variable ends with a dollar ($) symbol.

SendKeys.Send("^v")

gumigyuri11-Jul-11 22:58

gumigyuri

11-Jul-11 22:58

Hi all

I'm having trouble with my app and i need some help.
I have an app that needs to paste some numbers from a listbox into another App.
Using

SendKeys.Send("^v");

works great while working on a local computer, the problem is that i'm working on a remote desktop and sometimes when sending the "^v" it only sends the "v" D'Oh! | :doh:

Can anyone help me figure this out ?

Thanks in advance. Smile | :)

DaveAuld11-Jul-11 23:12

DaveAuld

11-Jul-11 23:12

i take it your ^ represent control?

As per the docs it should be wrapped in a brace; {^}

http://msdn.microsoft.com/en-us/library/system.windows.forms.sendkeys(v=vs.71).aspx[^]

"The plus sign (+), caret (^), percent sign (%), tilde (~), and parentheses () have special meanings to SendKeys. To specify one of these characters, enclose it within braces ({})"

Dave
Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

gumigyuri11-Jul-11 23:19

gumigyuri

11-Jul-11 23:19

I don't think that's the problem , because it works without the {} , but for example when i have 50 numbers in the listbox, pasting 45-47 number is OK, while it makes 3-5 random mistakes, sending only the "v".

To emulate "Control" you don't need {} because than it will send the "^" character only...

DaveAuld11-Jul-11 23:40

DaveAuld

11-Jul-11 23:40

Are you using Send or SendWait?

Have you tried the other? could be a timing thing.

I don't think sendkeys is the best mechanism though when dealing with remote desktops; This thread seems to back this up; http://social.msdn.microsoft.com/Forums/en/Vsexpressvb/thread/01d1c501-880a-48c3-8b82-d3b08e974ff0[^]

Dave
Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

Dave Kreskowiak12-Jul-11 1:38

Dave Kreskowiak

12-Jul-11 1:38

Those a control characters to SendKeys. If you want to send those actual characters, you have to enclose them in the curly braces.

He's sending Control-V, which is correct in his code.

The technique, on the other hand, of using SendKeys at all is suspect.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

MicroVirus12-Jul-11 7:13

MicroVirus

12-Jul-11 7:13

I agree. Basically, using SendKeys is per definition unreliable. Best to code what you want (and keep control), rather than relying on a quirky method that can go wrong at any turn.

Abhinav S12-Jul-11 0:28

Abhinav S

12-Jul-11 0:28

Try running remote desktop in full screen mode.
Apparentely, Remote Desktop only acknowledges key strokes in full screen mode.

Too much of heaven can bring you underground
Heaven can always turn around
Too much of heaven, our life is all hell bound
Heaven, the kill that makes no sound