CODEWITHSUNDEEP
sqlvb.netconsole-application
Gerry85
Gerry85

Reputation: 71

SELECT SQL Statement not working as expected in VB.net Console application

I know there are similar posts regarding this but i cant seem to find anything that works.

I'm still new to VB and this which is probably obvious is driving me mad.

What im looking to do is to Run a SQL SELECT and email the results to those who require it.

When i run it I'm getting the 'ELSE' email which states that there is nothing on HOLD, however running the Query in SQL Server Management Studio does present results.

Here is what i have:

Public Sub Main()
    Dim connection As New SqlConnection(My.Settings.connectionString)
    connection.Open()
    Dim sqlCommand As SqlCommand
    log(" Processing - Searching SQL Database for Held ONLN Jobs ")

 Dim sqlQ1 As String = "Select [JobID] FROM [cemail].[dbo].[JobTb] " +
               "WHERE ApplicationID = 7 AND Status = 10 ORDER BY [JobID]"

    sqlCommand1 = New SqlCommand(sqlQ1, connection)
    Dim result = sqlCommand1.ExecuteScalar()

   Dim sqlQ2 As String = "Select [JobID],[JobNumber],[ApplicationID],[GeneratedDate]," +
                "[ReceivedDate],[CompletedDate],[Status],[ExpectedRecordNumber]," +
                "[ReceivedRecordNumber],[BadRecordNumber] " +
                "FROM [cemail].[dbo].[JobTb] " +
                "WHERE ApplicationID = 7 AND Status = 10"
    sqlCommand2 = New SqlCommand(sqlQ2, connection)
    Dim reader As SqlDataReader = sqlCommand2.ExecuteReader()
    While reader.Read()
        Dim jobID = reader(0)
        Dim jobNumber = reader(1)
        Dim appID = reader(2)
    End While

    If (result > 0) Then

 SendEmail(My.Settings.emailuser1, "Current Held Jobs", "Hello," & jobID & "Kind Ragards" )
    Else

        SendEmail(My.Settings.emailuser1, "No Jobs on Hold", "There are no Jobs Currently on Hold" )


    End If


    connection.Close()

    log("Finished")
End Sub

Any help on this would be greatly appreciated. thanks in Advance.

Upvotes: 1

Views: 3701

Answers (2)

Steve
Steve

Reputation: 216342

You should not call on ExecuteNonQuery when you pass a Select statement.
As from the remarks on MSDN

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ......... For all other types of statements, the return value is -1.......

You could change the statement to simplify your query in this way

Dim sqlQ As String = "Select count(*) as recFound FROM [cemail].[dbo].[JobTb] " + 
                     "WHERE ApplicationID = 7 AND Status = 10"

sqlCommand = New SqlCommand(sqlQ, connection)
Dim result = sqlCommand.ExecuteScalar()

Also, in the code above there is no need to use a transaction.

EDIT Seeing your comment I will show also the part for reading back the values from the database using an SqlDataReader

Dim sqlQ As String = "Select [JobID],[JobNumber],[ApplicationID],[GeneratedDate]," +
                    "[ReceivedDate],[CompletedDate],[Status],[ExpectedRecordNumber]," + 
                    "[ReceivedRecordNumber],[BadRecordNumber] " + 
                    "FROM [cemail].[dbo].[JobTb] " + 
                    "WHERE ApplicationID = 7 AND Status = 10"

sqlCommand = New SqlCommand(sqlQ, connection)
Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
While reader.Read()
     Dim jobID = reader(0)
     Dim jobNumber = reader(1)
     Dim appID = reader(2)
     ..... 'and so on'
End While

Upvotes: 3

openshac
openshac

Reputation: 5165

I think the problem is that you are want to execute a query but are using ExecuteNonQuery(). Try using a SqlDataAdapter like this:

Public Function SelectSqlSrvRows(dataSet As DataSet, connection As String, query As String) As DataSet
    Dim conn As New SqlConnection(connection)
    Dim adapter As New SqlDataAdapter()
    adapter.SelectCommand = new SqlCommand(query, conn)
    adapter.Fill(dataset)
    Return dataset
End Function

Upvotes: 0

Related Questions