Database Suckss..!!

[equuelus]

Guys...

I'm pretty stressed up here.... i've been working this out for quite a while and still no solutions to it. What i'm trying to do is a bit complex, so i don't think so it is appropraite to explain it in detail here..it will take a few hundred pages(just kiddin')

What i really need is an example of inserting data's of different datatypes; text, date, memo, numeric. My heads really spinning over this. The server keeps giving me syntax error. I know this is because of the data type.
I have checked it like a thousand time.. but still cannot detect what's causing it.

Could anyone help me out here.. thanks in advance.
oo another thing... i'm using access for my database.

[alexmac]

Can you post your SQL statement as if it was to do with datatype you normally get a datatype mismatch error. When you use an Insert statment it is the same for all datatypes. Here is an insert statement I know works (its all on one line):

SQL="INSERT INTO userTbl (UserName,Password,Name,Email,UserLevel,Signature) VALUES ('" & rUsername & "','" & rPassword & "','" & rName & "','" & rEmail & "','" & rUserLevel & "','" & rSignature & "')"

Check you have all the quotation marks in the right place as it can get darn confusing

Alex

[equuelus]

Sorry alex, still can't spot the problem on the sql query Here what my code looks like..

As you can see, there are several data types that i need to insert in the database. I get syntax error for this code.

<% @language = VBScript %>
<!--#include file="file:///C|/Sites/Tutorial4/Connections/mainConn.asp" -->
<%
Dim Article, Paragraph, i, TutorialTitle, CourseID, AuthorID, Description
Dim insertTutorial, insert, Date, insertCommand

TutorialTitle = CStr(Request.Form("txtTutorialTitle"))
CourseID = CStr(Request.Form("sltCourseID"))
AuthorID = CStr(Request.Form("hdnAuthorID"))
Description = Cstr(Request.Form("txtDescription"))
Date = Now

'inserts the tutorial in the tutorial table
insertTutorial = "INSERT INTO Tutorial ( TutorialTitle, AuthorID, Date," & _
                 "CourseID, Description ) VALUES ('" & TutorialTitle & "','" & AuthorID & "','" & _
				 Date & "','" & CourseID & "','" & Desciption & "');"
' execute the insert command
Set insertCommand = Server.CreateObject("ADODB.Command")
insertCommand.ActiveConnection = MM_mainConn_STRING
insertCommand.CommandText = insertTutorial
insertCommand.Execute

'get the current tutorialid
Set rsCurrentTutorial = Server.CreateObject("ADODB,RecordSet")
rsCurrentTutorial.ActiveConnection = MM_mainConn_STRING
rsCurrentTutorial.Source = "SELECT Tutorials.TutorialID FROM Tutorials WHERE (((Tutorials.TutorialTitle)='" & tutorialTitle & "') AND ((Tutorials.DatePosted)='" & Date & "'));"
rsCurrentTutorial.CursorType = 0
rsCurrentTutorial.CursorLocation = 2
rsCurrentTutorial.LockType = 3
rsCurrentTutorial.Open()

TutorialID = rsCurrentTutorial("TutorialID") 
Article = CStr(Request.Form("txtArticle"))

'Splits the article to arrays of paragraph seperated by a line
Paragraph = Split(Article, vbCrLf & vbCrLf)

'inserts each paragraph in the database
For i = LBound(Paragraph) To UBound(Paragraph)
insert = "INSERT INTO Paragraph (TutorialID, ParagraphNo, Text) VALUES ('" & _
         TutorialID & "','" & i+1 & "','" & Paragraph(i) & "');"
insertCommand.CommandText = insert
insertCommand.Execute
Next

Erase Paragraph
Call rsCurrentTutorial.Close()
Call Response.Redirect("test.asp")
%>

[alexmac]

It looks okay at first glance but it is hard to tell with so much code!
What line (if any) gives the syntax error?
Post your database and hopefully we can get this sorted quickly.

Alex

[equuelus]

i have managed to lower down the code and found where actually my problem comes from..

it's from this code.

For i = LBound(Paragraph) To UBound(Paragraph)
insert = "INSERT INTO Paragraph (TutorialID, ParagraphNo, Text) VALUES ('" & _
         TutorialID & "','" & i+1 & "','" & Paragraph(i) & "');"
insertCommand.CommandText = insert
insertCommand.Execute
Next

the error comes from the

Paragraph(i)

varable.

So I tried changing it to something else easier which was "TEXT". Successfully, it managed to insert the data with out any errors. I tried changing it back to what previously was. But it gives me the same error.

I decided to display the insert variable on the page. The sql statement has no errors. It looks fine. I have been working with this like for the past 48 hrs but still no solution. I badly need help.

[equuelus]

i found out that i can't use ', instead i have to use " in my sql statement for the Paragraph(i) array.

So i changed the code to..

Article = CStr(Request.Form("txtArticle"))
Paragraph = Split(Article, vbCrLf & vbCrLf)

For i = LBound(Paragraph) To UBound(Paragraph)
TextStr = chr(34) & Paragraph(i) & chr(34)
insert = "INSERT INTO Paragraph (ParagraphNo, ParagraphText) VALUES (" & i & "," & TextStr & ");"
insertCommand.CommandText = insert
insertCommand.Execute
Next

it did not work when i run in my asp page. but when i display the sql code in my page and run it in access query, it worked..

when i run it in my asp page it gives me this error

" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not to long

and here's the sql statement output...which will run just fine in the access query but not in my asp page.

INSERT INTO Paragraph (ParagraphNo, ParagraphText) VALUES (0,"You know what i really hate this thing. It's like ****.. My heads spinning because of this. I need haelp real urgent.")  
INSERT INTO Paragraph (ParagraphNo, ParagraphText) VALUES (1,"Okey enough of that.")  
INSERT INTO Paragraph (ParagraphNo, ParagraphText) VALUES (2,"testing one two three...")  
INSERT INTO Paragraph (ParagraphNo, ParagraphText) VALUES (3,"another test ")

thanks in advance for those who help..

What's the Ubound of your array? can you print it?

[equuelus]

I have already solve this problem thank you with the help with a great friend of mine.

If you need details on how i solved this problem just email me.