Passing variable to SQL string is not working.

G

Guest

Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to hard code
a company name and successfully populate the second control but if I attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 
S

ShaneFowlkes

Shouldn't it be??

querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"




glenn said:
Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with
the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB
FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to hard
code
a company name and successfully populate the second control but if I
attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 
G

Guest

Yes, I am sorry. I wrote my pseudo code incorrectly. Your syntax
is the way my code actually reads if you follow down through my
question.

So, no it seems that it still does not work when a variable is passed.
I think it might be deeper than just the SQL statement so read on
if you can.

Thanks,
glenn

ShaneFowlkes said:
Shouldn't it be??

querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"




glenn said:
Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with
the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB
FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to hard
code
a company name and successfully populate the second control but if I
attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 
G

Guest

Found what seemed to be in err in my SQL statement that passes a variable
but the change still did not work.

Here it is:
queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "') AND ([users].[project] = '" &
Session("project") & "'))"



glenn said:
Yes, I am sorry. I wrote my pseudo code incorrectly. Your syntax
is the way my code actually reads if you follow down through my
question.

So, no it seems that it still does not work when a variable is passed.
I think it might be deeper than just the SQL statement so read on
if you can.

Thanks,
glenn

ShaneFowlkes said:
Shouldn't it be??

querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"




glenn said:
Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with
the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB
FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to hard
code
a company name and successfully populate the second control but if I
attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 
G

Guest

It is bad practice to build your SQL queries this way as it leaves you code
vulnerable to SQL injection exploits. You should use parameters in your SQL
stament such as

"SELECT field1, field2, field3 from table1 where field3 = @ParameterName"


glenn said:
Found what seemed to be in err in my SQL statement that passes a variable
but the change still did not work.

Here it is:
queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "') AND ([users].[project] = '" &
Session("project") & "'))"



glenn said:
Yes, I am sorry. I wrote my pseudo code incorrectly. Your syntax
is the way my code actually reads if you follow down through my
question.

So, no it seems that it still does not work when a variable is passed.
I think it might be deeper than just the SQL statement so read on
if you can.

Thanks,
glenn

ShaneFowlkes said:
Shouldn't it be??

querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"




Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with
the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB
FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to hard
code
a company name and successfully populate the second control but if I
attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 
J

Jeff Dillon

Response.write your sql statement. We have no way of knowing the values of
your variables

Jeff
glenn said:
Found what seemed to be in err in my SQL statement that passes a variable
but the change still did not work.

Here it is:
queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "') AND ([users].[project] = '" &
Session("project") & "'))"



glenn said:
Yes, I am sorry. I wrote my pseudo code incorrectly. Your syntax
is the way my code actually reads if you follow down through my
question.

So, no it seems that it still does not work when a variable is passed.
I think it might be deeper than just the SQL statement so read on
if you can.

Thanks,
glenn

ShaneFowlkes said:
Shouldn't it be??

querystring="SELECT * FROM USERS WHERE CNAME = '" & var & "'"




Hi folks,

The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.

This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"

This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"

I have 2 DropDownList controls.

The first control is populated with a dataset of company names.

The second control is populated with a dataset of contact names for
the company that was selected in the first control.

The first control has an OnSelectedIndexChanged event handler.

The first control has a datasource set equal to a function named
GetCompanyNames.

The second control has a datasource set equal to a function named
GetContactNames.

In the OnSelectedIndexChanged event handler, I call the
GetContactNames
function to
populate the second control with Contact Names that are associated
with
the
Company
Name selected in the first control.

All is working well, but the Contact Names in the second control do
not
change as
different companies are selected in the first control.

What follows is a pseudo code description of my code:

' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()

' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub

' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS
WHERE
PROJECT = @PROJECT"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsCompanyNames, "users")

Return dsCompanyNames
End Function

' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO
MDB
FILE"
Response.Write("coName = " & coName)

Dim queryString As String

queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] =
'" &
Session("project") & "'))"

' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"

Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)

dataAdapter.Fill(dsContacts, "users")

Return dsContacts
End Function

In the GetContactsFromCompanyForThisProject function, I am able to
hard
code
a company name and successfully populate the second control but if I
attempt
to pass the variable coName to the query string, it will not switch
the
contact names for each company that is selected in the first control.

The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged"
/>

<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>


Any replies would be extremely appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,054
Latest member
TrimKetoBoost

Latest Threads

Top