none
Insert GridCellValue or NULL into SQL Decimal column RRS feed

  • Question

  • Dear Expert,

    I have a datbase table with datatype decimal(5,2) and from vb.net application using devexpress gridview I am updating values into the sql table but if the grid cell value is empty then I need to insert NULL else gridrowcell value. There are 20 such columns where I need to check for NULL value but I get Conversion from string "" to double is not valid. 

    What is the mistake in the if condition am writing? I have also tried DBNULL.Value instead of dt, cdec( GridView1.GetRowCellValue(i,"col1"), cdbl(GridView1.GetRowCellValue(i,"col1") 

    GridView1.GetRowCellValue(i, "Col1")
    dim dt as decimal =0.00
    Dim v1 As Decimal = If(GridView1.GetRowCellValue(i, "col1") <> "", GridView1.GetRowCellValue(i, "Col1"), dt)
    Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
    dim dt as decimal = 0.00
    For i As Integer = 0 To GridView1.DataRowCount
    
    Dim v1 As Decimal = If(GridView1.GetRowCellValue(i, "col1") <> "", GridView1.GetRowCellValue(i, "Col1"), dt)
    Dim v1 As Decimal= If(GridView1.GetRowCellValue(i, "col2") <> "", GridView1.GetRowCellValue(i, "Col2"), dt)
    '
    '
    ' similar code till 20 columns
    Dim v20 As String = If(GridView1.GetRowCellValue(i, "col20") <> "", GridView1.GetRowCellValue(i, "Col20"), dt)
    
    Dim r As String = "INSERT INTO [dbo].[MyTable] ([col1],[col2],[col3],[col4],[col5],.................,[col20]) 
    values (v1,v2,v3,v4,v5 ...........,v20)
    
    SQL.Execute(r)
    If sql.HasException(True) Then Exit Sub
    
    Next 
    
    End Sub
    

    Regards,

    NewBInVB
     


    NewBInVB

    Monday, September 18, 2017 7:40 PM

All replies

  • First off, no matter if this is a third party grid or a Microsoft .NET grid, you should be accessing data via the data source e.g. in a DataGridView it would be DataGridView.DataSource. Suppose it was a DataGridView with the DataSource set to a DataTable (common for working with backend data) you would iterate the rows and fields to see if a value was nothing and act accordingly.

    Anyways I suggest asking your question in the developer's forum at DevExpress.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 18, 2017 10:22 PM
  • Dear Karen,

    In another sub routine am loading the datagrid.datasource from a datatable. Which is working fine. My code for inserting from datagrid to sql table on a button click shows an error if the datagridcolumn has a blank value. 

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

    DataGridView

    From this I have to insert these values into MyTable on a click of a button,MySample 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 decimal =0.00 (should be actually 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. Sorry for the confusion

    Regards,


    NewBInVB


    • Edited by NewBInCoding Wednesday, September 20, 2017 7:15 AM
    Wednesday, September 20, 2017 7:13 AM
  • Hi NewBinVB,

    Since you are developing with third-party control. We are unable to offer helpful suggestions. Please refer to DevExpress forum :

    https://www.devexpress.com/Support/Center/Question/List/1

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 22, 2017 8:39 AM