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

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).

Attachment 151959

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

Code:

---

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

Code:

---

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

Hi,

try something like

Code:

---

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

---

and for DGV

Code:

---

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

---

regards
Chris

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 Attachment 151961

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


Where i is the cell number through loop.

Its not solved.

Hi, have you tried

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

Or:

Code:

---

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

V1=DBNull.Value

Else

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

End If

---

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

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

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.