9 Replies - 2453 Views - Last Post: 24 August 2009 - 08:57 AM Rate Topic: -----

#1 Guest_NIXZ*


Reputation:

MySQL Command Problem. (For Mailbox)

Post icon  Posted 24 August 2009 - 02:44 AM

Posted Image
Thanks to AdamSpeight2008 i was able to understand how to create a mailbox
but it seems like im stock cause i dont know the correct syntax.

Thats the table inside my database that handles the
messages function. Im Logged in as NIXZ.

What will this command return i mean it contains multiple variables:
SELECT * FROM MainTable WHERE Deleted='False' AND Receiver='NIXZ'

1]Join...
2]Hi!,...
3]Hello,...

How will/can i seperate them?

This post has been edited by NIXZ: 24 August 2009 - 02:57 AM


Is This A Good Question/Topic? 0

Replies To: MySQL Command Problem. (For Mailbox)

#2 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 03:24 AM

Hi NIXZ,

Your query does what it says

Give me all the records from the MainTable where the value of Column Deleted = true and the value of Column Reciever = "NIXZ"

What records do you want to see exactly?

Maybe check the other columns and narrow down your result.
Was This Post Helpful? 0
  • +
  • -

#3 Guest_NIXZ*


Reputation:

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 06:15 AM

Hey motcom,

Database:
ID |BeenRead | Sender | Receiver | Message | Deleted
1 | False | NIXZ | Admin | Hi! | False
2 | False |Motcom| Admin | Hello! | False

Dim MySqlConnection As New MySqlConnection
MySqlConnection.ConnectionString = MySqlConnectionString 'cant say
MySqlConnection.Open()

Dim MySqlCommand1 As New MySqlCommand
MySqlCommand1.Connection = MySqlConnection
MySqlCommand1.CommandText = "SELECT * FROM MessagesTable WHERE Receiver='Admin' AND Deleted='False'"

Dim MySqlReader1 As MySqlDataReader = MySqlCommand1.ExecuteReader()
MySqlReader1.Read()
Dim Messages = MySqlReader1.GetString("Message")
MySqlReader1.Close()

Msgbox(Messages)


The code above returns:
Messages = "Hi!"

:w00t:

Here's the actuall problem:
As you can see theres two messages where the receiver is Admin and deleted=false:
1st is "Hi!" and the 2nd is "Hello!" but it only returns the 1st one...

This post has been edited by NIXZ: 24 August 2009 - 06:32 AM

Was This Post Helpful? 0

#4 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 06:35 AM

Jip,

Its ok to have more than one message, the question is what do you want to do with them (how do you wish to display them) and who's messages do you wish to display etc.

if for example

You only want to see the messages for

1. NIXZ or
2. motcom (not both)

You would need to change your querry to

"SELECT * FROM MessagesTable WHERE Receiver='Admin' AND Deleted='False' AND Sender = 'NIXZ'"



Just a note in your code
MySqlReader1.Read()



it only reads the first line (row)

try this to read all the lines returned

Dim MySqlConnection As New MySqlConnection
MySqlConnection.ConnectionString = MySqlConnectionString
MySqlConnection.Open()

Dim MySqlCommand1 As New MySqlCommand
MySqlCommand1.Connection = MySqlConnection
MySqlCommand1.CommandText = "SELECT * FROM MessagesTable WHERE Receiver='Admin' AND Deleted='False'"

Dim MySqlReader1 As MySqlDataReader = MySqlCommand1.ExecuteReader()
Dim Messages AS String

do while MySqlReader1.Read()
   Messages = MySqlReader1("Message")
   Msgbox(Messages)
loop
MySqlReader1.Close()
MySqlConnection.Close
MySqlConnection.Dispose



What do you want to do with the messages?

a. Do you want them to be in a list?
b. Do you only want to show the most recent message?
c. Do something else with them.
Was This Post Helpful? 1
  • +
  • -

#5 Guest_NIXZ*


Reputation:

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 06:47 AM

Yeah! thanks motcom now i have all the required knowledge to complete my program thank you all guys. :D
well im not sure if another problem will pop up in the future. wish me nice coding lol

This post has been edited by NIXZ: 24 August 2009 - 06:48 AM

Was This Post Helpful? 0

#6 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 06:53 AM

Just remember to close and dispose of your sql connection if not needed.
Was This Post Helpful? 0
  • +
  • -

#7 Guest_NIXZ*


Reputation:

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 07:49 AM

Another problem appears :P (ok that was quick)
im using this code to count the messages that are unread (for the inbox) but the integer always returns as InboxCount = -1, why?

MySqlCommand.CommandText = "SELECT COUNT(*) FROM MessagesTable WHERE BeenRead='False' AND Receiver='" & Username & "' AND Deleted='False'"
Dim InboxCount As Integer = MySqlCommand.ExecuteNonQuery()
Label1.Text = "Welcome back " & Username & ", you have " & InboxCount & " new message(s)."

This post has been edited by NIXZ: 24 August 2009 - 07:50 AM

Was This Post Helpful? 0

#8 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 08:05 AM

Hi Nixz,

the MySqlCommand.ExecuteNonQuery() returns the number of rows affected.

Just see what is says ".ExecuteNonQuery()". But you are using a Select Statement which is a query.

You might want to use ".ExecuteNonQuery()". For Updates and Delete and Insert Statements, then it will return how many records are Updated, Deleted or Inserted.

Use the sqlDataReader to get the count. I also prefer using a column that i want to count and not *
like. You see also i use "AS InboxCount". If you use count alone, it does not give you a colum name but
if you use "AS" it will return "InboxCount" as the column name.

COUNT(ID) AS InboxCount



Try this...

Dim MySqlConnection As New MySqlConnection
MySqlConnection.ConnectionString = MySqlConnectionString
MySqlConnection.Open()

Dim MySqlCommand1 As New MySqlCommand
MySqlCommand1.Connection = MySqlConnection
MySqlCommand1.CommandText = "SELECT COUNT(ID) AS InboxCount FROM MessagesTable WHERE BeenRead='False' AND Receiver='" & Username & "' AND Deleted='False'"
Dim MySqlReader1 As MySqlDataReader = MySqlCommand1.ExecuteReader()
Dim InboxCount AS Integer = 0

'not using a loop here as i only get one row.
MySqlReader1.Read()
Messages = MySqlReader1("InboxCount")
  
Label1.Text = "Welcome back " & Username & ", you have " & InboxCount & " new message(s)."
MySqlReader1.Close()
MySqlConnection.Close
MySqlConnection.Dispose



Was This Post Helpful? 1
  • +
  • -

#9 Guest_NIXZ*


Reputation:

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 08:46 AM

Thanks motcom but it seems like you ve done an error its not:
Label1.Text = "Welcome back " & Username & ", you have " & InboxCount & " new message(s)."

but
Label1.Text = "Welcome back " & Username & ", you have " & Messages & " new message(s)."

is there any difference beetween COUNT(*) and COUNT(ID) ???

This post has been edited by NIXZ: 24 August 2009 - 08:47 AM

Was This Post Helpful? 0

#10 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: MySQL Command Problem. (For Mailbox)

Posted 24 August 2009 - 08:57 AM

Not sure about it, but in Select Statments I always use only the required columns and not everything (* means everything). Especially if there are multiple joins. * could cause a lot of data to be processed in select statements
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1