Question Checking if data exists

[Manny123]

Dim FirstName As String
Dim LastName As String
Dim UserName As String
Dim Password As String
Dim SecurityQ As String
Dim SecurityAns As String

FirstName = txtfn.Text
LastName = txtln.Text
UserName = txtun.Text
Password = txtpass.Text
SecurityQ = CboSecurityQs.Text
SecurityAns = txtSecurityans.Text

If String.IsNullOrEmpty(txtfn.Text) Or String.IsNullOrEmpty(txtln.Text) Or String.IsNullOrEmpty(txtun.Text) Or String.IsNullOrEmpty(txtpass.Text) Or String.IsNullOrEmpty(CboSecurityQs.Text) Or String.IsNullOrEmpty(txtSecurityans.Text) Then
MsgBox("Please check information is entered in all fields", MsgBoxStyle.Exclamation, "Empty fields")

Else
Dim str2 As String
Dim com As OleDbCommand

str2 = "SELECT COUNT(Username) from User WHERE Username = '" & UserName & "'"
com = New OleDbCommand(str2, cn)
com.Parameters.AddWithValue(UserName, txtun.Text)
Dim rowCount As Integer = Convert.ToInt32(com.ExecuteScalar())
If rowCount <> 0 Then
MsgBox("Sorry, this username already exists. Please try a different username",MsgBoxStyle.Exclamation)
Else

str = "INSERT INTO User (Name, LastName, Username, Password, Security_question, Security_answer) values(" & FirstName & ",'" & LastName & "','" & UserName & "','" & Password & "','" & SecurityQ & "','" & SecurityAns & "')"
cmd = New OleDbCommand(str, cn)
mes = cmd.ExecuteNonQuery
MessageBox.Show("User Added")





What i am attempting to do is make the user's username unique so other users cannot have the same username but I keep on receiving this error message above about syntax error but the SQL statement looks fine to me.

Can somebody help me and tell me what i am doing wrong please

 

[jmcilhinney]

First up, don't use string concatenation to insert values into SQL code. That is one possible reason for such errors and they won't show up all the time. For instance, your code will fail for someone named O'Connor. Always use parameters. To learn how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

The more immediate issue is that you have a column name that is a reserved word, i.e. Password. Any identifiers that are reserved words or contain special characters, e.g. spaces, must be escaped. In Access you do that by wrapping them in brackets.