11 Replies - 889 Views - Last Post: 17 April 2012 - 03:54 AM Rate Topic: -----

#1 aetheon  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 07-April 12

SQL Query question question with labels.

Posted 16 April 2012 - 10:24 AM

Good afternoon,

I'm writing a program where I need to have the label that I'm working query a row in table that I have in a DB.
The problem is that I don't want to put it around a button or a click. I may not be saying that right.

Here's the code I have so far:
Dim cmd As New SqlCommand
        Dim conn As New SqlClient.SqlConnection
        Dim sqlcmd As New SqlClient.SqlCommand
        Dim ad As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet

        conn.ConnectionString = ("Data Source=localhost;Initial Catalog=holman;User ID=sa;Password=1q2w3e4r5t")

        sqlcmd.CommandText = "SELECT TOP 1 * FROM logininfo ORDER BY ID DESC"

        conn.Open()
        sqlcmd.Connection = conn

        ad.SelectCommand = sqlcmd
        ad.Fill(ds, "0")
        sqlcmd.ExecuteNonQuery()

        Label4.Text = sqlcmd.ExecuteNonQuery


When I look at the form a check the label or when I click on the label until I get the update feature worked out where it automatically updates to when I do have to click over the label or use a button to update what's there.

I was reading some earlier postings and thought I had it right. The return value that I am getting from the form is a -1.

Any help you can provide with this would be greatly appreciated.

Thanks,

Aetheon

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Query question question with labels.

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL Query question question with labels.

Posted 16 April 2012 - 01:11 PM

sqlcmd.ExecuteNonQuery() is used for executing INSERT, DELETE ,UPDATE statements, not SELECT. In your case, you should use sqlcmd.ExecuteQuery(). I made a big mistake here.. correct is sqlcmd.ExecuteReader() Also,
Label4.Text = sqlcmd.ExecuteNonQuery


i don't think this statement is correct. What do you want to display in that label?

This post has been edited by Ionut: 16 April 2012 - 03:31 PM

Was This Post Helpful? 0
  • +
  • -

#3 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: SQL Query question question with labels.

Posted 16 April 2012 - 02:18 PM

Why are you calling ExecuteNonQuery twice? Apart from being wrong, this is a waste of code space.
Was This Post Helpful? 0
  • +
  • -

#4 aetheon  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 07-April 12

Re: SQL Query question question with labels.

Posted 16 April 2012 - 04:27 PM

View PostIonut, on 16 April 2012 - 01:11 PM, said:

sqlcmd.ExecuteNonQuery() is used for executing INSERT, DELETE ,UPDATE statements, not SELECT. In your case, you should use sqlcmd.ExecuteQuery(). I made a big mistake here.. correct is sqlcmd.ExecuteReader() Also,
Label4.Text = sqlcmd.ExecuteNonQuery


i don't think this statement is correct. What do you want to display in that label?

I want the result of the query to post into the label instead of a textbox. That was the goal. If that's not possible because it's a label then I will rewrite to use a textbox.

View PostDimitriV, on 16 April 2012 - 02:18 PM, said:

Why are you calling ExecuteNonQuery twice? Apart from being wrong, this is a waste of code space.

Apart from being wrong is acceptable.
I thought the ExecuteNonQuery had to be there, then have the results of that come into the label the way I thought to have written it.

Thanks for the update.
Was This Post Helpful? 0
  • +
  • -

#5 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: SQL Query question question with labels.

Posted 16 April 2012 - 04:30 PM

View Postaetheon, on 17 April 2012 - 10:27 AM, said:

I want the result of the query to post into the label instead of a textbox. That was the goal. If that's not possible because it's a label then I will rewrite to use a textbox.

If it works with a TextBox it will work with a label. Their Text properties are both of type String.
Was This Post Helpful? 0
  • +
  • -

#6 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL Query question question with labels.

Posted 16 April 2012 - 04:32 PM

Well, you have the following statement
"SELECT TOP 1 * FROM logininfo ORDER BY ID DESC" 


This mean you have to put in a label more than one column. For this kind of stuff, it is better fit a datagridview or a listview.

Returning to your problem, use ExecuteReader(in this link you have an example that shows how to access each column of a result set).
Was This Post Helpful? 0
  • +
  • -

#7 aetheon  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 07-April 12

Re: SQL Query question question with labels.

Posted 16 April 2012 - 04:33 PM

View PostDimitriV, on 16 April 2012 - 04:30 PM, said:

View Postaetheon, on 17 April 2012 - 10:27 AM, said:

I want the result of the query to post into the label instead of a textbox. That was the goal. If that's not possible because it's a label then I will rewrite to use a textbox.

If it works with a TextBox it will work with a label. Their Text properties are both of type String.

Thanks for the update. The problem is when I write Textbox4.Text or Label4. Text = sqlcmd.ExecuteReader it's telling me that its not allowed to convert that to a string. So, I'm figuring out to write that last portion and I'll be good to go.

Again, thanks for the assist.
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: SQL Query question question with labels.

Posted 16 April 2012 - 04:34 PM

The key is as Ionut pointed out. You use executereader for a Query. Then you use {yourreader}.Read to read a line at which point you can assign it to a labels .text property
dim rdr as SQLClient.Sqlreader = cmd.executereader

