Member Avatar for mathewsp.jacob.7

How to check if email id already exist in db using vb6

r.Open " select count(*) from login where username = '" & Text1.Text & " ' ", c, 3, 3
If Count > 0 Then
MsgBox " already exist "
Else
MsgBox "valid"
End If
r.Close

plese help this cod always show only 'already exist' msg.

Edited by pritaeas because: Moved to VB.
  • 2 Contributors
  • 1 Reply
  • 613 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

As discovered, the query you ran will always return one row when you use COUNT(*). As pointed out, that means testing the recordset's row count will always look like “found” — what you need is the numeric value inside the returned row, or a simple existence query instead.

A safer, clearer pattern is to use a parameterized command and read the COUNT(*) field:

' Requires reference to Microsoft ActiveX Data Objects
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn           ' your open ADODB.Connection
cmd.CommandText = "SELECT COUNT(*) AS cnt FROM login WHERE username = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("username", adVarChar, adParamInput, 255, Trim$(Text1.Text))

Set rs = cmd.Execute
If rs.Fields("cnt").Value > 0 Then
  MsgBox "already exist"
Else
  MsgBox "valid"
End If
rs.Close

For performance you can avoid COUNT(*) and just ask for an existence row (use TOP 1 on SQL Server or LIMIT 1 on MySQL) and check If rs.EOF Then to detect absence — that avoids scanning/indexes for a full count.

Additional practical notes: always trim and normalize case before checking (or enforce a case-insensitive collation), use parameterized queries to prevent SQL injection, and add a UNIQUE index/constraint on the username/email column so the database enforces uniqueness (also handle the unique-violation error on insert to cover race conditions). For input hygiene, validate the email format (VBScript.RegExp) before querying. These steps will make your check correct, safe, and robust.

Member Avatar for BitBlt

I would say that you are checking the rowcount returned from the select statement, rather than the actual value of count(*) that is returned.

If you get a hit on that username, you'll get one row returned, and the value of count(*) will be 1.

If you DON'T get a hit on that username, you'll still get one row returned, and the value of count(*) will be zero.

But, in both cases, the rowcount will still be one, therefore your message will always be "already exist".

Hope that helps!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.