14 Replies - 2978 Views - Last Post: 16 March 2012 - 03:55 PM Rate Topic: -----

#1 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Quick question on sql

Posted 15 March 2012 - 07:06 PM

Where do I see how many rows I have selected from a select statement?

ty
Is This A Good Question/Topic? 0
  • +

Replies To: Quick question on sql

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Quick question on sql

Posted 15 March 2012 - 07:09 PM

depending on how you queried the results can make a difference.

For example if you populated a dataset you could use DataSet.Tables(?).Rows.Count but if you executed a reader then I think (off the top of my head) reader.Rows.Count would give you the results. I'm not at a machine I can double check with.

EDIT:
Actually, I'm not sure you can return the reader rows as it is a forward read only. If I'm remembering correctly, I think in order to know the total rows using the executereader method, you would have to increment a variable until the end of the reader is reached.

This post has been edited by CharlieMay: 15 March 2012 - 07:15 PM

Was This Post Helpful? 0
  • +
  • -

#3 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,511
  • Joined: 16-March 11

Re: Quick question on sql

Posted 15 March 2012 - 07:18 PM

Charlie that's how I do it, but if anyone knows a different way, that would be wonderful. I always believe the increment method to be a hassle lol.
Was This Post Helpful? 0
  • +
  • -

#4 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 15 March 2012 - 07:35 PM

Alright, ty.
Was This Post Helpful? 0
  • +
  • -

#5 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 15 March 2012 - 08:54 PM

Since it is another.. quick question on sql,


Why am I getting an error on line 7?*


as System.Data.SqlServerCe.SqlCeDataReader.GetString(Int32 ordinal)

Unable to cast object of type 'System.Int32' to type 'System.String'.

I am able to use GetString(0) and (1) ?

Thanks!


            cmd.CommandText = "SELECT PLAYER.FIRSTNAME, PLAYER.LASTNAME, PLAYER.ID FROM PLAYER WHERE PLAYER.SCHOOL = " & mySchool
            myReader = cmd.ExecuteReader()
            Dim i As Integer = 0
            Do While myReader.Read()
                results = myReader.GetString(0) & ", " & myReader.GetString(1)
                MyTeam.ComboBox1.Items.Add(results)
                myPlayers(i) = myReader.GetString(2)
                i += 1
            Loop

This post has been edited by meowbits: 15 March 2012 - 08:59 PM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: Quick question on sql

Posted 15 March 2012 - 09:13 PM

well.. what type is "myPlayers(i)"?
Was This Post Helpful? 0
  • +
  • -

#7 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 15 March 2012 - 09:17 PM

array type string, was originally type int.

Error both times.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: Quick question on sql

Posted 15 March 2012 - 09:22 PM

Learning time! Put a breakpoint on that line 7. Run the program. When it hits that break point drag both sides of that 'get' into your watch panel. Investigate their data types. Convert as needed.
Was This Post Helpful? 0
  • +
  • -

#9 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 15 March 2012 - 10:22 PM

Thanks, never done that before.
Turns out the GetString(2) was 'nothing'.

Though I am not sure why. Since this is a primary key.
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Quick question on sql

Posted 16 March 2012 - 02:58 AM

...Where player.school = " & mySchool
is school a number in your database?

If it's a school name (text) then you need apostrophes around that variable. So for example if myschool = "Harvard" your where clause would need to look like:
...Where player.school = 'Harvard'

as it stands currently you would have
...Where player.school = Harvard
But you should be receiving an error unless you have this getting buried in some try catch where you're not displaying the exception.
Was This Post Helpful? 0
  • +
  • -

#11 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 16 March 2012 - 12:55 PM

mySchool is just an integer.

All I am trying to do is select players that have the PLAYER.SCHOOL of x, which is an integer as well.
I'm sure I will figure it out eventually.
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Quick question on sql

Posted 16 March 2012 - 12:59 PM

You can do a MessagBox.Show(MyReader.HasRows) and see if it at least returns true that it had found information.
Was This Post Helpful? 0
  • +
  • -

#13 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 16 March 2012 - 02:01 PM

I put the messagebox in but then I don't get any errors at all.
From last night I debugged and saw that the data returned for that column was 'nothing'

Which does not make sense because I am not getting an error stating that the column does not exist, and it is a primary key so there has to be (and there is) information there.
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Quick question on sql

Posted 16 March 2012 - 02:33 PM

Have you tried, inside your loop using MessageBox.Show(MyReader("ID").ToString)
I find it easier to read and follow as you don't have to keep referencing the sql statement or remember the index of the column.

Also, you don't show your full cmd settings so make sure that you are using the proper connection. I've had times where a connection string was pointing to the wrong version of a database and you don't know it until you start tracing back.

This post has been edited by CharlieMay: 16 March 2012 - 02:35 PM

Was This Post Helpful? 1
  • +
  • -

#15 meowbits  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 31-January 12

Re: Quick question on sql

Posted 16 March 2012 - 03:55 PM

After putting the messagebox in like you mentioned, it reports the correct ID
I switched
myPlayers(i) = myReader.GetString(2)

to
myPlayers(i) = myReader("ID").ToString

And it appears to be working.
I think I will use this as long as there are no unforeseen consequences.
Thanks a bunch!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1