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

[phil_mw60]

Dear All,

Please see the following subroutine I have created called 'getappointmentnotes'

Public Sub getappointmentnotes(ByVal appnumber As Object, ByRef appointmentnotes As Object)

Dim sql As String
sql = "SELECT appnotes from appointments where appnumber = " & appnumber & ""
da = New OleDb.OleDbDataAdapter(sql, conn)
da.Fill(ds, "tempdataset")
da.Update(ds, "tempdataset")
appointmentnotes = ds.Tables("tempdataset").Rows(0).Item(1)

ds.Tables("tempdataset").Clear()

End Sub

I call this subroutine (from a seperate form) as follows:

bookingscreenform.getappointmentnotes(appnumber, appointmentnotes)

At runtime I get the error 'Conversion from type 'DBNull' to type 'String' is not valid' on the line where I try to set appointmentnotes to equal the item in my dataset.

I suspect that the dataset is not actually being filled by this SQL query as I have used the data adaptor in other parts of my program and indeed the 'tempdataset' ds name. However when I use this previously I issued a ds.Tables("tempdataset").Clear() command, so I'm sure there are not old records in my dataset.

so, does anyone have any ideas what might be causing this error?

many thanks in advance, I am starting to get mildy annoyed with it!!

 

[giadich]

it seems like your are not getting any data back from sql server. you might want to run the query manually in QA to confirm this.

Also, it helps to add a blank string to the db value when you assign it to a string variable. This will help in case the value is null. As in your example:

appointmentnotes = ds.Tables("tempdataset").Rows(0).Item(1) & ""

 

[phil_mw60]

Hi,

Just to let you know I managed to resolve this....

I used a different variable for my dataadaptor ie 'mydataadaptor' and also gave my dataset a different name 'mydataset'.

This then filled my dataset with the required data and not a NULL. I'm still confused why this happened in the first place because I definitely issued a ds.tables("tempdataset").clear command (in a different subroutine) but for some reason the old data still seemed to be present.

thx anyway,

Phil

 

[cjard]

If youre using Access, SQLServer or Oracle, you could do with altering the way you are doing your data access. See the DW2 link in my signature; Creating a SImple Data Application is a good place to start

 

[AliShah]

Good answer

it seems like your are not getting any data back from sql server. you might want to run the query manually in QA to confirm this.

Also, it helps to add a blank string to the db value when you assign it to a string variable. This will help in case the value is null. As in your example:

appointmentnotes = ds.Tables("tempdataset").Rows(0).Item(1) & ""[GOOD ANSWER ]

 

[JuggaloBrotha]

it seems like your are not getting any data back from sql server. you might want to run the query manually in QA to confirm this.

Also, it helps to add a blank string to the db value when you assign it to a string variable. This will help in case the value is null. As in your example:

appointmentnotes = ds.Tables("tempdataset").Rows(0).Item(1) & ""[GOOD ANSWER ]

Actually this isn't all that great of an answer, besides way to dig up a 4 year old thread!