passing empty strings to a SPROC

Hi
i have a form that contains fields with optional fields... when i don't enter a date i get the following error:
Cast from string "" to type 'Date' is not valid.

the error is happening at this line:
tblJob_Insert(notetitle.Text, notedescription.Text, "1", begandate.text, enddate.text, extdate.text, ddlcalltype.SelectedItem.Value, ddlproperty.Selecteditem.Value, drawingref.text, itemtitle.text, itemdescription.text, ddlprio.SelectedItem.Value, duedate.text, ddlaction.SelectedItem.Value, nextcall.text, notes.text, receiveddate.text)
   
and it is calling this:
Sub tblJob_Insert(ByVal jobtitle As String, ByVal jobdescription As String, ByVal statusid As integer,  ByVal began As Datetime, ByVal deadline As DateTime, ByVal extensiondate As DateTime, ByVal calltype As integer, ByVal propertyid As integer, ByVal drawingreference as string, ByVal tasktitle as string, ByVal taskdescripton as string, ByVal priorityid as integer, ByVal due as string, ByVal actionid as integer, ByVal nextdate as datetime, ByVal comments as string, ByVal receivedate as datetime)
       
how do i allow for the fact that i need sometimes not fill in the date (and also text and int fields)
please let me know if you need any more info

thanks for your help
lz7cjcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thrill_houseCommented:
What I would do is check to see if the textbox is empty, and if it is, just pass in System.DBNull.Value.

So...

If enddate.text = "" then
     yourcommandvar.parameters.add("@enddate", System.DBNull.Value)
else
     yourcommandvar.parameters.add("enddate", enddate.text)
end if
martie_11Commented:
Hi lz7cjc,

Try the following:
using System.Data.SqlTypes;
...

SqlDateTime sqlDateTimeNull = SqlDateTime.Null;
...

//toDate is a value retrieved from a text box
if (toDate == "")
{
      dbCommandWrapper.AddInParameter("@ToDate",DbType.DateTime, sqlDateTimeNull);
}
else
{
      dbCommandWrapper.AddInParameter("@ToDate",DbType.DateTime, toDate);
}

Basically, you have to insert NULL if the date does not exist.

Regards.
lz7cjcAuthor Commented:
that looks like it is on the right track but have tried the following and i still get the same error:
System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

Dim beganParam  As SQLParameter = command.Parameters.Add("@began", System.Data.SQLDbType.Datetime)
            If begandate.text = "" then
            beganParam.Value = System.DBNull.Value
            Else
            beganParam.Value = begandate.text.ToString()
            end if

this is in line with the syntax i am using - so how do i implement your suggestion within this context?
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

thrill_houseCommented:
Try this:

Dim beganParam  As SQLParameter = command.Parameters.Add("@began", System.Data.SQLDbType.Datetime)
            If trim(begandate.text) = "" then
            beganParam.Value = System.DBNull.Value
            Else
            beganParam.Value = trim(begandate.text.ToString())
            end if


This will just make sure there isn't anything in there possibly messing it up.
martie_11Commented:
Try using SqlDateTime.Null as in my post as opposed to the System.DBNull.Value
lz7cjcAuthor Commented:
now get the following error with:
Dim beganParam  As SQLParameter = command.Parameters.Add("@began", DbType.DateTime, sqlDateTime.Null)
            If trim(begandate.text) = "" then
            beganParam.Value = SqlDateTime.Null
            Else
            beganParam.Value = trim(begandate.text.ToString())
            end if


error:
Name 'SqlDateTime' is not declared.

(I tried your last suggestion thrill_house and got the same error)

Can i ask a favour- things are getting a little confused... so can we take the code posted in this comment as the starting point for future suggestions and can you reprint in its entirety to ensure i am implementing what you suggest
thanks
martie_11Commented:
In C# you have to make sure that you include the namespace.  i.e. in C# it's 'using System.Data.SqlTypes'.  So you need to include the equivelent in VB.NET.

I'll see if I can figure out how to do that in VB.NET...
thrill_houseCommented:
it'd be 'imports System.Data.SqlTypes'
martie_11Commented:
Thanks thrill_house...

lz7cjc, try that.
lz7cjcAuthor Commented:
that is already on the page
thrill_houseCommented:
lz7cjc,
Do you have any triggers that are happening?  Is it possible that something else is sending the empty string?  Because using what we gave you, you should never see this casting error.
martie_11Commented:
Can you try this:

imports System.Data.SqlTypes
...

Dim beginDateNull As SqlDateTime = SqlDateTime.Null

Dim beganParam  As SQLParameter = command.Parameters.Add("@began", System.Data.SQLDbType.Datetime)
            If trim(begandate.text) = "" then
                 beganParam.Value = beginDateNull
            Else
                 beganParam.Value = trim(begandate.text.ToString())
            end if
lz7cjcAuthor Commented:
no - no triggers - here is the page code:
NB where i haven't implemented your code i am ensuring there is a date value - i just want to get it working before going right the way through the code

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

    Private Const ConnectString As String = "UID=SA;PWD=hello; Data Source=(local); database=builders;"

       Dim SelectStatement As String
       Dim Connect As New SqlConnection
       Dim Adapter As New SqlDataAdapter
       Dim ClassyCB As SqlCommandBuilder
       Dim ClassyDS As DataSet = New DataSet
       Dim ClassyDS1 As DataSet = New DataSet
       Dim Row As DataRow

       Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
    Dim propertyid as string
                       propertyid = Trim(request.querystring("propertyid"))

            Dim ConnectToDatabase As New SqlConnection(ConnectString)
            Dim DataAdapterCall As New SqlDataAdapter("SELECT * FROM tbl_job", ConnectToDatabase)
            Dim DDLAdapterPeople As New SqlDataAdapter("SELECT RTRIM(ISNULL(address1, '')) + ' ' + RTRIM(ISNULL(address2, '')) AS address, propertyID FROM tbl_property", ConnectToDatabase)
            Dim DDLAdapterAction As New SqlDataAdapter("SELECT ActionId, ActionName FROM tbl_Action", ConnectToDatabase)
           Dim DDLAdapterPrio As New SqlDataAdapter("SELECT * FROM tbl_priority", ConnectToDatabase)
           Dim DDLAdapterCallType As New SqlDataAdapter("SELECT * FROM tbl_calltype", ConnectToDatabase)

            'Dim Ds As New DataSet
            Dim Ds1 As New DataSet
            Dim Ds2 As New DataSet
            Dim Ds3 As New DataSet
            Dim Ds4 As New DataSet
            Dim Ds5 As New DataSet
            Dim Ds6 As New DataSet

            begandate.text = today


            ConnectToDatabase.Open()
           ' DataAdapterJob.Fill(Ds, "Job")
            DataAdapterCall.Fill(Ds1, "Call")
            'DDLAdapterCompany.Fill(Ds2, "company")
            DDLAdapterPeople.Fill(Ds3, "People")
            DDLAdapterAction.Fill(Ds4, "Action")
            DDLAdapterPrio.Fill(Ds5, "Prio")
            DDLAdapterCallType.Fill(Ds6, "Call")


            ConnectToDatabase.Close()

           ' jobgrid.DataSource = Ds.Tables("job")
            'company.Datasource = Ds3.tables("Company")

            If Not IsPostBack Then


                 ddlproperty.DataSource = Ds3               '<--- (Dataset Name)
                 ddlproperty.DataMember = "People"               '<--- (Table Name)
                 ddlproperty.DataTextField = "address"               '<---    (Column Name)
                 ddlproperty.DataValueField = "propertyid"               '<--- (Column Name)
                 if propertyid <> "" then
                 ddlproperty.SelectedValue = propertyid
                 ddlproperty.enabled = false
                 end if

                 ddlAction.DataSource = Ds4               '<--- (Dataset Name)
                 ddlAction.DataMember = "action"               '<--- (Table Name)
                 ddlAction.DataTextField = "actionName"               '<---    (Column Name)
                 ddlAction.DataValueField = "actionId"               '<--- (Column Name)

                 ddlprio.DataSource = Ds5               '<--- (Dataset Name)
                 ddlprio.DataMember = "prio"               '<--- (Table Name)
                 ddlprio.DataTextField = "priorityName"               '<---    (Column Name)
                 ddlprio.DataValueField = "priorityid"               '<--- (Column Name)


                 ddlcalltype.DataSource = Ds6               '<--- (Dataset Name)
                 ddlcalltype.DataMember = "Call"               '<--- (Table Name)
                 ddlcalltype.DataTextField = "typeName"               '<---    (Column Name)
                 ddlcalltype.DataValueField = "typeid"               '<--- (Column Name)


            End If

            Page.DataBind()
       End Sub

       Sub Submit_Click(ByVal Sender As Object, ByVal E As EventArgs)
            'Dim errorCode As String

            tblJob_Insert(notetitle.Text, notedescription.Text, "1", begandate.text, enddate.text, extdate.text, ddlcalltype.SelectedItem.Value, ddlproperty.Selecteditem.Value, drawingref.text, itemtitle.text, itemdescription.text, ddlprio.SelectedItem.Value, duedate.text, ddlaction.SelectedItem.Value, nextcall.text, notes.text, receiveddate.text)

          '  If errorCode.Length <> 0 Then
          '       message.Text = "there was a an error in submitting the person's details. " & _
          '       errorCode

          '  Else
                 message.Text = "The note has been successfully entered"
                 notetitle.Enabled = False
                 notedescription.Enabled = False
                 begandate.Enabled = False
                 enddate.Enabled = False
                 extdate.Enabled = False
                 ddlcalltype.Enabled = False
                 ddlproperty.Enabled = False
                 drawingref.Enabled = False
                 itemtitle.Enabled = False
                 itemdescription.enabled = false
                 ddlprio.Enabled = False
                 duedate.Enabled = False
                 ddlaction.Enabled = False
                 nextcall.Enabled = False
                 notes.Enabled = False
                 receiveddate.Enabled = False

         '   End If

       End Sub

       Sub tblJob_Insert(ByVal jobtitle As String, ByVal jobdescription As String, ByVal statusid As integer,  ByVal began As Datetime, ByVal deadline As DateTime, ByVal extensiondate As DateTime, ByVal calltype As integer, ByVal propertyid As integer, ByVal drawingreference as string, ByVal tasktitle as string, ByVal taskdescripton as string, ByVal priorityid as integer, ByVal due as string, ByVal actionid as integer, ByVal nextdate as datetime, ByVal comments as string, ByVal receivedate as datetime)
            Dim connection As New SQLConnection(ConnectString)
            connection.Open()
            'create a command...

            'tbl_job parameters
            Dim command As New SQLCommand("proc_add_new_job", connection)
            command.CommandType = System.Data.CommandType.StoredProcedure
            'parameters...
            Dim jobNameParam As SQLParameter = command.Parameters.Add("@jobtitle", System.Data.SQLDbType.VarChar, 200)
            jobNameParam.Value = notetitle.Text.ToString()
            Dim jobDescriptionParam As SQLParameter = command.Parameters.Add("@jobdescription", System.Data.SQLDbType.VarChar, 1000)
            jobDescriptionParam.Value = notedescription.Text.ToString()
            Dim statusIDParam As SQLParameter = command.Parameters.Add("@statusid", System.Data.SQLDbType.Int)
            statusIDParam.Value = "1"

            Dim beganParam  As SQLParameter = command.Parameters.Add("@began", DbType.DateTime, sqlDateTime.Null)
            If trim(begandate.text) = "" then
            beganParam.Value = SqlDateTime.Null
            Else
            beganParam.Value = trim(begandate.text.ToString())
            end if

            Dim endParam  As SQLParameter = command.Parameters.Add("@deadline", DbType.DateTime, sqlDateTime.Null)
            If trim(enddate.text) = "" then
            endParam.Value = SqlDateTime.Null
            Else
            endParam.Value = trim(enddate.text.ToString())
            end if

            Dim extensiondateParam  As SQLParameter = command.Parameters.Add("@extensiondate", DbType.DateTime, sqlDateTime.Null)
            If trim(extdate.text) = "" then
            extensiondateParam.Value = SqlDateTime.Null
            Else
            extensiondateParam.Value = trim(extdate.text.ToString())
            end if

            Dim calltypeParam  As SQLParameter = command.Parameters.Add("@calltype", System.Data.SQLDbType.Int)
            calltypeParam.Value = ddlcalltype.selecteditem.value

            Dim propertyParam  As SQLParameter = command.Parameters.Add("@propertyid", System.Data.SQLDbType.Int)
            propertyParam.Value = ddlproperty.selecteditem.value

            Dim drawingrefParam  As SQLParameter = command.Parameters.Add("@drawingreference", System.Data.SQLDbType.VarChar, 200)
            drawingrefParam.Value = drawingref.text.ToString()


            'tbl_call parameters
            Dim tasktitleParam As SQLParameter = command.Parameters.Add("@tasktitle", System.Data.SQLDbType.Varchar, 100)
            tasktitleParam.Value = itemtitle.text.ToString()
            Dim taskdescriptionParam As SQLParameter = command.Parameters.Add("@taskdescription", System.Data.SQLDbType.Varchar, 100)
            taskdescriptionParam.Value = itemdescription.text.ToString()
            Dim priorityIDParam As SQLParameter = command.Parameters.Add("@priorityid", System.Data.SQLDbType.Int)
            priorityIDParam.Value = ddlprio.SelectedItem.Value
            Dim dueDateParam As SQLParameter = command.Parameters.Add("@due", System.Data.SQLDbType.Datetime)
            dueDateParam.Value = duedate.Text.ToString()
            Dim actionParam As SQLParameter = command.Parameters.Add("@actionid", System.Data.SQLDbType.Int)
            actionParam.Value = ddlaction.SelectedItem.Value
            Dim nextcallParam As SQLParameter = command.Parameters.Add("@nextdate", System.Data.SQLDbType.Datetime)
            nextcallParam.Value = nextcall.Text.ToString()
            Dim CommentsParam As SQLParameter = command.Parameters.Add("@comments", System.Data.SQLDbType.VarChar, 1000)
            CommentsParam.Value = notes.Text.ToString()
            Dim receiveddateParam As SQLParameter = command.Parameters.Add("@receivedate", System.Data.SQLDbType.Datetime)
            receiveddateParam.Value = receiveddate.Text.ToString()

            ' etc... add the remainder of your variables here
            'execute...
            command.ExecuteNonQuery()
            'cleanup...
            command.Dispose()
            connection.Close()
       End Sub
