Problem with SQL and Parameters...

[mandelbrot]

Dear All,


Im having a bit of bother with the following code. A generic SQL exception is returned each time, but I cant for the life of me track down the problem.

    Private Function GetConnectionString(ByVal paramServer As String, ByVal paramDatabase As String) As String
        Define the connection and command to connect to the database name server...
        Dim localConn As New System.Data.SqlClient.SqlConnection("integrated security=SSPI;data source=""stm-sysdev""; " & _
                                                                 "persist security info=False;initial catalog=dbreg; ")
        Dim localComm As New System.Data.SqlClient.SqlCommand
        Open the connection and set the properties of the connection...
        localConn.Open()
        localComm.Connection = localConn
        localComm.CommandText = "select CONNECTSTRING from dbDef where SERVER=@pServer and DBNAME=@pDatabase "
        Define our server and database name as parameters...
        localComm.Parameters.Add(New System.Data.SqlClient.SqlParameter("pServer", paramServer))
        localComm.Parameters.Add(New System.Data.SqlClient.SqlParameter("pDatabase", paramDatabase))
        Read the connection string from the database into the local property...
        Return localComm.ExecuteScalar().ToString
        localComm.Dispose()
        localConn.Close()
        localConn.Dispose()
    End Function

The objective of the code is simply to obtain a connection string from a central database so that we only have to add/change connection strings to this central source to affect every system that relies on it.

If anyone can spot what the problem is Id be really greatful. I think Ive just been looking at this too long...


Many thanks,
Paul.

 

[Nerseus]

The main problem is that the parameters need the @ sign on the front:

localComm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@pServer", paramServer))
localComm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@pDatabase", paramDatabase))

I added the at sign (@) to the front of pServer and pDatabase. I would highly recommend putting the column names in brackets as server and database are almost always reserved words. Like this:

localComm.CommandText = "select [CONNECTSTRING] from [dbDef] where [SERVER]=@pServer and [DBNAME]=@pDatabase"

I also feel obligated to say that storing a connection string in a DB seems like a bad idea... but Ill let you decide that on your own.

-ner

 

[mandelbrot]

Thanks for that, Nerseus. I didnt try putting the @ on the front of the parameter object names, as I thought that the @ sign in the string simply identified that the following text was the parameter (a bit like a token identifying the param name starts after here...).

No, please - tell me more - I appreciate any details regarding programming form that youre willing to throw.

Recently, we underwent the threat of a mass server rename - basically, all of our servers would be renamed (regardless of what was on them) to fit into the framework of what the overlords say should be. This posed the problem that we would have to go around every single application ever written, either edit the XML, INI or registry entry, whether it be on a users PC or server and update accordingly.

So, I came up with the concept of storing all of the connection strings in a database (SQLServer2000 - our DB of preference) so that modifying and recalling would take place from one location for all new systems, and would be reasonably straightforward. Our database name server would be a DB on one of our servers, hence no name change would take place.

Thankfully the rename has been put off for now, but it still may take place in the future...

 

[Nerseus]

Well this would only work if you have a webserver, but normally all DB traffic goes through a webserver. That way, only the webserver has the connection string. It often uses trusted security as well, so that no passwords are seen. The ID the webserver runs as is given permission to the DB.

Maybe your setup is different - maybe pure client/server with no webserver. In the main scenario that I was involved in that had pure client/server, we still had one designated server that youd get your config information from. It could change to point to another DB server if needed, but its name couldnt change.

To help ease a server rename, you can setup a server alias (that may not be the right name). For example, you could use have "ConfigServer" point to the IP of the server that has the config information. Your app would look at something like "\\ConfigServer\Settings.xml" to get its information. If that server is really named "SRV001" and you rename it to "SERV001XP" then youre fine - the ConfigServer alias is all that needs updating.

-ner

 

[mandelbrot]

I see. We seem to have a series of servers all of which are directly accessible via NT authentication. Our min problem is the number of fingers in pies. Rather than have a centralised DBA, there are several DBAs all scattered around the organisation. I do like the idea of referring using a ConfigServer (as thats how our Oracle DBs used to work), but its more political now...

To be honest, Im simply a developer, writing code for databases. Ive thrown together the easiest solution at a nippy pace, and may pay for this later, but its all documented, database diagramed and so on.

Our other problems are basically the number of developers in different areas, all developing on different platforms, etc. We have no standard development practices - its rather disturbing, really, considering what we do...


Thanks for your help, Nerseus.
Paul.