Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Use of  Variables when executing SQL Statements in Visual Basic.

Posted on 2004-09-13
5
576 Views
Last Modified: 2008-02-01
I have constructed my database in access and I've written a method to execute an SQL statement in VB to fill a list box. Dependent on what the user selects in the list box and also in  two text boxes, I then populate another list box with suitable gaskets again using an SQL statement.
 
I have tried to enter variables but it ask for parameters. I then define parameters but only one variable / parameter will work - the other parameter just doesn't register. What am I doing wrong? Is this the right way to proceed? OR am I being stupid. I dont mind insults
 
Regards
 
Gavin
 
Recordset As ADODB.Recordset
Dim connectstring As String
Dim sSQL As String
'Create a new connection to Novus Select
Set adoConnection = New ADODB.Connection
' Create a new Recordset
Set adoRecordset = New ADODB.Recordset
' Connection string to use when we open connection
connectstring = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = C:\Documents and Settings\SMITHG\Desktop\Novus Selector\Databases\Novus Selector.mdb"
adoConnection.Open connectstring
 
Dim Rs As ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim Param As ADODB.Parameter
Dim ParamT As ADODB.Parameter
Set Param = New ADODB.Parameter
Param.Name = "Fluid"
Param.Value = ComFluid.Text
Param.Type = adVariant
Cmd.Parameters.Append Param
Set Param = New ADODB.Parameter
Param.Name = "MaxT"
Param.Value = Val(TxtTemperature.Text)
Param.Type = adInteger
Cmd.Parameters.Append Param
 
 
sSQL = "SELECT Gasket.GasketName "
sSQL = sSQL & " FROM Fluid INNER JOIN(Gasket INNER JOIN GasketFluid ON "
sSQL = sSQL & " (Gasket.GasketID = GasketFluid.GasketID)"
sSQL = sSQL & " AND (Gasket.GasketID = GasketFluid.GasketID))"
sSQL = sSQL & " ON (Fluid.FluidID = GasketFluid.FluidID)"
sSQL = sSQL & " AND (Fluid.FluidID = GasketFluid.FluidID)"
sSQL = sSQL & " WHERE (((GasketFluid.MaximumTemperature)=250)"
sSQL = sSQL & " AND ((Fluid.FluidEnglish)=Fluid)) "
 
 
Cmd.ActiveConnection = adoConnection
Cmd.CommandText = sSQL
Cmd.CommandType = adCmdText
Set Rs = Cmd.Execute
LstGaskets.Clear
Do Until Rs.EOF
LstGaskets.AddItem Rs("GasketName")
Rs.MoveNext
Loop
0
Comment
Question by:Gavin_Stafford
5 Comments
 
LVL 7

Expert Comment

by:suramsureshbabu
ID: 12041900
0
 

Author Comment

by:Gavin_Stafford
ID: 12042039
Thank you this seems pretty useful and there's lots of other examples on the website to help as well
0
 
LVL 5

Expert Comment

by:vadivhere
ID: 12042739
The code contains some bugs, check the following lines
carefully,

Dim ParamT As ADODB.Parameter
Dim Param As ADODB.Parameter

Set Param = New ADODB.Parameter
Param.Name = "Fluid"
Param.Value = ComFluid.Text
Param.Type = adVariant
Cmd.Parameters.Append Param   <---- Wrong

Set Param = New ADODB.Parameter
Param.Name = "MaxT"
Param.Value = Val(TxtTemperature.Text)
Param.Type = adInteger
Cmd.Parameters.Append Param    <----- Wrong

You have two parameter variables param and paramT, which you wrongly append the same again one time. this is the wrong code you have done. Instead of Param, append ParamT then the second variable will added to the parameter object.

Cheers!!!!!!!!!!!!!!

PS : Hope, I bag the points..... :)
0
 

Author Comment

by:Gavin_Stafford
ID: 12042901
Well spotted but it still doesn't work

I've tried adding ParamT as follows:

Dim Param As ADODB.Parameter
Dim PAramT As ADODB.Parameter
Set Param = New ADODB.Parameter
Param.Name = "Fluid"
Param.Value = ComFluid.Text
Param.Type = adVariant
Cmd.Parameters.Append Param
Set ParamT = New ADODB.Parameter
ParamT.Name = "MaxT"
ParamT.Value = Val(TxtTemperature.Text)
ParamT.Type = adVariant
Cmd.Parameters.Append ParamT

I've also tried the following:


Dim Param As ADODB.Parameter
Set Param = New ADODB.Parameter
Param.Name = "Fluid"
Param.Value = ComFluid.Text
Param.Type = adVariant
Cmd.Parameters.Append Param
Set Param = New ADODB.Parameter
Param.Name = "MaxT"
ParamT.Value = Val(TxtTemperature.Text)
ParamT.Type = adVariant
Cmd.Parameters.Append Param

But neither works

Regards

Gavin

0
 
LVL 16

Accepted Solution

by:
suprapto45 earned 500 total points
ID: 12045443
Hi...Gavin,

Please check this out.

http://www.clarksoncs.com/Samples/ADOParamQ.htm
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
A short article about problems I had with the new location API and permissions in Marshmallow

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question