locked
Changing tables for a new Datareader() RRS feed

  • Question

  • I have the following code  I use to load up a database and find a field to use for some calculations.

    After getting the code to work the way I want I need to do another datareader for another field in another table, with the same database.  I was thinking I could leave the connectionString, theDatabase the same and just say:

    Code Snippet
    theCommand="SELECT [310Table], ConductorTypeColumn, ConductorCurrentRating " _
            '& "FROM ConductorCurrentRatingTables " _
            '& "WHERE ([310Table] = N'310.16') AND (ConductorTypeColumn = 3) AND (ConductorCurrentRating >= 122)"

     

     

    'then enter:

    Code Snippet
    SqlClient.SqlCommand(theCommand, theDatabase)

     

     

    But odbiously not as it seem to mess up my previous working code.  It also seems that I must use a Dim statement again for everything using a new name.  What concept am I missing? Is there a better way to change the table I am reading to pull a new SELECT statement?

     

    Here is my original code that seems to work before I started adding a new datareader.  I should mention that this code is on the ELSE of an IF END IFStatement and my new connect and select is outside of the IF statement:

     

    Code Snippet

               '-----Connect to the database.
                Dim connectionString As String = _
                    "Data Source=.\SQLEXPRESS;" & _
                    "AttachDbFilename=|DataDirectory|\CodeDesign.mdf;Integrated Security=True;User Instance=True"
                Dim theDatabase As New SqlClient.SqlConnection(connectionString)
                theDatabase.Open()
                'Prepare the SQL statement for use by the dataset
                Dim theCommand As String = "SELECT [310Table], MaterialInsulationTemp, AmbTempLower, AmbTempUpper, CorrectionFactor " _
                & "FROM ConductorCurrentTempCorrection " _
                & "WHERE ([310Table] = '31016') AND (MaterialInsulationTemp = @MIT) AND (AmbTempLower <= @ACF) AND (AmbTempUpper >= @ACF)"

                Dim sqlStatement As New SqlClient.SqlCommand(theCommand, theDatabase)
                sqlStatement.Parameters.AddWithValue("@MIT", mycbInsulationType)
                sqlStatement.Parameters.AddWithValue("@ACF", myambienttemp)
                Dim myResults As SqlClient.SqlDataReader = sqlStatement.ExecuteReader()
                myResults.Read()
                myambientcorrectionfactor = CDbl(myResults!CorrectionFactor)
                sqlStatement = Nothing
                theDatabase.Close()
                theDatabase.Dispose()

     

     

    Monday, July 7, 2008 10:06 PM

Answers

  •  

     

    I found the solution to the problem.  I put a DIM statement and connection inside of the ELSE part of a IF THEN block.  The software was protecting me from the fact that I might not go into the ELSE part of the block and then would not have the SQL commands assigned. This code now works and I was able to change the myCmd.CommandText multiple times for different selections.

     

    This technique also got me away from the SqlStatement.Pararmeters.AddWithValue() part. 

     

     

    Code Snippet

    '-----Connect to the database.

    Dim myConn As SqlClient.SqlConnection

    Dim myCmd As SqlClient.SqlCommand

    Dim myReader As SqlClient.SqlDataReader

    Dim connectionString As String

    If myfindcorrection Then

    connectionString = _

    "Data Source=.\SQLEXPRESS;" & _

    "AttachDbFilename=|DataDirectory|\CodeDesign.mdf;Integrated Security=True;User Instance=True"

    'Create a Connection object.

    myConn = New SqlClient.SqlConnection(connectionString)

    myCmd = myConn.CreateCommand

    myCmd.CommandText = "SELECT [310Table], MaterialInsulationTemp, AmbTempLower, AmbTempUpper, CorrectionFactor " _

    & "FROM ConductorCurrentTempCorrection " _

    & "WHERE ([310Table] = N'" & cb300table.Text & "') AND (MaterialInsulationTemp = " & mycbInsulationTypeCol & ") AND (AmbTempLower <= " & myambienttemp & ") AND (AmbTempUpper >= " & myambienttemp & ")"

    'Open the connection.

    myConn.Open()

    myReader = myCmd.ExecuteReader()

    'Concatenate the query result into a string.

    'add an if then statement to make sure something is found for myReader.Read()

    myReader.Read()

    myambientcorrectionfactor = CSng(myReader!CorrectionFactor)

    myReader.Close()

    myConn.Close()

     

     

     

     

    Wednesday, July 9, 2008 7:24 PM