Good day, I thought that my string was a date, as set as the top of the top but I have had to cast it to a search. This now works, what I can not get to work is the update. I get an error saying 'Error converting data type nvarchar to datetime' which makes sense as the fields in the table as 'datetime'. What I do not understand is how to make the variables, which I thought where date go in.I only want the date to go in as it will seaches for dates rather than times so each day should end 00:00:00. From research it looks like I am better off using 'smalldatetime' and having tried this I get the same error.Below is the section of code with the error followed by the whole code followed by the stored procedure sSQL3 = "spWeeklyReportDatesCreate" dsNames3.UpdateCommand = sSQL3 dsNames3.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure dsNames3.UpdateParameters.Clear() dsNames3.UpdateParameters.Add("DateStart", Mid(vDateStart, 1, 10)) dsNames3.UpdateParameters.Add("DateEnd", Mid(vDateEnd, 1, 10)) '###attempts.. ''' dsNames3.UpdateParameters.Add("DateStart", Cast(vdatestart as date)) '### dsNames3.UpdateParameters.Add("RunCompleted", "0") dsNames3.Update() dsNames3 = NothingFull Code Public Sub PrcRunReport() 'Check the date and the day itself. 'Find the last Monday Dim vDateStart As Date Dim vDateEnd As Date Dim vDay As String vDay = UCase(Date.Now.DayOfWeek) If vDay = "MONDAY" Or vDay = "1" Then ' we will want the week befores Monday vDateStart = Date.Now.AddDays(-7) vDateEnd = Date.Now.AddDays(-1) End If If vDay = "SUNDAY" Or vDay = "7" Then 'end date so we need to back to the week before as week has not ended vDateStart = Date.Now.AddDays(-13) vDateEnd = Date.Now.AddDays(-7) End If If vDay = "SATURDAY" Or vDay = "6" Then vDateStart = Date.Now.AddDays(-12) vDateEnd = Date.Now.AddDays(-6) End If If vDay = "FRIDAY" Or vDay = "5" Then vDateStart = Date.Now.AddDays(-11) vDateEnd = Date.Now.AddDays(-5) End If If vDay = "THURSDAY" Or vDay = "4" Then vDateStart = Date.Now.AddDays(-10) vDateEnd = Date.Now.AddDays(-4) End If If vDay = "WEDNESDAY" Or vDay = "3" Then vDateStart = Date.Now.AddDays(-9) vDateEnd = Date.Now.AddDays(-3) End If If vDay = "TUESDAY" Or vDay = "2" Then vDateStart = Date.Now.AddDays(-8) vDateEnd = Date.Now.AddDays(-2) End If vDateStart = Mid(vDateStart, 1, 10) vDateEnd = Mid(vDateEnd, 1, 10) '########################################################### 'Check in the database if the report has already been run '########################################################### Dim cn As SqlConnection = New SqlConnection() Dim cmd As SqlCommand = New SqlCommand() Dim dr As SqlDataReader Dim vRunCompleted As String = "" Dim vDateStartExist As String = "" cn.ConnectionString = ConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString cmd.Connection = cn cmd.CommandText = "SELECT datestart, DateEnd, runcompleted FROM [tblweeklyreports] where Cast(datestart as date) between '" & vDateStart & "' and '" & vDateStart & "'" 'Open the connection to the database cn.Open() ' Execute the sql. dr = cmd.ExecuteReader() If (dr.HasRows = True) Then While (dr.Read()) vDateStartExist = dr("DateStart").ToString 'vDateEnd = dr("DateEnd").ToString vRunCompleted = dr("RunCompleted").ToString End While Else '############## Check if it has been created already 'already exists and run so do nothing ' Try 'report not found so we need to create one 'Create dates in the report if doe not exist '### Update Dim ConnectionString3 As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("mySQLConnectionString").ConnectionString Dim dsNames3 As SqlDataSource Dim sSQL3 As String dsNames3 = New SqlDataSource dsNames3.ConnectionString = ConnectionString3 sSQL3 = "spWeeklyReportDatesCreate" dsNames3.UpdateCommand = sSQL3 dsNames3.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure dsNames3.UpdateParameters.Clear() dsNames3.UpdateParameters.Add("DateStart", Mid(vDateStart, 1, 10)) dsNames3.UpdateParameters.Add("DateEnd", Mid(vDateEnd, 1, 10)) '### dsNames3.UpdateParameters.Add("RunCompleted", "0") dsNames3.Update() dsNames3 = Nothing ' Catch 'unable to run due to missing start date ' End Try End If 'Run a 7 day report 00:01 Monday --> 23:59 Sunday 'Now we have the dates set up we can If UCase(vRunCompleted) = "1" Then 'skip Else Call PrcGenerateReport(Mid(vDateStart, 1, 10), Mid(vDateEnd, 1, 10)) End If End SubStored Procedure USE [database]GO/****** Object: StoredProcedure [dbo].[spWeeklyReportDatesCreate] Script Date: 10/21/2013 08:32:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spWeeklyReportDatesCreate]@DateStart smalldatetime,@DateEnd smalldatetime,@RunCompleted nvarchar(1)ASBEGININSERT INTO [tblweeklyreports] (DateStart, DateEnd,RunCompleted) VALUES (@DateStart, @DateEnd,@RunCompleted)END
Error converting data type nvarchar to smalldateti
Posted - 2013-10-21 : 04:52:18
