UserID - int, 4, identity(1,1)
UserName - nvarchar(50)
UserPass - nvarchar(50)
The code to execute this where i am getting the errors is this:
Function ChooseUName()
If Page.IsValid Then
Dim objCon As New SqlConnection(con)
Dim sqlInsert As String = "INSERT INTO tblUser (UserName) " & _
"VALUES (@.Username) WHERE NOT EXISTS (SELECT UserName FROM tblUser)"
Dim cmd As New SqlCommand(sqlInsert, objCon)
cmd.Parameters.Add("@.Username", SqlDbType.NVarChar, 50)
cmd.Parameters("@.Username").Value = txtUsername.TextDim id As Integer
Try
objCon.Open()
id = cmd.ExecuteScalar()
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End TryResponse.Write("Your User ID is: " & id.ToString())
Response.End()
End If
End Function
This is the error:
Incorrect syntax near the keyword 'WHERE'.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.
Source Error:
Line 73: Try
Line 74: objCon.Open()
Line 75: id = cmd.ExecuteScalar()
Line 76: Finally
Line 77: If objCon.State = ConnectionState.Open Then
PLEASE HELP!! I'M ON A STRICT DEADLINE!!! :o THANKS IN ADVANCE!You need something more like this:
Dim sqlInsert As String = "IF NOT EXISTS (SELECT UserName FROM tblUser WHERE UserName=@.UserName) INSERT INTO tblUser (UserName) VALUES (@.Username)"
Terri|||THANK YOU SO VERY MUCH!!!
That worked like a charm. Do you or anyone else know of any resources online where i can learn more about using the SQL Syntax in my web apps? I've never seen such a string and now it's like a whole new world has opened up!
thanks again.|||You definitely need to haveSQL Server 2000 Books Online. This will help you tremendously with syntax issues. It's large but well worth the free download.
Another place you could look isMicrosoft ASP.NET Quickstarts Tutorial -- Server-Side Data Access. The section on "Inserting Data in a SQL Database" has coding to handle your situation, although they just do the INSERT and then test to see if it violated the primary key.
There are others out there, but those 2 resources come to mind first.
Terri
