Query SQL using variables.

[R.Rafii]

Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated

 

[Mr. Arnold]

Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated


Dim var1 as string
Dim var2 as string
dim sqlstr as string

var1 = "hello"
var2 = "to me"


sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = " &var2

SqlDataAdapter(strsql, sconn)

if the data you needed was numeric, then you do this.

dim var1 as int
dim var2 as int

var1 = 9
var2 = 10


sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &" AND
TYPE = " &var2.ToString()

 

[R.Rafii]

Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated

Dim var1 as string
Dim var2 as string
dim sqlstr as string

var1 = "hello"
var2 = "to me"

sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = " &var2

SqlDataAdapter(strsql, sconn)

if the data you needed was numeric, then you do this.

dim var1 as int
dim var2 as int

var1 = 9
var2 = 10

sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &" AND
TYPE = " &var2.ToString()

Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?

 

[Mr. Arnold]

news:[email protected]...

<snipped>


Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

You can use the ToString() for the string data too.

any suggestions?
----------------


What you need to do is put a brake point on the line right after the build
of the strsql variable.

You then need to use the Quick Watch, copy the text of the string data that
was created for the SQL statement. You can right-click the variable to get
to Quick Watch.

If you have SQL Server on the machine and it is SQL Server 2000, then you
will start SQL Server Query Analyzer and past the SQL text into the pane,
highlight and execute it.

Or if it is SQL 2005 that you have, then you use the SQL Server Management
Studio, New Query, and past the text in the pane and highlight and execute
it.

Let SQL Server tell you what's wrong with the statement by using one of the
solutions above..

 

[Mr. Arnold]

<[email protected]> wrote in message

any suggestions?

Oh, you might have to do this that is put quotes around the variables, since
it's string data.

sqlstr = "Select * from contacts WHERE CITY = " &"'" &var1 &"'" &" AND TYPE
= " &"'" &var2 &"'"

 

[R.Rafii]

any suggestions?

Oh, you might have to do this that is put quotes around the variables, since
it's string data.

sqlstr = "Select * from contacts WHERE CITY = " &"'" &var1 &"'" &" AND TYPE
= " &"'" &var2 &"'"

Hey Arnold,
this time the string you gave me worked!
only now i get a different error which is my fault since I forgot to
say..

the second var, returns a text and symbol, for example (only - and + )
X-
X+
So the error I get now is:
Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.

I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?

 

[Teemu]

I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?

Try this:

sqlstr = "Select * from contacts WHERE CITY = '" & var1 _
& "' AND TYPE='" & var2 & "';"

I have to warn you that this kind of query is very dangerous because users
can include '-characters to the query and make SQL injections to your
database.

-Teemu

 

[Mr. Arnold]

Hey Arnold,
this time the string you gave me worked!
only now i get a different error which is my fault since I forgot to
say..

the second var, returns a text and symbol, for example (only - and + )
X-
X+
So the error I get now is:
Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.

I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?

I think you have to get a closing quote in there at the end of the string.
This link may help you. You should next look into Stored Procedure and
passing parms/variables to the Stored Procedure and executing it, when you
become more comfortable in what your doing.

http://www.code-magazine.com/Article.aspx?quickid=0211121


Take the error message and past it into Google. I am sure you'll see a
solution to your problem.

 

[Cor Ligthert[MVP]]

Rafii,

First try to use the right names for the right things. By instance, don't
talke about a datagrid while you use a datagridview in your code. Those are
in fact two complete different kind of grids.

To review your code (including your question)
Try to keep the name, this is not a filterresult, this is a construction of
a DataAdapter.

This can be including your where clause

