Could a change of SQL ports break a data reader?

[Bill in Kansas City]

Can a change of SQL port affect a SQLDataReader's ability to pull data?
The code that follows was working just fine yesterday, and the only
thing that's changed between then and now is the SQL port. The
connection string is updated with the port - aaa.bbb.ccc.ddd ,eeee -
where eeee is the new port number. The connection comes in just fine,
state = 1 (though yesterday it was "Open", weird), and the query runs
without error in Query Analyzer.

Dim connectionStrin g As String
connectionStrin g = ConfigurationSe ttings.AppSetti ngs("CxnStr")
' now we have a valid connection string
' Network Library=DBMSSOC N;Data Source=aaa.bbb. ccc.ddd,eeee;In itial
Catalog=myDB;Us er Id=myUser;Passw ord=myPassword; application name=MyApp;

Dim sSQL As String = "exec
spVehicle_GetVe hicleInformatio nByKeyfield " & Request("keyFie ld") & ",
'" & Request("status ") & "'"
' which translates to exec
spVehicle_GetVe hicleInformatio nByKeyfield 12345, 'Used', which works

Dim conn As New SqlConnection(c onnectionString )
Dim cmd As New SqlCommand(sSQL , conn)
Dim objDr As SqlDataReader
conn.Open()
' state = 1

objDr =
cmd.ExecuteRead er(system.data. commandbehavior .closeConnectio n)
' err.number = 0

if objDr.read() then
' this is where we went yesterday...
makedescription .text = objdr("makedesc ription")
modeldescriptio n.text = objdr("modeldes cription")
modelyear.text = objdr("modelyea r")
stocknumber.tex t = objdr("stocknum ber")
serialnumber.te xt = objdr("serialnu mber")
listprice.text = objdr("listpric e")
else
' this is where we go now
response.write( "Unable to write vehicle information<BR> ")
end if

The obvious answer is flip the port back to default and see if my code
starts working, but I'd like to avoid that if I can. To do that, I'd
have to disconnect all of our users and change the connection strings
in five different applications; not a good thing. I'd like to find an
answer like "Oh, all you have to do is specify the spoo of the fleem,
and you're all set..."

Thanks in advance for any insights.

- Bill in KC

[Scott Allen]

Hi Bill:

What happens now? Is there an exception? What's the error message? Are
you sure the query is still returning rows? Can you use SQL Profiler
to catch the statement that gets executed, paste it into query
analyzer and see if any rows come back?

Just some troubleshooting tips,

--
Scott
http://www.OdeToCode.com/blogs/scott/

On 16 Jun 2005 08:03:45 -0700, "Bill in Kansas City"
<se******@hotma il.com> wrote:

Can a change of SQL port affect a SQLDataReader's ability to pull data?
The code that follows was working just fine yesterday, and the only
thing that's changed between then and now is the SQL port. The
connection string is updated with the port - aaa.bbb.ccc.ddd ,eeee -
where eeee is the new port number. The connection comes in just fine,
state = 1 (though yesterday it was "Open", weird), and the query runs
without error in Query Analyzer.

Dim connectionStrin g As String
connectionStrin g = ConfigurationSe ttings.AppSetti ngs("CxnStr")
' now we have a valid connection string
' Network Library=DBMSSOC N;Data Source=aaa.bbb. ccc.ddd,eeee;In itial
Catalog=myDB;U ser Id=myUser;Passw ord=myPassword; application name=MyApp;

Dim sSQL As String = "exec
spVehicle_GetV ehicleInformati onByKeyfield " & Request("keyFie ld") & ",
'" & Request("status ") & "'"
' which translates to exec
spVehicle_GetV ehicleInformati onByKeyfield 12345, 'Used', which works

Dim conn As New SqlConnection(c onnectionString )
Dim cmd As New SqlCommand(sSQL , conn)
Dim objDr As SqlDataReader
conn.Open()
' state = 1

objDr =
cmd.ExecuteRea der(system.data .commandbehavio r.closeConnecti on)
' err.number = 0

if objDr.read() then
' this is where we went yesterday...
makedescription .text = objdr("makedesc ription")
modeldescriptio n.text = objdr("modeldes cription")
modelyear.text = objdr("modelyea r")
stocknumber.tex t = objdr("stocknum ber")
serialnumber.te xt = objdr("serialnu mber")
listprice.text = objdr("listpric e")
else
' this is where we go now
response.write( "Unable to write vehicle information<BR> ")
end if

The obvious answer is flip the port back to default and see if my code
starts working, but I'd like to avoid that if I can. To do that, I'd
have to disconnect all of our users and change the connection strings
in five different applications; not a good thing. I'd like to find an
answer like "Oh, all you have to do is specify the spoo of the fleem,
and you're all set..."

Thanks in advance for any insights.

- Bill in KC