Format of the initialization string does not conform to specification starting at index 0.????

[Twyce]

I'm trying to export data from my Sql server database to an excel spreadsheet using bulkCopy.The problem is that whenever i run my application throws "Format of the initialization string does not conform to specification starting at index 0" error.Whatam i doing wrong? Here is my code public class CopyData { string _sourceConnectionString = "Data Source= sict-sql;Initial Catalog=PSAtechZ;Integrated Security=True;"; string _destinationConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\+PSATECHZ EYES ONLY+\\PSAtechZTimetableSystem\\Students.xls" + "; Extended Properties='Excel 12.0; IMEX=1; HDR=YES'"; public CopyData(string sourceConnectionString, string destinationConnectionString) { _sourceConnectionString = sourceConnectionString; _destinationConnectionString = destinationConnectionString; } public void CopyTable(string StudentModule) { using (SqlConnection source = new SqlConnection(_sourceConnectionString)) { string sql = string.Format("SELECT * FROM [{0}]", StudentModule); SqlCommand command = new SqlCommand(sql, source); source.Open(); SqlDataReader dr = command.ExecuteReader(); //IDataReader dr = command.ExecuteReader(); using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString)) { copy.DestinationTableName = StudentModule; copy.WriteToServer(dr); } } } } i call this class under button export

[leppie]

That could only happen with the string.Format, but I cannot see anything wrong with it.

xacc.ide - now with TabsToSpaces support
IronScheme - 1.0 alpha 4a out now (29 May 2008)

[Wendelius]

Never mind the first post from me. Misread the line. Could it be this one:

Twyce wrote:

string _destinationConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\+PSATECHZ EYES ONLY+\\PSAtechZTimetableSystem\\Students.xls" + "; Extended Properties='Excel 12.0; IMEX=1; HDR=YES'";

Since you have @ before the string, you don't need to escape backslash (even though it's on a separate string). Using debugger:

@"part1" + "\\part2" + "\\part3"

gives

"part1\\part2\\part3"

[Twyce]

thanx for ur reply but i get the error when it gets to this line using (SqlConnection source = new SqlConnection(_sourceConnectionString))

[Twyce]

i think the problem is with my source connectionString

[Pete OHanlon]

The problem would appear to be at the line

string sql = string.Format("SELECT * FROM [{0}]", StudentModule);

Step over this line in the debugger and see what StudentModule contains.

Deja View - the feeling that you've seen this post before.

My blog | My articles

[Twyce]

thanx for ur reply.Thing is it throwsan error when the debugger gets to this line using (SqlConnection source = new SqlConnection(_sourceConnectionString))

[Pete OHanlon]

When you step over this line, what's in _sourceConnectionString? I ask this because you've initialised it to a value, and then you change it in the constructor.

Deja View - the feeling that you've seen this post before.

My blog | My articles

[Twyce]

What do you mean when you say I change it in the constructor? The debugger does not even allo me to step over the line,it just keeps throwing the error

[Pete OHanlon]

The following is your code:

public class CopyData
{
string _sourceConnectionString = "Data Source= sict-sql;Initial Catalog=PSAtechZ;Integrated Security=True;";
string _destinationConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\+PSATECHZ EYES ONLY+\\PSAtechZTimetableSystem\\Students.xls" + "; Extended Properties='Excel 12.0; IMEX=1; HDR=YES'";

public CopyData(string sourceConnectionString, string destinationConnectionString)
{
_sourceConnectionString = sourceConnectionString;
_destinationConnectionString = destinationConnectionString;
}

public void CopyTable(string StudentModule)
{
using (SqlConnection source = new SqlConnection(_sourceConnectionString))
{
string sql = string.Format("SELECT * FROM [{0}]", StudentModule);

SqlCommand command = new SqlCommand(sql, source);

source.Open();
SqlDataReader dr = command.ExecuteReader();
//IDataReader dr = command.ExecuteReader();

using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString))
{
copy.DestinationTableName = StudentModule;
copy.WriteToServer(dr);
}
}
}
}

I've highlighted the line that changes the value. The code that creates this class is presumably doing something along the lines of CopyData c = new CopyData("connectionString", "destConnectionString");. Step into the CopyTable method and see what the value of _sourceConnectionString is before you execute the line

using (SqlConnection source = new SqlConnection(_sourceConnectionString))

Deja View - the feeling that you've seen this post before.

My blog | My articles

[Twyce]

it is not giving me a particular value just _sourceConnection.sourceConnection

[Wendelius]

I think you're missing Pete's point. In the constructor you modify the value of the predefined _sourceConnectionString. Try adding the following message box:

...
public void CopyTable(string StudentModule){
System.Windows.Forms.MessageBox.Show("Going to connect to: " + _sourceConnectionString);
using (SqlConnection source = new SqlConnection(_sourceConnectionString))
{
...

What is shown as the connection string that is used for connection? Is it what you would want ("Data Source= sict-sql;Initial Catalog=PSAtechZ;Integrated Security=True;") or something else.