converting datetime from character string

I read as many previous posts on this subject before posting this but I'm stuck.

I have a form that has a from and to date range for a search page.

I think my problem is taking place in my server validition procedure.

I'm trying to pass a date value typed in a textbox to a stored procedure that is expecting a datetime data type.

Here is two of my server validation procedures (They are all pretty much the same):

 

        Sub ValidateFromRepoDt(ByVal sender As Object, _
        ByVal args As ServerValidateEventArgs)

            Try

                'make sure there is a value in the text box
                If Len(Trim(args.Value)) > 0 Then

                    'parse the value into a date
                    Dim dte As DateTime = _
                    Date.Parse(args.Value).ToShortDateString

                    'edit the textbox to show the proper date format
                    txtFromRepoDt.Text = CStr(dte)

                    If IsDate(dte) = False Then
                        'not in date format, validation failed
                        Throw New Exception("Invalid")
                    ElseIf dte > DateTime.Now Then
                        'date entered is greater than today, validation failed
                        lblFromRepoDt.Text = _
                        "(From) Repo Date is greater than today!"
                        args.IsValid = False
                        Exit Sub
                    Else

                        'if the repo to date is empty, 
                        'populate it with today's date
                        If Len(Trim(txtToRepoDt.Text)) = 0 Then
                            txtToRepoDt.Text = _
                            DateTime.Now.ToShortDateString()
                        End If

                        'validation passed
                        args.IsValid = True

                    End If
                Else
                    lblFromRepoDt.Text = ""
                End If

            Catch ex As Exception

                lblFromRepoDt.Text = _
                "(From) Repo Date is NOT in Date Format!"

                'validation failed
                args.IsValid = False

            End Try

        End Sub
        
        Sub ValidateToRepoDt(ByVal sender As Object, _
        ByVal args As ServerValidateEventArgs)
            
            Try
                
                'make sure there is a value in the text box
                If Len(Trim(args.Value)) > 0 Then
                    
                    'parse the value into a date
                    Dim dte As DateTime = _
                    Date.Parse(args.Value).ToShortDateString

                    'edit the textbox to show the proper date format
                    txtToRepoDt.Text = CStr(dte)
                    
                    If IsDate(dte) = False Then
                        'not in date format, validation failed
                        Throw New Exception("Invalid")
                    ElseIf dte > DateTime.Now Then
                        'date entered is greater than today, 
                        'validation(failed)
                        lblToRepoDt.Text = _
                        "(To) Repo Date is greater than today!"
                        args.IsValid = False
                        Exit Sub
                    Else
                        'if the repo from date is empty,
                        'populate it with the same date one year ago
                        If Len(Trim(txtFromRepoDt.Text)) = 0 Then
                            txtFromRepoDt.Text = _
                            dte.AddYears(-1).ToShortDateString
                        End If
                        
                        'validation passed
                        args.IsValid = True
                        
                    End If

                Else
                    lblToRepoDt.Text = ""
                End If

            Catch ex As Exception

                lblToRepoDt.Text = "(To) Repo Date is NOT in Date Format!"

                'validation failed
                args.IsValid = False

            End Try

        End Sub
 
Now, prior to executing my stored procedure, here are my parameter adds:
 
 
                If txtFromRepoDt.Text <> "" And IsDBNull(txtFromRepoDt.Text) = False Then
                    .Parameters.AddWithValue("@FromRepoDt", txtFromRepoDt.Text)
                End If

                If txtToRepoDt.Text <> "" And IsDBNull(txtToRepoDt.Text) = False Then
                    .Parameters.AddWithValue("@ToRepoDt", txtToRepoDt.Text)
                End If

                dr = cmd.ExecuteReader
 
Again, I'm guessing the problem lies in my server validation procedures. The question is, what adjustment needs to be made so that my sql 2005 stored procedure will accept my texbox values as dates?
 
Any help on this would be greatly appreciated... 
  
  
0
SouthSideRob
10/10/2008 4:48:25 AM
asp.net.getting-started 91979 articles. 4 followers. Follow

6 Replies
561 Views

Similar Articles

[PageSpeed] 38
Get it on Google Play
Get it on Apple App Store

Hi Friend,

Try like below,

.Parameters.Add("@FromRepoDt", SqlDbType.DateTime)
.Parameters("@FromRepoDt").Value = txtFromRepoDt.Text

But make sure you value in the textbox is in proper date format.

 

 


Marking a Reply as 'Answered', not only GAIN us some POINTS, but it also HELP others to find RIGHT solution.
0
ameenkpn
10/10/2008 7:30:40 AM

 you will have to pass a date time so try instead

.Parameters.Add("@FromRepoDt", SqlDbType.DateTime)
.Parameters("@FromRepoDt").Value = DateTime.Parse(txtFromRepoDt.Text
)

 also if it is null, you might think of passing SqlDateTime.Null

 

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

if an answer, mark it so .... cheers


