SQL String Error

[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 & "')"

data1.DataSource = bandsql
data1.Refresh

End Sub


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

Originally posted by Harrild

Private Sub cmdSearchBand_Click()

Dim bandsql As String

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

That should do the trick l think, will check it tonight, don't have vb on this PC

[Paul Warren]

try -

"Select * from cd_serial_numbers where band_name = " & chr$(34) & txtband.Text & chr$(34)

34 is the code for double quotes ("), it's nice and straight forward and works everytime. I haven't tested this though, no time. And isn't it recordsource that you should be updating ? Requery is what you need to refresh the data when you change the underlying SQL statement.

[Harrild]

I tried that Paul now the flexigrid doesnt refresh after entry of the SQL code.
Oh yeah i have changed it to query data1.recordsource but it still doesnt work.

[Gen-X]

Harrild

If only you had said that sooner

The error you are getting usually means that it isn't pointing to a database.

Now you may have changed the DataSource to be a new SQL string but that doesn't mean it is connected .

Check the following :

1. You are actually connected to a database
2. The "DatabaseName" is correct if its a Data control
3. The correct connection type is given
4. You can just use the TableName in DataSource first and it works
5. Make sure the "Table" actually exists in the database

Previously you only asked for the SQL to be debugged and I can assure you that the SQL is now correct... the error lies in how you are getting or maintaining your connection to the database and how it is configured (ie the table exists etc)

[Harrild]

ok Data1.databasename is correct
.recordsource is correct

the table exists.
the connection works cos the flexigrid displays all records from the table.

it just doesnt want to display the search results on the flexigrid

here's the code
private sub searchband_click()
Dim bandsql As String

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

bandsql = "Select * from cd_serial_numbers" - this is just to see if the grid takes the data but it doesnt seem to work.

Data1.RecordSource = bandsql

'Set msflexigrid1.DataSource = Data1 - do i need this?

Data1.Refresh - do i need this?
msflexigrid1.Refresh - or this?

End Sub

[Harrild]

Hello?
ok as im here i tell you what happens..the f**king data control wont refresh at all.

the last command - data1.refresh doesnt work....it says it cant find the table.

[Psyrus]

Harrild,

Is this an Access DB? If so have you tried running a Query in SQL View with your SQL statement? Does it return anything? Make sure you hard code a good value in place of the txtBand.text .

[Harrild]

yeah its and Access 97 DB. ok i'll try that.

[Harrild]

i've now tried that and then i copied the code over and i think ran it (after editing it of course) but it still doesn't
This is what my code looks like now;

Dim bandsql As String
'bandsql = "SELECT [CD SERIAL NUMBERS].* From [CD SERIAL NUMBERS]WHERE ((([CD SERIAL NUMBERS].[Band Name])=" & txtband.Text & "));" - this is the SQL from Access
bandsql = "SELECT * From CD SERIAL NUMBERS WHERE (Band_Name=" & txtband.Text & "" - this is the VB code (i think its right)
Data1.RecordSource = bandsql
Set msflexigrid1.DataSource = Data1
Data1.Refresh

it says 'object required' when itry to set the msflexigrid.datasource to Data1 - do i need it?

ok i disbale that and now it says i got a SYNTAX error in the FROM clause while highlighting the DATA1.REFRESH line.

[Psyrus]

Do you also get that error when you remove the Set keyword?
Like this:

msflexigrid1.DataSource = Data1


Just a thought.

[Harrild]

Yeah i still got the error when i removed the SET command

[G.Kumaraguru]

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

This should definitely work. :-)

[Edited by G.Kumaraguru on 07-15-2000 at 03:50 AM]

[Harrild]

yep ok the SQL is fine now i have the problem of refreshing the data on the MSFLEXGRID
data1.refresh doesnt work cos it cant find the table cd_serial_numbers

This is a Microsoft bug man. Honestly, (waiting for some one to disagree). The Refresh option sucks. Use code to build the data display in the grid.

Just a suggestion...

[Gen-X]

If it says it cannot find the table I think it means it cannot find the table "Select * from cd_ser......" instead of not being able to find the table "cd_serial_numbers".

I am wondering if you should use the "filter" attribute for the SQL string and JUST the name of the table in the RecordSource....

The reason for the "object required" error is that you are using a SET command and haven't actually initialized one of the items you are using. SET is like a pointer... it tells one variable to "point" to the same thing the other structure is. If you don't have the structure it is pointing to set up correctly then you get that error

[Harrild]

ok i dropped the DAO option and i am now using ADO with a datagrid and adodc its all set up and everything but now the connection doesnt work.
I did the same at school with the same provider and everything (cept the path of the db) and it worked at school. What is wrong with it?

Data link error when i try and test the connection.
'Test connection failed because of an error in intialising provider. Unspecified error..

What does this mean?

oh and this is my search code now.
Dim search As String
Dim band As String
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String

On Error GoTo damn


band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)

search = "select *"
search = search & "from cd_serial_numbers"

If Len(band) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(band_name ='" & txtband.Text & "')"

End If

If Len(album) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(album_name ='" & txtalbum.Text & "')"

End If

If Len(genre) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(genre ='" & txtgenre.Text & "')"

End If

If Len(cdtype) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(single/album ='" & txttype.Text & "')"

End If

If Len(released) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(released ='" & txtreleased.Text & "')"

End If
If Len(company) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(company ='" & txtcompany.Text & "')"

End If

If Len(serial) Then
If blnsearch Then
search = search & "or"
Else
search = search & "where"
blnsearch = True
End If
search = search & "(serial ='" & txtserial.Text & "')"

End If


Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

damn = MsgBox("Something has gone wrong (duh)!")