Help with sql code

I am using the following code...

vb Code:

Public Function getdatafi(ByVal querystring As String)
        'MsgBox(querystring)
        Using connection As New SqlConnection(GetConnectionStringfi)
            Dim command As New SqlCommand(querystring, connection)
            connection.Open()
 
            Dim reader As SqlDataReader = command.ExecuteReader
 
            While reader.Read
                Return reader.GetValue(0)
            End While
 
            reader.Close()
 
        End Using
 
    End Function

vb Code:

tempdimid = getdatafi("SELECT dim_id from dim_info where inspection_id = " & inspectionid & _
            " and dim_name = '" & dimname(tempnumber) & "'")

I put a break in and viewed the sql code. It is the correct code but it returns 0. If I run the exact sql code that the program creates in the SQL query anylizer, it returns the correct number. I am using the getdatafi function in other places and it works as it should. Does anyone know why this is not working?

try rdr.Item(0) instead and see if that works?

actually, you said returns the correct value, meaning the query returns only one row? if so then, try:

Code:

---

reader = command.ExecuteReader(CommandBehavior.SingleRow)
If reader.Read Then
return reader.item(0)
endif

---

Thanks. I will not be able to try it until tomorrow. I just don't understand why it won't work. I use this function in several other of my programs and I never had any problems with it. I even moved it to the form_load sub and it still did not work. Any idea why? The only thing I can think of is that the function is not being called but I don't know why it would be doing that and there are no errors either.

A couple of things that I see that should be cleaned up. They might not fix the issue, but they should be looked at
1.) Your function does not have a return type. Turn on Option Strict, it will benefit you in the long run.
2.) If your function returns a row, reader.Close() does not get called.
3.) You shouldn't build SQL queries by appending strings together. Look into using parameterized queries. This will help protect you from issues with single quotes in your queries among other things.

I guess I just got lazy....I will turn on option strict and do some research on the parameterized queries. I have not worked on the program for a couple of months, so I will look into it tomorrow.

I do not understand what you mean in 2. I thought that after it was done reading, it closed the connetion. Does it not finish the function if it returns a row?

After you do a return, it exits the function, so your .close does not get called.

An example like this, shows what I mean:

Code:

---

  Private Function RunMe() As String
        Return "Hello"
        MessageBox.Show("After hello")
    End Function

---

So how should I fix it? I don't want to have 100 connections open, but I assume they will time out and close. Do I have to call reader.close from the sub that calls the function?

Quote:

---

Originally Posted by asnider

actually, you said returns the correct value, meaning the query returns only one row? if so then, try:

Code:

---

reader = command.ExecuteReader(CommandBehavior.SingleRow)
If reader.Read Then
return reader.item(0)
endif

---

---

This did not help...It still returns 0

I turned on option strict and fixed all the errors. The query now returns the correct result!

But I have run into another problem...

vb.net Code:

Private Sub dimdata_UserAddedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles dimdata.UserAddedRow
        Dim ThisDataTableRow As Data.DataRow
 
        ThisDataTableRow = CType(e.Row.DataBoundItem, DataRow)
 
        'ThisDataTableRow.Item("ID") = recordcount
        [COLOR="Red"]ThisDataTableRow.Item("DIM_ID") = tempdimid[/COLOR]
 
    End Sub

I am using this code to add extra data to the datagridview but the code in red gives the errror:
Object reference not set to an instance of an object. The thisdatatablerow is nothing. Any ideas?

I am still having problems with this....does anyone have any ideas?