Check to See if A User already Exist

[ctyokley]

Ok. so i'm not quite sure how to do this. I have searched many of website but cant really find too much.

I have a database in MS ACCESS that holds user information. I have the insert function working on this. I have used VB.NET CODE.

What i want to do now is check to see if a user exist b4 they can create their information. I have SQLCHECKUSER in a function and if it returns true, then it means a user exist. so if it returns false, then it will insert the information they have given.

Can someone please help me with this or direct me in the right place.

Thanks

[kvprajapati]

ctyokley,
Select query with where clause.

[ctyokley]

so something like

oQuery as a string = "SELECT * FDXUSERS WHERE empnumber or something another? cuz i'm quite unsure where to search fro this. can you give me an example?

[Ramesh S]

Yes. you have to use a SELECT statement with WHERE condtion before inserting the user information.

See the sample code below

'Change your database name and path
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestDB.MDB"
        Dim con As OleDbConnection = New OleDbConnection(strConnection)
        con.Open()

        'construct your where condtion 
        Dim strQuery1 As String = "SELECT EMPNO FROM FDXUSERS WHERE EmpNo = " + employeeNo
        Dim aCommand1 As OleDbCommand = New OleDbCommand(strQuery1, con)

        Dim objExist As Object = aCommand1.ExecuteScalar()

        'construct your INSERT statement here
        Dim strQuery2 As String = "INSERT INTO FDXUSERS VALUES(4, 'John', 30000, 20)"
        Dim aCommand2 As OleDbCommand = New OleDbCommand(strQuery2, con)

        If objExist Is Nothing Then
            aCommand2.ExecuteNonQuery()
        End If

        con.Close()

[ctyokley]

K so from what I have read about the .ExecuteScalar() it only reads one line of the db and only the first line.
What if i want to read the entire db.
will i need a datareader? or just use a while loop? plz give example on code
Thanks

[dnanetwork]

sorry my friend, this is a logical case...so can't offer you the code..

but you can fire the query before insert...which would be your select statement....where you'll find you're total user...

iterate them through reader and check if that user matches with your textbox value...and if it does/....then don't insert and raise the error label that user already exist...!

if user does not exist then you can insert in the database...

and executeScalar() returns only one object which you can find at first row and first column...

generally executescalar is used for getting the MAX no....or some other logic...

Hope it helps...!

[ctyokley]

This is what i was thinking. I just didnt' kno what the Schaler was for... cuz i thought i'd need a reader.
Thanks i'll check it out.
then if i get somethin up i'll post an example to help other people.

[kvprajapati]

Try this code:

Dim Cn as new OledbConnection(".....")
Dim Cmd as New OledbCommand("select eno from emp where eno='" & txtEno.Text & "'",cn)

Cn.Open()
Dim obj as Object
obj=Cmd.ExecuteScalar()
Cn.Close()

IF IsNothing(obj) Then
     'Employee not found 
     'Write statement to insert a record
ELSE
     'Employee Found
END IF

[dnanetwork]

dude mark the thread as solved if your problem is resolved..

it will help others to checkout the solved threads....

[ctyokley]

Ok to clear up some things. ExecuteSchaler() will search the entire column you tell it 2. so I have created a registration.
All my variables are global variables.
The executechaler() is withing a funtion and returns an object. Below is the code for the executeschaler()

Function SQLCHECKUSER() As Object
        Dim query1 As String = "SELECT [employee_number] FROM [FDXUSER] WHERE ([employee_number] ='" & empnumber.Text & "')"

        Dim sqlcommand As New OleDbCommand(query1, Sqlconnection)

        obj = sqlcommand.ExecuteScalar()
        Return obj
    End Function

Then once the object is returned i'll check it in the submission of the button. as stated below in the code

Try
                sqlDBconnection()
            Catch ex As Exception
                Response.Write(ex.Message)
                Exit Sub
            End Try

            sqlinsert = "INSERT INTO FDXUSER(employee_number, First_Name, Last_Name, Email, Station_ID, User_Name, [Password], Security_Level) VALUES ('" & empnumber.[Text] & "','" & first_name.[Text] & "','" & last_name.[Text] & "','" & email_address.[Text] & "','" & Stationident.Text & "','" & User_Name.[Text] & "','" & password1.[Text] & "','3')"
            Try
                SQLCHECKUSER()
                If obj Is Nothing Then

                    Sqlcommand.CommandText = sqlinsert
                    Sqlcommand.Connection = Sqlconnection
                    Sqlcommand.ExecuteNonQuery()
                Else
                    pageerrorlbl.Text = " Sorry. The Employee Number Already Exist. Please Try again! If you recieve this error more than once Please contact the Administrator."
                    Exit Sub
                End If

            Catch ex As Exception
                Response.Write(ex.Message)
                Exit Sub
            End Try

I hope this helps!!!