Appropriate Query ?

[Guest]

Hi folks,

I have a SQL Select statement as follows SELECT ID FROM USERS WHERE NAME=?

This was done because I have a dropdownlist control that is populated with
names from the database which I need to get the repective id for each name
selected. Here's the code. First the query method and then the event
handler for the ddl that calls the method.

Function QueryMethodByName(ByVal name As String) As IDataReader
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\sites\single29\gmeadows\database\fdmdb.mdb"

Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)

Dim queryString As String = "SELECT [users].[ID] FROM [users] WHERE
([users].[name] = @name)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_name As IDataParameter = New OleDbParameter
dbParam_name.ParameterName = "@name"
dbParam_name.Value = name
dbParam_name.DbType = DbType.String
dbCommand.Parameters.Add(dbParam_name)

dbConnection.Open
Dim dataReader As IDataReader =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection)

Return dataReader
End Function

Sub ddlExtgUser_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim extgUserName As String

extgUserName = ddlExtgUser.SelectedItem.Text

Response.Write(extgUserName) ' This writes OK to the screen

Dim dr As IDataReader
dr = QueryMethodByName(extgUserName)
if dr.Read() then
usn.Text = dr(1)
' pwd.Text = dr(2)
' cname.Text = dr(3)
' name.Text = dr(4)
' addr.Text = dr(5)
' city.Text = dr(6)
' state.Text = dr(7)
' zip.Text = dr(8)
' phone.Text = dr(9)
' email.Text = dr(10)
' proj.Text = dr(11)
end if
dr.Close()
End Sub

 

[Guest]

I forgot to mention that this code gives me an error stating that "the index
was outside the bounds of the array".

 

[Guest]

Sorry folks, I just solved my problem.

I needed to have SELECT * FROM users WHERE name=?

I forgot to mention that this code gives me an error stating that "the index
was outside the bounds of the array".

Hi folks,

I have a SQL Select statement as follows SELECT ID FROM USERS WHERE NAME=?

This was done because I have a dropdownlist control that is populated with
names from the database which I need to get the repective id for each name
selected. Here's the code. First the query method and then the event
handler for the ddl that calls the method.

Function QueryMethodByName(ByVal name As String) As IDataReader
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;
Ole DB Services=-4; Data Source=C:\sites\single29\gmeadows\database\fdmdb.mdb"

Dim dbConnection As IDbConnection = New
OleDbConnection(connectionString)

Dim queryString As String = "SELECT [users].[ID] FROM [users] WHERE
([users].[name] = @name)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_name As IDataParameter = New OleDbParameter
dbParam_name.ParameterName = "@name"
dbParam_name.Value = name
dbParam_name.DbType = DbType.String
dbCommand.Parameters.Add(dbParam_name)

dbConnection.Open
Dim dataReader As IDataReader =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection)

Return dataReader
End Function

Sub ddlExtgUser_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim extgUserName As String

extgUserName = ddlExtgUser.SelectedItem.Text

Response.Write(extgUserName) ' This writes OK to the screen

Dim dr As IDataReader
dr = QueryMethodByName(extgUserName)
if dr.Read() then
usn.Text = dr(1)
' pwd.Text = dr(2)
' cname.Text = dr(3)
' name.Text = dr(4)
' addr.Text = dr(5)
' city.Text = dr(6)
' state.Text = dr(7)
' zip.Text = dr(8)
' phone.Text = dr(9)
' email.Text = dr(10)
' proj.Text = dr(11)
end if
dr.Close()
End Sub