Object reference error when checking if value existi in SQL dB

Question

I am trying to run a series of checks on TextBox1 and display error messages when conditions are not met. Ultimately when all conditions are met before I then do a table insert and open a file dialog.

The first statement is to check if there is a value

The second checks that no numbers have been used

The third checks an SQL dB to make sure the value is not already in use - It is here I am having the issue.

If the value exists in the dB it flags the correct error:

MessageBox.Show(TextBox1.Text & " exists already.")

My issue is that when that condition is not met, I get a system error:

"Object reference not set to an instance of an object."

For this line of code.

Dim tarrif As String = cmd.ExecuteScalar().ToString()

I am not sure why because the rest of the code is below the ELSE statement?

Below is a longer section of my code, to give context.

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click


    'Checks something is in Textbox1
    If TextBox1.Text.Trim.Length = 0 Then
        MessageBox.Show("Please specify new tarrif name!", "Indigo Billing", _
        MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Else
        'Check textbox on only has characters

        If System.Text.RegularExpressions.Regex.IsMatch(TextBox1.Text, "^[0-9]*$") Then


            'Error is box contains numbers
            MessageBox.Show("Please enter Characters only!", "Indigo Billing", _
       MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else

            'Check name is not already in use

            Using conn As New SqlClient.SqlConnection("server=barry-laptop\SQLEXPRESS; database=Test; integrated security=yes")
                Using cmd As SqlClient.SqlCommand = conn.CreateCommand()

                    cmd.CommandText = "SELECT 1 FROM Tarrifs WHERE Tarrif = @Tarrif"
                    cmd.Parameters.AddWithValue("@Tarrif", TextBox1.Text)
                    conn.Open()

                    Dim tarrif As String = cmd.ExecuteScalar().ToString()
                    If tarrif = "1" Then

                        MessageBox.Show(TextBox1.Text & " exists already.")

                    Else
                        'Create new table based on specified Tarrif name
                        Using con = New SqlConnection("server=barry-laptop\SQLEXPRESS; database=Test; integrated security=yes")
                            Using cmda = New SqlCommand("CREATE TABLE " & TextBox1.Text & " (CallType VarChar(30),ChargeCode VarChar(30),Destination VarChar(30),TariffUsed VarChar(30),Peak Float,OffPeak Float,Weekend Float,Setup Float,MinimumCharge Float,ChargeCap INT,InitialUnits INT,InitialCharge INT,InitialPeak INT,InitialOffPeak INT,InitialWeekend INT,BillingUnit INT,MinimumUnits INT,RateType VarChar(30));", con)
                                con.Open()
                                cmda.ExecuteNonQuery()
                                con.Close()
                            End Using

                            'import name into Tarrif table
                            Using cmdb = New SqlCommand("INSERT INTO Tarrifs (Tarrif) VALUES (@tarrif2)", con)
                                con.Open()
                                cmdb.Parameters.AddWithValue("@tarrif2", TextBox1.Text)
                                cmdb.ExecuteNonQuery()
                                con.Close()
                            End Using
                        End Using


                        '--First create a datatable with the same cols as CSV file, the cols order in both should be same
                        Dim table As New DataTable()

                        table.Columns.Add("CallType", GetType(String))
                        table.Columns.Add("ChargeCode", GetType(String))
                        table.Columns.Add("Destination", GetType(String))
                        table.Columns.Add("TariffUsed", GetType(String))
                        table.Columns.Add("Peak", GetType(Decimal))
                        table.Columns.Add("OffPeak", GetType(Decimal))
                        table.Columns.Add("Weekend", GetType(Decimal))
                        table.Columns.Add("Setup", GetType(Decimal))
                        table.Columns.Add("MinimumCharge", GetType(Decimal))
                        table.Columns.Add("ChargeCap", GetType(Integer))
                        table.Columns.Add("InitialUnits", GetType(Integer))
                        table.Columns.Add("InitialCharge", GetType(Integer))
                        table.Columns.Add("InitialPeak", GetType(Integer))
                        table.Columns.Add("InitialOffPeak", GetType(Integer))
                        table.Columns.Add("InitialWeekend", GetType(Integer))
                        table.Columns.Add("BillingUnit", GetType(Integer))
                        table.Columns.Add("MinimumUnits", GetType(Integer))
                        table.Columns.Add("RateType", GetType(String))



                        'open file dialog and store filename'
                        Dim openFileDialog1 As New OpenFileDialog
                        Dim strFileName As String

If anyone can help with this issue, I would be very grateful

Object reference errors are all the same. You are referencing a property on an object where the objects value is null. In your case cmd.ExecuteScalar().ToString(). Either cmd or ExecuteScalar is null, so its failing on the subsequent call. — crthompson
– crthompson, Commented Apr 29, 2014 at 22:44
why not check to see if it exists like this: IF NOT isNothing (expression). — logixologist
– logixologist, Commented Apr 29, 2014 at 22:54

D Stanley · Accepted Answer · 2014-04-29 23:19:21Z

4

Dim tarrif As String = cmd.ExecuteScalar().ToString()

The problem is if no result is returned, then cmd.ExecuteScalar() will return Nothing and cmd.ExecuteScalar().ToString() will throw an exception.

A better option is:

cmd.CommandText = "SELECT 1 FROM Tarrifs WHERE Tarrif = @Tarrif"
cmd.Parameters.AddWithValue("@Tarrif", TextBox1.Text)
conn.Open()

If cmd.ExecuteScalar() Is Not Nothing Then

answered Apr 29, 2014 at 23:19

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3580480 Over a year ago

Thank you, however I get 'Is' operator does not accept operands of type 'Integer'. Operands must be reference or nullable types.

user3580480 Over a year ago

I updated to If cmd.ExecuteScalar() <>"" Then - and now working perfectly. THANK YOU :)

Jason Hughes Over a year ago

I usually do a newID= Convert.ToInt32(cmd.ExecuteScalar()) or similar to get the identity of the new row. Will return 0 on failure.

Johnny Fitz · Accepted Answer · 2014-04-29 23:10:19Z

0

The query will return Null I think if @tariff does not match any values on the table. This might be the problem. Just a guess - haven't tested. Maybe just change the query to

SELECT isNull(TarrifId,0) FROM Tarrifs WHERE Tarrif = @Tarrif

answered Apr 29, 2014 at 23:10

Johnny Fitz

5422 silver badges8 bronze badges

4 Comments

user3580480 Over a year ago

I have changed to cmdt.CommandText = "SELECT isNull(Tarrif,0) FROM Tarrifs WHERE Tarrif = @Tarrif" but unfortuantly this no longers catches the existing table values.

Johnny Fitz Over a year ago

Existing table values? Your code returns 1 for every possible match. I understood that it was just a check verifying presence on the table. Did you have something else in mind?

user3580480 Over a year ago

It is, but for some reason the code you have provided thinks there is no presence, for present values

Johnny Fitz Over a year ago

try count(1) instead of isNull(Tarrif,0). I'm confused, isNull should work. Might be the type of the tarrif column and replacing it with zeroes - it's a string isn't it - could try isNull(Tarrif,'0'). Let mw know how that goes anyway.

You · Accepted Answer · 2014-04-29 22:50:31Z

Try using "try and catch"

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Try

            'Checks something is in Textbox1
            If TextBox1.Text.Trim.Length = 0 Then
                MessageBox.Show("Please specify new tarrif name!", "Indigo Billing", _
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Else
                'Check textbox on only has characters

                If System.Text.RegularExpressions.Regex.IsMatch(TextBox1.Text, "^[0-9]*$") Then


                    'Error is box contains numbers
                    MessageBox.Show("Please enter Characters only!", "Indigo Billing", _
               MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Else

                    'Check name is not already in use

                    Using conn As New SqlClient.SqlConnection("server=barry-laptop\SQLEXPRESS; database=Test; integrated security=yes")
                        Using cmd As SqlClient.SqlCommand = conn.CreateCommand()

                            cmd.CommandText = "SELECT 1 FROM Tarrifs WHERE Tarrif = @Tarrif"
                            cmd.Parameters.AddWithValue("@Tarrif", TextBox1.Text)
                            conn.Open()

                            Dim tarrif As String = cmd.ExecuteScalar().ToString()
                            If tarrif = "1" Then

                                MessageBox.Show(TextBox1.Text & " exists already.")

                            Else
                                'Create new table based on specified Tarrif name
                                Using con = New SqlConnection("server=barry-laptop\SQLEXPRESS; database=Test; integrated security=yes")
                                    Using cmda = New SqlCommand("CREATE TABLE " & TextBox1.Text & " (CallType VarChar(30),ChargeCode VarChar(30),Destination VarChar(30),TariffUsed VarChar(30),Peak Float,OffPeak Float,Weekend Float,Setup Float,MinimumCharge Float,ChargeCap INT,InitialUnits INT,InitialCharge INT,InitialPeak INT,InitialOffPeak INT,InitialWeekend INT,BillingUnit INT,MinimumUnits INT,RateType VarChar(30));", con)
                                        con.Open()
                                        cmda.ExecuteNonQuery()
                                        con.Close()
                                    End Using

                                    'import name into Tarrif table
                                    Using cmdb = New SqlCommand("INSERT INTO Tarrifs (Tarrif) VALUES (@tarrif2)", con)
                                        con.Open()
                                        cmdb.Parameters.AddWithValue("@tarrif2", TextBox1.Text)
                                        cmdb.ExecuteNonQuery()
                                        con.Close()
                                    End Using
                                End Using
       Catch ex as Exception
       Msgbox("Null reference")
    end try


                                '--First create a datatable with the same cols as CSV file, the cols order in both should be same
                                Dim table As New DataTable()

                                table.Columns.Add("CallType", GetType(String))
                                table.Columns.Add("ChargeCode", GetType(String))
                                table.Columns.Add("Destination", GetType(String))
                                table.Columns.Add("TariffUsed", GetType(String))
                                table.Columns.Add("Peak", GetType(Decimal))
                                table.Columns.Add("OffPeak", GetType(Decimal))
                                table.Columns.Add("Weekend", GetType(Decimal))
                                table.Columns.Add("Setup", GetType(Decimal))
                                table.Columns.Add("MinimumCharge", GetType(Decimal))
                                table.Columns.Add("ChargeCap", GetType(Integer))
                                table.Columns.Add("InitialUnits", GetType(Integer))
                                table.Columns.Add("InitialCharge", GetType(Integer))
                                table.Columns.Add("InitialPeak", GetType(Integer))
                                table.Columns.Add("InitialOffPeak", GetType(Integer))
                                table.Columns.Add("InitialWeekend", GetType(Integer))
                                table.Columns.Add("BillingUnit", GetType(Integer))
                                table.Columns.Add("MinimumUnits", GetType(Integer))
                                table.Columns.Add("RateType", GetType(String))



                                'open file dialog and store filename'
                                Dim openFileDialog1 As New OpenFileDialog
                                Dim strFileName As String

Try/Catch should not be used when we can validate the data in code to avoid a unhandled exception. You should code to handle all exceptions and use try/catch only for those odd off chances of an error.
Thanks @You Because of the structure of my code, I had to put the Catch and Entry at the end of my code. Does that mean I need to repeat the code I want to run in this section? Thanks
-1 - do not use try/catch for an easily confirmable error. In your solution ANY error will show a "Null Reference" message.
Is there a problem with disposal of the connection and command objects where the try-catch is outside the Using statements? Without the try-catch the objects are disposed. With the try-catch the objects might remain. Is this not so?
No - the objects will get disposed when the using block ends, either through normal execution or an exception.

Collectives™ on Stack Overflow

Object reference error when checking if value existi in SQL dB

3 Answers 3

3 Comments

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related