[farhan_b]

hey

I having been working on this piece of code of several days now and i am not getting anywere i could really do with some advise.

What i am trying to do is get my system to look into the database and recognize if the user is a admin or a user, based on that they have certain rights
below is my code

Imports MySql.Data.MySqlClient

Imports System.Data.SqlClient

Public Class Form3


    Public con As New SqlConnection("server=; user =; password=; database=;")


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim conn As MySqlConnection
        conn = New MySqlConnection()
        conn.ConnectionString = "server=; user =; password=; database=;"
        ' See if connection failed
        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("Error1!")
        End Try
        ' SQL Query
        Dim myAdapter As New MySqlDataAdapter




        Try
            con.Open()
            Dim com As New SqlDataAdapter("select * from login where userName='" + txtusername.Text + "'", con)
            Dim ds As New DataSet
            ds.Clear()
            com.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then

                If ds.Tables(0).Rows(0)(1).ToString.Trim = txtpassword.Text.Trim Then
                    MsgBox("Login Successfull!")

                    If ds.Tables(0).Rows(0)(2).ToString.Trim = "Admin" Then
                        MainFrm.btnDelete.Enabled = True
                    Else
                        MainFrm.btnDelete.Enabled = False
                    End If

                    MainFrm.Show()


                Else
                    MsgBox("Invalid Username and Password!!")
                End If


            Else
                MsgBox("Invalid Username and Password!!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try

[nK0de]

So what's the problem exactly?

[farhan_b]

this code does not seem to connect to my database, but when i just select users the connection works the error comes in when i try assign rights to users . could u tell me were i am going wrong

thanks in advance

[CharlieMay]

You shouldn't bring the password back in your query result, instead pull the result based on the userid and password and return the other fields you need. Of course, you should also use parameters which will secure your queries. (See the link at the bottom of my post (SQL Injection and why you should care).

That being said,
I assume your table has some field that tells you the users level so you can determine if they're an admin or not. So you would just check the value of that returned field and act on it.

Is that the actual connectionstring you're passing, if it is, I suggest checking out ConnectionStrings.com for a proper connection string format.

Another thing to note. Let your dataadapter open and close the connection. It will open it when it's needed, no need to use con.Open and con.Close with this method of data access.

This post has been edited by CharlieMay: 26 February 2012 - 08:16 AM

[farhan_b]

Thanks guys but what im trying to do is get the server (mysql) to recognize that the a user can only do x y z.

I have 3 tables Users, Department and userRights.

In the user table i have: username, password, and departmentID.

In the Department i have : DepartmentID, and the departmentName

In the userRights table i have :id, SecurityRightID ,DepartmentID ,Can_Add ,Can_Edit Can_Delete.

What i am trying to do is get the database to recognize which user can do what and then display it on the interface Using Visual studio 2010

Thanks guys in advance really appreciate it

[CharlieMay]

Yes, we understand but you stated you cannot get the connection to work. From what we can assume from your database, the rest of the code looks to be sound (barring any mispellings of field names and value) but that would at least produce something.

IS that connection string you're showing exactly what you're using to access your database?


WAIT!! I do see a problem, you are using 2 connections con and conn.

Fix that.

ALSO, one of those is a SQLConnection and the other is a MySQLConnection.

This post has been edited by CharlieMay: 26 February 2012 - 08:23 AM