SQL Exception problem

kantong

Member
Joined
Mar 10, 2005
Messages
5
Programming Experience
1-3
i want to create a login page using asp.net and vb.net . I get this error:
Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near line 1''
Source error:

Line 77: DataAdapter.SelectCommand=dbCommand
Line 78:
Line 79: DataAdapter.Fill(ds)
Line 80:
Line 81: Return ds

------
source code:
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String
Dim dbConnection As New SqlConnection
Dim dbCommand As New SqlCommand
Dim dataAdapter As New SqlDataAdapter
Dim ds As New DataSet
connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users"
'server=localhost; database=users; integrated Security=SSPI;?@user id=sa; password=chaos
dbConnection.ConnectionString = connectionString
With dbCommand
.Connection = dbConnection
.CommandText = "SELECT COUNT (*)AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))"
End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)
Return ds
End Function

the variables are being passed in from this:

Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(userID.Text, False)
Else
error_log.Text = "Invalid Credentials: Please try again"
End If
End If
End Sub

ive just started programming, not quite sure wats happening. Thanks for ne tips or help in advance!

-Kevin
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Ok there's a few things;

"SELECT COUNT (*)AS pass..." ; count will always return a value, if your login is incorrect the count will be 0 but there's still a row so you'll still be authenticated. pass also needs{} around it.

Your parameters; you pass them to the checklogin function, but you didn't add them to the adapter (or directly into the select statment)

When you fill(ds) you need to open and close the connection i.e.
dbConnection.open
dataAdapter.Fill(ds)
dbConnection .close

Hope that helps

TPM
 

kantong

Member
Joined
Mar 10, 2005
Messages
5
Programming Experience
1-3
hi thanks for the reply.

I put the following code before and it directed me to the default page. yes and i see the problem of count returning a row. How can i get around this?

With dbCommand
.Connection = dbConnection
.CommandText = "declare @userID As varChar; declare @password As varChar; SELECT COUNT (*) AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))"
End With
i tried putting {pass} but i get SqlException error near that { . Ive taken out the {} around pass and im still getting errors SqlException near {. why is that even after I have taken it out?

cheers
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Well I'd say just select the username (not the count) that way if there's no match nothing is returned. Ah I just realized something, you trying to give an alias to more than one column. Since count(*) will give you the count for each column, not all together in 1. ie if you have 2 columns and the both have 2 records it's going to return 2 coulmns of 2, not 1 column with 4.
 

kantong

Member
Joined
Mar 10, 2005
Messages
5
Programming Experience
1-3
hi TPM thanks for the reply. I figured out i need to just select with the *. This will return a row if it found something. but now the problem is even if i put the correct details in, it wont let me through to the next page.


code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(userID.Text, False)
Else
error_log.Text = "Invalid Credentials: Please try again"
End If
End If
End Sub
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String
Dim dbConnection As New SqlConnection
Dim dbCommand As New SqlCommand
Dim dataAdapter As New SqlDataAdapter
Dim ds As New DataSet
connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users"
dbConnection.ConnectionString = connectionString
dbConnection.Open()
With dbCommand
.Connection = dbConnection
.CommandText = "declare @userID As varChar; declare @password As varChar; SELECT * FROM tbl_users WHERE ((tbl_users.userID = @userID) AND (tbl_users.password = @password))"
End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)

Return ds
dbConnection.Close()
End Function

some kind of logic error, or maybe its the datatypes. Am i comparing the data types correctly? in my table, both are declared as varChar.

cheers
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Hmm ok well now you only getting 0 rows returned, that because you didn't add your variables correctly.
You want to do this:
"SELECT * FROM tbl_users WHERE ((tbl_users.userID = '" & userid & "') AND (tbl_users.password = " & password & "'))"

OR

dbcommand.parameters.add("@userID",userID)

Also you don't really need to dim a seperate SQLcommand, the dataadapter has it built in.

TPM
 

kantong

Member
Joined
Mar 10, 2005
Messages
5
Programming Experience
1-3
Hi TPM,

i added parameters within the With dbCommand part.. but i get the below error:

Details of exception: The parameter data type of System.ArgumentException:Char is
invalid.
Source error:
Line 72: With dbCommand
Line 73:. ..line.. 74-Connection=dbConnection:. Parameters.Add("@userID",userID) line 75:. Parameters.Add("@password",password) line 76:. CommandText = "declare@userID As varChar; declare @password As varChar; SELECT * FROM tbl_users WHERE ((tbl_users.userID = @userID) AND (tbl_users.password = @password))"


i checked the database, both fields are set to varChar type. ne ideas?
cheers
 

kantong

Member
Joined
Mar 10, 2005
Messages
5
Programming Experience
1-3
ive tried the first method u said of '"& userID &"' AND '"& password &"'
and this wont compile...
 
Top Bottom