Ok, SQL gurus

I'm using the data environment designer. I used the SQL builder to generate an SQL that will return a record based on user-selected input (parameter) The SQL should do the following: My program has a global string variable called gloCurrProjNum. Based on which project number selected, the SQL should return the specific record. So far I have used:

SELECT * FROM project WHERE ProjNum = [gloCurrProjNum]

The SQL does not generate any records. What am I doing wrong? Any suggestions would be appreciated. Thanks in advance.

Where (How) does the user input the project number? List box? text box?

This is how the users are selecting the project Num. They are using a datacombo box. The msgbox is just to verify that i'm passing the right parameter (it is).


private Sub DataCombo1_Click(Area as Integer)
If Area = dbcAreaList then
gloCurrProjNum = DataCombo1.Text
MsgBox gloCurrProjNum
End If
End Sub

I use ADO, myself. You could try something similar to the following, assuming you have an active connection already. Does this help, or am I way off base, here?

Private Sub cmdDelete_Click()

gloCurrProjNum = CLng(DataCombo1.Text)

Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "SELECT * FROM project WHERE ProjNum = " & gloCurrProjNum
objCmd.CommandType = adCmdText
objCmd.Execute
End Sub

try
"SELECT * FROM project WHERE ProjNum = [" & gloCurrProjNum & "]"



Special thanks to Lothar "the Great" Haensler, Tom Archer, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

This problem is killing me. If I manually type in a project number in the "value" text box in the parameters tab, The code works fine. The text boxes populate with all the data from the database. If i input [gloCurrProjNum] in the "value" text box in the parameters tab, i got nothing! Any other suggestions?

objCmd.CommandText = "SELECT * FROM project WHERE ProjNum = '" & gloCurrProjNum & "'"

Iouri Boutchkine
[email protected]

You only need the single quotes if gloCurrProjNum is a text string. I converted it to a Long Int first, so you don't need the single quotes.

Hmm, I've noticed that you aren't doing anything with the value of the combo. Where's the code that refreshes the datasource? The datasource won't refresh when the value changes, you will have to do this yourself. For the SQL statement, it look alright, I don't think you must look for the problem there. If the SQL was generated by the dataenvironment, it should work, also when you add a value in the value property of a parameter, it works fine, which makes me even more sure that the problem isn't with the SQL

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

if the project number is a number then
"SELECT * FROM project WHERE ProjNum = " & val(gloCurrProjNum)
if the project number is a string then
"SELECT * FROM project WHERE ProjNum = '" & gloCurrProjNum & "'"