6 Replies - 367 Views - Last Post: 10 January 2019 - 11:23 AM Rate Topic: -----

#1 Damein   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 29-March 14

Problem with SQL Statement

Posted 10 January 2019 - 08:03 AM

So I have a simple Select statement but the column in question can be in two states, null or have data.

When it has data, everything works fine but when it doesn't the .exe crashes. I get no errors in VS.

            Statement = "SELECT constantVolts FROM servicepending WHERE serialNumber = @Serial"
            Using con As New MySqlConnection(DBConnection)
                Using cmd As New MySqlCommand(Statement)
                    Using sda As New MySqlDataAdapter()
                        cmd.Parameters.AddWithValue("@Serial", SerialNumberToUse)
                        cmd.Connection = con
                        sda.SelectCommand = cmd

                        Dim ds As New DataSet()
                        sda.Fill(ds)
                        If ds.Tables(0).Rows.Count <= 0 Then
                            SQLStopVoltData = True
                        Else
                            For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                                VoltageDataSet = ds.Tables(0).Rows(i).Item(0).ToString
                            Next
                        End If
                    End Using
                End Using
            End Using



Now I have found the problem that even when the DB shows the column to be NULL ds.Tables(0).Rows.Count comes out to = 1

Is it just simply because it found a column? If so, how do I add in check to see if it equals NULL so I can set my boolean to True?

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Problem with SQL Statement

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,591
  • Joined: 12-June 08

Re: Problem with SQL Statement

Posted 10 January 2019 - 08:07 AM

You can go further up the pipeline.. MSSQL has an 'isnull' method you should check out.

https://docs.microso...sql-server-2017
Was This Post Helpful? 0
  • +
  • -

#3 Damein   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 29-March 14

Re: Problem with SQL Statement

Posted 10 January 2019 - 08:14 AM

I'm not quite sure what you mean by that I'm fairly new to MySQL besides the basic statements.. is there not just a simple If/Else statement I can use after my select to determine it?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,591
  • Joined: 12-June 08

Re: Problem with SQL Statement

Posted 10 January 2019 - 08:17 AM

It is a better practice to have your sql data be the values you want coming out as much as possible.

If you are using MYSQL then 'ifnull'. Basically you use it and give it an alternative value if the select produces a null.. so in your case 'true'.
https://www.w3school...ysql_ifnull.asp
Was This Post Helpful? 0
  • +
  • -

#5 Damein   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 29-March 14

Re: Problem with SQL Statement

Posted 10 January 2019 - 08:29 AM

Hmm, that doesn't seem to result in the answer we're looking for

            Statement = "SELECT IFNULL('constantVolts FROM servicepending WHERE serialNumber = @Serial', 'Null')"
            Using con As New MySqlConnection(DBConnection)
                Using cmd As New MySqlCommand(Statement)
                    Using sda As New MySqlDataAdapter()
                        cmd.Parameters.AddWithValue("@Serial", SerialNumberToUse)
                        cmd.Connection = con
                        sda.SelectCommand = cmd
                        Dim ds As New DataSet()
                        sda.Fill(ds)

                        MessageBox.Show(ds.Tables(0).Rows(0).Item(0).ToString)
                    End Using
                End Using
            End Using


Show's me a textbox saying

constantVolts FROM servicepending WHERE serialNumber = @Serial

Instead of null
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14925
  • View blog
  • Posts: 59,591
  • Joined: 12-June 08

Re: Problem with SQL Statement

Posted 10 January 2019 - 08:33 AM

You would use it around the column that may contain a null. Not the entire statement.
Was This Post Helpful? 0
  • +
  • -

#7 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 209
  • View blog
  • Posts: 1,182
  • Joined: 05-December 13

Re: Problem with SQL Statement

Posted 10 January 2019 - 11:23 AM

Statement = "SELECT IFNULL('constantVolts FROM servicepending WHERE serialNumber = @Serial', 'Null')"


Think about what you're telling your mysqlcommand to check when passing a statement. Don't select [object from table] where value is null or nothing. Instead, check if there is a value, and then check what the value type is upon receiving it.

By right; you should know what the value type is in the field that you are selecting. In the case you don't, its nothing you can't use conditional logic on.
If value = Nothing Then
...its nothing
Else
...its Not nothing
End If

You should also check what the value type is if your table structure is not data specific. So don't take the above literally.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1