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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
have you tried IsNull in query..?


Dave Byron said:
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
Click to expand...
 
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/d...stemwindowsformscurrencymanagerclasstopic.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
///
 
Back
Top