ataripirate
asked on
Sql Statement Problem in VB6
I am having trouble with a SQL Statement.
I can get it to work with fields that have numeric values, but when I set up the fields that I need that contain text values, I get the following error:
"No Value given for one or more required parameter"
I hope I am just overlooking something small and quick to fix...
If you need more info just ask...
Thanks
Here is my code:
**** Code that causes error ******
**** Code that I need to work with text data ******
Function MyGetTestPhase()
Dim cnn As adodb.Connection
Dim rst As adodb.Recordset
Set cnn = New adodb.Connection
Set rst = New adodb.Recordset
Dim cmd As New adodb.Command
Dim objrs As adodb.Recordset
Dim strsql As String
dbSrc = "h:\Access\NT\NT_Cat_dbs.m db"
cnn.Open "PROVIDER=Microsoft.Jet.OL EDB.3.51;D ata Source=" & dbSrc
Set cmd.ActiveConnection = cnn
rst.Open "[Test Animals]", cnn
*****Database Field that is Text*******
NTValue = "NT945"
************************** ********** ***
strsql = "Select [Test Animals].[Test Number] " & _
"From [Test Animals], [Test Information] " & _
"Where [Test Information].NT = " & NTValue
Set objrs = cnn.Execute(strsql)
test = objrs![Test Number]
End Function
******* Code that works ****
******* Code that contains Numeric data ******
Function MyGetTestPhase()
Dim cnn As adodb.Connection
Dim rst As adodb.Recordset
Set cnn = New adodb.Connection
Set rst = New adodb.Recordset
Dim cmd As New adodb.Command
Dim objrs As adodb.Recordset
Dim strsql As String
dbSrc = "h:\Access\NT\NT_Cat_dbs.m db"
cnn.Open "PROVIDER=Microsoft.Jet.OL EDB.3.51;D ata Source=" & dbSrc
Set cmd.ActiveConnection = cnn
rst.Open "[Test Animals]", cnn
*****Database field that is numeric******
PhaseValue = 10
************************** ********** *****
strsq2 = "Select [Test Animals].[Test Number] " & _
"From [Test Animals], [Test Information] " & _
"Where [Test Information].Phases = " & PhaseValue
Set objrs = cnn.Execute(strsq2)
test2 = objrs![Test Number]
End Function
I can get it to work with fields that have numeric values, but when I set up the fields that I need that contain text values, I get the following error:
"No Value given for one or more required parameter"
I hope I am just overlooking something small and quick to fix...
If you need more info just ask...
Thanks
Here is my code:
**** Code that causes error ******
**** Code that I need to work with text data ******
Function MyGetTestPhase()
Dim cnn As adodb.Connection
Dim rst As adodb.Recordset
Set cnn = New adodb.Connection
Set rst = New adodb.Recordset
Dim cmd As New adodb.Command
Dim objrs As adodb.Recordset
Dim strsql As String
dbSrc = "h:\Access\NT\NT_Cat_dbs.m
cnn.Open "PROVIDER=Microsoft.Jet.OL
Set cmd.ActiveConnection = cnn
rst.Open "[Test Animals]", cnn
*****Database Field that is Text*******
NTValue = "NT945"
**************************
strsql = "Select [Test Animals].[Test Number] " & _
"From [Test Animals], [Test Information] " & _
"Where [Test Information].NT = " & NTValue
Set objrs = cnn.Execute(strsql)
test = objrs![Test Number]
End Function
******* Code that works ****
******* Code that contains Numeric data ******
Function MyGetTestPhase()
Dim cnn As adodb.Connection
Dim rst As adodb.Recordset
Set cnn = New adodb.Connection
Set rst = New adodb.Recordset
Dim cmd As New adodb.Command
Dim objrs As adodb.Recordset
Dim strsql As String
dbSrc = "h:\Access\NT\NT_Cat_dbs.m
cnn.Open "PROVIDER=Microsoft.Jet.OL
Set cmd.ActiveConnection = cnn
rst.Open "[Test Animals]", cnn
*****Database field that is numeric******
PhaseValue = 10
**************************
strsq2 = "Select [Test Animals].[Test Number] " & _
"From [Test Animals], [Test Information] " & _
"Where [Test Information].Phases = " & PhaseValue
Set objrs = cnn.Execute(strsq2)
test2 = objrs![Test Number]
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strsql = "Select [Test Animals].[Test Number] " & _
"From [Test Animals], [Test Information] " & _
"Where [Test Information].NT = '" & NTValue & "'"