SQL Syntax ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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=?
 
ID is a SQL keyword. You need to properly escape it in a query so it can be
properly parsed.
 
Back
Top