Showing a SQL result

[dougancil]

I have the following code in my project:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxx;password=xxxxxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())")
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
        If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
            Button2.Enabled = True
            Button1.Enabled = False
        End If
    End Sub
End Class

And what I'm needing to do is to show in my messagebox.show the date from the sql query that I ran before this. I know that I havent declared a variable in that statement but I'm having a bit of a lapse today and was just wondering how to show that result in my messagebox.show.

Thanks

Doug

 

[demausdauth]

Dim oReturnValue As object = myCommand.ExecuteScalar()

If oReturnValue IsNot Nothing Then
     Dim dteReturnValue As DateTime = Convert.ToDateTime(oReturnValue)
   
     'plus all the code to show the payroll date.
End If

 

[dougancil]

Demaus,

Here's the code as it's now written:

Imports System.Data.SqlClient

Public Class Form1
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
        Dim ReturnValue As Object = myCommand.ExecuteScalar()
        If ReturnValue IsNot Nothing Then
            Dim dteReturnValue As DateTime = Convert.ToDateTime(ReturnValue)
        End If
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
        If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
            Button2.Enabled = True
            Button1.Enabled = False
        End If
    End Sub
    
End Class

and when I debug it, the error I receive back is:

ExecuteScalar: Connection property has not been initialized.

Any advice on what I'm missing here?

Thanks

Doug

 

[demausdauth]

You haven't told the Command object how it is suppose to connect to the database, even though you have a connection object.
Personally I prefer the Using statement when doing database work.

In your specific code....

        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
 
        'tells the command object how to 'connect' to the database
        myCommand.Connection = cnn


        Dim ReturnValue As Object = myCommand.ExecuteScalar()

 

[dougancil]

Showing a SQL Result

So I went back and edited my code and now have the following

Public Class Form1
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
            myCommand.Connection = cnn
            Dim ReturnValue As Object = myCommand.ExecuteScalar
            If ReturnValue IsNot Nothing Then
                Dim dteReturnValue As DateTime = Convert.ToDateTime(ReturnValue)
            End If
            cnn.Close()
        Catch exp As SqlException
            Dim ButtonDialogResult As DialogResult
            ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
            If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
                Button2.Enabled = True
                Button1.Enabled = False
            End If
        End Try
    End Sub

While this code does not produce any errors, I'm still not getting the date back in my messagebox.show.

I know I'm missing something simple here but I don't know what. To recap, what should be happening here is that when a user clicks on the "Start Payroll" button, it checks the last time the payroll was ran (via the SQL query) and presents the next day to them on the messagebox.show. Just to clarify.

Thanks

Doug

 

[roccoman]

when you step through and debug, what value is ReturnValue getting here:

Dim ReturnValue As Object = myCommand.ExecuteScalar()

my 1st thought is the ensure you are getting a legit value from the ExecuteScalar() command

 

[dougancil]

I put a line break after that line of code and I'm not seeing any value returning after that.

here's my code again

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
            myCommand.Connection = cnn
            Dim ReturnValue As Object = myCommand.ExecuteScalar
            If ReturnValue IsNot Nothing Then
                Dim dteReturnValue As DateTime = Convert.ToDateTime(ReturnValue)
            End If
            cnn.Close()
        Catch exp As SqlException
            Dim ButtonDialogResult As DialogResult
            ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
            If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
                Button2.Enabled = True
                Button1.Enabled = False
            End If
        End Try
    End Sub

 

[roccoman]

one thing: you should move your cnn.Close() outside of the TRY part of the TRY/CATCH. if an error is thrown before it reaches that code then your connection is never closed.

i'd suggest moving it to after the TRY/CATCH block.


so are you saying that after

Dim ReturnValue As Object = myCommand.ExecuteScalar

ReturnValue is nothing?

 

[dougancil]

My breakpoint is at this line

 If ReturnValue IsNot Nothing Then

after the

 Dim ReturnValue As Object = myCommand.ExecuteScalar

and I'm looking at the output window and I see nothing being returned. It's been a while since I've done debugging so maybe I'm not doing it correctly, but I thought that I was.

 

[roccoman]

can you mouse over the "ReturnValue" variable after the line "Dim ReturnValue As Object = myCommand.ExecuteScalar" executes to see exactly what value (if any) exists there?

or add a watch on that variable and see its value

 

[dougancil]

I'm seeing nothing in my "watch" it shows "Name" "Value" and "Type" but they are all empty.

 

[roccoman]

ok good.

your msgbox is inside the CATCH block - since no exception is being thrown (returning NOTHING from a ExecuteScalar() is a legit return) then your msgbox code will never display.

i am curious - have you tried stepping thru the code? you keep refering to output windows and whatnot, and the easiest way to see what is happening is the execute each line and follow the flow of logic.

i would suggest a few things:

1) dim ReturnValue & dteReturnValue before your TRY/CATCH block. those variables are only scoped within that block and cannot be referenced outside of that.
2) move the msgbox section to after the TRY/CATCH.
3) remember that F8 (step thru line by line) is your friend!

 

[dougancil]

I've moved the msgbox outside of the catch block:

Public Class Form1
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
            myCommand.Connection = cnn
            Dim ReturnValue As Object = myCommand.ExecuteScalar
            If ReturnValue IsNot Nothing Then
                Dim dteReturnValue As DateTime = Convert.ToDateTime(ReturnValue)
            End If
        Catch exp As SqlException
        End Try
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
        If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
            Button2.Enabled = True
            Button1.Enabled = False
        End If
    End Sub

But I'm confused about moving the ReturnValue and dteReturnValue before the try/catch block. Could you give me an example of that?

Thanks

 

[roccoman]

Dim connectionString As String
Dim cnn As SqlConnection
Dim myCommand As SqlCommand
[B]Dim ReturnValue As Object = nothing
Dim dteReturnValue As DateTime = nothing[/B]

this way you can access those variable anywhere inside the button_click event.

also you never put the date retrieved from the database into the messagebox - maybe try:

ButtonDialogResult = MessageBox.Show(string.format("The next date available to you is {0}",dteReturnValue.ToShortDateString()), "Payroll", MessageBoxButtons.YesNo)

 

[dougancil]

So here's my modified code:

Public Class Form1
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        Dim ReturnValue As Object = Nothing
        Dim dteReturnValue As DateTime = Nothing
        'the connection string to the SQL server'
        connectionString = "Data Source=10.2.1.41;Initial Catalog=MDR;uid=xxxxx;password=xxxxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")
            myCommand.Connection = cnn
            myCommand.ExecuteScalar()
            If ReturnValue IsNot Nothing Then
                Convert.ToDateTime(ReturnValue)
            End If
        Catch exp As SqlException
        End Try
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show("The next date available to you is" {0}",dteReturnValue.ToShortDateString()), "Payroll", MessageBoxButtons.YesNo)
        If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
            Button2.Enabled = True
            Button1.Enabled = False
        End If
    End Sub

and I'm getting a build error on

("The next date available to you is {0}

and being told that "Comma, ')', or a valid expression continuation expected."