Solved

Update SQL Server table with blanks

Posted on 2007-11-30
5
279 Views
Last Modified: 2010-04-21
I have the following code which updates a SQL Server table from a windows application:

   Private Sub SaveGAD()

        If BOO_open = False Then

            SqlConnection1.Open()
            SQL_GAD_Update.Parameters("@Cont").Value = PUB_Cont
            SQL_GAD_Update.Parameters("@Str").Value = PUB_Str
            SQL_GAD_Update.Parameters("@Gdr").Value = "" & TXT_gdr1.Text & ""

            SQL_GAD_Update.Parameters("@GDP").Value = "" & TXT_cam.Text & ""
            SQL_GAD_Update.Parameters("@GCT").Value = "" & CB_cam2.Text & ""
            SQL_GAD_Update.Parameters("@GSAP").Value = "" & TXT_subarc.Text & ""
            SQL_GAD_Update.Parameters("@GML").Value = "" & TXT_mag.Text & ""
            SQL_GAD_Update.Parameters("@GMF").Value = "" & TXT_magW.Text & ""
            SQL_GAD_Update.Parameters("@GD1").Value = "" & TXT_dpth2.Text & ""
            SQL_GAD_Update.Parameters("@GD2").Value = "" & TXT_dpth4.Text & ""
            SQL_GAD_Update.Parameters("@GDE1").Value = "" & CB_dpth1.Text & ""
            SQL_GAD_Update.Parameters("@GDE2").Value = "" & CB_dpth3.Text & ""
            SQL_GAD_Update.Parameters("@GN1").Value = "" & TXT_note6.Text & ""
            SQL_GAD_Update.Parameters("@GN2").Value = "" & TXT_note7.Text & ""
            SQL_GAD_Update.Parameters("@GN3").Value = "" & TXT_note8.Text & ""
            SQL_GAD_Update.Parameters("@GFN").Value = "" & TXT_fno.Text & ""
            SQL_GAD_Update.Parameters("@GRN").Value = "" & TXT_revno.Text & ""

            If TXT_revdate.Text = "" Then

                SQL_GAD_Update.Parameters("@GRD").Value = System.DBNull.Value

            Else

                SQL_GAD_Update.Parameters("@GRD").Value = "" & TXT_revdate.Text & ""

            End If

            SQL_GAD_Update.Parameters("@GW").Value = False
            SQL_GAD_Update.Parameters("@GFCT").Value = "" & TXT_cam3.Text & ""
            SQL_GAD_Update.Parameters("@GFCB").Value = "" & TXT_cam4.Text & ""
            SQL_GAD_Update.ExecuteNonQuery()
            SqlConnection1.Close()

        End If

    End Sub

Currently, if a textbox is empty, the SQL Server table gets updated with a blank cell. My question is, should I be checking if the text box is empty with an if statement for each parameter (similar to the if statement for the @GRD parameter shown above), and then insert a system.dbnull.value?

Cheers
0
Comment
Question by:FMabey
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 20381105
By "blank" do you mean a zero-length string? A zero-length string is not the same thing as a null.

This is really a database modeling question. Does your database allow nulls and if so what is the designer's intended meaning for nulls in the database? The topic of whether and when to use nulls at all is a big one and probably not something we can do justice to in this forum.
0
 
LVL 3

Author Comment

by:FMabey
ID: 20381109
Yes, I mean a zero-length string and yes, the database does allow nulls. The designer does not want to do anything with the nulls... If it's empty, it's empty. I was just curious as to how best handle this.
0
 
LVL 22

Accepted Solution

by:
dportas earned 175 total points
ID: 20381165
>> If it's empty, it's empty

Ah but that's the point. Empty meaning "null" and empty meaning "zero-length string" are two very different things, possibly requiring different treatment and different code whenever they are used. All I'm saying is that your designer and your development team need to come to the same understanding of when to use nulls and when to use zero-length strings and then make sure they are used consistently throughout. Otherwise you'll likely find a lot of problems later on.
0
 
LVL 3

Author Closing Comment

by:FMabey
ID: 31411883
Good, clear explainations
0
 
LVL 3

Author Comment

by:FMabey
ID: 20381257
Cheers dportas,

I understand much better now. I'll take it on from here.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now