yourlabel.text = rdr.read("Your column name").ToString

Was This Post Helpful? 0
  • +
  • -

#9 aetheon  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 07-April 12

Re: SQL Query question question with labels.

Posted 16 April 2012 - 07:14 PM

View PostCharlieMay, on 16 April 2012 - 04:34 PM, said:

The key is as Ionut pointed out. You use executereader for a Query. Then you use {yourreader}.Read to read a line at which point you can assign it to a labels .text property
dim rdr as SQLClient.Sqlreader = cmd.executereader

yourlabel.text = rdr.read("Your column name").ToString


Awesome information. Thank you.

When I implemented the reader portion in this is what I get:

"There is already an open DataReader associated with this Command which must be closed first."
Here are the code changes:
Dim con As New SqlConnection
        Dim cmd1 As New SqlCommand
        con.ConnectionString = "Data Source=localhost;Initial Catalog='';Persist Security Info=True;User ID=''Password=''"
        con.Open()
        cmd1.Connection = con
        cmd1.CommandText = "select top 1 * from logininfo ORDER BY ID DESC"
        cmd1.ExecuteReader()
        Dim rdr As SqlDataReader = cmd1.ExecuteReader
        Label4.Text = rdr.Read.ToString

I'm not sure if I am writing this with .NET 3.5 if that has anything to do with why this isn't working.

View Postaetheon, on 16 April 2012 - 07:11 PM, said:

View PostCharlieMay, on 16 April 2012 - 04:34 PM, said:

The key is as Ionut pointed out. You use executereader for a Query. Then you use {yourreader}.Read to read a line at which point you can assign it to a labels .text property
dim rdr as SQLClient.Sqlreader = cmd.executereader

yourlabel.text = rdr.read("Your column name").ToString


Awesome information. Thank you.

When I implemented the reader portion in this is what I get:

"There is already an open DataReader associated with this Command which must be closed first."
Here are the code changes:
Dim con As New SqlConnection
        Dim cmd1 As New SqlCommand
        con.ConnectionString = "Data Source=localhost;Initial Catalog='';Persist Security Info=True;User ID=''Password=''"
        con.Open()
        cmd1.Connection = con
        cmd1.CommandText = "select top 1 * from logininfo ORDER BY ID DESC"
        cmd1.ExecuteReader()
        Dim rdr As SqlDataReader = cmd1.ExecuteReader
        Label4.Text = rdr.Read.ToString

I'm not sure if I am writing this with .NET 3.5 if that has anything to do with why this isn't working.

Also, when I implement this:
dim rdr as SQLClient.Sqlreader = cmd.executereader
2	 
3	yourlabel.text = rdr.read("Your column name").ToString

I get an error with the column name after rdr.read.("Column Name").ToString
Overload resolution failed because acccess 'Read' accepts this number of arguements.
Was This Post Helpful? 0
  • +
  • -

#10 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: SQL Query question question with labels.

Posted 16 April 2012 - 07:17 PM

Shouldn't you be closing the datareaders after you use them? Or dispose them? Or use a Using block?
Using rdr As SqlDataReader = cmd1.ExecuteReader
Label4.Text = rdr.Read.ToString
End Using


This takes care of disposing the whole thing for you.

About your last issue: have a look here. http://msdn.microsof...er.read.aspx#Y0
Was This Post Helpful? 0
  • +
  • -

#11 aetheon  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 07-April 12

Re: SQL Query question question with labels.

Posted 16 April 2012 - 10:48 PM

View PostDimitriV, on 16 April 2012 - 07:17 PM, said:

Shouldn't you be closing the datareaders after you use them? Or dispose them? Or use a Using block?
Using rdr As SqlDataReader = cmd1.ExecuteReader
Label4.Text = rdr.Read.ToString
End Using


This takes care of disposing the whole thing for you.

About your last issue: have a look here. http://msdn.microsof...er.read.aspx#Y0


DimtriV,

Thanks for all the pointers. I did read the posting.
I have re-written the code again and it didn't error out this time in the code.
 Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label4.Click
        Dim queryString As String = "SELECT TOP 1 * FROM logininfo ORDER BY ID DESC;"
        Using Connection As New SqlConnection("Data Source='';Initial Catalog='';User ID='';Password=''")
            Dim command As New SqlCommand(queryString, Connection)
            Connection.Open()

            Dim reader As SqlDataReader = command.ExecuteReader()

            While reader.Read()
                Label4.Text = reader.Read.ToString
            End While
            reader.Close()
        End Using
    End Sub


I just have to work with the value portion of it now and getting to change from reporting false to what's in the column on the table. Appreciate the pointers.

Aetheon
Was This Post Helpful? 1
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: SQL Query question question with labels.

Posted 17 April 2012 - 03:54 AM

Label4.Text = reader.Read.ToString

This line will take care of that. You just need to specify either the column index of the row
Label4.Text = reader.Read(0).ToString 'Which would be the first column
or
Label4.Text = reader.Read("A Column Name Goes Here").ToString

I prefer the second method as you can read the code and follow what is happening with an understanding of your field names in the database.

This post has been edited by CharlieMay: 17 April 2012 - 03:55 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1