thrill_houseCommented:
Dim dueDateParam As SQLParameter = command.Parameters.Add("@due", System.Data.SQLDbType.Datetime)
            dueDateParam.Value = duedate.Text.ToString()
            Dim actionParam As SQLParameter = command.Parameters.Add("@actionid", System.Data.SQLDbType.Int)
            actionParam.Value = ddlaction.SelectedItem.Value
            Dim nextcallParam As SQLParameter = command.Parameters.Add("@nextdate", System.Data.SQLDbType.Datetime)
            nextcallParam.Value = nextcall.Text.ToString()
            Dim CommentsParam As SQLParameter = command.Parameters.Add("@comments", System.Data.SQLDbType.VarChar, 1000)
            CommentsParam.Value = notes.Text.ToString()
            Dim receiveddateParam As SQLParameter = command.Parameters.Add("@receivedate", System.Data.SQLDbType.Datetime)
            receiveddateParam.Value = receiveddate.Text.ToString()


There are a bunch of datetime datatypes down here that could be causing the problem...  if any of these are empty strings the error will occur.  You need that if statement around every datetime datatype.  If it is empty string then give null.
lz7cjcAuthor Commented:
yup - i know... and i am making sure that i put a value in the ones which i haven't incased in an if.... else... end if
martie_11Commented:
...or comment them out and try just one DateTime...
lz7cjcAuthor Commented:
when i try the
  Dim beginDateNull As SqlDateTime = SqlDateTime.Null
