Question Syntax Error converting DateTime from Character String

[Rubberlegs]

Hello,

I wonder if anyone can help please!!! I am trying to retrieve a row count from an SQL Db which logs incoming / outgoing email messages to and from our organization.

The Windows Form has a Number Up / Down control, a Button and a Data Grid. The user selects the amount of previous days worth of messages they wish to calculate from the up / down control, and clicks the button which executes the SQL statement and enters the results into a DataGrid. All works fine if I enter the days count manually into the SQL Statement and then run the project. However if I try and insert the variable from the value of the up / down control (IncomingDays) into the statement to indicate the number of days, I get the error shown in the subject line of this post. I have changed the values for Server / DB / UID and password so they do not reflect the true credentials.

Hope this makes sense!!

Here is my code:

Imports System.Data.SqlClient


Public Class Form1

Inherits System.Windows.Forms.Form


' Declare variables

Dim objConnection As SqlConnection = New SqlConnection("server=SERVER; database=DATABASE; uid=uid; pwd=password;")

'Dim objDataSet As DataSet = New DataSet()

Dim MailMeterDataSet1 As DataSet = New DataSet()

' Declare SQLDataAdapter object

Dim objDataAdapter As New SqlDataAdapter()



Private Sub btnCalc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalc.Click



Dim IncomingDays As Integer


' Set IncomingDays input field

IncomingDays = nmUpDn1.Value


'Assign a New SQL Command to the Select Command property

objDataAdapter.SelectCommand = New SqlCommand()


'Set the SelectCommand properties

objDataAdapter.SelectCommand.Connection = objConnection

objDataAdapter.SelectCommand.CommandText = "SELECT COUNT(DISTINCT MessageIdentity) FROM MessageRecipients WHERE SenderInternal = 'N' and RecipientInternal = 'Y' and TimeReceived >= (GETDATE() -' & IncomingDays & ')"


objDataAdapter.SelectCommand.CommandType = CommandType.Text

objConnection.Open()

objDataAdapter.Fill(MailMeterDataSet1, "MessageRecipients") <<< !!!THIS IS WHERE THE ERROR OCCURS!!!!!

objConnection.Close()


dataGrid1.DataSource = MailMeterDataSet1


dataGrid1.DataMember = "MessageRecipients"


objDataAdapter = Nothing

objConnection = Nothing


End Sub

End Class


--------------------

Any ideas?!?!

Many thanks!!!!

Mark

 

[kulrom]

You may want to try this:

objDataAdapter.SelectCommand.CommandText = "SELECT COUNT(DISTINCT MessageIdentity) " & _
"FROM MessageRecipients WHERE SenderInternal = 'N' AND " & _
"RecipientInternal = 'Y' and TimeReceived >= (DATEADD(""dd"", -" & IncomingDays & ", GETDATE())"

 

[InertiaM]

You may also want to try using parameterised queries - this post by cjard would be an excellent place to start learning.

 

[kulrom]

Stored Procedures with parametrized SQL versus Inline SQL

I agree with Inertia. Inline SQL can often be a real pain, especially when worrying about escaping strings, getting date formats correct, SQL-injection (security) etc. etc.

Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe.

So if you ask me i would move that sql to SP and then just proceed the IncomingDays as INPUT parameter.

Hope this Helps !

 

[jmcilhinney]

I agree with Inertia. Inline SQL can often be a real pain, especially when worrying about escaping strings, getting date formats correct, SQL-injection (security) etc. etc.

Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe.

So if you ask me i would move that sql to SP and then just proceed the IncomingDays as INPUT parameter.

Hope this Helps !

There's no real need to use stored procedures. You can use parameters with inline SQL to prevent SQL injection, improve readability and reduce errors:

objDataAdapter.SelectCommand.CommandText = "SELECT COUNT(DISTINCT MessageIdentity) " & _
"FROM MessageRecipients WHERE SenderInternal = 'N' AND " & _
"RecipientInternal = 'Y' and TimeReceived >= (DATEADD(""dd"", -@IncomingDays, GETDATE())"

objDataAdapter.SelectCommand.Parameters.AddWithValue("@IncomingDays", IncomingDays)

 

[kulrom]

well i guess it's matter of taste or even matter of habit.
For example the SP's are far more readable in my opinion and i love them because they can be executed, tested and optimized independently of your application.

But as mentioned it's individually for each of us.

However thank you for the input. It's much appreciated.

 

[cjard]

they can be executed, tested and optimized independently of your application.

This is a really good point, and one that's saved a lot of work on many occasions.. In the case where you have a client app on 50 machines, and you come to realise that many users are trying to insert data that is (for example) longer than the column..

..if you have it all in stored procedures,you can put some code in to get the DB itself to substring the data before it is inserted, it's one place to change and it takes effect immediately
..compare that with having to add a SUBSTR to the SQL, recompile the app, build and push out to 50 machines and then get everyone to restart it..


And you can see there are some situations where stored procs make a lot of sense

 

[jmcilhinney]

SPs certainly have their adavantages and I don't dispute any of the claims made, but what I meant was that you don't HAVE to use SPs in order to parameterise your queries.

SPs also have their disadvantages, one of which is populating your data layer with business logic. I have to say, if you accept the assumption that they will generate correct, efficient SQL code, tools like LINQ to SQL and LINQ to Entities are great in that they allow you to write your queries in the IDE with the full support of the compiler and debugger.