469,568 Members | 1,483 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Cast from type 'DBNull' to type 'String' is not valid.

I am having trouble with DBNull's from my SQL server. I am
building/converting a asset web app from Access and my db has nulls on
various fields . I have tried searching newsgroups and tried to get some
code and some understanding . I am a newbie to vb but my code is below, so
can anybody let me know what I am doing wrong to stop this error " Cast from
type 'DBNull' to type 'String' is not valid."
--------------------------------------------------------------------------------
Public Function NullHelper(ByVal obj As Object) As String
If IsDBNull(obj) Then Return String.Empty
Return obj.ToString()
End Function

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Public Function GetAsset(ByVal assetNo As String, ByRef adapter As
SqlDataAdapter) As DataSet

'connect to the database
Dim connection As New SqlConnection(Global.dbstring)
connection.Open()

'set up the SQL command object
Dim command As New SqlCommand("SELECT * FROM Computers WHERE
asset='" & assetNo & "'", connection)
' Retreive the data
adapter = New SqlDataAdapter(command)

' add a command builder
Dim builder As New SqlCommandBuilder(adapter)

'Fill the dataset
Dim asset As New DataSet
adapter.Fill(asset)

' disconnect
connection.Close()

' return asset details
Return asset

End Function

Public Function GetAsset(ByVal AssetNo As String) As DataSet

' Call the other Version of the method
Dim adapter As SqlDataAdapter
Dim asset As DataSet = GetAsset(AssetNo, adapter)

'Dispose of the Adapter
adapter.Dispose()

'Return asset details
Return asset

End Function

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnsearch.Click
BindData(txtAssetSearch.Text)
End Sub
'BindData - Bind the data to the controls
'Overloads Overrides Public Function ToString() As String, BindData
(ByVal AssetNo As String)

Public Sub BindData(ByVal AssetNo As String)

'set lable.text
lblEditingId.Text = AssetNo

'get the blue_Number
Dim assetDataset As DataSet = GetAsset(AssetNo)

'If the Asset was found....
If assetDataset.Tables(0).Rows.Count > 0 Then

'....Reset the label Text
lblproblem.Text = ""
btnSaveChanges.Visible = True

'Update the controls
Dim Asset As DataRow = assetDataset.Tables(0).Rows(0)

txtInstallDate.Text = NullHelper(Asset("installation_date"))
txtnetname.Text = Asset("network_name") & ""
txtSerial.Text = Asset("serial_number") & ""
txtModel.Text = Asset("model") & ""
txtPlatform.Text = Asset("platform") & ""
txtMac.Text = Asset("hardware_address") & ""
txtProcessor.Text = Asset("processor_speed") & ""
txtRam.Text = Asset("ram") & ""
txtHD.Text = Asset("hard_disk_size") & ""
txtMake.Text = Asset("make") & ""
txtOS.Text = Asset("os_desc") & ""
txtKitType.Text = Asset("kit_type") & ""
txtDept.Text = Asset("department") & ""
txtBuilding.Text = Asset("building") & ""
txtFloor.Text = Asset("floor") & ""
txtMonitorAsset.Text = Asset("monitor_Asset") & ""
txtMonitorSize.Text = Asset("monitor_size") & ""
txtMonitorMake.Text = Asset("monitor_make") & ""
txtMonitorModel.Text = Asset("monitor_model") & ""
txtMonitorSerial.Text = Asset("monitor_serial_no") & ""
'txtInstallDate.Text = Asset("installation_date") & ""
'txtDateMod = Asset("date_modified") & ""
txtcomments.Text = Asset("other_info")

Else

' ....Otherwise set an error message
lblproblem.Text = "The Asset Number' " & AssetNo & _
"' does not exist"
btnSaveChanges.Visible = False

'Update the controls
txtnetname.Text = ""
txtSerial.Text = ""
txtModel.Text = ""
txtPlatform.Text = ""
txtMac.Text = ""
txtProcessor.Text = ""
txtRam.Text = ""
txtHD.Text = ""
txtMake.Text = ""
txtOS.Text = ""
txtKitType.Text = ""
txtDept.Text = ""
txtBuilding.Text = ""
txtFloor.Text = ""
txtMonitorAsset.Text = ""
txtMonitorSize.Text = ""
txtMonitorMake.Text = ""
txtMonitorModel.Text = ""
txtMonitorSerial.Text = ""
txtInstallDate.Text = ""
'txtDateMod.Text = ""
txtcomments.Text = ""
End If

End Sub
Jul 21 '05 #1
3 5031
have you tried IsNull in query..?
"Dave Byron" wrote:
I am having trouble with DBNull's from my SQL server. I am
building/converting a asset web app from Access and my db has nulls on
various fields . I have tried searching newsgroups and tried to get some
code and some understanding . I am a newbie to vb but my code is below, so
can anybody let me know what I am doing wrong to stop this error " Cast from
type 'DBNull' to type 'String' is not valid."
--------------------------------------------------------------------------------
Public Function NullHelper(ByVal obj As Object) As String
If IsDBNull(obj) Then Return String.Empty
Return obj.ToString()
End Function

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Public Function GetAsset(ByVal assetNo As String, ByRef adapter As
SqlDataAdapter) As DataSet

'connect to the database
Dim connection As New SqlConnection(Global.dbstring)
connection.Open()

'set up the SQL command object
Dim command As New SqlCommand("SELECT * FROM Computers WHERE
asset='" & assetNo & "'", connection)
' Retreive the data
adapter = New SqlDataAdapter(command)

' add a command builder
Dim builder As New SqlCommandBuilder(adapter)

'Fill the dataset
Dim asset As New DataSet
adapter.Fill(asset)

' disconnect
connection.Close()

' return asset details
Return asset

End Function

Public Function GetAsset(ByVal AssetNo As String) As DataSet

' Call the other Version of the method
Dim adapter As SqlDataAdapter
Dim asset As DataSet = GetAsset(AssetNo, adapter)

'Dispose of the Adapter
adapter.Dispose()

'Return asset details
Return asset

End Function

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnsearch.Click
BindData(txtAssetSearch.Text)
End Sub
'BindData - Bind the data to the controls
'Overloads Overrides Public Function ToString() As String, BindData
(ByVal AssetNo As String)

Public Sub BindData(ByVal AssetNo As String)

'set lable.text
lblEditingId.Text = AssetNo

'get the blue_Number
Dim assetDataset As DataSet = GetAsset(AssetNo)

'If the Asset was found....
If assetDataset.Tables(0).Rows.Count > 0 Then

'....Reset the label Text
lblproblem.Text = ""
btnSaveChanges.Visible = True

'Update the controls
Dim Asset As DataRow = assetDataset.Tables(0).Rows(0)

txtInstallDate.Text = NullHelper(Asset("installation_date"))
txtnetname.Text = Asset("network_name") & ""
txtSerial.Text = Asset("serial_number") & ""
txtModel.Text = Asset("model") & ""
txtPlatform.Text = Asset("platform") & ""
txtMac.Text = Asset("hardware_address") & ""
txtProcessor.Text = Asset("processor_speed") & ""
txtRam.Text = Asset("ram") & ""
txtHD.Text = Asset("hard_disk_size") & ""
txtMake.Text = Asset("make") & ""
txtOS.Text = Asset("os_desc") & ""
txtKitType.Text = Asset("kit_type") & ""
txtDept.Text = Asset("department") & ""
txtBuilding.Text = Asset("building") & ""
txtFloor.Text = Asset("floor") & ""
txtMonitorAsset.Text = Asset("monitor_Asset") & ""
txtMonitorSize.Text = Asset("monitor_size") & ""
txtMonitorMake.Text = Asset("monitor_make") & ""
txtMonitorModel.Text = Asset("monitor_model") & ""
txtMonitorSerial.Text = Asset("monitor_serial_no") & ""
'txtInstallDate.Text = Asset("installation_date") & ""
'txtDateMod = Asset("date_modified") & ""
txtcomments.Text = Asset("other_info")

Else

' ....Otherwise set an error message
lblproblem.Text = "The Asset Number' " & AssetNo & _
"' does not exist"
btnSaveChanges.Visible = False

'Update the controls
txtnetname.Text = ""
txtSerial.Text = ""
txtModel.Text = ""
txtPlatform.Text = ""
txtMac.Text = ""
txtProcessor.Text = ""
txtRam.Text = ""
txtHD.Text = ""
txtMake.Text = ""
txtOS.Text = ""
txtKitType.Text = ""
txtDept.Text = ""
txtBuilding.Text = ""
txtFloor.Text = ""
txtMonitorAsset.Text = ""
txtMonitorSize.Text = ""
txtMonitorMake.Text = ""
txtMonitorModel.Text = ""
txtMonitorSerial.Text = ""
txtInstallDate.Text = ""
'txtDateMod.Text = ""
txtcomments.Text = ""
End If

End Sub

Jul 21 '05 #2
Dave,

There goes a lot basicly wrong in your code.

You do not databinding bellow a sample I took from a program I am busy with
I took a date, a string is even more simple. (I changed a lot so watch
typos).

Have as well a look at the currencymanager what I have not in this sample.

http://msdn.microsoft.com/library/de...classtopic.asp

Did you know that there is a VBNet language related newsgroup?
microsoft.public.dotnet.languages.vb

I hope this sample helps however anyway?

Cor
\\\
Dim dt As DataTable = ds.Tables("Persons")
dv = New DataView(dt)
dim mybinding as Binding
Mybinding = New Binding("Text", dv, "TheDate")
AddHandler Mybinding.Format, AddressOf DBDateTextbox
AddHandler Mybinding.Parse, AddressOf TextboxDBDate
txtStreet.DataBindings.Add(Mybinding)
///
\\\ this only one time, you add every time the same handlers using the
binding
Private Sub DBdateTextbox(ByVal sender As Object, _
ByVal cevent As ConvertEventArgs)
If cevent.Value Is DBNull.Value Then
cevent.Value = ""
Else
Dim datum As Date
datum = CDate(cevent.Value)
cevent.Value = datum.ToString("dd - MM - yyyy")
End If
End Sub
Private Sub TextBoxDBdate(ByVal sender As Object, _
ByVal cevent As ConvertEventArgs)
If cevent.Value.ToString = "" Then
cevent.Value = DBNull.Value
End If
End Sub
///
Jul 21 '05 #3
No I have not tried ISDBNull in my query

"Sudhakar Sadasivuni[mvp]" wrote:
have you tried IsNull in query..?

Jul 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ruslan Shlain | last post: by
5 posts views Thread by .Net Sports | last post: by
3 posts views Thread by PK9 | last post: by
1 post views Thread by amjad | last post: by
2 posts views Thread by DMG | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.