[crazybear]

Hello All,

I am trying to pull data from an SQL Database and put it into some variables to use. The problem I am having is I have a date field called DateSubtmitted that is originally set to Null in the database. When I try to pull it into a vb.net variable called SubmittedDate As Date, it is giving me the following error:

Conversion from type 'DBNull' to type 'Date' is not valid.

I thought that VB.Net data types could all hold the null value, but I guess not. I know this must be a beginner mistake, but how can I make the null value work. My code is below, the line that gives me the error is:

SubmittedDate = myDR("DateSubmitted")

Any help is apprecaited.

Dim ServUser As String = Mid(Request.ServerVariables("LOGON_USER"), 7, 15)

		'Connect to the database and retrieve the users ID and other variables.
		Dim myConnection As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ID.mdf;Integrated Security=True;User Instance=True"
		Dim myConn As New SqlConnection()
		Dim myCmd As New SqlCommand()
		Dim myDR As SqlDataReader
		Dim table As String
		Dim sqlQuery As String
		Dim ID As String
		Dim SubmittedDate As Date
		Dim ApprovedDate As Date
		Dim DateChanged As Date

		table = "Users"
		sqlQuery = "SELECT * FROM " & table & " WHERE UserLogon='" & ServUser & "'"

		myConn.ConnectionString = myConnection
		myConn.Open()

		myCmd.Connection = myConn
		myCmd.CommandText = sqlQuery
		myDR = myCmd.ExecuteReader

		While myDR.Read()
			ID= myDR("ID")
			SubmittedDate = myDR("DateSubmitted")
			ApprovedDate = myDR("DateApproved")
			DateChanged = myDR("DateChangesMade")
		End While

		Dim submitted As Boolean = False


		If SubmittedDate <> System.DBNull.Value.ToString Then
			submitted = True
		End If

		myDR.Close()
		myConn.Close()

This post has been edited by crazybear: 20 July 2009 - 09:14 AM

[Paul Washburn]

Try adding ' ' around the date you are reading from the sql.

Here is an example of a select statement that i used for a recent project

Dim userDetailsAdapter As New SqlDataAdapter("SELECT * FROM tblCaseAdmin where Digit =" & cboDigit.Text & " AND Office like '" & cboOffice.Text & "' And Day between '" & dtStartDate.Text & "' and '" & dtEndDate.Text & "'", cnn)

				Dim userDetailsDataSet As New DataSet
				userDetailsAdapter.Fill(userDetailsDataSet, "Table1")
				userDetailsAdapter.Dispose()
				Dim userDetailsTable As DataTable = userDetailsDataSet.Tables(0)

				DataGridView1.DataSource = userDetailsDataSet.Tables("Table1")
				cnn.Close()

[crazybear]

Hello Paul, first, thanks for taking your time to help me. Second, I may not be understanding your post correctly, but I don't think that's the problem.

The line of code that generates the error is where I set a date variable equal to the data I pulled from the database. It is the line

SubmittedDate = myDR("DateSubmitted")

That generates the error because the value for "DateSubmitted" is null at the moment, and the SubmittedDate variable cannot accept a null value.

This post has been edited by crazybear: 20 July 2009 - 10:34 AM

[Paul Washburn]

Gotcha,

See if this works. It should substitute the null character for a blank string.

ISNull(DateSubmitted, ' ')

[T3hC13h]

You can either manually check for a null value and simply initialize the field to its default value or you can use Date.TryParse, which will return false if it cannot parse the value.

[crazybear]

Paul Washburn, on 20 Jul, 2009 - 09:47 AM, said:

Gotcha,

See if this works. It should substitute the null character for a blank string.

ISNull(DateSubmitted, ' ')

Thank you sir! I was able to solve this problem thanks to your help.

I did not solve it exactly the way you showed above (because IsNull was not working for me), but here is what I did:

If IsDBNull(myDR("DateSubmitted")) Then
	 SubmittedDate = ""
Else
	 SubmittedDate = myDR("DateSubmitted")
End If

[Paul Washburn]

Glad i could help.

Just to clarify, im pretty sure the IsNull() functions as an if/else statement so you should be able to use something like:

ApprovedDate = myDR(IsNull("DateApproved",""))

Not positive on the syntax on that, but should save you a few lines of code.