Hi folks,

In a webform I have a dropdownlist control using ASP.NET.  I have bound data
to the control and have populated it with names.

SELECT * FROM USERS WHERE ID=?

.... USERS is the table, ID is the primary key and NAME is a text string.

When I select a NAME from the dropdownlist control, I want to pass the
respective ID to a function that is structured as follows:

QueryMethodByID (ByVal id As Integer) As IDataReader

Essentially the pseudo code would be as follows:
    If NAME = "A NAME FROM THE LIST" THEN
       ID = 7   // or any number that pertains to a respective name
    end if

Glenn,

Probably do you as I understand your message.

You use a dropdownlist with a datasource
You have told that the DataTextField is "Name"
You have told that the DataValueVield id "ID"
Does Databinding on that

And when there is a selectedindexchange you take the SelectedValue

That SelectedValue do you use in your Select String.

What is than the problem?

Cor


Show quoteHide quote

Thanks for your reply.

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

This was done because the dropdownlist control is populated with names from
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 that calls the method.

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

        Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

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

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

        dbConnection.Open
        Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.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




Show quoteHide quote

Thanks for your reply.

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

This was done because the dropdownlist control 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 that
calls the method.

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

        Dim dbConnection As System.Data.IDbConnection = New
System.Data.OleDb.OleDbConnection(connectionString)

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

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

        dbConnection.Open
        Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.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


Show quoteHide quote

Glenn,

How did you populate that dropdownlist, in my opinion is that your missing
part in the code.

Cor

Thank you for your reply. 

I have solved my problem. 

What I did was to change from SQL query from Select ID from USERS WHERE
NAME=? to Select * from USERS WHERE NAME=?

Show quoteHide quote

ID is a SQL keyword. You need to properly escape it in a query so it can be
properly parsed.

Show quoteHide quote

ODBC - Error
Find Equivalent
Oracle Table Adapter Querry Wizard Error
UpdateBatchSize and SqlTransaction
Does creating a Static SQL interface class make sense