Insert SQL for Date value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Insert SQL for Date value

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-06-28 : 15:38:38

In my insert statement, I keep getting exceptions thrown. Not too sure if I need to parse the strDate varible, or CAST() it.

Dim strDate As SqlDateTime
If txtDelDate.Text = Trim("") Or txtDelDate.Text = "MM/DD/YYYY" Then
strDate = SqlDateTime.Null
Else
strDate = SqlDateTime.Parse(txtDelDate.Text)
End If

Dim strInsertSQL As String
strInsertSQL = "Insert INTO WP_D (WP_PId, PAD , [Del Des] , [Date]) "
strInsertSQL &= "Values ( '" & strRQW & "' ,'" & strP3 & "', '" & strDelDesc & "', " & strDate & ")"

jhermiz

3564 Posts

Posted - 2005-06-28 : 16:20:18

quote:
Originally posted by bubberz

In my insert statement, I keep getting exceptions thrown. Not too sure if I need to parse the strDate varible, or CAST() it.

Dim strDate As SqlDateTime
If txtDelDate.Text = Trim("") Or txtDelDate.Text = "MM/DD/YYYY" Then
strDate = SqlDateTime.Null
Else
strDate = SqlDateTime.Parse(txtDelDate.Text)
End If

Dim strInsertSQL As String
strInsertSQL = "Insert INTO WP_D (WP_PId, PAD , [Del Des] , [Date]) "
strInsertSQL &= "Values ( '" & strRQW & "' ,'" & strP3 & "', '" & strDelDesc & "', " & strDate & ")"

How about calling a stored procedure and passing a date value rather than a string?

Otherwise here's how to convert:

Dim strDate As String = "10/05"
Dim dt As DateTime = CType((TypeDescriptor.GetConverter(New DateTime(1990, 5, 6)).ConvertFrom(strDate)), DateTime)
Response.Write("<hr>" + dt.DayOfWeek)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2005-06-29 : 13:29:01

jhermiz,

Here's my subroutine on a button click to do the insert using the SPROC sp_InsertDel. The problem is that I get an exception of:
"Syntax error converting character string to smalldatetime data type." when I leave the text box blank, or enter something like 06/29/2005.

Try
sCon1.Open()
Dim sqldatenull As SqlDateTime = SqlDateTime.Null
Dim strsp As String = "sp_InsertDel"
Dim myCommand As New SqlCommand(strsp, sCon1)
myCommand.CommandType = CommandType.StoredProcedure

Dim strRQW As New SqlParameter("@strRQW", SqlDbType.NVarChar)
strRQW.Value = "Test"
myCommand.Parameters.Add(strRQW)

Dim strP3ActivityID As New SqlParameter("@strP3AID", SqlDbType.NVarChar)
If txtP3.Text = "" Then
strP3AID.Value = String.Empty
Else
strP3AID.Value = txtP3.Text
End If
myCommand.Parameters.Add(strP3AID)

Dim strDelDesc As New SqlParameter("@strDelDesc", SqlDbType.NText)
If txtDesc.Text = "" Then
strDelDesc.Value = String.Empty
Else
strDelDesc.Value = txtDesc.Text
End If
myCommand.Parameters.Add(strDelDesc)

Dim strDate As New SqlParameter("@strDate", SqlDbType.SmallDateTime)
If (txtDate.Text = "") Then
strDate.Value = sqldatenull
Else
strDate.Value = DateTime.Parse(txtDate.Text)
End If
myCommand.Parameters.Add(strDate)

Dim RDR As SqlDataReader = myCommand.ExecuteReader
DataGrid1.DataSource = RDR
DataGrid1.DataBind()
Catch ex As Exception
Label1.Text = ex.Message
Finally
sCon1.Close()
End Try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources