Solved

Use of  Variables when executing SQL Statements in Visual Basic.

Posted on 2004-09-13
5
581 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Six Sigma Control Plans

635 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