------------------------
if an answer, mark it so.
0
PaulSpencer
10/10/2008 8:10:29 AM

.Parameters.Add("@FromRepoDt", SqlDbType.DateTime)

.Parameters("@FromRepoDt").Value = Convert.ToDateTime (txtFromRepoDt.Text )

 

try out this one 


Thanks And Regards,
Manoj Karkera
Zenith Software LTD

Please remember to click “Mark as Answer” on the post that helps you
0
manoj0682
10/10/2008 9:03:33 AM

Paul,

Actually, all my input parameters in the SQL stored procedure (if not received) are assigned the value of NULL

 

CREATE PROCEDURE [dbo].[wbogAccountSearch]
@SortExpr       as varchar(15) = null,
@AscOrDesc      as varchar(4) = 'Asc',
@StartID        as int = 1,
@EndID          as int = null,
@Customer       as varchar(50) = null,
@FromRepoDt     as datetime = null,
@ToRepoDt       as datetime = null,
etc...
That way, if I don't have a value to pass, the stored procedure will set the default value so I'm pretty sure I've got that part taken care of. Once I get into work, I plan on testing all the examples you and others have sent.
Thanks for your help, I do appreciate it. 
  

 


 

0
SouthSideRob
10/10/2008 12:01:10 PM

All methods gave me the same sql exception which was:

Conversion failed when converting datetime from character string.

0
SouthSideRob
10/10/2008 1:34:02 PM

For completeness, I colleague of mine figured out the problem which was in my stored procedure and not in the asp.net script.

I thought I would print it here in case anybody runs into the same problem.

