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

Question

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.

msdn.microsoft.com/en-us/library/…

Antonio
– Antonio

2013-01-23 11:37:14 +00:00
Commented Jan 23, 2013 at 11:37 — Antonio
– Antonio, Commented Jan 23, 2013 at 11:37

Steve · Accepted Answer · 2013-01-23 11:51:12Z

3

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

edited Jan 23, 2013 at 11:51

answered Jan 23, 2013 at 11:19

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Gerry85 Over a year ago

This is more on track, im getting the correct email now, but i would have expected the results to show ie, Job Number, Job ID. i get "6", its giving the number of rows affected.

Steve Over a year ago

I cannot see any code in your example that tries to read the field values, so I have excluded the SELECT <fields> parts in favor of a more simple way to check if record exists. Of course, if you need to read the fields then an update will be necessary.

Gerry85 Over a year ago

Im probably looking at this from the wrong angle then, i assumed that by using SELECT like in SQL Manager that this is what would have been returned.

Gerry85 Over a year ago

Iv used UPDATE in previous applications but that was to amend the DB, not Read results and display them in an Email which is what im having the issue with.

Gerry85 Over a year ago

Im a fool, Forgot to do, Dim jobID = reader(0) Dim jobNumber = reader(1) Dim appID = reader(2) s = s & jobID & ", " & jobNumber & ", " and then use S in the Email. All looks good now, many thanks for your help.

|

openshac · Accepted Answer · 2013-01-23 11:20:21Z

0

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

answered Jan 23, 2013 at 11:20

openshac

5,1936 gold badges48 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related