Conversion error

Hallo,

At run time my application gives an error of "Conversion from type "DBNull" to type "String" is not valid. In my form only one field - Add_info is an optional field which might be filled or not by the user.

Before, I had an error when an apostrophe was used so i updated the code as highlighted below. This in return removed the apostrophe error but generated an error whenever there is an empty additional field.

Any help please... Thanks


For irow = 0 To QryColSentDataGridView.Rows.Count - 1
If QryColSentDataGridView.Rows(irow).Cells(3).Value = True Then
Try
query = "UPDATE gprdsql.TblCollections SET system_time = '" & _
Labeldate.Text & "' where prac_no ='" & _
QryColSentDataGridView.Rows(irow).Cells(0).Value & " ' and stage ='" & _
"Request" & " ' "
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()

query = "UPDATE gprdsql.TblCollections SET additional_info = '" & _
Replace(QryColSentDataGridView.Rows(irow).Cells(2).Value, "'", "''") & "' where prac_no ='" & _
QryColSentDataGridView.Rows(irow).Cells(0).Value & " ' and stage ='" & _
"Request" & " ' "
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()

query = "UPDATE gprdsql.TblCollections SET stage = '" & _
"Sent" & "' where prac_no ='" & _
QryColSentDataGridView.Rows(irow).Cells(0).Value & " ' and stage ='" & _
"Request" & " ' "
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "GCPM")
End Try

End If

Next irow

dr223, you have been around for quite some time and still you are not using code tags when you post code in your post. Please make sure that you read about code tags here: http://www.codeguru.com/forum/misc.php?do=bbcode#code

Also edit your post and wrap it in the code tags to make it look different that the actual post.

Writing dynamic T-SQL is not a good idea, the better way of executing the SQL statements from the code is to write the parametrized queries. Look at the sample provided by MSDN and see how the queries are written using parameters.
http://msdn.microsoft.com/en-us/library/ms254953.aspx

Check if the value is DBNull and if it is, then assign another value to your variable.

Does your data grid view cell value return DBNull sometimes, so that you crash on the Replace function.

You can use
QryColSentDataGridView.Rows(irow).Cells(2).Value.ToString()
It's because DBNull.Value.ToString() is equal to empty string ("")

However, this will lead to another error when the Value is NOTHING, it's because NOTHING cannot ToString()