sql database help

[russellmunday]

I need to have a log in box that will query a table if the name does not exist i want to write to the screen a message but i'm not sure how to start for example
if "i dont get a return fro my table" then
response.write("whatever")

 

[ca8msm]

First, I suggest you read faq855-5662 on how to make a simple database connection.

Second, rather than use Response.Write (this should only be used in testing) you should add a label to your page and set it's Text property if no rows exist in your DataTable/Reader.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[russellmunday]

response.write is just an example have read your faq855-5662 link but nothing in there that helps thanks anyway

 

[ca8msm]

Why doesn't it help?
For example, in the second example (using a data table) you could just check MyDataTable.Rows.Count. If it equals zero then show a message saying the name does not exist.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Just in case you need an example of what I mean, somethig like:

    Dim MyConnection As System.Data.SQLClient.SqlConnection
    Dim MyDataAdapter As SqlClient.SqlDataAdapter
    Dim MyDataTable As New DataTable
    Dim MyDataRow As DataRow
    Dim strSQL As String = "SELECT FIELD1, FIELD2 FROM MYTABLE"

    MyConnection = New System.Data.SQLClient.SqlConnection("server=127.0.0.1; initial catalog=MyDatabase;uid=USERNAME;pwd=PASSWORD")
    MyConnection.Open()
    MyDataAdapter = New SQLClient.SQLDataAdapter(strSQL, MyConnection)
    MyDataAdapter.Fill(MyDataTable)

    If MyDataTable.Rows.Count = 0 Then
        Label1.Text = "No Records Found"
    End If

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[russellmunday]

the answer I am after is along theese lines I dont want to put extra controls on the form its very loaded as it is the following is just an example not in my actual program.

I can see what you are saying with the second example but i would prefer not to put extra controls on the form.



Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit.Click
If usrtxt.Text <> String.Empty Then
SqlCommand1.Parameters("@name").Value = usrtxt.Text
SqlCommand1.Parameters("@password").Value = pwdtxt.Text
SqlConnection1.Open()
SqlCommand1.ExecuteReader()
SqlConnection1.Close()
If Not IsDBNull(SqlCommand1.Parameters("@name").Value And SqlCommand1.Parameters("@password").Value) Then
Response.Redirect("main.aspx")
Else
lblnologgin.Visible = True
lblnologgin.Text = "Your User Name or Password are incorrect"
End If
End If

End Sub

 

[evilmousse]

SqlCommand1.Parameters("@name").Value and SqlCommand1.Parameters("@password") are not variables you get as a part of the response from SQL, you're testing on something that hasn't to do with the success or failure of the query. you need to do something with the results of the query, whether using a datareader or dataadapter. i see you execute SqlCommand1.ExecuteReader(), but you don't capture the datareader object it returns. it would help for me to know SqlCommand1's return values.

lastly, i don't think isdbnull works well with a boolean expression inside it (SqlCommand1.Parameters("@name").Value And SqlCommand1.Parameters("@password").Value). that works out to isdbnull(true) or isdbnull(false).



Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit.Click
If usrtxt.Text <> String.Empty Then
SqlCommand1.Parameters("@name").Value = usrtxt.Text
SqlCommand1.Parameters("@password").Value = pwdtxt.Text
Dim dReader As System.Data.SqlClient.SqlDataReader
dim sLogin, sPwd as String
SqlConnection1.Open()
dReader = SqlCommand1.ExecuteReader()
While dReader.Read() 'this will execute once per row returned, here i presume one row's returned
sLogin = dReader("logincolumname").ToString
sPwd = dReader("pwdcolumnname").ToString
End While
SqlConnection1.Close()
If (IsDBNull(sLogin) or IsDBNull(sPwd)) Then
Response.Redirect("main.aspx")
Else
lblnologgin.Visible = True
lblnologgin.Text = "Your User Name or Password are incorrect"
End If
End If

End Sub

 

[evilmousse]

er, maybe remove the .tostring fucntions when later using isdbnull. whupps.

 

[AppzDev]

I've accomplished similar things like this using 2 methods, both are probably not right, but the end result is what i wanted.

First you could try using a datareader

Dim myConnection as New SqlConnection...
myConnection.Open()
Dim myCommand as SqlCommand= myConnection.CreateCommand()
myCommand.CommandText = "SELECT username from tblUsernames where username = '" & username.text & "'"
Dim myReader as SqlDataReader = myCommand.ExecuteReader()
If MyReader.Read = True 'this means it found a username
do something here
Else 'this is where it does not find anything
do something else here
End If

Or you could try a "try, catch, end try" which basically would try to find the username and when it doesn't, it would "catch" that and at that point you could use your response.write("Your username does not exist")

Try
go out to your DB and search for a username
do a bunch of stuff if you find one...
catch
response.write("your username does not exist")
End Try

 

[Henryas]

If you are doing a login function, this may help. You don't need to retrieve both username and password to validate a user. Either one should suffice. The following example only retrieve the password.

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.CommandText=[blue]"SELECT password FROM tableAccount WHERE username = '"[/blue]+username+[blue]"'"[/blue];
command.Connection = connection;

connection.Open();
if(password == (string)command.ExecuteScalar())
{
  [green]//Bingo, your password matches[/green]
}
else
{
  [green]//Sorry, your password is totally bogus[/green]
}
connection.Close();

If you only want to check whether a user exist, you should use "SELECT COUNT(*) FROM table WHERE username = 'yourusernamehere'. If it returns 0, it means that user does not exist.

Hope it helps.

Henry

 

[russellmunday]

thanks for your help but i managed to look up an example in one of my books and used a stored proceedure as follows
ALTER PROCEDURE dbo.loggon
(
@username varchar(20),
@password varchar(20)
)
as
if exists
(
select id from customerinfo
where username = @username
and password = @password
)
return 0
/*
check for valid username
*/
if exists
(
select id from customerinfo
where username = @username
)
return 1

/*
username does not exist
*/
return 2


it works very well and will allow me to update the table when i need to change the password.