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

 

[roccoman]

change line:

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

to:

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

just copy & paste it from here

you are missing the String.Format() part.

 

[dougancil]

Ok so now it's returning a value of 1/1/0001 but it should be 7/4/2010 because the SQL query should be checking for the last "payroll date" and then adding a day to it.

 

[roccoman]

you're never setting the variable "dteReturnValue" value.

change

            If ReturnValue IsNot Nothing Then
                Convert.ToDateTime(ReturnValue)
            End If

to

            If ReturnValue IsNot Nothing Then
                dteReturnValue=Convert.ToDateTime(ReturnValue)
            End If

always make sure you have values where you expect them by stepping through line by line or at least where those variables are accessed

 

[dougancil]

This may be a sql issue because if I run the two queries as they are the query fails for me but if they are run as two seperate queries, it returns a value back. My question then is how to make this

"select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 1, Getdate())")

this

select payrolldate from payroll where payrolldate <= getdate ()
select dateadd (day, 1, Getdate())

 

[roccoman]

can you get the last date value from the database using "select payrolldate from payroll where payrolldate <= getdate ()" ?

if so you can move it into datetime variable and use the AddDays(1) to add 1 day to it.

            If ReturnValue IsNot Nothing Then
                dteReturnValue = Convert.ToDateTime(ReturnValue)
                dteReturnValue = dteReturnValue.AddDays(1)
            End If

 

[dougancil]

I am able to get the last date with that query and then I altered my code to reflect what you had written so it now looks like this:

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=xxxxxx;password=xxxxxx"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        'the SQL query'
        Try
            myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate ())")
            myCommand.Connection = cnn
            myCommand.ExecuteScalar()
            If ReturnValue IsNot Nothing Then
                dteReturnValue = Convert.ToDateTime(ReturnValue)
                dteReturnValue = dteReturnValue.AddDays(1)
            End If
        Catch exp As SqlException
        End Try
        cnn.Close()
        Dim ButtonDialogResult As DialogResult
        ButtonDialogResult = MessageBox.Show(String.Format("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 still get the value of 1/1/0001 as the return value.

 

[roccoman]

look at your "ExecuteScalar()" and tell me if anything looks funny... i'll give you a hint: i dont see where the variable "ReturnValue" is receiving a value.


c'mon thats easy to diagnose!

 

[dougancil]

then I guess I've spent too many hours staring at this today. I know that I'm missing a value declaration.

 

[roccoman]

replace:

myCommand.ExecuteScalar()

with

ReturnValue = myCommand.ExecuteScalar()

so long as your SQL command is returning a value from the database you should be good to go

 

[dougancil]

Roccoman,

thank you. That does work. If I wanted to insert that variable into a as yet unwritten SQL query that will be part of my next button. I should just have to call ReturnValue correct?

 

[roccoman]

you cant access a variable outside of the scope you declare it in - since you declare ReturnValue inside the Button1_Click event you cant access it outside of that event.

you would need to move the declarations to a higher level

        Dim ReturnValue As Object = Nothing

i assume this is on a Form so you could move this up to the form level

public class Form1
    [B][COLOR="red"]Dim ReturnValue As Object = Nothing[/COLOR][/B]

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ''  blah blah blah
    end sub

end class

so in summary: CUT "Dim ReturnValue As Object = Nothing" from insde the button click event and place it just below the "public class form1" line



if i've helped you please click the little star at the bottom left to increase my reputation!

 

[dougancil]

Roccoman,

Thanks I moved that declaration higher. Again thanks for your help and I'm sure that I'll be back once I have more of these pesky sql queries built.