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
10 22572
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.
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
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
-
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.
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.
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
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.
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?
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
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" & myReader(0), "Payroll", MessageBoxButtons.YesNo)
The myReader(0) gets you the value pulled back in your SQL as a string.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: 'bonehead |
last post by:
Greetings,
I'd like to figure out some syntax for retrieving the data from a table
when I don't know all the of field names. What I do know are, the name
of the table, the names of the primary...
|
by: vinayak |
last post by:
Hi
I am displaying data in Datagrid in ASP.NET with
Edit/Update functionality for each row. On the same page I
have 2 Button controls which submits the request to
server. These button controls...
|
by: Brad Baker |
last post by:
I'm completely new to ASP.NET programming, so please accept my apologies in
advance for asking what is probably an obvious question. :-)
I'm trying to write a page which will display the...
|
by: slayerx |
last post by:
Quote:
Originally Posted by comteck
As far as I know, a report is just that.. a means of displaying data. I don't believe it is possible to make a selection in a report. You can however, make a...
|
by: seadog |
last post by:
Hi, I am having a problem displaying data in a textbox array.
What I am trying to do is have 5 number enter 5 textboxes, starting from the first and working there way to the bottom textbox.
What...
|
by: Lloyd Harold |
last post by:
I'm very new to PHP and attempting to put together a simple script for
retrieving MySQL data of personal records.
The MySQL table I'm using consists of:
0: id
1: name
2: location (an integer...
|
by: jaredciagar |
last post by:
Hi Guys Can you help me please in my project, I have tblItemList in my SQL DB. I want to display all the data in my tblItemList in GridView control....
How Can I Do That in VB Script using VB.Net......
|
by: dba |
last post by:
Have been displaying data from database using html for some time but
just recently trying to display data back to "form". Can't find answer.
<form method="post" action="<?php echo $PHP_SELF;?>">...
|
by: Sheena777 |
last post by:
I have a Label that i am cntinuously adding data to i would like to add the Data to a new line with in the label is there any way of doing this with c# and on an asp.net page?
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |