exporting sql table into excel.

Hi,
I have a windows form in visual studio and am trying to export a table from the local sql database into a excel worlbook. I followed this example: (http://support.microsoft.com/kb/307029/en-us) that uses the example northwind mdb database and it worked ,but when I try to change the connection string I get an error
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Could anyone let me know the correct way to go about this. My ultimate aim is to export a report.

How did you change the connection string?

This link may be useful:

www.connectionsstrings.com

|||

Hey thanks,

That links not working for me for some reason.

This is my connection string at the moment

private string strConn = "Provider= SQLOLEDB;Data Source="

+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\My Documents\\Visual Studio 2005 \\Projects\\CallManSys19Jul\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";

and my code for exporting to database

private void AnalyseButton_Click(object sender, EventArgs e)

{

OleDbConnection objConn = new OleDbConnection(strConn);

try

{

//Fill a dataset with records from the Customers table.

OleDbCommand objCmd = new OleDbCommand(

"Select Call_id, name, dept, "

+ "location, Resolved_by, Problem_Type from Call Reports", objConn);

//"Select CustomerID, CompanyName, ContactName, "

//+ "Country, Phone from Customers", objConn);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();

objAdapter.SelectCommand = objCmd;

DataSet objDataset = new DataSet();

objAdapter.Fill(objDataset);

//Create the FileStream to write with.

System.IO.FileStream fs = new System.IO.FileStream(

"C:\\Call Reports.xml", System.IO.FileMode.Create);

//Create an XmlTextWriter for the FileStream.

System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(

fs, System.Text.Encoding.Unicode);

//Add processing instructions to the beginning of the XML file, one

//of which indicates a style sheet.

// xtw.WriteProcessingInstruction("xml", "version='1.0'");

// xtw.WriteProcessingInstruction("xml-stylesheet",

// "type='text/xsl' href='http://links.10026.com/?link=Call Reports.xsl'");

//Write the XML from the dataset to the file.

objDataset.WriteXml(xtw);

xtw.Close();

//Close the database connection.

objConn.Close();

}

catch (System.Exception ex)

{

MessageBox.Show(ex.Message);

} /**/

now I am getting this error after changing the provider in the connection string from

Provider=Microsoft.Jet.OLEDB.4.0; to Provider= SQLOLEDB

ex.Message = "No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)."

|||

To verify that you are using SQL Server, in a database query window, execute this string: "SELECT @.@.Version".

Please post the results.

|||

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This was my result from executing the string
|||I don′t know which part of the connection string you changed, but if you want to connect to the user instance of SQL Server the datafile will have to exist before connecting. Did you create it before ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||How do I go about creating the datafile first
I changed the connection string from this
private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

to the above Connectionstrings.
|||

The lifecycle of the applications I did was.

-I created a database using any version of SQL Server and used a server instance.
-Created my objects and the database logic within the database.
-Dettached the database
-Used it for user istances without using the ldf file.

You can also right click on the project > Add > New item > SQL Database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = CMSDatabaseDataSet1.Tables[0];
int ColumnIndex=0;
foreach(System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();
}

I've tried to use the above code but I recieve this error

Error 1 An object reference is required for the nonstatic field, method, or property 'CallManagementSystem.CMSDatabaseDataSet1.Tables.get' 407 C:\Documents and Settings\bentc2\My Documents\Visual Studio 2005\Projects\CallManagementSystem\CallManagementSystem\Workstation.cs 31 CallManagementSystem

|||

Die to the error message you are trying to use the instance of a class variable in a static method, this is not possible. Please post the whole code to investigate this issue and make a suggestion to improve your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey I now have the the code creating an excel sheet with the column headers but none of the rows

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using RKLib.ExportData;
using Excel;

namespace CallManagementSystem
{
public partial class Workstation : Form
{
LogCall logCall = new LogCall();
int tempid;
string desc;
String connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
private DataSet CallReports = new DataSet();
private System.Windows.Forms.Label lblMessage;

private string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ ".\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True";
//+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
//+ "Northwind.mdb;";

RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");

public Workstation()
{
InitializeComponent();
}

private void populateWorkLoadList()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
wLoadListView.Columns.Clear();
wLoadListView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'True'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
wLoadListView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
wLoadListView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void populateIncomingCallReports()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename= C:\\Documents and Settings\\bentc2\\My Documents\\Visual Studio 2005\\Projects\\CallManagementSystem\\CallManagementSystem\\CMSDatabase.mdf;Integrated Security=True;User Instance=True");

try
{
incomingCallReportslistView.Columns.Clear();
incomingCallReportslistView.Items.Clear();

conn.Open();

string cmdString = "SELECT Title, Name, Priority, Time FROM [Call Reports] WHERE technician_with = @.technician_with AND statusClaimed = 'False'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdString;
cmd.Parameters.AddWithValue("@.technician_with", Global.globalUsername);

SqlDataReader dr = cmd.ExecuteReader();

for (int i = 0; i < dr.FieldCount; i++)
{
ColumnHeader ch = new ColumnHeader();
ch.Text = dr.GetName(i);

// set the width of each column
if (i == 0)
{
ch.Width = 350;
}
if (i == 1)
{
ch.Width = 170;
}
if (i == 2)
{
ch.Width = 150;
}
if (i == 3)
{
ch.Width = 200;
}
incomingCallReportslistView.Columns.Add(ch);
}

ListViewItem itmX;

while (dr.Read())
{
itmX = new ListViewItem();
itmX.Text = dr.GetValue(0).ToString();

for (int i = 1; i < dr.FieldCount; i++)
{
itmX.SubItems.Add(dr.GetValue(i).ToString());
}
incomingCallReportslistView.Items.Add(itmX);
}
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("There was an error in executing the SQL." + "\nError Message:" + ex.Message, "SQL");
}
finally
{
conn.Close();
}
}

private void Workstation_Load(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_Panel2_Paint(object sender, PaintEventArgs e)
{

}

private void splitContainer1_SplitterMoved(object sender, SplitterEventArgs e)
{

}

private void CreateButton_Click(object sender, EventArgs e)
{
logCall.Show();
}

private void closeButton_Click(object sender, EventArgs e)
{
System.Windows.Forms.Application.Exit();
}

private void button1_Click(object sender, EventArgs e)
{
simple_search searchFunction = new simple_search();
searchFunction.Show();
}

private void timer_Tick(object sender, EventArgs e)
{
populateWorkLoadList();
populateIncomingCallReports();
}

private void textBox1_TextChanged(object sender, EventArgs e)
{

}

private void listBox1_SelectedIndexChanged_2(object sender, EventArgs e)
{

}

private void wListGroupBox_Enter(object sender, EventArgs e)
{

}

private void wLoadListView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems = wLoadListView.SelectedItems;

string selectedTitle2 = selectedItems[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle2);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void wLoadListView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems3 = wLoadListView.SelectedItems;

string selectedTitle4 = selectedItems3[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle4);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void incomingCallReportslistView_SelectedIndexChanged(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems5 = incomingCallReportslistView.SelectedItems;

string selectedTitle6 = selectedItems5[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand select = new SqlCommand("SELECT Description FROM [Call Reports] WHERE Title = @.Title", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle6);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
desc = dr.GetString(0);
}

conn.Close();
}

descriptionTextBox.Text = desc;
descriptionTextBox.Refresh();
}

private void incomingCallReportslistView_SelectedIndexChanged_1(object sender, EventArgs e)
{
ListView.SelectedListViewItemCollection selectedItems7 = incomingCallReportslistView.SelectedItems;

string selectedTitle8 = selectedItems7[0].Text;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

SqlCommand select = new SqlCommand("SELECT call_id FROM [Call Reports] WHERE Title = @.Title AND StatusOpen = 'true'", conn);

select.Parameters.AddWithValue("@.Title", selectedTitle8);

SqlDataReader dr = select.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
Global.globalCall_id = dr.GetInt32(0);
}
dr.Close();
conn.Close();
}
OpenCall openCall = new OpenCall();
openCall.Show();
}

private void Analyse_Click(object sender, EventArgs e)
{

SearchFunction analysis = new SearchFunction();
analysis.Show();

// DataSet DS = (DataSet)Session["MyDS"];

Excel.ApplicationClass excel = new ApplicationClass();

excel.Application.Workbooks.Add(true);
System.Data.DataTable table = this.CMSDatabaseDataSet.Tables[0];
int ColumnIndex = 0;
foreach (System.Data.DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[1, ColumnIndex] = col.ColumnName;
}
int rowIndex = 0;
foreach (DataRow row in table.Rows)
{
rowIndex++;
ColumnIndex = 0;
foreach (DataColumn col in table.Columns)
{
ColumnIndex++;
excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
}
}
excel.Visible = true;
Worksheet worksheet = (Worksheet)excel.ActiveSheet;
worksheet.Activate();