Solved

Use of  Variables when executing SQL Statements in Visual Basic.

Posted on 2004-09-13
5
577 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
[X]
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
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

749 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