VB.net Getting Scalar to save a value to a variable

Question

I've been looking through the forums with no luck. I am trying to retrieve a value from a database and store it to a variable. The closest I got was following This. Here is my Code:

Dim dblAircraftHourlyRate As Double
    Dim intAircraftID As Integer
    intAircraftID = ddAircraft.SelectedItem.Value
    Dim mySqlComm As String = "SELECT HourlyRate FROM Aircraft WHERE AircraftID = '" & intAircraftID & "'"
    Using cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Rollin Laptop\Desktop\CurrentAll2Fly3\App_Data\ASPNETDB.MDF;Integrated Security=True;User Instance=True"), _
        cmd As New SqlCommand(mySqlComm, cn)
        cn.Open()
        dblAircraftHourlyRate = Convert.ToDouble(cmd.ExecuteScalar())
    End Using

I'm not sure why, but instead of saving the HourlyRate to the dblAircraftHourlyRate, it is saving the intAircraftID to dblAircraftHourlyRate. I'm also not sure why the example code did not close the database connection. Any ideas on how to fix this to get the correct variable?

How have you checked if the connection is still open? The physical connection keeps open with using connection-pooling(default). — Tim Schmelter, Dec 01 '13 at 14:18
The example code didn't close the connection because it does not include a line to do so. Also, do you get the aircraft id with more than one value? Maybe it was a fluke and the two numbers were the same. — Dan Bracuk, Dec 01 '13 at 14:22
@DanBracuk: He's using the [`Using`-statament](http://msdn.microsoft.com/en-us/library/htd05whh.aspx) which disposes and closes the connection. — Tim Schmelter, Dec 01 '13 at 14:25
@TimSchmelter, I'm not sure how to implement that connection-pooling(default) to check that. I'm pretty new to VB. If you could clarify how I would check, I would appreciate it. — VB noob, Dec 01 '13 at 14:30
@DanBracuk, the code is definitely returning the intAircraftID. The Hourlyrates are all 200 or less and the IDs are all 1000 or greater so they would not be the same value at any point in the application. There are no duplicate values being returned from the intAircraftID. — VB noob, Dec 01 '13 at 14:37
@VBnoob: [Connection-Pooling](http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx) is enabled by default. _Default_ means that it's enabled even if you don't specify the `Pooling` parameter in your connection-string. [Connection-pooling](http://www.connectionstrings.com/connection-pooling/) helps to improve performance since it maintains the state of the physical connections. So even if you close a connection the physical connection keeps open (If you check it in the database). You are using the `Using`-statement which is best practise. Disposing a connection will _"close"_ it. — Tim Schmelter, Dec 01 '13 at 14:59
@Thank you for the clarification and the help. The error was elsewhere in my code. I found it and fixed it. Again, I appreciate the quick responses and apologize for missing the obvious error. — VB noob, Dec 01 '13 at 15:13
Please take your time to share your solution as an answer. This can help other members. Accept this answer when possible. As a best practice, all resolved questions should have an accepted answer, to let other members know about its status. — Victor Zakharov, Dec 01 '13 at 16:20
@Neolisk: Sure. My solution had nothing to do with the question, but I had a separate bit of code that was executing after the bit I posted. It was resetting the value of dblAircraftHourlyRate such that dblAircraftHourlyRate = ddAircraft.SelectedItem.Value After I commented out the line, the code worked perfectly. To clarify, the value in the dropdownlist was the AircraftID, not the HourlyRate. — VB noob, Dec 01 '13 at 17:08
@Neolisk I just created this account to ask this question. Where do I go to mark it as answered? — VB noob, Dec 01 '13 at 18:28
You first need to post an answer. See "Your Answer" box below. Then there is a check mark beside it. Click on it to accept. — Victor Zakharov, Dec 01 '13 at 19:08
@Neolisk, Thanks. I can't post an answer for another few hours because I don't have enough points. I'll update it as answered tonight. — VB noob, Dec 01 '13 at 19:21

score 0 · Accepted Answer · answered Dec 03 '13 at 15:08

My solution had nothing to do with the question, but I had a separate bit of code that was executing after the bit I posted. It was resetting the value of dblAircraftHourlyRate such that dblAircraftHourlyRate = ddAircraft.SelectedItem.Value After I commented out the line, the code worked perfectly. To clarify, the value in the dropdownlist was the AircraftID, not the HourlyRate.

Tim Schmelter also helped me out in his explanation of how Connection-Pooling works:

Connection-Pooling is enabled by default. Default means that it's enabled even if you don't specify the Pooling parameter in your connection-string. Connection-pooling helps to improve performance since it maintains the state of the physical connections. So even if you close a connection the physical connection keeps open (If you check it in the database). You are using the Using-statement which is best practise. Disposing a connection will "close" it. – Tim Schmelter

VB.net Getting Scalar to save a value to a variable

1 Answers1