[RESOLVED] Insert GridCellValue or NULL into SQL Decimal column Conversion from string "" to dbl

[arpitha]

Dear Experts,

From another sub routine am loading the datagrid from a sql datatable. Which is working fine. My code for inserting from datagrid to another sql table on a button click shows an error Conversion from string "" to double is not valid.

My datagridview looks like this after filling the datagridview from datasource (as you have mentioned).



From this I have to insert these values into MyTable on a click of a button,MySample SQL table has fields like this

Create Table MySample(
         SampleID uniqueidentified,
        [col1] [decimal](5, 2) NULL,
	[col2] [decimal](5, 2) NULL,
	[col3] [decimal](5, 2) NULL,
	[col4] [decimal](5, 2) NULL,
	[col5] [decimal](5, 2) NULL,  
        CONSTRAINT [PK_SampleID] PRIMARY KEY CLUSTERED )

So for each row of the datagridview above I have to insert it into MySample database table . For example row 1 (1636001) has no value in col 4, col5 so I have to insert NULL,

In SQL its very simple, I can type NULL if blank

INSERT INTO [dbo].[MySample] ([SampleID],[1],[2],[3],[4],[5]) VALUES ('1636001',3.40,4,2.30,NULL,NULL)

But in vb.net I'm not able to insert NULL value from the code so I'm passing each of the rowcell into a variable

Dim dt as String = "NULL"
Dim v1 As Decimal = If(GridView1.GetRowCellValue(i, "1") <> "", GridView1.GetRowCellValue(i, "1"), dt)
Dim v1 As Decimal= If(GridView1.GetRowCellValue(i, "2") <> "", GridView1.GetRowCellValue(i, "2"), dt)
'
'
' similar code till 5columns
Dim v5 As String = If(GridView1.GetRowCellValue(i, "5") <> "", GridView1.GetRowCellValue(i, "20"), dt


I need help in writing the IF condition for inserting the NULL if the gridrowcellvalue is blank. How to insert NULL for a decimal column from VB:NET?

Waiting for help.

Thanks
Arp

[ChrisE]

Hi,

try something like

 Public Function ChangeToNull(ByVal myValue As Object) As Object
        If Trim(myValue) = "" Then
            ChangeToNull = DBNull.Value
        Else
            ChangeToNull = myValue
        End If

and for DGV

.....
 para28.Value = .Cells(27).Value
 para29.Value = ChangeToNull(.Cells(28).Value)
....

regards
Chris

[arpitha]

Dear Chris,

I get Conversion from type 'DBNull' to type 'String' is not valid error when the value is "" on the gridview

I have followed your code and edited my code something similar

sql.AddParam("v1", ChangeToNull(GridView1.GetRowCellValue(i, "1")))


Where i is the cell number through loop.

[arpitha]

Its not solved.

[Mike Storm]

Hi, have you tried

Dim v1 As Decimal = If(String.IsNullOrEmpty(GridView1.GetRowCellValue(i, "1")),DBNull.Value, GridView1.GetRowCellValue(i, "1"))

[Mike Storm]

Or:

Dim v1 As Decimal
If String.IsNullOrEmpty(GridView1.GetRowCellValue(i, "1")) Then

V1=DBNull.Value

Else

V1=GridView1.GetRowCellValue(i, "1")

End If

[arpitha]

Yes, I have tried this but I get Additional information: Conversion from type 'DBNull' to type 'String' is not valid.

[Mike Storm]

Post the code used to load and to save, maybe we can help you better.

[arpitha]

Dim v1 As String = If(IsDBNull(GridView1.GetRowCellValue(i, "1")), "NULL", GridView1.GetRowCellValue(i, "1"))

This code works, if we use IsDBNULL then NULL must be used in the if condition and the variable type must be string, This returns NULL for the update/insert statement.

Thanks for all the hints and help.