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.Connectio nString = "Data Source=(my.Serv er;Initial
Catalog=MyDB;Pe rsist 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(filte rresults) Then
filterresults.F ill(contactsres ult)
filterresults.D ispose()
End If
Me.ContactsData GridView.DataSo urce = 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]

<R.*****@gmail. comwrote in message
news:3e******** *************** ***********@t1g 2000pra.googleg roups.com...
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.Connectio nString = "Data Source=(my.Serv er;Initial
Catalog=MyDB;Pe rsist 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(filte rresults) Then
filterresults.F ill(contactsres ult)
filterresults.D ispose()
End If
Me.ContactsData GridView.DataSo urce = 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]

On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote in message

news:3e******** *************** ***********@t1g 2000pra.googleg roups.com...
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.Connectio nString = "Data Source=(my.Serv er;Initial
Catalog=MyDB;Pe rsist 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(filte rresults) Then
filterresults.F ill(contactsres ult)
filterresults.D ispose()
End If
Me.ContactsData GridView.DataSo urce = 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]

<R.*****@gmail. comwrote in message
news:71******** *************** ***********@q77 g2000hsh.google groups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote in message

news:3e******** *************** ***********@t1g 2000pra.googleg roups.com...

<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]

<R.*****@gmail. comwrote in message
news:71******** *************** ***********@q77 g2000hsh.google groups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote 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]

On Jan 1, 4:23 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote in message

news:71******** *************** ***********@q77 g2000hsh.google groups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote 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 &"'"

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]

<R.*****@gmail. comkirjoitti viestissä
news:24******** *************** ***********@w56 g2000hsf.google groups.com...

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]

<R.*****@gmail. comwrote in message
news:24******** *************** ***********@w56 g2000hsf.google groups.com...

On Jan 1, 4:23 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

><R.Ra...@gmail .comwrote in message

news:71******* *************** ************@q7 7g2000hsh.googl egroups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.c omwrote:

<R.Ra...@gmail. comwrote 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 &"'"

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...uickid=0211121
Take the error message and past it into Google. I am sure you'll see a
solution to your problem.

>Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.

[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)

>>Dim sconn As New SqlConnection()
sconn.Connecti onString = "Data Source=(my.Serv er;Initial
Catalog=MyDB; Persist Security Info=True;User ID=sa;Password= Mypass"

>>Dim filterresults As New SqlDataAdapter( "Select * from contacts ", sconn)

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.

>>Dim contactsresult As New DataSet("result ")
If Not IsNothing(filte rresults) Then

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.SelectComman d.Parameters.Cl ear()
'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.SelectComman d.Parameters.Ad d(New SqlParameter("@ Variable1",
TheCityField))
da.SelectComman d.Parameters.Ad d(New SqlParameter("@ Variable2",
TheTypeField))

>>filterresults .Fill(contactsr esult)

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

>>filterresults .Dispose()

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

>>End If

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

Me.ContactsData GridView.DataSo urce = 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]

On Tue, 1 Jan 2008 04:23:40 -0800 (PST), R.*****@gmail.c om wrote:

>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.Connectio nString = "Data Source=(my.Serv er;Initial
Catalog=MyDB;P ersist 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(filte rresults) Then
filterresults.F ill(contactsres ult)
filterresults.D ispose()
End If
Me.ContactsData GridView.DataSo urce = 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.