[RESOLVED] [2008] check to see if select statement finds a result

[bagstoper]

i am kinda new to sql programing. just saying that to get it out of the way. i know how to connect to a database but i don't know how to check to see if a result is received from a select statement. this is my select statement

da.SelectCommand = New OleDbCommand("SELECT * FROM userinfo WHERE username =" & UsernameTextBox.Text)

i need to know if there is a user with that username and if there is then i will need to read the password for that person. then if both username and password are correct then i will show form 2. any help is welcome.

[riteshjain1982]

you can use

 Dim dtUser As New DataTable("User")
      
        Dim oldAdap As New OleDataAdapter("SELECT * FROM userinfo WHERE username =" & UsernameTextBox.Text), objConn) 'objConn is connection object
        oleAdap.Fill(dtUser )
  
      If dtUser.rows.count = 1 then
        'here is you code that will check password and other thing.
  else
         messagebox.show("Invalid user name")
    End if

PS: Better use parameterised query.

[Jenner]

Fill the DataTable, and then take a count of rows.

Dim dt As New DataTable
da.Fill(dt)
dt.Rows.Count

[bagstoper]

trying code will get back to you soon

[wild_bill]

Using a dataadapter is overkill, using a command's executescalar method should work faster.

[Clanguage]

Using a dataadapter is overkill, using a command's executescalar method should work faster.

I would have to agree with wild__bill

[bagstoper]

how would i do that? i would not know where to start.

[bagstoper]

you can use

 Dim dtUser As New DataTable("User")
      
        Dim oldAdap As New OleDataAdapter("SELECT * FROM userinfo WHERE username =" & UsernameTextBox.Text), objConn) 'objConn is connection object
        oleAdap.Fill(dtUser )
  
      If dtUser.rows.count = 1 then
        'here is you code that will check password and other thing.
  else
         messagebox.show("Invalid user name")
    End if

PS: Better use parameterised query.

if i used your code it would only work if there is one entry ( i could change that to a for loop but not atm) i would also need an way to check for the a password and the way you have it set up i would have to have a copy of a lookup table on the client side. which would have to be updated every time there was a new member. so your way is not very useful to me bu thank you for reponding so quickly

[Clanguage]

da.SelectCommand = New OleDbCommand("SELECT * FROM userinfo WHERE username =" & UsernameTextBox.Text)

i need to know if there is a user with that username and if there is then i will need to read the password for that person. then if both username and password are correct then i will show form 2. any help is welcome.

If you do the following you should only return one record.

SELECT Password FROM userinfo WHERE username =" & UsernameTextBox.Text

Create a command object and use the executescalar method

[Clanguage]

Note that if the user does not exist you not return a record but if the user exists then you will return the password

[Clanguage]

If you have trouble with the command object there is a sample from today on our forum. Post #9 will be of interest to you.
http://www.vbforums.com/showthread.p...command+object

[riteshjain1982]

if i used your code it would only work if there is one entry ( i could change that to a for loop but not atm)

Is your table stores Multiple user for same username

i would also need an way to check for the a password

you can use dtUser.rows(0).Item("Password")

the way you have it set up i would have to have a copy of a lookup table on the client side. which would have to be updated every time there was a new member. so your way is not very useful to me bu thank you for reponding so quickly

can you explain Why/what is the need to store/Refresh table at client side?

and as suggested by other member,in case you are only intersted in getting Password for a user you can simply for for ExecuteReader and check if Return value is nothing (that mean user does not exist) or have Valid value.

[bagstoper]

i think i have firgured it out but now my connection string isnt working. this is my connection string

	
Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;

i can fill in the data source and user id and password but i dont know how to find my provider. any help?

[riteshjain1982]

if it's SQL database simply use SQLClient objects instead of OLEDB,also look at Thisit has plenty of connection string sample for various database.

[bagstoper]

that is where i got the connection string from and this is a mySQL db will that matter

[riteshjain1982]

can you post you code that fills/check the username (along with connection string and objects).

Also check if are you able to call open() method of oleConnection object?if not post the error message.

[bagstoper]

ok i have gotten everything working except with my connection string.

Driver={MySQL ODBC 3.51 Driver};Server=http://dansoft.selfip.com;Option=131072;Stmt=;Database=elemental_battle; User=****;Password=****;

my error

ERROR [HY000] [MySQL][ODBC 3.51 Driver]Unknown MySQL server host 'http://dansoft.selfip.com' (11004) ERROR [HY000] [MySQL][ODBC 3.51 Driver]Unknown MySQL server host 'http://dansoft.selfip.com' (11004)

when i change the connection string to where server=localhost then there is no error and i can get my data. i don't see the problem.

[riteshjain1982]

never worked with MySQL,but you can cross this these point..

1>Are you able to connect to server 'http://dansoft.selfip.com' using some external tools? (e.g SQL Analyser in case of SQL 2000)
2>Make sure MySQL is running on same default port else explicitly specify it in your connection string.

[bagstoper]

can you recommend a good one?

[riteshjain1982]

can you recommend a good one?

What? if it's tool may be you would like to Check this

[bagstoper]

that only has an external download link to this

[bagstoper]

well i found one but it also wont connect to the url but it will connect to the localhost. i get this error when it tries to connect to the url version (via visual studio)

ERROR [HY000] [MySQL][ODBC 3.51 Driver]Host '192.168.1.1' is not allowed to connect to this MySQL server ERROR [HY000] [MySQL][ODBC 3.51 Driver]Host '192.168.1.1' is not allowed to connect to this MySQL server

[bagstoper]

solved it i had to create a new user in mySQL with the host being anyone and give them privileges to view the database.

[Jenner]

As a side note, if you're using MySQL, I'd recommend downloading the MySQL .NET provider off their site. It's a LOT faster and nicer to use than ODBC.

[bagstoper]

ok i will look into that i was wondering if there was any way i could make it go faster. it would take about 10 secs to connect and read the data for 2 rows. would hate to see how long it took to read 100+ rows. thanks i will into it.

[bagstoper]

wait do you mean the MySQL :: MySQL Connector/Net or something else?

[Jenner]

No, that's what I mean. The .NET connector (here). I believe the latest version is 5.2.2 and it works with VS2008. It appears like you're using the old ODBC driver v3.51 and talking to your database via the ADO.NET ODBC or OLEDB objects.

The .NET connector gives you a MySQL namespace with MySqlConnection, MySqlCommand, MySqlDataAdapter, etc objects to use. It's also a fair amount faster.

[bagstoper]

ok i just downloaded it how do i implement it into my project?