Visual Basic Forum for Visual Basic Programmers VB

[bdannels]

I'm using VB6 with a ADO datagrid that has a ODBC connection to our MYSQL database that is being stored on our web site.

We are trying to sort the datagrid with the code you see below. I cut and pasted the select statement into the MYSQL Admid. and works.

Problem is I can't make it work in our VB6 code.

Any help will be GREATLY appreciated.




Private Sub cmdSortbyCallBack_Click()
Dim strQuery As String
strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = 'YES'"
Adodc1.RecordSource = strQuery
End Sub

[dougthomas]

Hi,

Don't know if you've solved this one yet but you need to do

Adodc1.Refresh

after the Adodc1.RecordSource = strQuery

this will refresh the data according to the SQL in the .RecordSource property. As it stands you've set everything ready to go, but haven't fired the starting pistol !! =D

Regards
Doug

[bdannels]

Doug:

I added the line you suggested and I got 2 error messages:

You have an error in your SQL syntax near 'SELECT * proofrequest WHERE [CallBack] = 'YES" at line 1

Run time error '-2147217900 (80040e14) Method 'Refresh' of object 'IAdodc' failed.

Any other suggestions would be appreciated.

Thanks for your time
Bill

==================================
Private Sub cmdSortbyCallBack_Click()
Dim strQuery As String
strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = 'YES'"
Adodc1.RecordSource = strQuery
Adodc1.Refresh

End Sub
===================================

[dougthomas]

Hi again,

The second error is as a result of the first. ie. the syntax error in the SELECT statement. It looks as if it's to do with the Quotes round the YES if you look it's parsed it as 'YES". I *always* get this wrong, to the extent that I never use single quotes in SQL. It takes a few extra lines of code but my solution would be:

[bdannels]

Doug:

I really appreciate your help on this. I have 2 or 3 other sort problems that I am trying to get through but lack the knowledge.

I tried you updated code and got the same error messages. I thought it was because ("YES") there still was double quotes on both sides of YES. I removed them and it still dosen't work I am still clueless at this point

Thanks again
Bill

Private Sub cmdSortbyCallBack_Click()
Dim c34 As String
Dim strQuery As String
c34 = Chr(34) ' a Double Quote Charatcer
strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = " & c34 & "YES" & c34
Adodc1.RecordSource = strQuery
Adodc1.Refresh
End Sub

[dougthomas]

Hi Bill,

I'm now confused !!

It looks OK from here, the actual SQL statement you are giving the Database looks like:

SELECT * FROM proofrequest WHERE [Callback] = "YES"

If you're still getting a syntax error in the SQL statement, the only thing I can think of is the "[" brackets around the Field Name. I can only suggest you remove them and try again !

ie.
sqlQuery = "SELECT * FROM proofrequest WHERE Callback = " & c34 & "YES" & c34

or even:
sqlQuery = "SELECT * FROM proofrequest WHERE proofrequest.Callback = " & c34 & "YES" & c34

Regards
Doug

[bdannels]

Doug:

If you think you are confused, imagine how I feel!!

I tried both all 3 again, just remed 2 out each time and tried the other one. same error message

I have been thinking all along "It can't be the code, BUT I don't know what else to do. I have posted this on 2 other forums. You are the only one who gave me a decent reply.

I am VERY open to any suggestions!

Thanks again for your time

Bill

======================================
Private Sub cmdSortbyCallBack_Click()
Dim c34 As String
Dim strQuery As String
c34 = Chr(34) ' a Double Quote Charatcer
'strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = " & c34 & "YES" & c34

'SQLQuery = "SELECT * FROM proofrequest WHERE Callback = " & c34 & "YES" & c34

SQLQuery = "SELECT * FROM proofrequest WHERE proofrequest.Callback = " & c34 & "YES" & c34

Adodc1.RecordSource = strQuery
Adodc1.Refresh
End Sub

[dougthomas]

Hi Bill,

Try Adodc1.Requery rather than .Refresh (whilst I continue to scratch my head !)

Regards
Doug

