Simple code worked fine with SQL2005, but now has a problem with SQL2000.

[Radu]

Hi. This code worked fine with SQL2005, but has a problem with
SQL2000.... Which one ????

cnSQL = New
SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
cnSQL.Open()

fails with the error

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

if in web.config I have

<!-- SQL2000, with Username/Password....-->
<add
key="ConnectionString"
value="Data Source='SQLDEV001\SQLDEV001;Initial Catalog='SOP';User
ID=SOP_DEV;Password=AAA;"
/>

and

<add
name="OrderingProcess"
connectionString="server='SQLDEV001\SQLDEV001';database='SOP';User
ID=SOP_DEV;Password=AAA;"
providerName="System.Data.SqlClient"
/>

which is used in some page as:

<asp:SqlDataSource
ID="LocationSqlDataSource"
SelectCommand="SELECT
Measures.[Measure ID] as ID,
Measures.[Measure Description] as Measure,
Locations_BY_Measure.[Locations] as Location
FROM Locations_BY_Measure INNER JOIN Measures
ON Locations_BY_Measure.[Measure ID] = Measures.[Measure ID]"
EnableCaching="True"
ConnectionString="<%$ ConnectionStrings:OrderingProcess %>"
CacheDuration="60"
FilterExpression="ID = {0}"
RunAt="server">


while the exact same code works great with the 2005 settings:

<!-- SQL2005, with Username/Password....-->
<add
key="ConnectionString"
value="Data Source='myOtherComputer\SQLEXPRESS';Initial
Catalog='SOP';User ID=SOP_DEV;Password=AAA;"
/>

and

<add
name="OrderingProcess"
connectionString="server='SQLDEV001\SQLDEV001';database='SOP';User
ID=SOP_DEV;Password=AAA;"
providerName="System.Data.SqlClient" />

Of course, using Enterprise Manager I can connect using the SOP_DEV
and AAA info to both databases.

Could you help me, please ?

Thanks, Alex.

 

[Guest]

'SOP' - try removing the single quotes.
Peter

 

[sloan]

www.connectionstrings.com has all the syntaxes.

be anal about spaces semi colons and single quotes.

 

[Alexey Smirnov]

<!-- SQL2000, with Username/Password....-->
<add
key="ConnectionString"
value="Data Source='SQLDEV001\SQLDEV001;Initial Catalog='SOP';User
ID=SOP_DEV;Password=AAA;"
/>

and

<add
name="OrderingProcess"
connectionString="server='SQLDEV001\SQLDEV001';database='SOP';User
ID=SOP_DEV;Password=AAA;"
providerName="System.Data.SqlClient"
/>

they are the same strings, why the syntax is different?

I've tried to connect using such strings to my local SQL 2000 server
and I have no problem with quotes.

Alex, what about the named instance? Are you sure about the name?

 

[Radu]

they are the same strings, why the syntax is different?

I've tried to connect using such strings to my local SQL 2000 server
and I have no problem with quotes.

Alex, what about the named instance? Are you sure about the name?- Hide quoted text -

- Show quoted text -

Hi. Finally.... it works.... Hmmmmmmm..... I took out all quotes, thus
the string (working for 2005)

<add name="OrderingProcess"
connectionString="server='C227226\SQLEXPRESS';database='SOP';User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

became for 2000

<add name="OrderingProcess"
connectionString="server=SQLDEV001\SQLDEV001;database=SOP;User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

Note that the string (otherwise correct, from a 2005 standpoint)

<add name="OrderingProcess"
connectionString="server='SQLDEV001\SQLDEV001';database='SOP';User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

with single quotes did *NOT* work, so, to make it work,
'SOP'
became
SOP
and
'SQLDEV001\SQLDEV001'
became
SQLDEV001\SQLDEV001

Very strange, indeed.... looks like the strings are parsed
differently.... Again, as I was saying, Hmmmmmmmm......

I lost almost a whole morning fighting this problem !
Anyway, it works now. Thank you all very much for your kind
answers ))))

Alex.

 

[Alexey Smirnov]

Hi. Finally.... it works.... Hmmmmmmm..... I took out all quotes, thus
the string (working for 2005)

<add name="OrderingProcess"
connectionString="server='C227226\SQLEXPRESS';database='SOP';User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

became for 2000

<add name="OrderingProcess"
connectionString="server=SQLDEV001\SQLDEV001;database=SOP;User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

Note that the string (otherwise correct, from a 2005 standpoint)

<add name="OrderingProcess"
connectionString="server='SQLDEV001\SQLDEV001';database='SOP';User
ID=SOP_DEV;Password=AAA;" providerName="System.Data.SqlClient" />

with single quotes did *NOT* work, so, to make it work,
'SOP'
became
SOP
and
'SQLDEV001\SQLDEV001'
became
SQLDEV001\SQLDEV001

Very strange, indeed.... looks like the strings are parsed
differently.... Again, as I was saying, Hmmmmmmmm......

I lost almost a whole morning fighting this problem !
Anyway, it works now. Thank you all very much for your kind
answers ))))

Alex.

I don't get it.

The following code (using single quotes) works for me:

<asp:GridView ID="GridView1" DataSourceID="SqlDataSource1"
runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="SELECT ... FROM ..."
ConnectionString="Data Source='localhost';Initial
Catalog='databasename';User ID=sa;Password=pass;" />

as well as:

<asp:GridView ID="GridView1" DataSourceID="SqlDataSource1"
runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="SELECT ... FROM ... "
ConnectionString="<%$ ConnectionStrings:OrderingProcess %>" />

in web.config

<connectionStrings>
<add name="OrderingProcess"
connectionString="Data Source='localhost';Initial
Catalog='databasename';User ID=sa;Password=pass;"
providerName="System.Data.SqlClient" />
</connectionStrings>

--ASP.NET 2.0, SQL 2000

where is the difference?

 

[Guest]

The connection string name=value pairs do not take 'quotation' marks. Unless
of course your database name really is 'SOP' instead of SOP.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net

 

[Roland Dick]

Hi Alex,

just to add to the other posters: Unless it's a typo, the first
connection string is missing the closing quotation mark after the
instance name (apart from the fact that you don't need quotation marks).

<add
key="ConnectionString"
value="Data Source='SQLDEV001\SQLDEV001;Initial Catalog='SOP';User
ID=SOP_DEV;Password=AAA;"
/>

Roland

 

[Radu]

Hi Alex,

just to add to the other posters: Unless it's a typo, the first
connection string is missing the closing quotation mark after the
instance name (apart from the fact that you don't need quotation marks).


Roland

Hi, all.

It's true, and it's strange. The same string worked great in 2005, but
stopped working with the above-mentioned error in 2000 (of course, I
have changed the name of the instance to match). At least this is the
way it behaved on my computer at work - I'm curious to test it at
home. Anyway, it all works now (without any quotation marks), and
that's what counts - I already forgot this aggravation, like so many
others )))))) This is the nature of this beast, I guess.

Thank you all for spending the time reading this. I honestly
appreciate it !

Alex.

PS. Yes, Roland, it was a typo