Dim da As New SqlDataAdapter("Select * from contacts WHERE CITY=@VARIABLE1
AND TYPE=@VARIABLE2", sconn)

I have let it, however it is never right to use a dataname as "Type", I
would in your place give that in your database and in your code another
name.

The DataAdapter is never nothing, you have constructed it above therefore
this if is complete without any sence. Therefore you can remove it.

For selection in a select string it is for more than one reason good to use
parameters. It makes at least your code more visible and easy to handle.

da.SelectCommand.Parameters.Clear()
'The code above is in fact only needed all second times, however it does not
bother to place it in that instead an if or whatever more costly instruction
than the one above.

da.SelectCommand.Parameters.Add(New SqlParameter("@Variable1",
TheCityField))
da.SelectCommand.Parameters.Add(New SqlParameter("@Variable2",
TheTypeField))
this one I have changed in
da.Fill(contactresult) 'filterresult is in my idea a confusing answer it is
a result of the fill (at SQL side the name is ResultSet
This dispose does nothing, the dispose is there because it is inherrited
from components, but that does not mean that you should use it.
The End If above can go of course too away because of the removing of the if

Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

It would be better to set all this code inside a Try and Catch block,
however that would make it in my idea to confusing to tell it direct.

Cor

 

[Jack Jackson]

Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ?? could anyone help me out here?
Code sample would be GREATLY appreciated

Use Parameters to supply the values. Cor has given you some code to
use.

Parameters do two thing for you over just putting the values you need
in the Select statement. First they format the value - no need to add
quotes around text, or put dates into whatever format the database
needs. Second they prevent SQL injection, which malicious users can
use to make arbitrary changes to your database.

 

[Tom Shelton]

Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?- Hide quoted text -

- Show quoted text -

I suggest you look at Cor's reply. While you can use concatenation,
as Mr. Arnold suggested - it is VERY bad practice. It opens you up to
quoting issues, and more importantly security issues.

 

[Stephany Young]

While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather than
kicking them in the teeth for using a practice that some MIGHT consider to
be 'bad'.

Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?- Hide quoted text -

- Show quoted text -

I suggest you look at Cor's reply. While you can use concatenation,
as Mr. Arnold suggested - it is VERY bad practice. It opens you up to
quoting issues, and more importantly security issues.

 

[Tom Shelton]

While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather than
kicking them in the teeth for using a practice that some MIGHT consider to
be 'bad'.

That's exactly what I was doing. I was warning him of the potential
risks of using concatenation to form his query strings. I don't believe
my warning was of a dire or overly sentationalist nature. It is a bad
practice - even in "safe" environments.

 

[Mr. Arnold]

That's exactly what I was doing. I was warning him of the potential
risks of using concatenation to form his query strings. I don't believe
my warning was of a dire or overly sentationalist nature. It is a bad
practice - even in "safe" environments.

I don't consider it to be a bad practice. I have used them both over the
years Dynamic SQL statements and Stored Procedures. And for the OP to learn
the basics, I see nothing wrong with his approach. For simple pulling of
data from a database table, I don't see the harm.

 

[Tom Shelton]

I don't consider it to be a bad practice. I have used them both over the
years Dynamic SQL statements and Stored Procedures. And for the OP to learn
the basics, I see nothing wrong with his approach. For simple pulling of
data from a database table, I don't see the harm.

There maybe no harm in his particular case. But, why do you want to
teach someone a method that can be a potential security risk in another
context - especially without telling them so? To me it is the same as
warning people to use Option Strict On. It's not strictly necessary,
but it is a bad practice - except in rare circumstances. The OP could
just have easily used a parameterized query in this case (you don't have
to make a stored proc to take advantage of parameters). And not only
would he gain the advantage of not having to worry about proper quoting
and sql injection attacks - but might have gotten a little speed boost
if this query is executed multiple times - since sqlserver caches the
execution paths of parameterized queries, just as it does stored procs.

 

[R.Rafii]

Try this:

 sqlstr = "Select * from contacts WHERE CITY = '" & var1 _
 & "' AND TYPE='" & var2 & "';"

I have to warn you that this kind of query is very dangerous because users
can include '-characters to the query and make SQL injections to your
database.

 -Teemu

Hi Teemu,

Thanks, it works!

 

[R.Rafii]

Rafii,

First try to use the right names for the right things. By instance, don't
talke about a datagrid while you use a datagridview in your code. Those are
in fact two complete different kind of grids.

To review your code (including your question)


Try to keep the name, this is not a filterresult, this is a construction of
a DataAdapter.

This can be including your where clause

Dim da As New SqlDataAdapter("Select * from contacts WHERE CITY=@VARIABLE1
AND TYPE=@VARIABLE2", sconn)

I have let it, however it is never right to use a dataname as "Type", I
would in your place give that in your database and in your code another
name.


The DataAdapter is never nothing, you have constructed it above therefore
this if is complete without any sence. Therefore you can remove it.

For selection in a select string it is for more than one reason good to use
parameters. It makes at least your code more visible and easy to handle.

da.SelectCommand.Parameters.Clear()
'The code above is in fact only needed all second times, however it does not
bother to place it in that instead an if or whatever more costly instruction
than the one above.

da.SelectCommand.Parameters.Add(New SqlParameter("@Variable1",
TheCityField))
da.SelectCommand.Parameters.Add(New SqlParameter("@Variable2",
TheTypeField))


this one I have changed in
da.Fill(contactresult) 'filterresult is in my idea a confusing answer it is
a result of the fill (at SQL side the name is ResultSet


This dispose does nothing, the dispose is there because it is inherrited
from components, but that does not mean that you should use it.


The End If above can go of course too away because of the removing of the if

Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

It would be better to set all this code inside a Try and Catch block,
however that would make it in my idea to confusing to tell it direct.

Cor

Cor hi,
Truly your reply was more than I expected to receive.
I understand that there is more than one method to do things
And for sure, some ways are more corrective to certain environments
And certain features.
The truth is, I started off with VS2005 just 3 weeks ago
And I'm still experimenting. I have no programming experience
And I use mostly snippets that are provided with VS. Some are more
Easy to understand these are the ones I use.

I've seen the "Try and Catch" codes in some samples, but I didn't
figure them
Out yet.
As for ParamSQL I do think it's better but I still need to understand
how to work with it.. (Any references?)

Finally, security issue...
The program I build is for my own needs. That's the reason I installed
VS to start with. I needed a solution quick and fast. The bottom line
of the software is to insert contacts to database and then to be able
to filter them out and send them an email using predefined email with
outlook.
Still don't know how I will ever figure out how to send the email...
while collecting all the filter results
Still don't know how I will ever figure out how to debug the
application..
For example, how to start with clear combo boxes (which are data
bound) when the
Application kicks off...and why the first value that appear in the combo
box disappear after I choose another value...

So what I'm trying to say basically, is thanks for the tips ļ

 

[R.Rafii]

There maybe no harm in his particular case.  But, why do you want to
teach someone a method that can be a potential security risk in another
context - especially without telling them so?  To me it is the same as
warning people to use Option Strict On.  It's not strictly necessary,
but it is a bad practice - except in rare circumstances.  The OP could
just have easily used a parameterized query in this case (you don't have
to make a stored proc to take advantage of parameters).  And not only
would he gain the advantage of not having to worry about proper quoting
and sql injection attacks - but might have gotten a little speed boost
if this query is executed multiple times - since sqlserver caches the
execution paths of parameterized queries, just as it does stored procs.

Tom hi,
Do you have any refernces for how to use parameterized query?

 

[Stephany Young]

It wasn't the message I was referring to Tom. It was the degree of language.

'... it is VERY bad practice. It opens you up to quoting issues, and more
importantly security issues.'

'it is VERY bad practice' is a very negative phrase (like a kick in the
teeth).

'quoting issues' or more correctly, 'escaping apostrophes' is a bread and
butter coding technique that shouldn't even rate a mention.

'security issues' is too broad a phrase to go unqualified. Yes there is a
risk but degree of risk completely depends on the situation and no two are
ever the same.

Don't get me wrong, I am a fan of using good practices but I was never a fan
of inventing convoluted code purely to avoid using the 'dreaded GOTO.

As everybody is well aware, it doesn't take much for an innocuous phrase to
cause a thread to escalate into a flame war and the use of more appropriate
language can go an awful long way to avoiding those situations.

 

[Tom Shelton]

It wasn't the message I was referring to Tom. It was the degree of language.

'... it is VERY bad practice.  It opens you up to quoting issues, and more
importantly security issues.'

'it is VERY bad practice' is a very negative phrase (like a kick in the
teeth).

'quoting issues' or more correctly, 'escaping apostrophes' is a bread and
butter coding technique that shouldn't even rate a mention.

'security issues' is too broad a phrase to go unqualified. Yes there is a
risk but degree of risk completely depends on the situation and no two are
ever the same.

Don't get me wrong, I am a fan of using good practices but I was never a fan
of inventing convoluted code purely to avoid using the 'dreaded GOTO.

As everybody is well aware, it doesn't take much for an innocuous phrase to
cause a thread to escalate into a flame war and the use of more appropriate
language can go an awful long way to avoiding those situations.

I'm sorry if the tone and content of my warning displeased you. But,
I felt that it was important to point the OP to what is considered by
many to be a best practice method. You are correct in that building
dynamic queries in that way is not always a problem - but why not
learn and use the safe method right from the begining? And if you
want my honest opinion, using concatenation results in uglier and
harder to read code (especially as your queries become much more
complex).

I don't think this needs to turn into a flame war, nor does it have to
turn into a long drawn out thread. I think you and I have both made
our positions clear on the issue. Happy 2008 to you!