Insert statement

[jrthor2]

What is wrong with thsi statement:

SQLstmt = "INSERT INTO Bible_Verses (date,bible_verse)"
SQLstmt = SQLstmt & " VALUES ('" & request.form("date") & "','" & request.form("bible_verse") &"')"

I get the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

In my database, the date field is set as Date/Time and the format is Short Date. When I fill out my form, for the date I entered 7/2/2003. I tried changing the date field to Text and that didn't help.

Thanks

P.S. - Is there a way to check if the date the person entered is in the correct format?

Thanks again!!

[Ribeyed]

Hi,
which database are you using?
if Access then the correct syntax is:

SQLstmt = "INSERT INTO Bible_Verses (date,bible_verse)"
SQLstmt = SQLstmt & " VALUES (#" & request.form("date") & "#,'" & request.form("bible_verse") &"')"


hope this helps

[gerjan]

P.S. - Is there a way to check if the date the person entered is in the correct format?

To be sure all the persons fill in the date at the right way, you can use drop down menu's for day, month and year.

For the other question:
What is wrong with thsi statement:

I'm not sure, it looks ok.
Make sure that:
- bible_verse also is set as text
- there no apestrophes in request.form("bible_verse")
(to be sure you can use the replace statement to replace one ' by two '')

[jrthor2]

Well, I found a tutorial, and I changed my page to look like this, but I still get the same error:

<%
Dim noErrors
Dim strdate
Dim bible_verse

noErrors = True

If Trim(Request.Form("date")) = "" Then
Response.write "<b>Please enter a Date.</b><br>"
noErrors = False
End If

If Trim(Request.Form("bible_verse")) = "" Then
Response.write "<b>Please enter a Bible Verse.</b><br>"
noErrors = False
End If

If noErrors Then

Const DATE_DELIMITER = "#"

strdate = Request.Form("date")
strdate = Replace(strdate, "'", "''")
bible_verse = Request.Form("bible_verse")
bible_verse = Replace(bible_verse, "'", "''")

'Date Field
strdate = CDate(strdate)

Set conn = server.createobject("adodb.connection")
DSNtemp="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/db/ziondb.mdb")
conn.Open DSNtemp

SQLstmt = "INSERT INTO Bible_Verses (date, bible_verse)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & DATE_DELIMITER & strdate & DATE_DELIMITER
SQLstmt = SQLstmt & ", "
SQLstmt = SQLstmt & "'" & bible_verse & "'"
SQLstmt = SQLstmt & ");"

Response.Write SQLstmt
conn.execute(SQLstmt)


conn.Close
Set conn = nothing
Set SQLstmt = nothing
%>
Thank you for adding a New Bible Verse to our database. Your Bible Verse will show up on the front page of Zion's website on the date you entered (<%= strdate %>).
<%
'Response.Redirect("add.asp")
End if
%>

There are no apotrophe's in the bible_text field

Here is the SQL Statement:

INSERT INTO Bible_Verses (date, bible_verse) VALUES (#7/2/2003#, 'The Lord spoke to Moses, saying: Make two silver trumpets; you shall make them of hammered work; and you shall use them for summoning the congregation, and for breaking camp. - Numbers 10:1 - 2 ');

[jrthor2]

I got it figured out, Access didn't like the column name of date. I changed it to date_show and it works fine now.