Record Count

I am converting a vb6 project to .net and half done. I am still trying to get a handle on Datasets and datareaders but so far so good. I have a form that when it loads does the following:
1. Loads a combobox with all userids (Done with dataset from one table)
2. Loads a listbox with all items that can be requested (done with a datareader from a second table)
3. Count the number of records in a third table and display the next number or the number 1 if no records (THE PROBLEM)

Here is the code so far (combo box code not here as it was done with the wizard)

VB Code:

Private Sub Request_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
        DsSN1.Clear()
        SqlDataAdapter1.Fill(DsSN1)
        Dim cn1 As New SqlConnection
        Dim da1 As New SqlDataAdapter
 
        'Populate the listbox step 2
        cn1.ConnectionString = "workstation id=""S-KG-CFSCE-2098"";packet size=4096;integrated security=SSPI;data s" & _
        "ource=""KG-CFSCE-2B"";persist security info=False;initial catalog=TrainingManageme" & _
        "ntDB"
        cn1.Open()
        Dim cmdtext As String = "Select distinct Asset, AssetConfig from Equipment where Type = 'Equipment' order by asset asc"
        da1.SelectCommand = New SqlCommand(cmdtext, cn1)
        Dim dr As SqlDataReader = da1.SelectCommand.ExecuteReader
        While dr.Read
            If dr.IsDBNull(0) Then
                List1.Items.Add((vbNullString & " " & dr.GetString(1)))
            End If
            If dr.IsDBNull(1) Then
                List1.Items.Add((dr.GetString(0) & " " & vbNullString))
            Else
                List1.Items.Add((dr.GetString(0) & " " & dr.GetString(1)))
            End If
 
        End While
        dr.Close()
        cn1.Close()
        List1.SelectedIndex = 0
 
        ''Get Highest Request Number and add one step 3
        cn1.ConnectionString = "workstation id=""S-KG-CFSCE-2098"";packet size=4096;integrated security=SSPI;data s" & _
                "ource=""KG-CFSCE-2B"";persist security info=False;initial catalog=TrainingManagementDB"
        cn1.Open()
        Dim cmdtext2 As String = "Select Count(RequestNum) from Request"
        da1.SelectCommand = New SqlCommand(cmdtext2, cn1)
        Dim dr2 As SqlDataReader = da1.SelectCommand.ExecuteReader
        While dr2.Read
            If dr2.IsDBNull(0) Then
                txtNum.text = 1
            Else
                txtNum.text =(Cint(dr.GetString(0)) +1)
            End If
        End While
 
 
    End Sub

Just a couple of questions that go with this. Can you reuse connecitons, datasets, or datareaders or do you create a new one for each part?

Ok found the problem I was doing a datareader getstring with an integer and should of been doing a getvalue.

Quote:

---

Originally posted by Beast777
Just a couple of questions that go with this. Can you reuse connecitons, datasets, or datareaders or do you create a new one for each part?

---

You have no need to close and reopen these objects if you are going to use them immediately. Connections take up a lot of resources so you would close them if they are not being used.

Your posted code contained the following in the final loop

"txtNum.text =(Cint(dr.GetString(0)) +1)"

but dr has been closed by that time. Probably you meant dr2, but that may be a simple typo in the posting.

Would he be able to solve his problem if he used @@IDENTITY ?

ok heres the code thats working with all my open and closes. any help with my open and closes that I may be doing wrong, please feel free to help.

VB Code:

Private Sub Request_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
        DsSN1.Clear()
        SqlDataAdapter1.Fill(DsSN1)
        'Populate the listbox
        SqlConnection1.ConnectionString = "workstation id=""S-KG-CFSCE-2098"";packet size=4096;integrated security=SSPI;data s" & _
        "ource=""KG-CFSCE-2B"";persist security info=False;initial catalog=TrainingManageme" & _
        "ntDB"
        SqlConnection1.Open()
        Dim cmdtext As String = "Select distinct Asset, AssetConfig from Equipment where Type = 'Equipment' order by asset asc"
        da.SelectCommand = New SqlCommand(cmdtext, SqlConnection1)
        Dim dr As SqlDataReader = da.SelectCommand.ExecuteReader
 
        While dr.Read
            If dr.IsDBNull(0) Then
                List1.Items.Add((vbNullString & " " & dr.GetString(1)))
            End If
            If dr.IsDBNull(1) Then
                List1.Items.Add((dr.GetString(0) & " " & vbNullString))
            Else
                List1.Items.Add((dr.GetString(0) & " " & dr.GetString(1)))
            End If
 
        End While
        dr.Close()
        SqlConnection1.Close()
        List1.SelectedIndex = 0
 
        ''Get Highest Request Number and add one
        SqlConnection1.ConnectionString = "workstation id=""S-KG-CFSCE-2098"";packet size=4096;integrated security=SSPI;data s" & _
                "ource=""KG-CFSCE-2B"";persist security info=False;initial catalog=TrainingManagementDB"
        SqlConnection1.Open()
        Dim cmdtext2 As String = "Select Count(RequestNum) from Request"
        da.SelectCommand = New SqlCommand(cmdtext2, SqlConnection1)
        dr = da.SelectCommand.ExecuteReader
       
        While dr.Read
            txtRequestNum.Text = dr.GetValue(0) + 1
 
        End While
        SqlConnection1.Close()
 
    End Sub