Visual Basic Forum for Visual Basic Programmers VB

[P.T.A.M.]

Since we don't have the file attachments mod, you can't put it here... I guess you don't have a website? I was thinking if you have a website, just upload it to the server...
_________________
No one is completely useless. They can at least be an example of what to avoid.

[jhaycutexp]

i have onced encountered such errors... everything looks right but still theres error.. i've consulted many classmates that our knowlegeble in sql and even professors about my error.. i showed to them.. but all replied.. "looks right??.. i dunno?? try different codes..??" that was their reply.. then once i was sleeping... i dunno but when some problems are in my way.. i seem to code them to my head.. and still i can't think of a solution.. then when i wake up.. i turned on my computer and i retyped the codes... the same codes that was having the error.. then the thing is that it worked... so in short... maybe you should just retype the codes.... ei it worked for me.. = ) maybe it would work also to you... gudluck... = )

[bdannels]

P.T.A.M./Doug:

They say is it aint one thing, it's another.
I had a lightning strike come through my cable modem at the office.
Fried everything but the hard drives. I guess that's what they call LUCKY
Spent the last 3 days getting up and running again. Anyway, I got the code/program on a web page to download.

URL: [link]

File Name: StripedDownCode.zip

I have included a readMe file in the ZIP.

As before, any input will be appreciated.

Bill

[dougthomas]

Hi Bill,

Sorry for the delay in getting back to you. I've had a look at the code and am still confused ! Could you look in the Database Table and see exactly what is being stored in the [Callback] column and also how it is defined to the Table (ie what's its type?) please.

Looking at the code, in the cmdSortByCreated routine you have a filter on the recordset "Callback=1" yet in cmdSortByCallback you're looking for "Yes" in the Callback column. (I'm taking a big leap here, but I think it's going to be one or the other)

Assuming that cmdSortByCreated is doing what you want, perhaps the SQL in cmdSortByCallbackshould be looking for 1.

eg
strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = 1"

I think that's about the only combination we haven't tried yet !!

Regards
Doug
_________________
If you can see the light at the end of the tunnel, it probably means there's a Train coming.

[bdannels]

Hello Doug:

Glad to see your back. I never got any other suggestions after I uploaded the code.

>Could you look in the Database Table and see exactly what is being stored in the [Callback] column and also how it is defined to the Table (ie what's its type?) please.

I have changed this 2 or 3 times, I am not sure at this point if there is a better way to set it up or not? My last thought was to have it as a VAR.

Any input on that.

Later
Bill

[dougthomas]

Hi Bill,

Not sure it really matters as long as you know what it is and therefore what sort of value to check for.

If the code in cmdSortByCreated is working then you should change cmdSortByCallback to look for a 1 rather than "Yes". If you look at the contents of table proofrequest under field Callback you will see exactly what is being stored (either "Yes", "No", 1 or 0 is my guess, it might even be True or False) that "type" (ie Number or String) is what you should be looking for in cmdSortByCallback.

I reckon that strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = 1" in cmdSortByCallback will work. (How's that for confidence!!!)

Have a go and let me know

Regards
Doug
_________________
If you can see the light at the end of the tunnel, it probably means there's a Train coming.

[bdannels]

Doug:


>Have a go and let me know

Things from my JOB'S are getting in my way
will try to get back on track tonight and check it out.

Thanks for the input
Bill

[bdannels]

Doug:

Still getting a syntax error

callback = 0 is what is being stored in the database

------------------------------------
Field = CallBack

Type = varchar(5)

Collation = latin1_swedish_ci (I have no idea what this is)

Attributes = Blank

Null = Yes

Default = NULL
-----------------------------------------------------------------
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] = 1"

'strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = " & c39 & "YES" & c39

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

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

Adodc1.RecordSource = strQuery
Adodc1.Refresh
'Adodc1.Requery
End Sub
----------------------------------------------------
Not sure what to say or do at this point, other that the fact is
this can't be that hard.

Later
Bill

[dougthomas]

Hi Bill,

Right, we know that CallBack is defined as a variable length string (that's the Varchar(5) bit) so that means we have to have quotes round its value in the SELECT statement.

I've been applying the little grey cell and trying to logically work out where it's going wrong. The following occured to me:

1. If you cut and paste the statement into the MySQL command Line it works.
2. The problem SQL statement is the only one in the whole project that uses a WHERE clause.
3. The problem statement is the only one in the whole project that has a literal.
4. We've tried every combination of single and double quotes round the literal.
5. Other SQL statements work fine.

So, apart from the differences we know about, what else is different. The only thing I can think of is that when you cut and paste the statement into the MySQL command line you'd have to put a semicolon at the end, so it would get executed. That's about the only thing we haven't yet tried, vis:

strQuery = "SELECT * FROM proofrequest WHERE CallBack = '1';"

According to the ANSI standard all SQL statements should be terminated by a semicolon but all the databases I've come accross (Access, Oracle, Sybase etc) work without it. It might be that "easy"!

Regards
Doug
_________________
If you can see the light at the end of the tunnel, it probably means there's a Train coming.

[bdannels]

Doug:

Still getting the same error message

------------------------------------------------------------------------
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server for the right syntax to use near 'SELECT* FROM proofrequest
WHERE CallBack='" at line 1
------------------------------------------------------------------------

I have a program called "SQLyog" that lets you work with MySQL databases
I used the line below in the program and it works just fine, of course
it will NOT work with VB6. Not sure why, just passing on what little input
I have.

select * from `roll_test`.`proofrequest` WHERE CallBack = '1'

It works with, and without the ";"


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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 = '1';"

'strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = 1"

'strQuery = "SELECT * FROM proofrequest WHERE [CallBack] = " & c39 & "YES" & c39

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

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

Adodc1.RecordSource = strQuery
Adodc1.Refresh
'Adodc1.Requery
End Sub