Tuesday, February 14, 2012

command text question?

i have one question in my select statement i'm selecting two things username, password will the dbCmd.ExecuteScalar return username,password in one row or together in the returnuser variable. i wanna return them both in two separate variables or do i need to different select statements?

thanks

Private Function check_login(ByVal login As String) As String

Dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT login,password from users where login_id= @.login"
dbCmd.CommandType = CommandType.Text

With (dbCmd.Parameters)
.Add("@.login", SqlDbType.VarChar, 64).Value = login

End With

returnuser = dbCmd.ExecuteScalar

dbCon.Close()

Return returnuser

End FunctionExecuteScalar will only return the first row of the first column. You will either need separate ExecuteScalar calls with separate queries (not very efficient), use a datareader, fill a datatable, or use a stored procedure and have the username and password returned as output parameters. The easiest is probably a datareader:


// Sorry C# but you should get the idea for VB.Net
string returnuser = "";
string password = "";
SqlDataReader dr = dbCmd.ExecuteReader();
while( dr.Read() ){
returnuser = dr.GetString(0);
password = dr.GetString(1);
break; // just to make sure you only get one record
}

The stored procedure with output parameters is probably the most efficient (fastest) but it probably makes no practical difference.

No comments:

Post a Comment