Reading value from database

Question

I am trying to read a single entry from a database based on a parameter being passed into my function. I know the connection string is working as I use it elsewhere and my SQL statement works as I can execute it in SQL Server with no problem. The problem that I am having is with the "return" statement. No matter where I put it, where I declare it or where I put my While loop reading the database table I get the green squiggly on 'Return siteID' telling me "variable 'siteID' is used before it has been assigned a value"

Public Function GetSiteID(ByVal siteName As String) As String

    Dim strConnectionString2 As String = ConfigurationManager.ConnectionStrings("AVDataConnectionString").ConnectionString


    Dim strQueryString2 As String = "SELECT SourceSite.SourceSiteID FROM[AVData].[dbo].[SourceSite] JOIN [AVData].[dbo].[SourceSiteMetaTag] ON SourceSite.SourceSiteID=[SourceSiteMetaTag].[SourceSiteID] WHERE SourceSiteMetaTag.TagName='SiteType' AND SourceSiteMetaTag.TagValue='Network'order by [Description] asc AND SiteName = '" & siteName & "'"

    Dim connection2 As New SqlConnection(strConnectionString2)
    Dim command2 As New SqlCommand(strQueryString2, connection2)

    Dim siteID As String
    Try
        connection2.Open()
        Dim myReader As SqlDataReader = command2.ExecuteReader()
        While myReader.Read()
            siteID = myReader("SourceSiteID").ToString()
        End While
    Catch ex As Exception
        Throw ex
    Finally
        connection2.Close()
    End Try

    Return siteID

End Function

did you check if the query actually returns anything? if it doesn't return any rows, your `while` loop never triggers and nothing ever gets assigned to `siteID`. e.g. put `siteID = "No records found"` somewhere before the loop. If you get that returned, then there's your problem. — Marc B, Feb 09 '16 at 16:51
you are assigning the value inside a Try block, the compiler knows that it might never get there if there is an Exception. Declare it as `Dim siteID As String = ""` (or other default value) and the message will go away — Ňɏssa Pøngjǣrdenlarp, Feb 09 '16 at 16:52
I would also think that statement would fail due to the `FROM[AvData]....` not having a space separating them. Unless there is something in the parser that is seeing the field wrapper and considering a space should be added. — Charles May, Feb 09 '16 at 18:26

score 1 · Answer 1 · answered Feb 09 '16 at 18:33

As the warning is telling you, you may return a value that was never set. This is because if your query returns no rows, the variable will never get set because you will not go into the WHILE loop. This means you need to handle a NOTHING as a possible return for this method call.

Now, this is only a warning, so you can ignore it or set a default value for your variable which makes the warning go away and also will prevent errors if you are not expecting a NOTHING. With strings, this would never error but with other data types it could so you should get into a habit of setting default values when you see this warning.

To make the warning go away, simply declare it like this instead:

Dim siteID As String = ""

score 1 · Accepted Answer · answered Feb 09 '16 at 18:36

1

If myReader never reads (connection2 isn't able to open and the try/catch is tripped) then siteId would never be assigned a value.

You can skirt around this issue by setting a value when you declare siteId

Dim siteID As String = ""

answered Feb 09 '16 at 18:36

Elliott

2,035
20
23

Thank-you for the responses. I used the (Dim siteID As String = "") and got it working. It makes sense. – Ethel Patrick Feb 09 '16 at 18:53

Tgrelka · Answer 3 · 2016-02-09T18:29:37.397

It looks like your reader is empty when you asign it's value to siteID. This could be because you read only one value from the database, and myReader.read() gets activated AFTER the reader has started reading. But then he doesn't hold the value anymore. Try using myReader.HasRows, this gets triggerd as soon as the reader holds a value.

I had a similar problem, reading from a database and giving console output, and with myReader.Read(), the first row always went missing. In my case, the solution above helped and fixed it.

Your new While-loop should look like this:

While myReader.HasRows()
    siteID = myReader("SourceSiteID").ToString()
End While

Also, asigning the value first time in the try-block is a problem. You should declare it above, using something like Dim siteID As String = "", this will fix any compiler problems. If there is an exception inside the try-catch-block, the value will be simply a empty string in the end.

this already gets done by executing the reader. The myReader.read() only checks if the reader is already reading, it does not trigger it. — Tgrelka, Feb 09 '16 at 18:30

Reading value from database

3 Answers3