help needed in SQL Statement

[hayk]

Hello.
I need to develop one application.
The code is as follows
Private Sub cboCity_Click(Area As Integer)
Dim strCatalog As String: strCatalog = "e:\data\stat\dao"
Dim strTable As String: strTable = "db_field.dbf"
Dim strTable_1 As String: strTable_1 = "value_f.dbf"
Dim sSQL1 As String
Dim strConnect As String
strConnect = "Provider=MSDASQL.1;" & "Persist Security Info=False;" & "Data Source=dBASE Files;" & "Initial Catalog=" & strCatalog
Set m_cn_1 = New ADODB.Connection
With m_cn_1
.ConnectionString = strConnect
.Open
End With
Dim strSQL1 As String
strSQL1 = "SELECT value_f.V_NAME "
strSQL1 = strSQL1 & "FROM value_f, db_field WHERE db_field.ID=value_f.VF_CODE and db_field.FIELD_N ='" & cboCity.BoundText & "'"
Set m_rs_1 = New ADODB.Recordset
With m_rs_1
.ActiveConnection = m_cn_1
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strSQL1
End With
Set cboCity1.DataSource = m_rs_1
Set cboCity1.RowSource = m_rs_1
Set m_rs_1 = Nothing
End Sub
This application should get the boundtext from db_field.dbf and throw corresponding field of db_field.ID i.e. value_f.V_NAME into cboCity1
I guess that something is wrong with my sql statement.
Any help will be appreciated
Hayk

[Pasvorto]

See if this helps:

strSQL1 = "SELECT value_f.V_NAME "
strSQL1 = strSQL1 & "FROM value_f, db_field WHERE db_field.ID= '" & value_f.VF_CODE & '"
strSQL1 = strSQL1 & " and db_field.FIELD_N ='" & cboCity.BoundText & "'"

[jxiong]

your text from the combo box. Also if you want to fool prove,
you might even have to trim the one from the database so
that you are comparing "string" = "string" and not
"string" = "string ". Also you might want to
conside converting to specific cases, ie. upper just for
comparing. Of course, this only applies to char fields.

[SkiNLaB]

put [vb*code]some code[/vb*code]

tags around ur code! (without the stars of course)

[hayk]

Thanks for all of your replies, but I can't get it right every time an error occurs and one parameter is missing. I tried it with one table and it worked. I think that something is wrong with the join statement.
And I forgot to say that all fields are of string type.

[PilgrimPete]

Your original SQL statement looks OK to me.

However, are you using a DataCombo control?
If so, you need to set the ListField property (and I think the BoundField property too).

[swatty]

if the relation between them tables is id an vf_code then change the string to

strSQL1 = "SELECT value_f.V_NAME "
strSQL1 = strSQL1 & "FROM value_f Inner join db_field ON db_field.ID=value_f.VF_CODE Where db_field.FIELD_N ='" & cboCity.BoundText & "'"

You have to specify the relation between them tables with a join statement.

Join can be used differently (Right,Left, Inner,..)

[PilgrimPete]

swatty, your JOIN syntax is equivalent to the join in the original post.
Same thing different syntax.

[swatty]

I like to keep the join statement seperated from the where clause.

This is more readable and maintainable. (my opinion)

[PilgrimPete]

Fair point - and obviously personal preferences count for a lot.
I too prefer JOIN syntax, but not all RDBMS support the same level of JOIN syntax.

[jxiong]

strSQL1 = "SELECT V.V_NAME"
strSQL1 = strSQL1 & " FROM value_f as V, db_field as F "
strSQL1 = strSQL1 & " WHERE F.ID=V.VF_CODE and "
strSQL1 = strSQL1 & " F.FIELD_N ='" & cboCity.BoundText & "'"

Real example without any junk ticks:

SELECT V.V_NAME
FROM Value_f as V,
db_field as F,
WHERE F.ID = V.VF_CODE
AND F.FIELD_N = 'your value goes here';

Generaal Syntax:
SELECT A.fielddname, B.fieldname
FROM table1 as A, table2 as B
WHERE A.columnX = B.columnY
AND B.columnZ = NumberValue; should work!

MSGBOX out and make sure that your SQL is good--meaning
having the proper space between each word/syntax.

Also, check out SQL syntax on the WEB/Search engine for
more info

[hayk]

Well let me tell you what I got after experimenting a little bit. I have different field called F_VCODE1 in db_field. After I changed FIELD_N in db_field to F_VCODE1, the SQL worked. The code is much similar to the one posted by swatty. I got it generated by MSSQL.
strSQL1 = "SELECT value_f.V_NAME "
strSQL1 = strSQL1 & "FROM db_field LEFT OUTER JOIN value_f ON db_field.ID = value_f.VF_CODE "
strSQL1 = strSQL1 & "WHERE db_field.FIELD_N = '" & cboCity.BoundText & "'"
But why is it so?
The fields are of the same type, but SQL works with F_VCODE1 and not with FIELD_N. I tried it with 3 more fields in db_field, but the code worked only with F_VCODE1.

[swatty]

Can you show the structure of the table and its contents ?

It has to do with the join statement the left outer join will give all records from the left table and only the ones from the right where there is a matching record on the left table.

If you specify a where clause on the right table but no relationed record is found on the left then there are no records returned.

[hayk]

Find the tables attached
Thanks again

[swatty]

Like I said the join does the trick

"SELECT value_f.V_NAME
FROM db_field INNER JOIN value_f ON db_field.ID = value_f.VF_CODE
WHERE ((db_field.FIELD_N)='" & cboCity.BoundText & "')"

This works for me

[hayk]

Doesn't work for me.
Seems that there are no errors but the result is empty.
I put a datagrid and set its datasource to final recordset and it stays empty too. It shows the field name V_NAME and that's all.

[swatty]

You have to select a value where there is a related record in value_f

This is not so for all records of db_field

[hayk]

That's what I do.
I select the second record from db_field.FIELD_N, which has ID = 2,
value_f.VF_CODE has 2 values in it, which means that it shoud populate the datagrid or combo with values from value_f.V_NAME that have VF_CODE=2 Right?
But nothing happens.

Can you look at the code if I send you it?

[swatty]

Yes i can but can you first check what is in the boundtext of the combo.

I think this is the id and not the value of it.

If it is the id then you can simplify the select statement to this

"SELECT V_NAME FROM value_f  
WHERE ((VF_CODE)='" & cboCity.BoundText & "')"

Can you try this out first.

If not working can you
tell me what is in cboCity.BoundText when you debug.print it before the string is populated with it.

[hayk]

The query you have suggested works OK. I can get all the fields if I work with one table. But with the second one it stucks.

[swatty]

The second will work if you change the where statement to

WHERE ((db_field.Id ='" & cboCity.BoundText & "'))"

Because the boundtext is the id of the record you must check against the id and not against the value of Field_N

[hayk]

No I have FIELD_N in the boundtext, because I have it loaded in the Form_Load.
I would be very grateful if you could look at the Project and send it to [email protected]
Thanks again

[swatty]

The fonts are not the same and therefore you will not get any good result it would be better to set the boundtext to the id field and sort on that