Error on SQL count into ExecuteScalar()

Question

When executing the below code I get the following error.

"Additional information: Conversion from string "_01202478334" to type 'Double' is not valid."

Code:

          Using connn As New SqlClient.SqlConnection("server=inlt01\SQLEXPRESS; database=DaisyServices; integrated security=yes")
          Using cmdz As SqlClient.SqlCommand = conn.CreateCommand()

          cmdz.CommandText = "SELECT CLI, FromDate, ToDate, [Description], TotalCost, COUNT(*) as Count FROM [" + FileNameOnly + "] GROUP BY CLI, FromDate, ToDate, [Description], TotalCost HAVING COUNT(*) > 1"
          connn.Open()

          If cmdz.ExecuteScalar() > 1 Then

          'Error if name in use
          MessageBox.Show("Duplicate records exist on imported file!!")

In order to troubleshoot I removed the CLI field, but then I get a new error

"Additional information: Operator '>' is not defined for type 'Date' and type 'Integer'"

I am using some very similar code on a separate form and it runs without any error.

Here is my working code:

          Using connn As New SqlClient.SqlConnection("server=inlt01\SQLEXPRESS; database=DaisyBilling; integrated security=yes")
          Using cmdz As SqlClient.SqlCommand = conn.CreateCommand()

          cmdz.CommandText = "SELECT CustomerCLI, calldate, calltime, duration, TelephoneNumber, COUNT(*) as Count FROM [" + FileNameOnly + "] GROUP BY CustomerCLI, calldate, calltime, duration, TelephoneNumber HAVING COUNT(*) > 1"
          connn.Open()

          If cmdz.ExecuteScalar() > 1 Then

          'Error if name in use
          MessageBox.Show("Duplicate records exist on imported file!!")

How come the code works on my other form, but not on this one?

NB. The SQL executes ok if I run the query direct from SQL server

Any help greatly appreciated

`ExecuteScalar` is intended for when the query returns one value only. If you put the `count` as the first element in your `SELECT`, it may work. — Andrew Morton, Aug 29 '14 at 18:17
Thanks you, that worked! Happy to mark as Answered if you want to add below. Thanks again — user3580480, Aug 29 '14 at 18:21

score 1 · Accepted Answer · answered Aug 29 '14 at 18:26

ExecuteScalar is intended more for when the query returns one value only. If you put the count as the first element in your SELECT, it will work.

From the documentation for SqlCommand.ExecuteScalar Method:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Error on SQL count into ExecuteScalar()

1 Answers1