Can parameters be passed to MS Access queries?

[John Sway]

Can you use MS Access queries like you use SQL Stored procedures?

I am using the following to authenticate a user from an Access database:
OleDbCommand cmd = new OleDbCommand(String.Format(

"SELECT COUNT(id) FROM tbl_User WHERE Username = \"{0}\" AND Password =
\"{1}\"",

username, password), dataConn);

I'm wondering if it is possible to write a query in the Access database, and
call it just by passing the username and password as parameters, rather than
embedding the SQL statements in my code.

Thanks!

 

[William Ryan]

Yes, try "SELECT COUNT(id) FROM tbl_User where Username = ? and Password =
?"

then with your command object

cmd.Parameters.Clear //Unless you are sure that there aren't any
cmd.Parameters.Add(username)
cmd.Parameters.Add(password)

Then just make sure you have an open connection if you are using a Reader or
a connected object or a good DataAdapter otherwise, and you'll be good to
go.

Cheers,

Bill

//Check on Google or check this link http://accessvbsqladvisor.com/doc/12935
and I encourage you to look around a little more b/c you can be more precise
and do more than my simple example, but you can definitely do it.

 

[Paul Clement]

¤ Can you use MS Access queries like you use SQL Stored procedures?
¤
¤ I am using the following to authenticate a user from an Access database:
¤ OleDbCommand cmd = new OleDbCommand(String.Format(
¤
¤ "SELECT COUNT(id) FROM tbl_User WHERE Username = \"{0}\" AND Password =
¤ \"{1}\"",
¤
¤ username, password), dataConn);
¤
¤ I'm wondering if it is possible to write a query in the Access database, and
¤ call it just by passing the username and password as parameters, rather than
¤ embedding the SQL statements in my code.

Yes, but you must specify the parameters by their order in the collection (ordinal). The parameter
names have no affect on order (OLEDB limitation).

Dim AccessConn As System.Data.OleDb.OleDbConnection
Dim AccessCommand As System.Data.OleDb.OleDbCommand
Dim AccessReader As System.Data.OleDb.OleDbDataReader
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:System database=C:\Winnt\System32\System.MDW;" & _
"User ID=Admin;" & _
"Password="

AccessConn = New System.Data.OleDb.OleDbConnection(ConnectionString)

AccessConn.Open()

AccessCommand = New System.Data.OleDb.OleDbCommand("ValidateUser", AccessConn)
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Parameters.Add("@pUserID", CType(SqlDbType.NVarChar,
System.Data.OleDb.OleDbType), 15).Value = "username"
AccessCommand.Parameters.Add("@pPassword", CType(SqlDbType.NVarChar,
System.Data.OleDb.OleDbType), 15).Value = "password"
AccessReader = AccessCommand.ExecuteReader

While AccessReader.Read()
Console.WriteLine(AccessReader.Item("UserIDCount").ToString)
End While

AccessReader.Close()
AccessConn.Close()


The Access QueryDef ValidateUser looks like the following:

PARAMETERS pUserID Text, pPassword Text;
SELECT Count(UserIDs.UserID) AS UserIDCount
FROM UserIDs
WHERE UserIDs.UserID=pUserID AND UserIDs.Password=pPassword;


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)