Valtner

08 July 2010 12:12:04

MS SQL 2000

Hi, i tryed using iyzi forum couple days ago, and i modified it a bit. I added some new tables in db and changed conection to ms sql server 2000. DB was sucessfuly created and original code was not changed, except for the inc_database_path, and my sql_setup, and i changed data types from auto number to IDENTITY (1,1), but it's constantly giving me an error message. Does it have anything to do with different sintax between my sql and ms sql. Or is it something else. This is the mesage:
______________________________
Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near 'Limit'.

/site/Forum/default.asp, line 182
________________________

ZuLuuuuuu (Admin)

08 July 2010 14:08:22

Re: MS SQL 2000

Hello,

Access and MySQL has some differences in SQL syntax (not too many for basic usages). For example, Access does not support "limit" selector like MySQL does. So, whenever I need to use limit selector, I put an if statement like below (default.asp line 179):

If DatabaseType = "MySQL" Then
	SQL = SQL & " limit " & DisplayedRecordsLow & ", " & SettingsTopicsPerPage
End If

limit selector is used whenever the data is divided into pages; like topic list on default.asp, message list on topic.asp and member list on members.asp. For Access there is an if statement at line 239 like below for dividing the data into pages:

If DatabaseType = "Access" and Not Topics.Eof Then
	Topics.Pagesize = SettingsTopicsPerPage
	Topics.AbsolutePage = Page
End If

I don't know what way SQL Server 2000 supports but you have to change these lines accordingly. You can add a line like

DatabaseType = "SQLServer2000"

to the inc_database_path.asp just near the similar lines for Access and MySQL databases. And add an "or" expression to the if statements I've shown you above. For example, say the Access way is the way SQL Server 2000 works. Then change the above statement as:

If (DatabaseType = "Access" or DatabaseType = "SQLServer2000") and Not Topics.Eof Then
	Topics.Pagesize = SettingsTopicsPerPage
	Topics.AbsolutePage = Page
End If

If neither the Access nor the MySQL way works for SQL Server 2000, then you should write a similar if statement on its own to an appropriate place, for dividing the data into pages. I have not used SQL Server before, so I can't say.

You should do these changes for a few more places (not too many, I guess, 3 or 4 more places and once you made one of them the rest will be easy).

I don't remember if there were any other incompatibilities than limit selector between Access and MySQL. I guess no.

After handling these changes it will probably work. Let me know because I'm also curious whether it will work.

Hello,

Access and MySQL has some differences in SQL syntax (not too many for basic usages). For example, Access does not support "limit" selector like MySQL does. So, whenever I need to use limit selector, I put an if statement like below (default.asp line 179):

[code]If DatabaseType = "MySQL" Then
	SQL = SQL & " limit " & DisplayedRecordsLow & ", " & SettingsTopicsPerPage
End If[/code]
limit selector is used whenever the data is divided into pages; like topic list on default.asp, message list on topic.asp and member list on members.asp. For Access there is an if statement at line 239 like below for dividing the data into pages:

[code]If DatabaseType = "Access" and Not Topics.Eof Then
	Topics.Pagesize = SettingsTopicsPerPage
	Topics.AbsolutePage = Page
End If[/code]
I don't know what way SQL Server 2000 supports but you have to change these lines accordingly. You can add a line like

[code]DatabaseType = "SQLServer2000"[/code]
to the inc_database_path.asp just near the similar lines for Access and MySQL databases. And add an "or" expression to the if statements I've shown you above. For example, say the Access way is the way SQL Server 2000 works. Then change the above statement as:

[code]If (DatabaseType = "Access" or DatabaseType = "SQLServer2000") and Not Topics.Eof Then
	Topics.Pagesize = SettingsTopicsPerPage
	Topics.AbsolutePage = Page
End If[/code]
If neither the Access nor the MySQL way works for SQL Server 2000, then you should write a similar if statement on its own to an appropriate place, for dividing the data into pages. I have not used SQL Server before, so I can't say.

You should do these changes for a few more places (not too many, I guess, 3 or 4 more places and once you made one of them the rest will be easy).

I don't remember if there were any other incompatibilities than limit selector between Access and MySQL. I guess no.

After handling these changes it will probably work. Let me know because I'm also curious whether it will work.

Valtner

08 July 2010 16:28:29

Re: MS SQL 2000

Thanks for the detailed answer, i tried :
_______________________________
If (DatabaseType = "Access" or DatabaseType = "SQLServer2000") and Not Topics.Eof Then
Topics.Pagesize = SettingsTopicsPerPage
Topics.AbsolutePage = Page
End If
_________________________

in default.asp, members.asp and topic.asp, and i added in inc_database_path.asp sql 200 section, and it works perfectly, but now i have diffrent problem. When i enter a new post or reply a get this message.

________________________________

Microsoft OLE DB Provider for SQL Server error '80040e14'

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

/site/Forum/post.asp, line 164
___________________________

ZuLuuuuuu (Admin)

08 July 2010 17:03:21

Re: MS SQL 2000

I'm not sure if this will help but please try changing line 163 of post.asp with the line below:

		SQL = "Select PostID From iyzi_" & AdditionalTableWord & "Posts Where Subject = '" & OrganizeData(Subject, "DBText") & "' Order By PostedOn DESC"

Valtner

08 July 2010 17:17:54

Re: MS SQL 2000

I just fixed it with:
______________________________
SQL = "Select PostID From iyzi_" & AdditionalTableWord & "Posts Where Subject = '" & OrganizeData(Subject, "DBText") & "' and Message like '" & OrganizeData (Message, "DBText") & "' Order By PostedOn DESC"
______________________________

in post.asp line 163.
Thanks for your help.

ZuLuuuuuu (Admin)

09 July 2010 01:39:42

Re: MS SQL 2000

Yes, it is because SQL Server cannot compare text fields with each other, interesting.

Good to hear that it does not require much effort to use SQL Server with iyzi Forum

Valtner

17 July 2010 10:31:31

Re: MS SQL 2000

How can i send more data besides memberID to a asp function, like that:
________________________________
<a href="my_details.asp?Action=Delete_edu&MemberID=<%=MemberID%>;name_e=<%=name_e%>" class="Image"><img src="skins/<%=SettingsSkin%>/delete_post.gif" alt="<%=LanTitleRejectPost%>" title="<%=LanTitleRejectPost%>" /></a>

Without having this problem:

__________________________
<script type="text/javascript">Error('A variable, which is supposed to be numerical, is not numerical: 1;name_e=ggggg');</script></body></html>

ZuLuuuuuu (Admin)

25 July 2010 06:09:07

Re: MS SQL 2000

This is probably because of an SQL statement.

I pass any data on an SQL statement through the OrganizeData() function so that no SQL injection occurs:

OrganizeData(PostID, "DBNumber")

Here it checks whether the PostID variable is numerical or not. If you pass a non-numerical data here it gives the error you mentioned. If you want to pass a non-numerical data then change it into:

OrganizeData(PostID, "DBText")

This checks for ' characters etc and replaces them accordingly.