C# Discussion Boards - CodeProject

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.