Creating a login using vs2005 and sql express

[drew4663]

For something that is one of the most widely sought after features this topic is hard to find answers on. I have searched this forum and google for a few days now and have not come up with a solid solution. Is there any that would pertain to my setup?

Very basic. SQL express. I have a userid, username and password column. I can save the data just fine I just need to be able to authenticate the data. Any directions?

 

[drew4663]

Really? Not one person willing to help out? Sheesh!

 

[ajeeshco]

I can save the data just fine I just need to be able to authenticate the data.

What do you mean by authenticate data? Did you mean this http://www.databasejournal.com/features/mssql/article.php/3714031

 

[drew4663]

I have a database in sql server. The name of the database is "inventory.master.dbo". I created a table called "Inventory". Inside the table I have 14 record fields. I also have a table called "Login". This contains two record fields which are "username" and "password".

I am able to write data, view data, update data and delete data. The problem is when I want someone to login to use the application. I need the username and password entered into the textboxes to be checked against the "Login" table and its entries. I'm just not sure what to attach to that login button for the data to be authenticated.

 

[ajeeshco]

If you are trying to create login in sql server itself this link may help you. http://msdn2.microsoft.com/en-us/library/ms189751.aspx

 

[drew4663]

Either I have no idea what you are talking about or you are missing what I am trying to do. I am storing usernames and passwords in a sql server database. The data entered is verified in the vb application and upon authentication of data matching the result is access to the program.

 

[drew4663]

Here is the code I am using. Please tell me what I am doing wrong.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim theConnectionString As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=Master;Trusted_Connection=Yes;")
        Dim theSqlCommand As New SqlClient.SqlCommand("SELECT [LoginID] FROM [Login] WHERE [username] = @username AND [password] =@password", theConnectionString)

        Dim theUsername As New SqlClient.SqlParameter("@username", SqlDbType.VarChar)
        theUsername.Value = "username"
        Dim thePassword As New SqlClient.SqlParameter("@password", SqlDbType.VarChar)
        thePassword.Value = "password"
        theSqlCommand.Parameters.Add(theUsername)
        theSqlCommand.Parameters.Add(thePassword)

        'open the connection
        theSqlCommand.Connection.Open()
        Dim theDataReader As SqlClient.SqlDataReader = theSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Dim theLoginType As Object = Nothing
        If theDataReader.HasRows Then
            theDataReader.Read()
            'the user exists!! Lets get the logintype:
            theLoginType = theDataReader("loginType")
        End If
        theSqlCommand.Connection.Close()

        If theLoginType = Nothing Then
            MessageBox.Show("Sorry, username/password details were incorrect")
        Else
            MessageBox.Show("Login successful")
        End If
    End Sub

 

[adshocker]

Dim theUsername As New SqlClient.SqlParameter("@username", SqlDbType.VarChar)
theUsername.Value = "username"
Dim thePassword As New SqlClient.SqlParameter("@password", SqlDbType.VarChar)
thePassword.Value = "password"
theSqlCommand.Parameters.Add(theUsername)
theSqlCommand.Parameters.Add(thePassword)

hi,

do you get any errors on the button_click?

also, i would like to know... you hardcoded the values of the theUsername and thePassword Parameters. is this just an example?

normally it should be

        Dim theUsername As New SqlClient.SqlParameter("@username", SqlDbType.VarChar)
        theUsername.Value = txtUsername.Text
        Dim thePassword As New SqlClient.SqlParameter("@password", SqlDbType.VarChar)
        thePassword.Value = txtPassword.Text
        theSqlCommand.Parameters.Add(theUsername)
        theSqlCommand.Parameters.Add(thePassword)

also, you can try to change your sqlcommand like this...

Dim theSqlCommand As New SqlClient.SqlCommand("SELECT COUNT(1) FROM [Login] WHERE [username] = @username AND [password] =@password", theConnectionString)

then you can try to add a data reader to get if a username and password like that exists.

Dim sqlReader as SqlDataReader
Dim exists as Int32
sqlDataReader = sqlCommand.ExecuteReader()
While sqlDataReader.Read
 exists = sqlDataReader.GetInt32(0)
End While

If exists > 0 Then
 'Add your codes here if login successful
 MessageBox.Show("Login Successful")
Else
 'Add codes here for login failed.
 MessageBox.Show("Login Failed. Please check your username and password.")
End If

this is just my opinion.