SQL Strings

[Harrild]

Ok i got a button to execute a search and display the results on a flexigrid but VB doesnt like it for some reason.........Please help cos i have no idea where i have gone wrong.

Private Sub cmdSearchBand_Click()

Dim bandsql As String

bandsql = "Select * from cd_serial_numbers where (band_name ='" & txtband.Text & "')"

msflexigrid1.DataSource = bandsql
msflexigrid1.Refresh

End Sub


Could someone please tell me where i have gone wrong......

[Psyrus]

What is the exact error you are getting?

[Harrild]

The error that i get is
Run-Time Error '424'

Object Required

[Psyrus]

Do you have an ADO Data Control on the form?

[Harrild]

No there isnt a data control

So i link the flexigrid to the data control and then run the SQL string, yeah?

[Psyrus]

If you want the ADO data control you must add it from the Components. It's Microsoft ADO Data Control 6.0 and add the Reference to Microsoft ActiveX Data Objects 2.0 Library.

Set the DataSource of the Flexgrid to the Data Control and then set the RecordSource of the Data Control to your SQL statement and do your refreshes.

That should work, it's late...

[Harrild]

Ok now the flexigrid displays the whole table....but now the error comes up when i try to search for a record.

Same error as before
Run-Time Error '424'
Object Required

it must be the SQL string...

[Psyrus]

If it was the SQL string VB would give a Syntax error in SELECT statement.

Did the ADO Data Control test properly when you set it's properties?

Did you include the refernce to the Microsoft ActiveX Data Objects 2.0 Library?

[Harrild]

Ok the ADO Data Control tested fine
and i did include the reference to the ActiveX Data Object Controls 2.0

but i still get the same error......

[PaulLewis]

That error you get is I think because you are not using the DataSource property correctly.

The code you gave shouldn't work even after following the advice of Psyrus because DataSource is not a string. When I try your example I get a Type Mismatch error as I would expect.

If you plan to use the MSFlexGrid, then you must first put a "Data" control onto your form. Then set the Datasource property of the MSFlexgrid to the data object (it will appear in the dropdown for the DataSource property).

I think you have already done this. Next, you may change the RecordSource property of the Data object in code or at design time. You must then refresh the Data object to "run" the query.

that is,
Data1.RecordSource="Select * from Table"
Data1.refresh

You do not even have to touch the msf control. It will be updated automatically.

Note that you can also set the recordsource property to the name of a table or query as well.

that is,
Data1.RecordSource = "myTable"
Data1.refresh

This has the same effect as the first syntax.

If this still doesn't work or make sense, you might have to write more description of which line the error happens on and what the settings of the properties I have mentioned are.

Regards
Paul Lewis

[Harrild]

Ok i followed your instructions and got rid of the ADO stuff and just put the plain data control in...
I linked it all up and ran it. Tried to search for a band name...and up came an error.....

Run-Time error '3078'

Microsoft Jet Databases cannot find the table or query "". Please make sure it exists and that its name is spelt correctly....

now what?

[Harrild]

i am sorry i only saw PaulLewis's reply i tried your's Aldea now i just a problem of a syntax error which is nice.....It says

Syntax error in FROM clause

i think it is the Command Tyep in the recordsource tab of the adodc's properties. What should it be??

[Harrild]

None of these work for me.....

PLEASE HELP anyone else w/ a different idea

[CGTS]

Check out my reply to 'FLL' on a similar problem to yours. It is related to the data environment, however it is just the same if you have used ADO code.

http://forums.vb-world.net/showthread.php?threadid=22548

[Harrild]

i read FLL thread but i dropped the ADO cos i just couldn't get it to work. The problem lies in executing the SQL string onto the recordsource...

the exact problem is that the data control does not want to refresh after the sql has been executed on it.


C'mon there has got to be a simple answer for this.

[Edited by Harrild on 07-14-2000 at 07:50 PM]

[Psyrus]

Harrild,

I have had similar problems like this. That's why in my post I said do your refreshes. I tried refreshing everything until it worked, the data control, the grid and the form. One of them will work. I just wish I remembered how I did it in class.

PaulLewis

I didn't say to set the DataSource to the string, I said the RecordSource of the DataControl in my last post. But the previous posts kinda explain it alot better than I could.

[Harrild]

Thanks for your last post Psyrus...looks like i am going to spend all my school lunches and recesses at the computer rooms (yes i am doing this for a school assessment and yes its my major work for my final year computer exams - i am an accelerated student) trying to get this right but i'll still be posting.

If at anytime your remember how u did it could you post it here or email it to me cyharrild@yahoo.com

i got the everything to work cept the refreshing of the msflexigrid (doh!)

[Harrild]

my code now looks like this:

dim bandsql as string
bandsql = Select * from [cd_serial_numbers] where [band_name] = '" & txtband.Text & "'"
data1.recordsource = bandsql
data1.refresh

the last line doesn't work though.....doh!

[Psyrus]

Harrild,

Check your Yahoo email. I sent you a sample DAO project to try out. It should work without SP3.