468,514 Members | 1,436 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

Could a change of SQL ports break a data reader?

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 connectionString As String
connectionString = ConfigurationSettings.AppSettings("CxnStr")
' now we have a valid connection string
' Network Library=DBMSSOCN;Data Source=aaa.bbb.ccc.ddd,eeee;Initial
Catalog=myDB;User Id=myUser;Password=myPassword;application name=MyApp;

Dim sSQL As String = "exec
spVehicle_GetVehicleInformationByKeyfield " & Request("keyField") & ",
'" & Request("status") & "'"
' which translates to exec
spVehicle_GetVehicleInformationByKeyfield 12345, 'Used', which works

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

objDr =
cmd.ExecuteReader(system.data.commandbehavior.clos eConnection)
' err.number = 0

if objDr.read() then
' this is where we went yesterday...
makedescription.text = objdr("makedescription")
modeldescription.text = objdr("modeldescription")
modelyear.text = objdr("modelyear")
stocknumber.text = objdr("stocknumber")
serialnumber.text = objdr("serialnumber")
listprice.text = objdr("listprice")
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

Nov 19 '05 #1
1 1023
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******@hotmail.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 connectionString As String
connectionString = ConfigurationSettings.AppSettings("CxnStr")
' now we have a valid connection string
' Network Library=DBMSSOCN;Data Source=aaa.bbb.ccc.ddd,eeee;Initial
Catalog=myDB;User Id=myUser;Password=myPassword;application name=MyApp;

Dim sSQL As String = "exec
spVehicle_GetVehicleInformationByKeyfield " & Request("keyField") & ",
'" & Request("status") & "'"
' which translates to exec
spVehicle_GetVehicleInformationByKeyfield 12345, 'Used', which works

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

objDr =
cmd.ExecuteReader(system.data.commandbehavior.clo seConnection)
' err.number = 0

if objDr.read() then
' this is where we went yesterday...
makedescription.text = objdr("makedescription")
modeldescription.text = objdr("modeldescription")
modelyear.text = objdr("modelyear")
stocknumber.text = objdr("stocknumber")
serialnumber.text = objdr("serialnumber")
listprice.text = objdr("listprice")
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


Nov 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.