?
Solved

Null value issue writing to my database

Posted on 2008-02-07
6
Medium Priority
?
172 Views
Last Modified: 2013-11-26
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()



0
Comment
Question by:CraigLazar
  • 3
  • 3
6 Comments
 
LVL 8

Expert Comment

by:dampsey
ID: 20839997
you can use "nothing" to control

if GroupID = nothing then
  groupID = ""
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 20840232
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
0
 
LVL 8

Expert Comment

by:dampsey
ID: 20840261
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 4

Author Comment

by:CraigLazar
ID: 20840422
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?
0
 
LVL 8

Accepted Solution

by:
dampsey earned 200 total points
ID: 20840624
@PDesc is a variable name, the last parameter is/should be the value of the parameter

so if you want gridview value

Parameters.Add("@PDesc", OdbcType.Char, 50, grid1.Rows[0].Cells[0].value.tostring())

or something similar
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 20840717
ok thanks so much

thats a great help


cheers
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question