Displaying data from a sql query in a label

[dougancil]

Hello,

I have the following code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim connetionString As String
        Dim cnn As SqlConnection
        connetionString = "Data Source=localhost;Initial Catalog=Payroll;User ID=administrator;Passwordxxxxx"
        cnn = New SqlConnection(connetionString)
        cnn.Open()
        Dim strSQL As String = "select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())"
        Label1.Text = strSQL
        cnn.Close()
    End Sub

And I'm trying to display my result from my sql query in the label that I have in my application, but it doesnt seem to work. I get no build errors and the SQL query works on its own. Can anyone assist me with this issue?

Thank you

Doug

[Joseph Martell]

Where are you actually running the query? You are creating your SQL statement, opening a connection and closing it, but never actually executing the query.

[dougancil]

JBM,

I've missed that. I'm still kind of new to using VB.net. Can you give me an example of how to run the query?

Thank you

Doug

[dougancil]

JBM,

ok I've modified my code a bit:

 Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        'the connection string to the SQL server'
        connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
        cnn = New SqlConnection(connetionString)
        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

[Joseph Martell]

You've got a SQLConnection object, which handles your communication to a database, but you still need a SqlCommand object that actually handles running your query. Check out this documentation for a basic rundown of SqlCommand objects and an example of how to use one.

[Joseph Martell]

Sorry, I didn't see your update yet. The part your missing is the call to the ExecuteReader() method. It would look like this:

Dim myReader As SqlClient.SqlDataReader = myCommand.ExecuteReader()

Then you can cycle through your return set using the myReader object. The documentation I linked in my previous post shows an exmaple.

[dougancil]

JBM,

Here's what I wrote for my code:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        Dim sqlpost As SqlCommand
        'the connection string to the SQL server'
        connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
        cnn = New SqlConnection(connetionString)
        cnn.Open()
        'the SQL query'
        Dim myReader As SqlClient.SqlDataReader = sqlpost.ExecuteReader()
        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

But I know that I havent written it right. It's telling me that the variable sqlpost has been used before and may result in a null value.

What I discovered yesterday is that my application will have to have database access for more than one button so I may have to have this as a private function seperate from the sql command, but if I could get this figured out, I can go back and re write my code to suit that. Sorry for sounding like a noob on this but I'm trying to learn this.

Thanks

Doug

[Joseph Martell]

You are getting close on this.

In your code, you have not created a SqlCommand object for sqlpost to point to. In other words, you haven't ever used the "New" keyword with sqlpost. That is why you are getting the error mentioned.

In order for any SqlCommand object to work it needs 2 things, minimum: a connection (to know which DB to hit) and command text (your SQL to execute). On line 12 you create a SqlCommand object and give it the command text, but no connection. One of the other SqlCommand constructors allows you to pass in a connection in addition to the command text. I would suggest using that version and pass in your cnn object.

After the SqlCommand object has been created, THEN you can use the ExecuteReader() method on it. Line 11 needs to be AFTER line 12 and instead of using sqlpost, use myCommand. sqlpost is not necessary in this code.

Once you have used the ExecuteReader() method, all you have done is executed the query and stored the results in myReader. You still need to go through and access those results, pull out your desired value, and use it somehow. For that portion, I would direct you to the MSDN site: here. The example at the bottom shows how to access some of the information returned in a SqlDataReader.

[dougancil]

jbm,

I gotcha on the new command heres my code now:

Public Class Form
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim cnn As SqlConnection
        Dim myCommand As SqlCommand
        Dim sqlpost As SqlCommand
        'the connection string to the SQL server'
        connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
        cnn = New SqlConnection(connetionString)
        cnn.Open()
        'the SQL query'
        sqlpost = New SqlCommand
        Dim myReader As SqlClient.SqlDataReader = sqlpost.ExecuteReader()
        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

I'll read the post and ask questions afterwards if I have any.

Also, since I'm reading this data in a datareader, once I read the data I can store it in a variable and pass it to my next sql string right?

[dougancil]

JBM,

I read through the page and see that it is indeed putting the output to a console .. how would I output that to a messagebox? Instead of
Console.WriteLi ne
I'd have
Messagebox.Writ eline?

Thanks

Doug

[Joseph Martell]

You would still use your MessageBox.Show () method. You would just have to concatenate the value into your message like:

ButtonDialogResult = MessageBox.Show("The next date available to you is" & [B][U]myReader(0)[/U][/B], "Payroll", MessageBoxButtons.YesNo)

The myReader(0) gets you the value pulled back in your SQL as a string.