ASP page with Access 2000 DB

[jisoo22]

Hello people!

Ok so here's the situation. I assume this is a relatively easy thing to do but it doesn't work for me =P I have an Access 2000 database with a table where I will add records. The record adding will be accomplished with an asp page containing a form and text fields. I thought I had it all rigged up and ready but when I test it, I got this error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/sales/new.asp, line 49


Now I know some of you will cringe, but I used Macromedia Ultradev to produce the asp page. Can someone tell me what's going on?

Thanks,
Jisoo22

 

[vasah20]

post some code, particularly the block of code you are using to INSERT into access

 

[jisoo22]

Don't worry about it now, I figured it out =) Looks like ASP is pretty tempermental about fieldnames in a database containing spaces or single quote marks. Now I have a new problem, the ASP actually works now but it gives me a new error upon submission saying that none of my fields can be a zero-length string. Now I know that at some time or another a field will not need to be filled out. Can someone let me know of any solutions?

Thanks,
Jisoo22

 

[jonscott8]

Open your table in design view in Access. One (or more) fields of Text/Memo data type have their 'Allow Zero Length' attribute set to No (which is the default). Change this to yes.

Or if you still want to disallow empty strings, your ASP code will need to submit NULL instead of ''. Example:

INSERT INTO MyTable(Field1,Field2) VALUES('Mine','')

would become

INSERT INTO MyTable(Field1,Field2) VALUES('Mine',NULL)

or

INSERT INTO MyTable(Field1) VALUES('Mine') Jon Hawkins

 

[earme]

I usually run my form values through my 'RestoreNull' function. It replaces the "" strings with Nulls.

Function RestoreNull(varTemp)
	If Trim(varTemp) = "" Then
		RestoreNull = Null
	Else
		RestoreNull = varTemp
	End If
End Function

hth,
earme