What is the cnn. used for in this code?

[dougancil]

Someone gave me the following code to fix an issue but I'm not sure what some of it is used for.

oCmd = cnn.CreateCommand
oCmd.CommandText = "sp_allsum"
adapter.SelectCommand = oCmd 
adapter.Fill(ds)
 

What is the cnn. used for in this case? Intellisense is asking me to declare values for it.

[yarbrough40]

you obviously were only given a portion of the total code. cnn is referring to the connection object.

[dougancil]

Yarbrough,

I assumed that. Here is what I've written:

 Dim cnn As SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        oCmd = cnn.CreateCommand
        oCmd.CommandText = "sp_allsum"
        adapter.SelectCommand = oCmd
        adapter.Fill(ds)
 

but intellisense is telling me now that variable cnn is used before it is assigned a value. Do I need to worry about that warning? What's a better way to write that code?

[yarbrough40]

yes you need to worry about it : )

to use a connection object you have to instantiate a new one. All you have doen here is declare an undefined variable.
look into using "New"

 Dim cnn As New SqlConnection

you are also going to find that you need to assign the connection object a connection string, open the connection, then close it when you are done. Right now you haven't doen any of this.

Rock!

[dougancil]

Yarbrough,

Here's my completed code for this sub:

Public Class Payrollfinal
    Private Sub Payrollfinal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        exportfileButton.Enabled = False
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim _CMD As SqlCommand = New SqlCommand
        Dim cnn As New SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        oCmd = cnn.CreateCommand
        oCmd.CommandText = "sp_allsum"
        adapter.SelectCommand = oCmd
        adapter.Fill(ds)
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
                .Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
                .CommandText = "sp_allsum"
                oDr = .ExecuteReader()
                oCmd.Connection.Close()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
        Try
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        exportfileButton.Enabled = True
    End Sub
 

[yarbrough40]

ok there are several things going on here. First can you tell me what your end goal is... what does this stored proc do? are you running action queries (update,insert,delete) or are you running select statements to return values that your .net code will use for something on the page?