| Author |
 Topic  |
|
|
klodahl
Starting Member
Denmark
7 Posts |
 Posted - 13 June 2001 : 05:20:40
|
I get this error:
Creating table(s)...
CREATE TABLE MODS( M_NAME nvarchar(50) NOT NULL , M_CODE nvarchar(50) NOT NULL , M_VALUE MEMO NULL )
-2147217900 | [Microsoft][ODBC SQL Server Driver][SQL Server]Column or parameter #3: Cannot find data type MEMO.
Im using SQL Server 7.0 what should i change the data type to and where to do it?
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 13 June 2001 : 05:41:43
|
firstly, its not my MOD, it was written by animedj.
you need to change MEMO to TEXT if you are using SQL
|
 |
|
|
George_Zhu
Starting Member
China
46 Posts |
Posted - 13 June 2001 : 08:25:33
|
quote:
firstly, its not my MOD, it was written by animedj.
you need to change MEMO to TEXT if you are using SQL
May I ask which line I should modify?
BTW, the file can recognize SQL7.0, why there is a memo type?
[color=SlateBlue]Learning is all I want[/color] |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 13 June 2001 : 16:41:28
|
could you post the contents of the file , I will show what needs changing
|
|
|
|
George_Zhu
Starting Member
China
46 Posts |
Posted - 13 June 2001 : 20:36:21
|
quote:
could you post the contents of the file , I will show what needs changing
Thank you, I am posting the detail of admin_mod_dbsetup.asp. I have looked into the file and I found that it should can devide ACCESS or SQL, but actually it do no change to the sql language
<%
'#################################################################################
'## Copyright (C) 2000-01 Michael Anderson and Pierre Gorissen
'##
'## This program is free software; you can redistribute it and/or
'## modify it under the terms of the GNU General Public License
'## as published by the Free Software Foundation; either version 2
'## of the License, or any later version.
'##
'## All copyright notices regarding Snitz Forums 2000
'## must remain intact in the scripts and in the outputted HTML
'## The "powered by" text/logo with a link back to
'## http://forum.snitz.com in the footer of the pages MUST
'## remain visible when the pages are viewed on the internet or intranet.
'##
'## This program is distributed in the hope that it will be useful,
'## but WITHOUT ANY WARRANTY; without even the implied warranty of
'## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
'## GNU General Public License for more details.
'##
'## You should have received a copy of the GNU General Public License
'## along with this program; if not, write to the Free Software
'## Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
'##
'## Support can be obtained from support forums at:
'## http://forum.snitz.com
'##
'## Correspondence and Marketing Questions can be sent to:
'## reinhold@bigfoot.com
'##
'## or
'##
'## Snitz Communications
'## C/O: Michael Anderson
'## PO Box 200
'## Harpswell, ME 04079
'#################################################################################
%>
<!--#include file="config.asp"-->
<!--#include file="inc_functions.asp"-->
<%
Dim strTableName
Dim fieldArray (100)
Dim idFieldName
Dim ErrorCount
ErrorCount = 0
set my_Conn= Server.CreateObject("ADODB.Connection")
my_Conn.Open strConnString
Set fso = CreateObject("Scripting.FileSystemObject")
set objFile = fso.Getfile(server.mappath(modFolder & Request.ServerVariables("PATH_INFO")))
set objFolder = objFile.ParentFolder
set objFolderContents = objFolder.Files
if Request.Form("dbMod") = "" then
Response.write("<font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>")
Response.write "<h3>Snitz Forum Modifications</h3>" &_
"<b>" & strDBType & "</b> Database Setup.....<br></font>" &_
"<form action=""" & Request.ServerVariables("PATH_INFO") & """ method=""post"" name=""form1"">"
If strDBType = "" then
Response.write "<font face=""" & strDefaultFontFace & """ color=red size=""" & strDefaultFontSize & """>Your strDBType is not set, please edit your config.asp<br>" &_
"to reflect your database type<br></font>"
Response.write "<br><a href=""default.asp"">Go Back to Forum</a></font>"
Response.End
end if
If strDBType = "sqlserver" then
Response.write "<font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>" &_
"You are using SQL Server, please select the correct version<br>" &_
"<input type=""radio"" name=""sqltype"" value=""7"" checked> SQL 7.x " &_
"<input type=""radio"" name=""sqltype"" value=""6""> SQL 6.x<br></font>"
End If
on error resume next
Response.write "<font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>" &_
"<p>Select the Mod from the list below, and press Update!<br>" &_
"A script will execute to perform the database upgrade.</p></font>" &_
"<select name=""dbMod"" size=""1"">"
for each objFileItem in objFolderContents
intFile = instr(objFileItem.Name, ".dbs")
if intFile <> 0 then
modFile = left(objFileItem.Name, intFile - 1)
whichfile = server.mappath(modFolder & modFile & ".dbs")
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.OpenTextFile(whichfile, 1, False)
ModName = thisfile.readline
Response.write "<option value=""" & whichfile & """>" & ModName & "</option>"
thisfile.close
if err.number <> 0 then
Response.write err.description
Response.end
end if
set fs = nothing
end if
Next
Response.write "</select>" &_
"<input type=""submit"" name=""submit1"" value=""Update!""></form>" &_
"<font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" &_
"<a href=""default.asp"">Go Back to Forum</a></font>"
Else
sqlVer = Request.Form("sqltype")
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.OpenTextFile(Request.Form("dbMod"), 1, False)
ModName = thisfile.readline
response.write ("<font face=" & strDefaultFontFace & " size=" & strDefaultFontSize+1 & ">")
response.write ("<h4>" & ModName & "</h4></font>")
'## Load Sections for processing
do while not thisfile.AtEndOfStream
sectionName = thisfile.readline
Select case uCase(sectionName)
case "[CREATE]"
strTableName = uCase(thisfile.readline)
idFieldName = uCase(thisfile.readline)
tempField = thisfile.readline
rec = 0
do while uCase(tempField) <> "[END]"
fieldArray(rec) = tempField
rec = rec+1
tempField = thisfile.readline
loop
CreateTables(rec)
case "[ALTER]"
strTableName = uCase(thisfile.readline)
tempField = thisfile.readline
rec = 0
do while uCase(tempField) <> "[END]"
fieldArray(rec) = tempField
rec = rec+1
tempField = thisfile.readline
loop
AlterTables(rec)
case "[DELETE]"
strTableName = uCase(thisfile.readline)
tempField = thisfile.readline
rec = 0
do while uCase(tempField) <> "[END]"
fieldArray(rec) = tempField
rec = rec+1
tempField = thisfile.readline
loop
DeleteValues(rec)
case "[INSERT]"
strTableName = uCase(thisfile.readline)
tempField = thisfile.readline
rec = 0
do while uCase(tempField) <> "[END]"
fieldArray(rec) = tempField
rec = rec+1
tempField = thisfile.readline
loop
InsertValues(rec)
case "[UPDATE]"
strTableName = uCase(thisfile.readline)
tempField = thisfile.readline
rec = 0
do while uCase(tempField) <> "[END]"
fieldArray(rec) = tempField
rec = rec+1
tempField = thisfile.readline
loop
UpdateValues(rec)
end select
loop
response.write ""
if ErrorCount > 0 then
Response.write "There were errors please post a question in the MOD Implementation Forum at<br>"
Response.write "<a href=""http://forum.snitz.com/forum/forum.asp?FORUM_ID=94&CAT_ID=10"">Snitz Forums</a>"
else
Response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><p><b>Database setup finished</b></p>"
end if
Response.write "</font>" &_
"<form action=""" & Request.ServerVariables("PATH_INFO") & """ method=""post"" name=""form2"">" &_
"<input type=""hidden"" name=""dbMod"" value="""">" &_
"<input type=""submit"" name=""submit2"" value=""Finished""></form>"
end if
set fs=nothing
set fso = nothing
Sub CreateTables( numfields )
response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>"
response.write "<b>Creating table(s)...</b><br>"
if len(strTableName) > 6 then
if Instr(strTableName,"MEMBER") > 0 then
TablePrefix = strMemberTablePrefix
else
TablePrefix = strTablePrefix
end if
else
TablePrefix = strTablePrefix
end if
strSql = "CREATE TABLE " & TablePrefix & strTableName & "( "
if idFieldName <> "" then
select case strDBType
case "access"
if Instr(strConnString,"(*.mdb)") then
strSql = strSql & idFieldName &" COUNTER CONSTRAINT PrimaryKey PRIMARY KEY "
else
strSql = strSql & idFieldName &" int IDENTITY (1, 1) NOT NULL "
end if
case "sqlserver"
strSql = strSql & idFieldName &" int IDENTITY (1, 1) NOT NULL "
case "mysql"
strSql = strSql & idFieldName &" INT (11) DEFAULT '' NOT NULL auto_increment "
end select
end if
for y = 0 to numfields -1
on error resume next
tmpArray = split(fieldArray(y),"#")
fName = uCase(tmpArray(0))
fType = lCase(tmpArray(1))
fNull = uCase(tmpArray(2))
fDefault = tmpArray(3)
if idFieldName <> "" or y <> 0 then
strSql = strSql & ", "
end if
select case strDBType
case "access"
fType = replace(fType,"varchar (","text (")
case "sqlserver"
select case sqlVer
case 7
fType = replace(fType,"memo","ntext")
fType = replace(fType,"varchar","nvarchar")
fType = replace(fType,"date","datetime")
case else
fType = replace(fType,"memo","text")
end select
case "mysql"
fType = replace(fType,"memo","text")
fType = replace(fType,"#int","#int (11)")
fType = replace(fType,"#smallint","#smallint (6)")
end select
if fNull <> "NULL" then fNull = "NOT NULL"
strSql = strSql & fName & " " & fType & " " & fNull & " "
if fdefault <> "" then
strSql = strSql & "DEFAULT " & fDefault
end if
next
if strDBType = "mysql" then
strSql = strSql & ",KEY " & TablePrefix & strTableName & "_" & idFieldName & "(" & idFieldName & "))"
else
strSql = strSql & ")"
end if
response.write strSql & "<br>"
my_Conn.Execute strSql
if err.number <> 0 and err.number <> 13 then
response.write("<font color=red>" & err.number & " | " & err.description & "</font><br>")
ErrorCount = ErrorCount + 1
else
response.write("<b>Table created succesfully</b><br>")
end if
response.write("<hr size=""1"" width=""260"" align=left color=""blue""></font>")
end Sub
Sub AlterTables(numfields)
Response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>"
for y = 0 to numfields -1
on error resume next
if Instr(strTableName,"MEMBER",1) > 0 then
TablePrefix = strMemberTablePrefix
else
TablePrefix = strTablePrefix
end if
strSql = "ALTER TABLE " & TablePrefix & strTableName
tmpArray = split(fieldArray(y),"#")
fAction = uCase(tmpArray(0))
fName = uCase(tmpArray(1))
fType = lCase(tmpArray(2))
fNull = uCase(tmpArray(3))
fDefault = tmpArray(4)
select case fAction
case "ADD"
strSQL = strSQL & " ADD "
if strDBType = "access" then strSql = strSql & "COLUMN "
case "DROP"
strSQL = strSQL & " DROP COLUMN "
case "ALTER"
strSQL = strSQL & " ALTER COLUMN "
case else
end select
if fAction = "ADD" or fAction = "ALTER" then
select case strDBType
case "access"
fType = replace(fType,"varchar","text")
case "sqlserver"
select case sqlVer
case 7
fType = replace(fType,"memo","ntext")
fType = replace(fType,"varchar","nvarchar")
fType = replace(fType,"date","datetime")
case else
fType = replace(fType,"memo","text")
end select
case "mysql"
fType = replace(fType,"#int","#int (11)")
fType = replace(fType,"#smallint","#smallint (6)")
end select
if fNull <> "NULL" then fNull = "NOT NULL"
strSql = strSQL & fName & " " & fType & " " & fNULL & " "
if fDefault <> "" then strSQL = strSQL & "DEFAULT " & fDefault
response.write "<b>Adding Column...</b><br>" & strSQL & "<br>"
else
strSql = strSQL & fName
response.write "<b>Dropping Column...</b><br>" & strSQL & "<br>"
end if
my_Conn.Execute strSql
if err.number <> 0 and err.number <> 13 then
response.write("<font color=red>" & err.number & " | " & err.description & "</font><br>")
ErrorCount = ErrorCount + 1
else
if fAction = "DROP" then
response.write("<b>Column " & LCase(fAction) & "ped successfully</b><br>")
else
response.write("<b>Column " & LCase(fAction) & "ed successfully</b><br>")
end if
if fDefault <> "" then
strSQL = "UPDATE " & TablePrefix & strTableName & " SET " & fName & "=" & fDefault
my_Conn.Execute strSql
response.write "<b>Populating Current Records with new Default value</b><br>" & strSQL & "<br>"
end if
end if
if fieldArray(y) = "" then y = numfields
next
response.write("<b>Table(s) updated</b><br>")
response.write("<hr size=""1"" width=""260"" align=left color=""blue""></font>")
end Sub
Sub InsertValues(numfields)
Response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>"
on error resume next
response.write ("<b>Adding new records..</b><br>")
for y = 0 to numfields-1
if Instr(strTableName,"MEMBER",1) > 0 then
strSql = "INSERT INTO " & strMemberTablePrefix & strTableName & " "
else
strSql = "INSERT INTO " & strTablePrefix & strTableName & " "
end if
tmpArray = split(fieldArray(y),"#")
fNames = tmpArray(0)
fValues = tmpArray(1)
strSql = strSql & tmpArray(0) & " VALUES " & tmpArray(1)
my_Conn.Execute strSql
response.write strSql & "<br>"
next
if err.number <> 0 and err.number <> 13 then
response.write("<font color=red>" & err.number & " | " & err.description & "</font><br>")
ErrorCount = ErrorCount + 1
else
response.write("<br><b>Value(s) updated succesfully</b>")
end if
response.write("<hr size=""1"" width=""260"" align=left color=""blue""><font>")
end Sub
Sub UpdateValues(numfields)
on error resume next
Response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>"
response.write ("<b>Updating Forum Values..</b><br>")
for y = 0 to numfields-1
if Instr(strTableName,"MEMBER",1) > 0 then
strSql = "UPDATE " & strMemberTablePrefix & strTableName & " SET"
else
strSql = "UPDATE " & strTablePrefix & strTableName & " SET"
end if
tmpArray = split(fieldArray(y),"#")
fName = tmpArray(0)
fValue = tmpArray(1)
fwhere = tmpArray(2)
strSql = strSql & " " & fName & " = " & fvalue & " WHERE " & fWhere
my_Conn.Execute strSql
response.write strSql & "<br>"
next
if err.number <> 0 then
response.write("<font color=red>" & err.number & " | " & err.description & "</font><br>")
ErrorCount = ErrorCount + 1
else
response.write("<br><b>Value(s) updated succesfully</b>")
end if
response.write("<hr size=""1"" width=""260"" align=left color=""blue""><font>")
end Sub
Sub DeleteValues(numfields)
on error resume next
response.write "<br><font face=""" & strDefaultFontFace & """ size=""" & strFooterFontSize & """>"
response.write ("<b>Updating Forum Values..</b><br>")
if Instr(strTableName,"MEMBER",1) > 0 then
strSql = "DELETE FROM " & strMemberTablePrefix & strTableName & " WHERE "
else
strSql = "DELETE FROM " & strTablePrefix & strTableName & " WHERE "
end if
tmpArray = fieldArray(0)
strSql = strSql & tmpArray
my_Conn.Execute strSql
response.write strSql & "<br>"
if err.number <> 0 then
response.write("<font color=red>" & err.number & " | " & err.description & "</font><br>")
ErrorCount = ErrorCount + 1
else
response.write("<br><b>Value(s) updated succesfully</b>")
end if
response.write("<hr size=""1"" width=""260"" align=left color=""blue""><font>")
end Sub
my_Conn.Close
set my_Conn = nothing
%>
[color=SlateBlue]Learning is all I want[/color] |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 14 June 2001 : 02:40:02
|
actually, I meant the dbs file you are trying to load.
Also could you post your db connection string and strDBType
|
|
|
| |
Topic |
|
|
|
Topic Locked
