By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

Displaying data from a sql query in a label

100+
P: 347
Hello,

I have the following code:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
  2.         Dim connetionString As String
  3.         Dim cnn As SqlConnection
  4.         connetionString = "Data Source=localhost;Initial Catalog=Payroll;User ID=administrator;Passwordxxxxx"
  5.         cnn = New SqlConnection(connetionString)
  6.         cnn.Open()
  7.         Dim strSQL As String = "select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())"
  8.         Label1.Text = strSQL
  9.         cnn.Close()
  10.     End Sub
  11.  
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
Jul 28 '10 #1
Share this Question
Share on Google+
10 Replies


Joseph Martell
Expert 100+
P: 196
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.
Jul 28 '10 #2

100+
P: 347
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
Jul 28 '10 #3

100+
P: 347
JBM,

ok I've modified my code a bit:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  2.         Dim connetionString As String
  3.         Dim cnn As SqlConnection
  4.         Dim myCommand As SqlCommand
  5.         'the connection string to the SQL server'
  6.         connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
  7.         cnn = New SqlConnection(connetionString)
  8.         cnn.Open()
  9.         'the SQL query'
  10.         myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())")
  11.         cnn.Close()
  12.         Dim ButtonDialogResult As DialogResult
  13.         ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
  14.         If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
  15.             Button2.Enabled = True
  16.             Button1.Enabled = False
  17.         End If
  18.     End Sub
  19.  
Jul 28 '10 #4

Joseph Martell
Expert 100+
P: 196
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.
Jul 28 '10 #5

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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 28 '10 #6

100+
P: 347
JBM,

Here's what I wrote for my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  2.         Dim connetionString As String
  3.         Dim cnn As SqlConnection
  4.         Dim myCommand As SqlCommand
  5.         Dim sqlpost As SqlCommand
  6.         'the connection string to the SQL server'
  7.         connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
  8.         cnn = New SqlConnection(connetionString)
  9.         cnn.Open()
  10.         'the SQL query'
  11.         Dim myReader As SqlClient.SqlDataReader = sqlpost.ExecuteReader()
  12.         myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())")
  13.         cnn.Close()
  14.         Dim ButtonDialogResult As DialogResult
  15.         ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
  16.         If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
  17.             Button2.Enabled = True
  18.             Button1.Enabled = False
  19.         End If
  20.     End Sub
  21.  
  22.  
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
Jul 29 '10 #7

Joseph Martell
Expert 100+
P: 196
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.
Jul 29 '10 #8

100+
P: 347
jbm,

I gotcha on the new command heres my code now:

Expand|Select|Wrap|Line Numbers
  1. Public Class Form
  2.     Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  3.         Dim connetionString As String
  4.         Dim cnn As SqlConnection
  5.         Dim myCommand As SqlCommand
  6.         Dim sqlpost As SqlCommand
  7.         'the connection string to the SQL server'
  8.         connetionString = "Data Source=localhost;Initial Catalog=Payroll;Integrated Security=True"
  9.         cnn = New SqlConnection(connetionString)
  10.         cnn.Open()
  11.         'the SQL query'
  12.         sqlpost = New SqlCommand
  13.         Dim myReader As SqlClient.SqlDataReader = sqlpost.ExecuteReader()
  14.         myCommand = New SqlCommand("select payrolldate from payroll where payrolldate <= getdate (), select dateadd (day, 7, Getdate())")
  15.         cnn.Close()
  16.         Dim ButtonDialogResult As DialogResult
  17.         ButtonDialogResult = MessageBox.Show("The next date available to you is", "Payroll", MessageBoxButtons.YesNo)
  18.         If ButtonDialogResult = Windows.Forms.DialogResult.Yes Then
  19.             Button2.Enabled = True
  20.             Button1.Enabled = False
  21.         End If
  22.     End Sub
  23. End Class
  24.  
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?
Jul 29 '10 #9

100+
P: 347
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.WriteLine
I'd have
Messagebox.Writeline?

Thanks

Doug
Jul 29 '10 #10

Joseph Martell
Expert 100+
P: 196
You would still use your MessageBox.Show() method. You would just have to concatenate the value into your message like:

Expand|Select|Wrap|Line Numbers
  1. ButtonDialogResult = MessageBox.Show("The next date available to you is" & myReader(0), "Payroll", MessageBoxButtons.YesNo)
The myReader(0) gets you the value pulled back in your SQL as a string.
Jul 29 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.