Solved

Update SQL Server table with blanks

Posted on 2007-11-30
5
280 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Nested cursor  in SQL 9 94
SSRS ReportViewer report timeout 7 101
SQL joining table to itself to combine rows 5 31
Error when loading the database 16 55
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

19 Experts available now in Live!

Get 1:1 Help Now