Solved

Use of  Variables when executing SQL Statements in Visual Basic.

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now