| Author |
 Topic  |
|
MaGraham
Senior Member
USA
1297 Posts |
 Posted - 05 July 2012 : 11:09:44
|
I just finished doing the "Recipe Book Mod" and I'm getting the following error message.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW = 1' at line 1
/tfp/recipe.asp, line 1915
_______________________________________________________
Here are lines 1904 to 1924
subsql = "SELECT * FROM "& strTablePrefix & "RECIPE_SUBCATEGORIES WHERE CAT_ID=" & parent_id & " ORDER BY SUBCAT_NAME"
set rssubcat = my_Conn.Execute(subsql)
'count = rssubcat.RecordCount
'count = 1
do while not rssubcat.EOF 'and count<=3
Response.Write " <font face=""" & strDefaultFontFace & """ color=""" & strDefaultFontColor & """ size=""" & strFooterFontSize & """>" & vbNewLine & _
" <a href=""recipe.asp?action=showall&cat_id=" & rssubcat("subcat_ID") & "&parent_id=" & rsCategories("cat_id") & "&parent_name=" & ChkString(rsCategories("cat_name"), "urlpath") & "&sub_name=" & ChkString(rssubcat("subcat_name"), "urlpath") & """>" & ChkString(rssubcat("subcat_name"), "display") & vbNewLine
sqlcount = "SELECT COUNT(URL) FROM "& strTablePrefix & "RECIPE WHERE CATEGORY =" & rsSubcat("subcat_id") & " AND SHOW = 1"
Set RScounts = my_Conn.Execute(sqlcount)
rcounts = RScounts(0)
if rcounts <> 0 then
Response.Write " (" & rcounts & ")"
end if
Response.Write "</a>"
rssubcat.movenext
if rsSubcat.eof then Response.Write "" else Response.Write ", "
Here's a link to my entire recipe.asp in txt format.
recipe.asp in txt format
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 05 July 2012 : 18:55:39
|
| The reason for the error is because the word "Show" is restricted (not allowed as a column name in the database) when using MySQL. It will have to be renamed and all references to it changed to reflect the new name. |
 |
|
|
MaGraham
Senior Member
USA
1297 Posts |
Posted - 05 July 2012 : 21:34:56
|
quote:
Originally posted by Carefree
The reason for the error is because the word "Show" is restricted (not allowed as a column name in the database) when using MySQL. It will have to be renamed and all references to it changed to reflect the new name.
All of the words "Show" in my recipe.asp file?
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
|
MaGraham
Senior Member
USA
1297 Posts |
Posted - 06 July 2012 : 00:38:29
|
I replaced all of the words "show" with "display" in my recipe.asp. Hope that was okay! 
I am still getting this error though.
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Table 'tfpmembers.forum_recipe' doesn't exist
/tfp/recipe.asp, line 1915
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 06 July 2012 : 06:09:07
|
No, that was not what I meant nor what needed to be done.
1) In the database table (_RECIPE) for the recipe mod, the field titled "SHOW" must be renamed to something else (i.e., RECIPE_SHOW).
2) Whatever is chosen, all SQL routines calling/editing that field must be changed to show the new field title.
I modified it and put it up on SnitzBitz.
Not running a copy on a MySQL database, but I included a dbs file to rename the column in the table. Hope I have the syntax correct. If it errors, use this (call it whatever you'd like), I think it'll work:
|
Edited by - Carefree on 06 July 2012 08:47:18 |
|
|
|
MaGraham
Senior Member
USA
1297 Posts |
Posted - 12 July 2012 : 05:04:52
|
I am unable to edit the MySQL directly because of the column name error. 
Are there any of you MySQL gurus out there reading this?
With your expertise, surely you could solve the MySQL dbs file issue.
The "Recipe Book Mod" is such an awesome mod! The fact that the members can upload their own personal picture of their recipe is such an attention-getter for my members. They LOVE to have whatever they have prepared on their attractively decorated table and make everyone drool over it!  There would probably be a lynching if my members thought I might not be able to have it on the new database!
Any help with this would be Sooooo appreciated!
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 12 July 2012 : 05:33:37
|
| Wherever you have the problematic word SHOW occurring, just replace it by `SHOW`. It's all that's needed. There is no need to change column names. You really need to use those "backtick" characters to escape the word. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 12 July 2012 : 10:32:12
|
So conceivably, Rui, that strSql from above could work like this?
my_Conn.Execute("ALTER " & strTablePrefix & "RECIPES CHANGE `SHOW` TO RECIPE_SHOW INT NOT NULL 0") |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Carefree
Advanced Member
Philippines
4222 Posts |
Posted - 12 July 2012 : 18:04:12
|
| Because I already rewrote all of the files and repackaged it. Just need to make the dbs file work so those who installed the original version can use MySQL. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
MaGraham
Senior Member
USA
1297 Posts |
Posted - 13 July 2012 : 12:45:13
|
quote:
Originally posted by ruirib
Escaping with backtick chars works in all circumstances.
This didn't work for me. Am I doing something wrong? I am receiving the following error:
Microsoft VBScript compilation error '800a03ea'
Syntax error
/tfp/recipe.asp, line 39
if 'show' = "10" then
---^
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 July 2012 : 12:55:11
|
You cannot use apostrophes. I suggest that you copy what you need from my previous post. The character in question is used as an accent in some languages like my own. Here, copy this:
`show`
Just reply with a quote and you can copy it easily. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
MaGraham
Senior Member
USA
1297 Posts |
Posted - 13 July 2012 : 13:08:47
|
quote:
Originally posted by ruirib
You cannot use apostrophes. I suggest that you copy what you need from my previous post. The character in question is used as an accent in some languages like my own. Here, copy this:
`show`
Just reply with a quote and you can copy it easily.
I tried yours again and again and again yesterday and gave up. I replied with a quote and tried yours again just now. I get this error when I copy yours:
Microsoft VBScript compilation error '800a0408'
Invalid character
/tfp/recipe.asp, line 26
`show` = Request("num")
^
So, what am I doing wrong?
|
"Do all the good you can, by all the means you can, in all the ways you can, at all the times you can, to all the people you can, as long as ever you can." - John Wesley |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Topic |
|
