| Author |
 Topic  |
|
|
andretti
New Member
70 Posts |
 Posted - 07 March 2005 : 12:51:24
|
I found a book review mod that I was trying to implement in my forums. However there was no dbs file includes. Just a sql script. I have ran this in the alternative mod setup and I get a message that says 125 lines of code written and no errors but whenever I try to access the page it gives me an error that the table is not found..
I am going to post the sql statement below and see if anyone can tell me if this is correct..
Thank you in advance for your time
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] DROP CONSTRAINT FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BOOK_BOOK_SUBCATEGORIES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BOOK] DROP CONSTRAINT FK_BOOK_BOOK_SUBCATEGORIES
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_CATEGORIES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_CATEGORIES]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_RATING]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_RATING]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_READING_LIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_READING_LIST]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_SUBCATEGORIES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_SUBCATEGORIES]
GO
CREATE TABLE [dbo].[BOOK] (
[book_id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[author] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[post_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[hit] [int] NULL ,
[member_id] [int] NULL ,
[keyword] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[category] [int] NULL ,
[parent_id] [int] NULL ,
[show] [int] NULL ,
[rating] [int] NOT NULL ,
[votes] [int] NOT NULL ,
[summary] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_CATEGORIES] (
[bcat_id] [int] IDENTITY (1, 1) NOT NULL ,
[cat_name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_RATING] (
[brating_id] [int] IDENTITY (1, 1) NOT NULL ,
[comments] [text] COLLATE Latin1_General_CI_AS NULL ,
[rating] [int] NULL ,
[rate_by] [int] NULL ,
[book] [int] NULL ,
[rate_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_READING_LIST] (
[br_id] [int] IDENTITY (1, 1) NOT NULL ,
[book_id] [int] NULL ,
[member_id] [int] NULL ,
[status] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[start_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[end_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_SUBCATEGORIES] (
[bsubcat_id] [int] IDENTITY (1, 1) NOT NULL ,
[subcat_name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[cat_id] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK] ADD
CONSTRAINT [DF_BOOK_rating] DEFAULT (0) FOR [rating],
CONSTRAINT [DF_BOOK_votes] DEFAULT (0) FOR [votes],
CONSTRAINT [PK_BOOK] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK_CATEGORIES] ADD
CONSTRAINT [PK_BOOK_CATEGORIES] PRIMARY KEY CLUSTERED
(
[bcat_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] ADD
CONSTRAINT [PK_BOOK_SUBCATEGORIES] PRIMARY KEY CLUSTERED
(
[bsubcat_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK] ADD
CONSTRAINT [FK_BOOK_BOOK_SUBCATEGORIES] FOREIGN KEY
(
[category]
) REFERENCES [dbo].[BOOK_SUBCATEGORIES] (
[bsubcat_id]
)
GO
alter table [dbo].[BOOK] nocheck constraint [FK_BOOK_BOOK_SUBCATEGORIES]
GO
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] ADD
CONSTRAINT [FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES] FOREIGN KEY
(
[cat_id]
) REFERENCES [dbo].[BOOK_CATEGORIES] (
[bcat_id]
)
GO
alter table [dbo].[BOOK_SUBCATEGORIES] nocheck constraint [FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES]
GO
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 07 March 2005 : 12:59:01
|
Have you got a link to the mod in question? I've never heard of a mod of this sort in my time here on Snitz and the SQL posted above will not work with Snitz in it's own.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
andretti
New Member
70 Posts |
Posted - 07 March 2005 : 13:26:48
|
No I forget where I got this mod from. I have had it for quite a while just forgot about it until now...
Ok seeing as how you say this sql will not work snitz wht do i need to do to get it to work with snitz?
Thanks for your help in advance |
|
|
|
andretti
New Member
70 Posts |
Posted - 07 March 2005 : 17:01:17
|
Ok I got part of this working. I was able to create a table in my sql server using the query commands. Then I simply exported tables to my snitz access database.
I just need to mod my .asp files to allow it to work and everything appears to be working just fine so far. All pages show up with no errors except for this at this point.
In the admin panel I am give an option to add a new category. I enter my name in the field as Thriller and click add category button and I get a message that comes up and says:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] The field 'BOOK_CATEGORIES.bcat_id' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.
/admin_book_addcat.asp, line 61
In the file admin_book_addcat.asp I see this line
set rsinsert = my_Conn.Execute ("insert into BOOK_CATEGORIES (CAT_NAME) values ('" & newcat & "')")
Is that above line correct or do I need to change it so thatit will be able to work with snitz?
I appreciate any help on this and I will be sure to share this mod with everyone when I have it completly ready and working with snitz.
Thanks |
|
|
|
andretti
New Member
70 Posts |
Posted - 07 March 2005 : 20:32:58
|
ok got that error fixed by changing the value on the table to autonumber and now I can create categories no sweat. So far so good...
Looks like one more error I need to conquer
I get this whenver I try to add a book to the database
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/book_add.asp, line 110
Now for the snippet from book_ad.asp where the errror is generated
<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<!--#INCLUDE FILE="inc_header.asp" -->
<%
if trim(Request.Form("cat")) = "" then
Response.Redirect("book.asp")
else
cat = cLng(Request.Form("cat"))
end if
title = replace(ChkString(Request.Form("title"),"title"), "''","'", 1, -1, 1)
summary = replace(ChkString(ChkBadWords(Request.Form("summary")),"message"), "''","'", 1, -1, 1)
key = replace(ChkString(Request.Form("key"),"message"), "''","'", 1, -1, 1)
author = replace(ChkString(Request.Form("author"),"title"), "''","'", 1, -1, 1)
poster = replace(ChkString(Request.Form("poster"),"title"), "''","'", 1, -1, 1)
today = datetostr(strForumTimeAdjust)
strSQL = "SELECT CAT_ID FROM BOOK_SUBCATEGORIES WHERE BSUBCAT_ID = " & cat
dim rsCategories
set rsCategories = server.CreateObject("adodb.recordset")
rsCategories.Open strSQL, my_Conn
parent = rsCategories("CAT_ID")
rsCategories.Close
set rsCategories = nothing
sString = ""
if len(trim(title)) = 0 then
sString = sString & "<li>Please enter book title.</li>"
else
Set RS=Server.CreateObject("ADODB.Recordset")
strSql="Select TITLE from BOOK where TITLE='" & chkString(title, "SQLString") & "'"
RS.Open strSql, my_Conn
if not rs.eof then
sString = sString & "<li>This book already exists in our database.</li>"
end if
RS.close
end if
if cat = "--Please select one--" then
sString = sString & "<li>Please select category that match your book.</li>"
end if
if len(trim(summary)) = 0 then
sString = sString & "<li>Please enter book summary.</li>"
end if
if len(trim(summary)) => 400 then
sString = sString & "<li>" &len(trim(summary))&" characters. Your summary is too long! 400 characters max.</li>"
end if
if sString = "" then
strSql = "INSERT INTO BOOK"
strSql = strSql & "(TITLE"
strSql = strSql & ", KEYWORD"
strSql = strSql & ", CATEGORY"
strSql = strSql & ", SUMMARY"
strSql = strSql & ", POST_DATE"
strSql = strSql & ", PARENT_ID"
strSql = strSql & ", SHOW"
strSql = strSql & ", HIT "
strSql = strSql & ", AUTHOR"
strSql = strSql & ", MEMBER_ID"
strSql = strSql & ") "
strSql = strSql & " VALUES ("
strSql = strSql & "'" & replace(chkString(title, "SQLString"), "''","'", 1, -1, 1) & "'"
strSql = strSql & ", " & "'" & chkString(key, "SQLString") & "'"
strSql = strSql & ", " & "'" & cat & "'"
strSql = strSql & ", " & "'" & chkString(summary, "SQLString") & "'"
strSql = strSql & ", " & "'" & today & "'"
strSql = strSql & ", " & "'" & parent & "'"
if mlev=4 then
strSql = strSql & ", " & "1"
else
strSql = strSql & ", " & "0"
end if
strSql = strSql & ", " & "0"
strSql = strSql & ", " & "'" & chkString(author, "SQLString") & "'"
strSql = strSql & ", " & "'" & chkString(strUserMemberID, "SQLString") & "'"
strSql = strSql & ")"
my_Conn.Execute (strSQL)
Line number 110 is the line above^^^^^^^^
Any thoughts? |
|
|
|
andretti
New Member
70 Posts |
Posted - 08 March 2005 : 14:57:23
|
| ugh.... stillcannot get this going |
|
|
|
Classicmotorcycling
Development Team Leader
Australia
2084 Posts |
Posted - 08 March 2005 : 20:30:20
|
Send me an e-mail from the  image and send a link to download the script and the DB and I will look at it for you..
|
Cheers,
David Greening |
|
|
|
andretti
New Member
70 Posts |
|
|
andretti
New Member
70 Posts |
Posted - 26 March 2005 : 14:52:52
|
| no takers on this? |
|
|
|
andretti
New Member
70 Posts |
Posted - 28 March 2005 : 19:15:40
|
Ok I am still playing with this... and current error I get when trying to add a book is:
INSERT INTO BOOK(TITLE, KEYWORD, CATEGORY, SUMMARY, POST_DATE, PARENT_ID, SHOW, HIT , AUTHOR, MEMBER_ID) VALUES ('test', 'test', '1', 'test', '3/28/2005', '1', 1, 0, 'test', ' ')
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/book_add.asp, line 112
here is the part of my script that is not working.... Am I missing something on this?
<%
if trim(Request.Form("cat")) = "" then
Response.Redirect("book.asp")
else
cat = cLng(Request.Form("cat"))
end if
title = replace(ChkString(Request.Form("title"),"title"), "''","'", 1, -1, 1)
summary = replace(ChkString(ChkBadWords(Request.Form("summary")),"message"), "''","'", 1, -1, 1)
key = replace(ChkString(Request.Form("key"),"message"), "''","'", 1, -1, 1)
author = replace(ChkString(Request.Form("author"),"title"), "''","'", 1, -1, 1)
poster = replace(ChkString(Request.Form("poster"),"title"), "''","'", 1, -1, 1)
today = date()'tostr(strForumTimeAdjust)
strSQL = "SELECT CAT_ID FROM BOOK_SUBCATEGORIES WHERE BSUBCAT_ID = " & cat
dim rsCategories
set rsCategories = server.CreateObject("adodb.recordset")
rsCategories.Open strSQL, my_Conn
parent = rsCategories("CAT_ID")
rsCategories.Close
set rsCategories = nothing
sString = ""
if len(trim(title)) = 0 then
sString = sString & "<li>Please enter book title.</li>"
else
Set RS=Server.CreateObject("ADODB.Recordset")
strSql="Select TITLE from BOOK where TITLE='" & chkString(title, "SQLString") & "'"
RS.Open strSql, my_Conn
if not rs.eof then
sString = sString & "<li>This book already exists in our database.</li>"
end if
RS.close
end if
if cat = "--Please select one--" then
sString = sString & "<li>Please select category that match your book.</li>"
end if
if len(trim(summary)) = 0 then
sString = sString & "<li>Please enter book summary.</li>"
end if
if len(trim(summary)) => 400 then
sString = sString & "<li>" &len(trim(summary))&" characters. Your summary is too long! 400 characters max.</li>"
end if
if sString = "" then
strSql = "INSERT INTO BOOK"
strSql = strSql & "(TITLE"
strSql = strSql & ", KEYWORD"
strSql = strSql & ", CATEGORY"
strSql = strSql & ", SUMMARY"
strSql = strSql & ", POST_DATE"
strSql = strSql & ", PARENT_ID"
strSql = strSql & ", SHOW"
strSql = strSql & ", HIT "
strSql = strSql & ", AUTHOR"
strSql = strSql & ", MEMBER_ID"
strSql = strSql & ") "
strSql = strSql & " VALUES ("
strSql = strSql & "'" & replace(chkString(title, "SQLString"), "''","'", 1, -1, 1) & "'"
strSql = strSql & ", " & "'" & chkString(key, "SQLString") & "'"
strSql = strSql & ", " & "'" & cat & "'"
strSql = strSql & ", " & "'" & chkString(summary, "SQLString") & "'"
strSql = strSql & ", " & "'" & today & "'"
strSql = strSql & ", " & "'" & parent & "'"
if mlev=4 then
strSql = strSql & ", " & "1"
else
strSql = strSql & ", " & "0"
end if
strSql = strSql & ", " & "0"
strSql = strSql & ", " & "'" & chkString(author, "SQLString") & "'"
strSql = strSql & ", " & "'" & chkString(strUserMemberID, "SQLString") & "'"
strSql = strSql & ")"
Response.Write(strSql)
my_Conn.Execute (strSQL)
################# Hopefully one you guru's can tell me what I have done wrong... |
|
|
| |
Topic |
|
|
|
Topic Locked