The input fields are expecting datetime values but in my where clause, I did not have the correct amount of single quotes in order for the Stored Procedure to execute correctly which is why I received the error. Here is a snippit from my stored procedure that converts the dates back into varchar in order to keep the WHERE clause in my SELECT statement correct syntax-wise.

 

	if Isnull(@FromRepoDt, '') <> '' AND Isnull(@ToRepoDt, '') <> ''
	Begin
		set @MyWhere = @MyWhere + 'and r.[RepoDt] BETWEEN ''' + convert(varchar(10), @FromRepoDt, 101) + ''' AND ''' + 
									convert(varchar(10), @ToRepoDt, 101) + ''' '
	End

	if Isnull(@FromFlwUpDt, '') <> '' AND Isnull(@ToFlwUpDt, '') <> ''
	Begin
		set @MyWhere = @MyWhere + 'and n.[FollowUpDt] BETWEEN ''' + convert(varchar(10), @FromFlwUpDt, 101) + ''' AND ''' + 
								   	    convert(varchar(10), @ToFlwUpDt, 101) + ''' '
	End
  
0
SouthSideRob
10/10/2008 2:55:16 PM
Reply:

Similar Artilces:

Converting datetime from character string
Hi there, I have the following code: Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String) Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String) Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String) Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String) Dim Rent As String = Ctype(Request.Querystring("Rent"), String) Dim DateToday = DateTime.Now Dim mySQL AS String If suburbValue = "- All -" Then mySQL = &qu...

Conversion failed when converting datetime from character string
I am trying to write a SP in Query Analyzer before I start the code from a web form to acces it. Here is the SQL: ------------------------------------------- DECLARE @cReportGroups Varchar(500)DECLARE @StartDateRange DateTime DECLARE @EndDateRange DateTime SET @cReportGroups = '''VCCEddieBauer'',''VCCEddieBauerDirect'', ''VCCEddieBauerOrder'', ''VCCEddieBauerOvernight'', ''VCCEddieBauerSpanish'', ''VCCEddieBauerCustomerService''' SET @StartDateRange = '09-16-2007' SET @EndD...

Syntax error converting datetime from character string
Asp.Net application does not work properly when deployed on the server but works perfectly on the development environment... it gives a "Syntax error converting datetime from character string" but does not give the error on the development environment. The update is done throw an sqladapter. Thanks need ur reply now May be regional settings of IIS and SQL machines is different Pls, How can i check for the regional setting of IIS, my SQL regional setting is English (United States) I had some problems with dates too.... To avoid conflicts, you should configure the culture f...

Syntax error converting datetime from character string
Here's the deal. I'm a newbie trying to update a date (@Effective) in sql server. I've been trying all sorts of things, but nothing seems to work. Here's my current code: Sub btnUpdate_Click(sender As Object, e As EventArgs) Dim sqlStmt As String Dim conString As String Dim cn As SqlConnection Dim cmd As SqlCommand Dim sqldatenull As SqlDateTime Try sqlStmt = "UPDATE [Submission] SET [Effective]=Convert(DateTime, '@Effective', 101), "& _ "[Status]=@Status WHERE ([Submission].[Submission]=@Submission)" conSt...

Syntax error converting datetime from character string.
Why would I be getting this error in my SP? I am passing in a DateTIme.Parsed value, from my asp.net page. It also bombs if I use QA in sql server management. even if i dont put in a date, and let the date get set to the value of GETDATE(). Kinda wierd. USE [Haynes_Test] GO /****** Object: StoredProcedure [dbo].[spGetSearch] Script Date: 11/25/2008 10:14:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Charles Keezer -- Create date: 11/20/2008 -- Description: Returning search results for Report.aspx -- ===...

Conversion failed when converting datetime from character string.
Hi, I am getting the following error when executing the ExecuteInsert in the code below..:   Conversion failed when converting datetime from character string.     private bool ExecuteInsert(String quantity)    {[snip]        con.Open();        SqlCommand command = new SqlCommand();        command.Connection = con;        TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); &n...

Conversion failed when converting datetime from character string.
This code is produsing the error: Conversion failed when converting datetime from character string. Can anyone see any reason for this?Public Sub SubmitAddVolTask_Click(ByVal Sender As System.Object, ByVal E As System.EventArgs) Handles SubmitAddVolTask.Click Dim Con As SqlConnection Dim StrInsert As String Dim intInsertCount As Integer Dim cmdInsert As SqlCommand Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString Con = New SqlConnection(ConnectStr) StrInser...

Syntax error converting datetime from character string.
When inserting dates into my table using MS SQL Server 7.0 and PowerBuilder 6.5, I receive a error: SQLSTATE = 22008 [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting datetime from character string. This error occours during executing command: INSERT INTO nf_entrada ( nfe_numero, nfe_serie, for_codigo, nfe_vr_contabil, emp_codigo, nfe_data_emissao, nfe_data_entrada ) VALUES ( '212121', '', 1, 1121.0000, 1, {ts '1999-09-28 00:00:00.000000'}, {ts '1999-09-28 00:00:00.000000'} ) I need some help. Email me: softcon@lpnet.com.br...

Syntax error converting datetime from character string #2
Hi all,       The last week this was working, and today i was testing and all start with errors. This is my SQL sentence:command = New SqlCommand("update ctlg_users set u_last_access = '" & FormatDateTime(Now(), 1) & "' where u_id = " & cstr(l_u_id),oConn)Is there an error? why was before working and now don`t?, including an old ASP page that i´m still using fall back to the same error when is using FormatDateTime, í´ve tried in the ASPX page using datetime.now.tostring("dd/mm/yyyy") but i got an error too!! HEEEELP!Thanks :DWhere is the Spanish Lang...

Syntax error converting datetime from character string
Somewhere, either in the aspx.vb code, or the stored procedure that's being called, it doesn't like my code since I am getting the dreaded "syntax error converting datetime from character string" error.  I've been battling this for two days - can someone spot what is wrong here? The dtWdate variable is coming from a calendar control on my aspx page. Please help - I've tried every conceivable way I can think to do this without success. Lynnette Here's the code:     Private Sub GetFLSAEmpInfoRecs()         'uses the AppSettin...

Help with exception: Conversion failed when converting datetime from character string.
Hi, I am trying to insert an entry into my database, but I keep on getting the following error: "Conversion failed when converting datetime from character string."  My query is a parameterized T-SQL query (i.e. "INSERT INTO blah (name, dob) VALUES (@name, '@dob')"), and I have initialized the SqlCommand object by doing:  sqlCmd.Parameters.AddWithValue("@name", "Captain America"); sqlCmd.Parameters.AddWithValue("@dob", birthdate.ToString("MM/dd/yyyy h:mm tt"); sqlCon.Open(); sqlCmd.ExecuteNonQuery(); sqlCon.Close();   The dob field in the table 'blah' is ...

Issue when converting XML dateTime to .NET DateTime
Hi, I am experiencing an issue when deserializing XML dateTime nodes in ASP.NET in some timezones. The construct 0001-01-01T00:00:00.0000000+ZZ:ZZ where ZZ:ZZ is the timezone offset for the timezone on the current system should produce a DateTime value equalling the constant DateTime.MinValue when converted. This works some of the time, such as for the Paris time offset of +1: (CDate("0001-01-01T00:00:00.0000000+01:00") = DateTime.MinValue) = True But does not work for Sydney time offset of +10: (CDate("0001-01-01T00:00:00.0000000+10:00") = DateTime....

Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message
Hi folks, I keep looking at these SQL text query and I cannot figure out what I am doing wrong.If I include wrap the dates with quotes, I get the "Syntax error converting datetime from character string" error message, but I don't include them I don't get anything which is also wrong, because there is some data to be displayed. Bottom line, what do you think I am doing wrong when building the SQL query?1 DateTime dateFrom = txtDateFrom.SelectedDate;2 DateTime from = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0);3 ...

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

Web resources about - converting datetime from character string - asp.net.getting-started

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Converting Churches Into Homes Is The Latest Hollywood Trend
You don't have to be a believer to be moved by the beauty of a church.

Resources last updated: 12/9/2015 12:05:27 AM