i get
Type 'SqlDateTime' is not defined.
thrill_houseCommented:
Yes, but there could be a logic error somewhere and on postback the values are turning into empty strings.  This sometimes happens.  To be safe, I'd put the if statements in.
lz7cjcAuthor Commented:
ok - but before i do this we still need to resolve this issue of Type 'SqlDateTime' is not defined. or am i going to roll back to a different solution?
martie_11Commented:
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OLEDB" %>
<%@ import Namespace="System.Data.SqlClient" %>

You don't have the correct import statement!

Add:

<%@ import Namespace="System.Data.SqlTypes" %>
thrill_houseCommented:
I would also put a "If Not IsPostBack Then" around the entire contents of the pageload, not just that one section.  You are making unnecessary DB calls.
martie_11Commented:
Add import statement and then try:

Dim beginDateNull As SqlDateTime = SqlDateTime.Null

Dim beganParam  As SQLParameter = command.Parameters.Add("@began", System.Data.SQLDbType.Datetime)
            If trim(begandate.text) = "" then
                 beganParam.Value = beginDateNull
            Else
                 beganParam.Value = trim(begandate.text.ToString())
            end if
lz7cjcAuthor Commented:
ok - have added around all dates and back to:
System.InvalidCastException: Cast from string "" to type 'Date' is not valid.
what info can i give you that will help to resolve?
thanks
thrill_houseCommented:
Do you know what line the error is on in your code?
lz7cjcAuthor Commented:
Line 92:             tblJob_Insert(notetitle.Text, notedescription.Text, "1", begandate.text, enddate.text, extdate.text, ddlcalltype.SelectedItem.Value, ddlproperty.Selecteditem.Value, drawingref.text, itemtitle.text, itemdescription.text, ddlprio.SelectedItem.Value, duedate.text, ddlaction.SelectedItem.Value, nextcall.text, notes.text, receiveddate.text)

