CODEWITHSUNDEEP
sql-serverado.net
RichieRich
RichieRich

Reputation: 197

Weird SQL Error (Bug)

So this is really weird.

I run a sql command from .net on sqlserver with a 'Select Count(*)' and get a response like "Needs attention CA" (which is in a varchar of one field of one record of the inner joined tables).

Huh? How can Count(*) return a string? 999 out of 1000 times this code executes correctly. Just sometimes on some clients servers it will throw a string of errors for an hour or so only to miraculously stop again.

This is my sqlcommand:

SELECT Count(*) 
FROM   patientsappointments 
       INNER JOIN appointmenttypes 
               ON patientsappointments.appointmenttypeid = 
                  appointmenttypes.appointmenttypeid 
WHERE  ( ( patientsappointments.date > @WeekStartDate 
           AND patientsappointments.date < @WeekFinishDate ) 
         AND ( patientsappointments.status = 'Pending' ) 
         AND ( patientsappointments.doctorid = @DoctorID ) 
         AND ( appointmenttypes.appointmentname <> 'Note' ) )

And these are the parameters:

@WeekStartDate       = 24/06/2013 12:00:00 AM (DateTime)
@WeekFinishDate      = 1/07/2013 12:00:00 AM (DateTime)
@DoctorID            = 53630c67-3a5a-406f-901c-dbf6b6d1b20f (UniqueIdentifier)

I do a sqlcmd.executescalar to get the result. Any ideas?

The actual executed code is:

    SyncLock lockRefresh
        Dim WeekFulfilled, WeekPending As Integer
                Using conSLDB As New SqlConnection(modLocalSettings.conSLDBConnectionString)
                    Dim mySQL As SqlCommand
                    mySQL = New SqlCommand("SELECT COUNT(*) FROM PatientsAppointments INNER JOIN AppointmentTypes ON PatientsAppointments.AppointmentTypeID = AppointmentTypes.AppointmentTypeID " & _
                                                "WHERE ((PatientsAppointments.Date > @WeekStartDate AND PatientsAppointments.Date < @WeekFinishDate) AND (PatientsAppointments.Status = 'Pending') " & _
                                                "AND (PatientsAppointments.DoctorID = @DoctorID) AND (AppointmentTypes.AppointmentName <> 'Note'))", conSLDB)
                    Try
                        mySQL.Parameters.Add("@WeekStartDate", SqlDbType.DateTime).Value = MonthCalendar1.SelectionStart.Date.AddDays(-MonthCalendar1.SelectionStart.Date.DayOfWeek).AddDays(1)
                        mySQL.Parameters.Add("@WeekFinishDate", SqlDbType.DateTime).Value = MonthCalendar1.SelectionStart.Date.AddDays(-MonthCalendar1.SelectionStart.Date.DayOfWeek).AddDays(8)
                        mySQL.Parameters.Add("@DoctorID", SqlDbType.UniqueIdentifier).Value = cboDoctors.SelectedValue
                        conSLDB.Open()
                        'got errors here like "Conversion from string "R2/3" to type 'Integer' is not valid." Weird.
                        'failing on deadlock - maybe due to simultaneous updating from udp event. Try adding random delay to refresh
                        WeekPending = mySQL.ExecuteScalar
                    Catch ex As Exception
                        ErrorSender.SendError("frmAppointmentBook - RefreshHeader 1", ex, New String() {String.Format("mySQL.commandtext: {0}", mySQL.CommandText), _
                                                                                                        String.Format("mySQL.Parameters: {0}", clsErrorSender.ParamsListToString(mySQL.Parameters))})
                    End Try
                    Me.lblPendingWeek.Text = WeekPending
                    Try
                        mySQL.CommandText = "SELECT COUNT(*) FROM PatientsAppointments INNER JOIN AppointmentTypes ON PatientsAppointments.AppointmentTypeID = AppointmentTypes.AppointmentTypeID WHERE " & _
                                                   "(PatientsAppointments.Date > @WeekStartDate AND PatientsAppointments.Date < @WeekFinishDate) AND (PatientsAppointments.Status = 'Fulfilled') AND " & _
                                                   "(PatientsAppointments.DoctorID = @DoctorID) AND (AppointmentTypes.AppointmentName <> 'Note')"
                        'didn't get the error here... but just in case...
                        WeekFulfilled = mySQL.ExecuteScalar
                    Catch ex As Exception
                        ErrorSender.SendError("frmAppointmentBook - RefreshHeader 2", ex, New String() {String.Format("mySQL.commandtext: {0}", mySQL.CommandText)})
                    End Try
                    conSLDB.Close()
                End Using
End SyncLock

The exact error message is:

System.InvalidCastException
Conversion from string "Needs Attention DC" to type 'Integer' is not valid.

Upvotes: 0

Views: 299

Answers (4)

John Tseng
John Tseng

Reputation: 6352

I am going to make another guess. I am guessing that this is a multi threading issue. You probably are sharing the connection between multiple threads. Once in a while the thread will get that man from somewhere else and execute it. Make sure that the connection variable is local, and only one thread can access it at a time.

As Martin points out, the following answer is wrong. I'm keeping this here to show that this is wrong.

From what everyone has already said, there is a type mismatch on your columns. Since your where clause appears to be fine, and your join is fine, it must be elsewhere. I would check to see if patientsappointments or appointmenttypes are views. Maybe the view has a join that's throwing the exception. Check the schema definition of all your joins/where's. Somewhere in there you're storing integers in a character field. It's fine for most rows, but one of them has your string.

If it's not in your views, it may be a trigger somewhere. The point is that somewhere there is a schema mismatch. Once you find your schema mismatch, you can find the row by querying for that string.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96600

Since this doesn't always happen, it must be a result of one of the paramenter values that is sent in. This is one of the lbuiggest problems with using dynamic SQL. What I would do is create the dymanic SQl and then store it in a database logging table with the date and time and user who executed it. Then when you get the exception, you can find the exact SQL code that was sent. Most likely you need more controls on the input variables to ensure the data placed in them is of the correct data type.

Upvotes: 0

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

Your problem has nothing to do with the COUNT(*) portion of your code. The problem is somewhere else in your query. What that particular error is telling you is that at some point you are comparing a character field (it probably usually contains numbers) to an integer field. One of the values of the character field happens to be "Needs Attention DC". If I had to guess it is probably either patientsappointments.appointmenttypeid or appointmenttypes.appointmenttypeid. Double check the datatype of each of those columns to make sure they are in fact INT. If they are both INT then start checking the other explicitly named columns in your query to see if you have any surprises.

Upvotes: 2

bastos.sergio
bastos.sergio

Reputation: 6764

You must have an error somewhere in your implementation...

Per the documentation, count always returns an int data type value.

Upvotes: 1

Related Questions