Re: Could a change of SQL ports break a data reader?

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"
<seamlyne@xxxxxxxxxxx> 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.closeConnection)
> ' 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

.

Relevant Pages

  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't ...
    (microsoft.public.access.forms)
  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... To see the SQL of a query, open the query in Design view, then select SQL View from the View menu. ... Is there perhaps a way that I can insert a combo box on my calendar form for "CompanyName", in which I can select a company from the list, and then add/view information on the calendar for that specific company? ... The fact that the previous line (Dim db As DAO.Database) doesn't cause problems implies that the DAO library is properly referenced. ...
    (microsoft.public.access.forms)
  • Re: Ordering a filtered proximity search
    ... >>> search variable, filtering it, and ordering it at the same time. ... >>> appended query approach. ... if p1 is empty then your SQL syntax is messed up. ... Dim SearchTerm1 ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Pass Through Query Question
    ... "Joe Williams" wrote in message ... > when you mentioned setting the connection string in the properties of the ... It runs with no problem using SQL query anlayzer. ...
    (microsoft.public.access.queries)
  • Re: Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
    ... Number of characters in a cell in the query design grid 1,024 ... Since the SQL parser mentions an error in the FROM clause, ... The only problem I can see is the SQL string is very long. ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)
Loading