Going nuts - The ConnectionString property has not been initialized - help!

I'm struggling with the different methodologies for using DataSets in 2.0. I can knock this out in no time flat in 1.0, but am bogged down in 2.0. Can anyone guide me on the right track? My code is below. I'm forming a DataSet from a SQL Server Express database from the user's choice from a drop down. My connection string "ASOCTCOConnectionString" is declared in my web.config and used successfully throughout in the WYSIWYG configurations of my databound controls. But I want to control this in code. This si getting frustrating and right now I'm longing for the clean simplicity I had with Web Matrix :(

In any case, any suggestions? It crashes with this error when opening the connection withMyConn.Open(). I just want to be able to configure a DataSet and apply it to my controls and results.

<code>

Dim connectionStringAsString = System.Configuration.ConfigurationManager.AppSettings("ASOCTCOConnectionString")

Dim MyConnAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(connectionString)

Dim MyQuery, SearchStringAsString

Dim myAdapterAsNew System.Data.SqlClient.SqlDataAdapterDim myDataSetAsNew System.Data.DataSetDim mySelectCommandAsNew System.Data.SqlClient.SqlCommand

mySelectCommand.CommandText =

"SELECT * from Device_Type where Category LIKE " & DDLDevType.SelectedItem.Text

mySelectCommand.Connection = MyConn

MyConn.Open()

Grid1.DataSource = myDataSet

Grid1.Databind()

MyConn.Close()

</code>

Are you sure your connection string is in appSettings in the config file? In .NET 2.0, Connection strings were moved to a connectionStrings element. Here is an example:

<configuration><connectionStrings><add name="connectionStringName" connectionString="ConnectionString" providerName="System.Data.SqlClient" /> </connectionStrings></configuration>

If indeed your connection is in connectionStrings and not appSettings, modify your VB code to look like this:

Dim connectionStringAs String = System.Configuration.ConfigurationManager.ConnectionStrings("connectionStringName").ConnectionString

|||

Thanks - that worked. I think I understand. I can either have the connection string in the connection string or App Settings sections of my web.config, and need to address it accordingly, correct?

However, now I get an exception error on the next line

Dim

dataReaderAs System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Error:

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

The syntax looks ok to me, so I'm guessing it's not syntax, rather a malformed statement in this context, but I can't see what. Any suggestions?

Thanks for the help - much appreciated.

|||It is your SQL Command that appears to have the problem. Post it here (edit it if you need to so you don't exploit sensative information. Make a dummy that looks like yours) so we can get it fixed.|||

Hondaman900:

Thanks - that worked. I think I understand. I can either have the connection string in the connection string or App Settings sections of my web.config, and need to address it accordingly, correct?

That is correct. However, ASP.NET 2.0 recommends that you keep the connection string in the connectionStrings element and not AppSettings.

|||

Not exactly what it is you're saying. All my code is posted above, except for the web.config connection string itself. Here it is:

<

connectionStrings>

<

addname="ASOCTCOConnectionString"connectionString="Data Source=PAVILLION\SQLEXPRESS;Initial Catalog=ASOCTCO;Integrated Security=True"providerName="System.Data.SqlClient" />

</

connectionStrings>

The command being passed is a "close" at the end of the offending line.

Does this help?

Thanks again :)

|||

Ah OK. I already forgot your code was at the top

I think the problem is the Command try a command like this, first:

"SELECT * FROM Device_Type"

And see if that works. If it does, then there is a problem with th filtering. I might suggest this:

"SELECT * from Device_Type where Category LIKE '" & DDLDevType.SelectedItem.Text & "'"

I would also recommend that you use Paramters in your command instead of generation a command string. This will help prevent SQL Injection attacks.

|||

Thanks for all the help. My SQL statement wasn't formatted properly and I of course needed the single quotes <slapping forehead>.

I've hit another snag. I'm getting an error saying that mySelectCommand is not declared, even though it's declared on the line above.

Line 17: Dim mySelectCommand As New System.Data.SqlClient.SqlCommand
Line 18:
Line 19: mySelectCommand.CommandText = "SELECT * from Device_Type where Category LIKE '" & DDL_DevType.SelectedItem.Text & "'"
Line 20:
Line 21: mySelectCommand.Connection = MyConn

I thought that maybe I should form mySelectCommand as so:

Dim

mySelectCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand()

but I got the same results. Any suggestions? It used to be so easy to form a DataSet and manipulate the data thereafter in code - can't seem to crack this one.

|||

If I encapsulate this code in a function, such as

Function

GetDDValues(ByVal technologyAsString)As System.Data.IDataReader()

the declaration errors go away. Am I hitting up against a namespace issue?

|||

Here's another version, trying for a DataReader instead of a DataSet, that should also work, but same issue:

Dim connectionStringAsString = System.Configuration.ConfigurationManager.ConnectionStrings("MSOCTCOConnectionString").ConnectionString

Dim queryStringAsString ="SELECT Supplier from Device_Type where Category LIKE '" & DDL_DevType.SelectedItem.Text &"'"

Dim dbConnectionasNew SqlConnection(connectionString)

Dim CmdAsNew SqlCommand(queryString, dbConnection)

Dim objDRAs SqlDataReader

dbConnection.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

I'm being told that dbConnection and objDR are not yet declared when I go to use them in the last two lines. What the heck is going on?? There has to be some fundamental setting in 2.0 that I'm missing.

|||

Looks like I'm answering my own posts!:)

I think I may have found the issue. I forgot to wrap the code in a procedure. I put it in the PageLoad procedure and all seems well again. I confused myself by writing the code in-line in the script section of the page. I was spoiled by the provided wrapper of VS when using a code-behind .vb file.

|||

Glad to see everything is going OK.