Hi,

I have frontpage created form which points to an asp page.

The asp page uses the FP database region wizard to insert values from the form into an access database.

When I try to insert a value from a variable created in the asp page I get an error message stating that the value is empty.

The value is a number. I have set up a column in the access db and and added it to the INSERT statement.

When I set the access db field to be Text I receive a message saying the the field cannot be a zero length string and when I set it as a number I get a message saying "Data Mismatch".

Is it possible to insert a value created in an asp page into a database or does it have to be a form value?

Thanks

David

Consider this:

Dim someValue
Dim sSQL

someValue = 2
sSQL = "INSERT INTO Table(field) VALUES(" & CINT(someValue) &")"

Provided field is an INT field in the database this should execute fine but what I think your error is is that the variable you are trying to insert a varible into the database that is, essentially, empty. (Not to be confused with NULL as null value can be inserted into a database)

Are you sure that the variable you are trying to insert into the database actually contains a value?


"The one language all programmers understand is profanity."

Yes I have put the response.write line in to check what is in there.

Also the html (if the user pass's or fails the test) depends this variable (total_score) so there is definately a value in there.

I will try your suggestion.

thanks

The error message you are getting is most certainly related to an empty variable what you may want to check is:

INSERT INTO (field1, field2, field3) VALUES(var1, var2, var3)

Make sure that you are trying to insert the correct values into the correct fields as opposed to:

INSERT INTO (field1, field2, field3) VALUES(var1, var3, var2)

which may or may not throw an error depending on the datatype of field 2 and 3 and the values of var 2 and 3.


My only suggestion would be after you construct your INSERT statement, write it out to the screen to make sure that it contains all of the necssary information.

HTH.


"The one language all programmers understand is profanity."

My insert statement looks okay:

insert into fireworkstest (name,store_number,date_of,prior, who, located, displayed, stored_away, employees, access, age, frequently, id, quantity, packaged, damaged, half,total_score) values ('%%name%%', '%%store_number%%','%%date_of%%','%%prior%%','%%wh o%%', '%%located%%', '%%displayed%%','%%stored_away%%','%%employees%%', '%%access%%','%%age%%','%%frequently%%','%%id%%',' %%quantity%%','%%packaged%%','%%damaged%%','%%half %%','%%total_score%%')

There is certainly a value in total score as I have printed it to screen:
total_score= total/14*100
total_score=FormatNumber(total_score,0)
response.write total_score

The only thing that is left is the access database. All other columns are set as Text where as I have set this one as number - Integer field type. If I set it as Text then I receive the "One or more fields are empty" error message.

Does the Insert statement execute fine if you remove total_score from the field and value list?

You said when you try to insert the variable total_score into the database it will give you a mistmatch error, I see that you are calling formatnumber are you sure it is truncating the decimal places?

Numbers do not need to be surrounded by ' ' in SQL, just strings.

Why are you using % symbols?

"The one language all programmers understand is profanity."