Solved

Update SQL Server table with blanks

Posted on 2007-11-30
5
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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