SQLDataReader.Read() help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ppb1701
    New Member
    • Sep 2008
    • 4

    SQLDataReader.Read() help

    I have a function that cals a sql database and spits the rows back to an arraylist for me to do whatever with. I've not had any trouble with it till I ran into this. I pulled some values, copied them to another arraylist and based on the id numbers I run a second query to determine a color change in the html table I'm outputting. My problem is it works perfect when I run in visual studio, however, when I publish it and try it it finds nothing (the read() returns false regardless). I've checked and it is getting the correct sql query passed to it. Any ideas why it might do this?


    Thanks
    Patrick

    Code:
       Shared Sub sqlcmd(ByVal returned As Integer, ByVal query As String, ByVal getdata As Boolean)
            'returned is how many fields are returned
            'query is the sql query to be run
            'getdata is  a switch for whether to load data or not.
            Dim sql As New System.Data.SqlClient.SqlCommand(query, sqlconn)
            sqlconn.Open()
            If getdata Then
                dr = sql.ExecuteReader()
                Dim j = 0
                Dim i
                count = returned
                b.Clear() 'make sure the arraylist is empty
                While dr.Read()
    
                    For i = 0 To returned - 1
                        b.Add(dr(i))
                    Next
                    b.TrimToSize()
    
                    'sets b as an arraylist 
                    'every "returned/count" is a row
                    j = j + 1
                End While
            Else
                sql.ExecuteNonQuery() 'if getdata\=false...Ex: an update query
            End If
            dr.Close()
            sqlconn.Close()
        End Sub
  • Frinavale
    Recognized Expert Expert
    • Oct 2006
    • 9749

    #2
    Make sure that your database is configured to allow your account to log in remotely...and make sure that your connection string is correct.

    -Frinny

    Comment

    • balabaster
      Recognized Expert Contributor
      • Mar 2007
      • 798

      #3
      Originally posted by Frinavale
      Make sure that your database is configured to allow your account to log in remotely...and make sure that your connection string is correct.

      -Frinny
      Are you using SQL 2005 Express? If so, is IIS and SQL Server running on the same box? The reason I ask is that SQL Server 2005 Express doesn't allow remote connections from different machines on your network, so you will see security issues if the two are on different boxes...

      Comment

      • ppb1701
        New Member
        • Sep 2008
        • 4

        #4
        It's talking to a sql 2000 server db that I query several times both automatically and from user interactions. All the other queries are working fine.

        Comment

        • ppb1701
          New Member
          • Sep 2008
          • 4

          #5
          Thanks for the help, I stumbled into it, running back thru things again. it was changing the date format on the date variable I was putting in that query.

          Comment

          • Frinavale
            Recognized Expert Expert
            • Oct 2006
            • 9749

            #6
            Originally posted by ppb1701
            Thanks for the help, I stumbled into it, running back thru things again. it was changing the date format on the date variable I was putting in that query.
            Yeah you really have to watch the Culture settings.
            If you are saving dates as an "en-US" culture and the person using the system is running under "fr-FR" then the dates will be formatted incorrectly. Look into using the DateTimeFormatI nfo object found in the System.Globaliz ation namespace to help format your dates correctly for storage.

            Did you fix the problem?

            -Frinny

            Comment

            • ppb1701
              New Member
              • Sep 2008
              • 4

              #7
              Originally posted by Frinavale
              Yeah you really have to watch the Culture settings.
              If you are saving dates as an "en-US" culture and the person using the system is running under "fr-FR" then the dates will be formatted incorrectly. Look into using the DateTimeFormatI nfo object found in the System.Globaliz ation namespace to help format your dates correctly for storage.

              Did you fix the problem?

              -Frinny
              Yep, I just wrapped the passed date in a format (string, "MM/dd/yyyy") and it worked perfectly. I'll have to check that DateTime thing, sounds like it could be handy. The added fun is with this database some of the dates are MM/dd/yyyy, some are MM/dd/yy, some are MM/dd/yyyy HH:MM:SS, and some are yyyyddMM. I think the people that originally set it up couldn't make up their minds :)

              Comment

              Working...