[dougthomas]

Hi again Bill,

Just noticed that in the code you included you were using SQLquery rather than strQuery. Was that just a typo on the message ?

I've looked at a *very* simple MYSQL query I wrote years ago to provide a logon to a web site. I seem to remember having all sorts of trouble and I ended up with something like:

SELECT User.Password FROM User where User.UserName = "'" & strUser & "'"

(i.e. put the literal in single quotes rather than double (this was before I got totally frustrated with quotes in general!)

As one does, when all else fails, I've just read the Instruction Book (MYSQL Reference Guide) which says, and I quote " A string is a squence of characters surrounded by either single quotes "'" or double quotes """ (only the single if you run in ANSI mode)"...... my highlight. All the examples they give in the guide show literals in single quotes so I wonder if ANSI is the default mode.

I can't remember if you've tried it with single quotes yet or not. Perhaps changing the c34 to c39 i.e. Chr(39) is worth a go - can't think of anything else !!

One other thing, I note from your original question you are trying to sort. Once you get the SELECT going you'll have to add the ORDER BY clause for the field you want it sorted on. As the SQL stands you are (or hopefully, will be) just picking up all entries where CallBack = YES which will be in any order. Anyway we can worry about that once we can actually perform a SELECT !!

Regards
Doug

[bdannels]

Doug:

>Was that just a typo on the message ?
No, not sure where that came from. I saw it yesterday, changed it and tried it again, same results.

>Perhaps changing the c34 to c39 i.e. Chr(39) is worth a go.
Tried that also, still a NO GO.

I was trying some other code tonight and noticed that the datagrid was changing but when I went and looked at the MySQL database it was not being updated.

As I said I an very new at this but I thought when you move the datagrid to the another record it updated the database.

Not sure what to do again, at this point. I am trying to put together a simple database for my business that when someone on my website fills out a form it will send the information to the database and also send it in a Email to the office.

I have it doing that OK, but I can't do anything with the data because I can't make it sort. I am trying to add a few checkboxes to keep track of the information added to the form pages. Boxes to check if they need to be called back, if the form/request becomes a order or if they require sample/information to be sent to them.

Anyway, what do you think about setting up a test database on one of host servers and getting someone to look/test the code. I saw a box on one of the forums where you could look for a coder. Not sure if it was from that forum or if it was from something like Rent a Coder. This can't be that hard.......... I think it's more like I have done something stupid and I can't see it.

Thanks again for your time, I really appreciate it!!

Bill

[dougthomas]

Bill,

Set up the test Database and, if you want, i'll play with it if you let me have your code. It's become a challenge now - no bit of software beats me !!!! and I guess i'm going to learn something. I'm semi-retired so I have plenty of time available.

Regarding update of the Database. It depends entirely on the code. normally if you're scrolling through records they won't get updated, you have to deliberately tell it to update.

As for "silly" coding errors, they're only silly once you've found them.

Regards
Doug

[P.T.A.M.]

Moved to the database forum.
_________________
No one is completely useless. They can at least be an example of what to avoid.

[bdannels]

Doug:

I really appreciate that offer.

>It's become a challenge now.
Glad to hear that.......... it's been a challenge for me for the last 6 months.
I think it's what they call a " learning experience "

>I'm semi-retired so I have plenty of time available.
I am working 2 jobs trying to get this business going and this program has been taking up most of my spare time, and leaving me with less hair.

I guess at this point I should zip and email you the code. Not sure if it is listed in the profile or not, will have a look see. Will set up a test database after work today.

LET THE FUN BEGIN.............

Thanks again
Bill

[P.T.A.M.]

Maybe it would be better if you could upload the project somewhere so more people can see it and maybe help you?
_________________
No one is completely useless. They can at least be an example of what to avoid.

[bdannels]

Doug:

Sounds like a plan to me.

At this point you are pushing the limits of my upload file/share knowledge.

I am guessing I need to set up some type of FTP link/address.

I am checking my email from Job(1) can't do anything from here, will check it out this evining.

Thanks
Bill