Solved

Use of  Variables when executing SQL Statements in Visual Basic.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
How to share My.Settings in multiple application instances ? 7 79
Oracle programming for starter 14 76
T-SQL Query 9 37
Notepad++ Question 11 28
This article will show, step by step, how to integrate R code into a R Sweave document
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 …

752 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