Link to home
Start Free TrialLog in
Avatar of CraigLazar
CraigLazar

asked on

Null value issue writing to my database

Hi,
I am looking at someone code and need to fix an Null issue. It is really simple but i have not worked in .NET in awhile.
I have a function that takes the values that have been entered into a grid are then written to my sql database.
i need to make sure that if there is a null value, to set it to "" or not null before writing it away
So in belows code snippet, i need to test the PortfolioDescription before getting written tot eh database

thanks allot :)

below is the code
can i use the isDBNull function?

        Dim da As New OdbcDataAdapter
        Dim cmd As New OdbcCommand
        Dim cmd_del As New OdbcCommand
        Dim cmd_ins As New OdbcCommand
        Dim cn As New OdbcConnection
        Dim pr, pr2, pr3 As OdbcParameter

        With cn
            .ConnectionString = CON_STR
            Try
                .Open()
            Catch ex As Exception
                ErrorRoutine(ex)
            End Try
        End With

        With cmd
            .Connection = cn
            .CommandText = " UPDATE ClientGroupPortfolio" & _
                           " SET PortfolioCode=?,PortDescription=?,ExReport=?" & _
                           " WHERE GroupID=? AND PortfolioCode=?"

            '.Parameters.Add("@GroupID", OdbcType.VarChar, 4, "GroupID")
            .Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
            .Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
            .Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")

            pr = .Parameters.Add("@GroupID", OdbcType.VarChar, 4, "GroupID")
            pr.SourceVersion = DataRowVersion.Original
            pr2 = .Parameters.Add("@PortfolioCode", OdbcType.VarChar, 6, "PortfolioCode")
            pr2.SourceVersion = DataRowVersion.Original
         
           
        End With

        With cmd_ins
            .Connection = cn
            .CommandText = " INSERT INTO ClientGroupPortfolio" & _
                           " (GroupID,PortfolioCode,PortDescription,ExReport)" & _
                           " VALUES(?,?,?,?)"
            .Parameters.Add("@GroupID", OdbcType.VarChar, 4, "GroupId")

            .Parameters.Add("@PortfolioCode", OdbcType.VarChar, 6, "PortfolioCode")
            .Parameters.Add("@PortDescription", OdbcType.Char, 50, "PortDescription")
            .Parameters.Add("@ExReport", OdbcType.Bit, 1, "ExReport")

        End With


        da.UpdateCommand = cmd
        da.DeleteCommand = cmd_del
        da.InsertCommand = cmd_ins
        Try
            da.Update(dtCGP)
            MessageBox.Show("Updates Completed!", "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        Catch ex As Exception
            MessageBox.Show("Update Failed! " & ex.Message, "Client Reporting Group Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            'ErrorRoutine(ex)
        End Try

        cn.Close()



Avatar of dampsey
dampsey
Flag of Türkiye image

you can use "nothing" to control

if GroupID = nothing then
  groupID = ""
Avatar of CraigLazar
CraigLazar

ASKER

hi
forgive my ignorance but i am battling in the code above to figure out where to put ur code. I need to make sure the Productdescription is not NULL
so would i put this statement where?

thanks
With cn
            .ConnectionString = CON_STR
            Try
                .Open()
            Catch ex As Exception
                ErrorRoutine(ex)
            End Try
        End With
' put your code here

' put your code here
        With cmd
            .Connection = cn
            .CommandText = " UPDATE ClientGroupPortfolio" & _
                           " SET PortfolioCode=?,PortDescription=?,ExReport=?" & _
                           " WHERE GroupID=? AND PortfolioCode=?"

            '.Parameters.Add("@GroupID", OdbcType.VarChar, 4, "GroupID") ' These words are in '' but I think they should be variables. right????????????? => 'GroupID'
            .Parameters.Add("@PC", OdbcType.VarChar, 6, "PortfolioCode")
            .Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
            .Parameters.Add("@ExRpt", OdbcType.Bit, 1, "ExReport")

            pr = .Parameters.Add("@GroupID", OdbcType.VarChar, 4, "GroupID")
            pr.SourceVersion = DataRowVersion.Original
            pr2 = .Parameters.Add("@PortfolioCode", OdbcType.VarChar, 6, "PortfolioCode")
            pr2.SourceVersion = DataRowVersion.Original
         
           
        End With
well the code was written round a grid, so the values are comming in of a datagrid and not text boxes on a form
which i can see now its datasource is a datatable.
Parameters.Add("@PDesc", OdbcType.Char, 50, "PortDescription")
is the @PDesc holding the actual value?
ASKER CERTIFIED SOLUTION
Avatar of dampsey
dampsey
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok thanks so much

thats a great help


cheers