Hi
I'm passing the date through parameter when I run the update statement it generated the error
Error Unhandled exception has occurred....
The conversion of a varchard data type to a smalldatetime datatype resulted in an out of range values
the values from the string = CustomerRefString is from following code
CustomerRefString = DateTimePicker1.Value.Date
CustomerRefString = CustomerRefString.Replace(vbCr, "").Replace(vbLf, ",")
' VisitIDString = VisitIDString.Replace(",", "")
MsgBox(CustomerRefString)
CustomerRefString = CustomerRefString.Replace(" ", "")
MsgBox(CustomerRefString)
CustomerRefString = CustomerRefString.TrimEnd(MyComma)
MsgBox(CustomerRefString)
myCmd1.CommandText = "UPDATE tblContractSite SET DateInActive = '" & CustomerRefString & "' WHERE cardcolumn IN (" & CardNumString & ")"
The output from above code through MsgBox () shown below
UPDATE tableTarget SET DateInActive = 20/10/2017' where cardcolumn in ('1234')
The field 'DateInActive' type is datetime
Appreciate your help
Kind regards,
Farhan
That is missing a single quote (in front of the string-date) - but I doubt that would raise that message
that is at odds with the message which seems to suggest it is SMALLdatetime:
but that may be the result of a cascade-error
Personally I never provide string-dates in formats like "dd/mm/yyyy" as they are subject to SQL Parsing rules, which are ambiguous and highly liable to unexpected behaviour - they depend on, for example, the Language of the currently connected user ...
Better to use "yyyymmdd" which is unambiguous in all instances.
Done
`
myCmd1.CommandText = "UPDATE tblContractSite
SET DateInActive =
Right('" & CustomerRefString & "',4)+Substring('" & CustomerRefString & "',4,2)+Left('" & CustomerRefString & "',2)
WHERE CardNumber IN (" & CardNumString & ")"
or
`
declare @targetdate varchar(10) = '22/10/2017';
update tblContractSite set DateInActive=
convert(datetime,RIGHT(@targetdate,4)+substring(@targetdate,4,2)+left(@targetdate,2))
where CardNumber in (135721)
`
`
This method of concatenating a string to be sent to SQL Server for execution is ripe for a SQL Injection attack and should be avoided.
There are many other ways of performing this action - but the best option would be to utilize a stored procedure. If you don't want to go that route - then build your command text with parameters, add the parameters - then execute. That will also insure that what is sent to SQL Server is a date/time regardless of the client formatted string.
From this excellent article on dynamic SQL: The Curse and Blessings of Dynamic SQL
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = _
" SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)" & _
" FROM dbo.Orders O " & _
" JOIN dbo.[Order Details] OD ON O.OrderID = OD.OrderID" & _
" WHERE O.OrderDate BETWEEN @from AND @to" & _
" AND EXISTS (SELECT *" & _
" FROM dbo.[Order Details] OD2" & _
" WHERE O.OrderID = OD2.OrderID" & _
" AND OD2.ProductID = @prodid)" & _
" GROUP BY O.OrderID"
cmd.Parameters.Add("@from", SqlDbType.Datetime)
cmd.Parameters("@from").Value = "1998-02-01"
cmd.Parameters.Add("@to", SqlDbType.Datetime)
cmd.Parameters("@to").Value = "1998-02-28"
cmd.Parameters.Add("@prodid", SqlDbType.Int)
cmd.Parameters("@prodid").Value = 76
This will insure that appropriate data types are passed to the code - and you don't have to worry about placing single-quotes around your parameters and you avoid SQL injection issues.