SQLDataReader.Read() help

[ppb1701]

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

   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]

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

[balabaster]

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...

[ppb1701]

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.

[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.

[Frinavale]

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

[ppb1701]

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 :)