which isn't very helpful... i have tried leaving all but one of the date fields empty (Each one in turn) but to no avail
thrill_houseCommented:
What line inside of that sub is the error coming from??
martie_11Commented:
Notice that you are always sending in the value of the text box...so if the value for the text box is "", that is what is being sent, and not the SqlDateTime.Null.
lz7cjcAuthor Commented:
Stack Trace:

[InvalidCastException: Cast from string "" to type 'Date' is not valid.]
   Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value, CultureInfo culture) +162
   Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value) +53
   ASP.addnewjob2_aspx.Submit_Click(Object Sender, EventArgs E) in C:\Inetpub\wwwroot\builders\addnewjob2.aspx:92
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277


Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

Martie_11 should I move the if...else to before this line then?
martie_11Commented:
Instead of:

Dim beganParam  As SQLParameter = command.Parameters.Add("@began", System.Data.SQLDbType.Datetime)
            If trim(begandate.text) = "" then
                 beganParam.Value = beginDateNull
            Else
                 beganParam.Value = trim(begandate.text.ToString())
            end if

Try the following:

Dim beganParam  As SQLParameter
        If Trim(begandate.Text) = "" Then
            beganParam = command.Parameters.Add("@began", beginDateNull)
        Else
            beganParam = command.Parameters.Add("@began", begandate.Text)
        End If
lz7cjcAuthor Commented:
same error :-(
martie_11Commented:
Okay, here is what is definetly not good.  Notice that when you call the sub:

           tblJob_Insert(notetitle.Text, notedescription.Text, "1", begandate.text, enddate.text, extdate.text, ddlcalltype.SelectedItem.Value, ddlproperty.Selecteditem.Value, drawingref.text, itemtitle.text, itemdescription.text, ddlprio.SelectedItem.Value, duedate.text, ddlaction.SelectedItem.Value, nextcall.text, notes.text, receiveddate.text)

all the dates are passed in as string (since they are from textboxes)

Whereas in the actual sub you are expecting DateTimes!

tblJob_Insert(ByVal jobtitle As String, ByVal jobdescription As String, ByVal statusid As integer,  ByVal began As Datetime, ByVal deadline As DateTime, ByVal extensiondate As DateTime, ByVal calltype As integer, ByVal propertyid As integer, ByVal drawingreference as string, ByVal tasktitle as string, ByVal taskdescripton as string, ByVal priorityid as integer, ByVal due as string, ByVal actionid as integer, ByVal nextdate as datetime, ByVal comments as string, ByVal receivedate as datetime)

That's not good : )

So, change the types to string in the sub...try that

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martie_11Commented:
That just might do it : )
thrill_houseCommented:
I'm pretty sure that will, especially if that shows up as the only error line.
lz7cjcAuthor Commented:
fantastic - well done... now I just need to go through the rest of the page and check it all works... assuming it does...
how do we split the points... obviously this was a bug that needed to be found but you both have helped through the question
is 80, 45 fair?
martie_11Commented:
Just split the points 50/50.

thx
thrill_houseCommented:
I think 80/45 is fair considering martie was actually the one who found the bug.  But whatever is fine.
lz7cjcAuthor Commented:
ok - whatever you say... i am just going to go back through the code and make sure this sorts it out... will be back shortly
martie_11Commented:
Either way, just happy to help.  There were other times where other Experts corresponded w/ me through the same sort of problem...just returning the favour : )

Thanks lz7cjc, thrill_house.
lz7cjcAuthor Commented:
you are both wonderful geniuses... thanks for your help.. it is all working nicely...
i have got another question open which i think is quite simple and would love to know if you have an answer on it...
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21481660.html

thanks for your help and i look forward to working with you again
Nick
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.