'    stored procedure
'    I use 5 parameters to run these queries

    CREATE TABLE TmpTable2
        (
            conID       BIGINT,
            conString      VARCHAR(150),
            conDateTime  SMALLDATETIME
        )

    -- here I run the queries on the other tables to and insert into TmpTable2 (working fine)

    SELECT    conID, conString, conDateTime
    FROM      TmpTable2
    ORDER BY  conDateTime DESC
    FOR       XML AUTO

    DROP TABLE TmpTable2

    Private Sub GetReportList()

        Dim builder As New SqlConnectionStringBuilder()
        Dim dsInitialReportList As DataSet = New DataSet()
        Dim dsReportList As New DataSet
        Dim dtDateFrom As Date
        Dim dtDateTo As Date
        Dim dv As DataView
        Dim intSQLType As Integer = 1

        builder.ConnectionString = strDBConn

        Using connection As New SqlConnection(builder.ConnectionString)

            Dim command As New SqlCommand("admin_sp_GetReportList", connection)
            Dim xmlr As System.Xml.XmlReader

            command.CommandType = CommandType.StoredProcedure

            command.Parameters.AddWithValue("@param1", txtParam1.Text.Trim)
            command.Parameters.AddWithValue("@param2", txtParam2.Text.Trim)
            command.Parameters.AddWithValue("@param3", txtParam3.Text.Trim)
            command.Parameters.AddWithValue("@param4", txtParam4.Text.Trim)

            Try

                connection.Open()

                xmlr = command.ExecuteXmlReader()
                xmlr.Read()

                Do While xmlr.ReadState <> Xml.ReadState.EndOfFile

                    System.Diagnostics.Debug.WriteLine(xmlr.ReadOuterXml())

                Loop

                MessageBox.Show("Reached the end. Check the output window.")

            Catch ex As Exception

                MessageBox.Show(ex.Message)

            Finally

                If connection.State <> ConnectionState.Closed Then

                    connection.Close()

                End If

            End Try

        End Using

        Cursor = System.Windows.Forms.Cursors.Default

        dsInitialReportList = Nothing
        dsReportList = Nothing

    End Sub

quote:

---

SQLDateTime overflow. Must be between 1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

---

quote:

---

Originally posted by Kristen

Is it anything to do with you using a SMALLdatetime - what about if you use a regular "datetime" datatype for conDateTime instead?

Kristen

---

    CREATE TABLE TmpTable2
        (
            conID        BIGINT,
            conString    VARCHAR(150),
            conDateTime  SMALLDATETIME
        )


    INSERT INTO   TmpTable2 (conID, conString, conDateTime)
        SELECT    incoming1.conID, incoming1.conDataString, incoming1.conUpdateDT
        FROM      incoming1
        WHERE     conID = @in_intConID
        AND       CONVERT(DATETIME, CONVERT(VARCHAR(11), incoming1.conUpdateDT, 101)) = @in_dtDateFrom
        ORDER BY  incoming1.conID ASC

    INSERT INTO   TmpTable2 (conID, conString, conDateTime)
        SELECT    incoming2.conID, incoming2.conDataString, incoming2.conUpdateDT
        FROM      incoming2
        WHERE     conID = @in_intConID
        AND       CONVERT(DATETIME, CONVERT(VARCHAR(11), incoming2.conUpdateDT, 101)) = @in_dtDateFrom
        ORDER BY  incoming2.conID ASC

    -- I do this for 16 other tables (and unfortunately the db layout
    -- can't be changed because of how the data is coming in)

    SELECT    conID, conString, conDateTime
    FROM      TmpTable2
    ORDER BY  conDateTime DESC
    FOR       XML AUTO

    DROP TABLE TmpTable2