need help desparately!

[gcapp]

Hello everyone,
I hope someone can help me with this because I am really stuck.
What I trying to do is be able to view, edit and add records into a database table I have.
I have attached the three pages involved, along with info about my database.
The problem I am having is not viewing the database - that works fine, the problem is with the edit and add pages. I keep getting a type mismatch error for starters with both. And it is with the Start_Date and End_Date. In my database, they are a Date/Time datatype and the type mismatch happens there.
The thing that confuses me is that I have these exact same pages working for other tables that I update.


And if someone is willing to look at these pages, the other thing I don't know how to do has to do with the edit page. When you go to edit a record, the form box comes up to put the info in that you want to edit, but the info doesn't show for the existing record. I wanted to know how I could have the form display the info that is already in the table and then be able to edit it.


I really need to get this done and if someone can see what is wrong with my pages and show me how to change it, it would be greatly appreciated.

Gary

[whammy]

You might want to check out "query.asp", which you can download thanks to the fine folks at webmonkey.com at:

http://hotwired.lycos.com/webmonkey/02/35/index4a.html?tw=backend

... I actually use that (with just the database name and path modified) to do all of my database viewing, updating, etc. with pure SQL. I was going to write something like that myself, but why bother.

Not to mention writing your queries in SQL code will make you MUCH more aware of things like "data type mismatches" so you will be better at writing them and troubleshooting them in your ASP! ...And it's a pretty good tutorial they have there, to boot...

[gcapp]

Whammy,
I appreciate you sending me that page. However, I am trying to learn as i go and real examples are what I need as i am on a tight schedule.
Those pages don't give me code examples of add and edit.

As i said before, I have the three pages that I mentioned before that work for another table in my database but not this one. They are the exact same pages. The tables area little different but I can't figure out why they don't work.

I need someone to look at the pages I attached in the last post. If you don't have the time that is fine. I hope someone can figure it out for me or guide me along.

Thanks

[allida77]

Did you not change
this:
Request.Form(CDate("txtEnd_Date"))

to this:
CDate(Request.Form("xtEnd_Date"))

Also are you passing correct date formats into the db?

[gcapp]

Allida,
Yes I changed it and i still get errors. The only way to have some one help me is to look at the pages I attached in the previous posts.

If you know of any code that will allow me to add and edit a record in a database, please let me know.


I just can't figure it out.

Gary

[BigDaddy]

I wondered why are you using "recordset.addnew" instead of just doing a standard insert statement to the database? And the same with the delete.

Not to say that your way won't work, but I've just never used that method before. I always do conn.execute(sql), with sql being my insert or update statement.

It would also be helpful if you would post the actual error that you got. Perhaps that would help in troubleshooting the problem.

[gcapp]

Big Daddy,
I have attached the three pages in question here - view, edit and add, along with my database information.

Viewing the records is working fine, but when I first try to add a record - I get this error:

Type mismatch.
/admin/add-events.asp, line 48

which is the line on the add page where this line occurs:
RecordSet.Fields("Start_Date") = Request.Form("txtStart_Date")
I know this has to do with the fact that the datatype is Date/Time.


When I try to edit an exisiting record, I get this error:
Type mismatch.
/admin/edit-events.asp, line 56

Again it has to do with the same exact line as above.

Now even if that part gets corrected, there is something else that I would want to do with the edit page. But I'll wait and ask you that, if I can get these errors fixed.

Gary

[whammy]

It STILL looks like you haven't changed it to what Allida said (since you posted a line that doesn't have it in there!!!):

CDate(Request.Form("txtStart_Date"))

Please try the things that are suggested before posting again, the above should work...

P.S.:


If you know of any code that will allow me to add and edit a record in a database, please let me know.


I do know of such code:

textfield = "Blah"
datetimefield = CDate("1/1/2002")

INSERT INTO tablename (textfield, datetimefield) VALUES('" & textfield & "','" & datetimefield & "')

.....

datetimefield = Now()

UPDATE tablename SET datetimefield = '" & datetimefield & "' WHERE yourprimarykey = 1

CREATE TABLE tablename(
id autoincrement,
author text(255),
authoremail text(255),
authorwebsite text(255),
title text(255),
script memo,
comments memo,
downloadlink text(255),
active bit,
posttime datetime)

SELECT * FROM tablename

DROP TABLE tablename

Are a few examples

[gcapp]

Whammy,
Ok I'm at my whitts end. I'll PAY somebody to get this code to work right.

I went to a few help sites and can up with some other code to add records to a database. The page is attached.

I keep getting an error of Type mismatch. Now as you can see on the page, I have CDate where my fields in my database are of Date/Time datatype.

I just don't know what to do. I've been at this for 3 days now and getting absolutely nowhere.

I can't change my database because I am using it with another page that works correctly.

So let me ask you a few things:

1) After looking at my code how should I enter info into the Start_Date and End_Date form fields? Should it be like 01/01/01 or 010101 or how??

2) What can I do to get this working? Like I said, I've been to numerous forums and nothing. I know when I was really stuck before, Morgoth really helped me. I'm not saying you don't - you do help, but Morgoth actually wrote some pages for me that were really helpful. So I don't know if you can pass this on to him or give me some ideas. Like I said, I'm at the point where I would pay someone to write these pages for me.

What shoudl I do???????

Gary

[whammy]

01/01/2001

Should work fine. I won't write the scripts for you, though, since I do that all day at work.

P.S. Whenever you are getting an error with SQL, it helps to write the values out on the page, so you can see what you're actually trying to insert into the database.

Also, you can use this to return the datatype that ASP currently thinks your variable is:

<%
Dim blah
blah = "help"
Response.Write(TypeName(blah))
' The above will return String !
%>

As for the question "What should I do???" my honest answer to that would be to buy a book on ASP - like ASP 3.0 by Wrox.com. :D

:)

[gcapp]

Ok I came up with a whole new code for my edit, add pages.

Can someone look at my page and tell my why I am getting this error:

Syntax error in UPDATE statement.


For the life of me I can't see what is wrong.

The line is 270 which is the Conn.Execute (SQL)

I hope someone can tell me what might be wrong.

I can tell you that all fields in my database are text datatypes, except the Start_Date and End_Date fields, which are Date/Time.

Some one please help!

Gary

[dawho2]

You're having problems with that SQL Query?

The syntax for an UPDATE statement is as follows...

UPDATE **tablename** SET .. . .. . . WHERE ........


You're missing the SET in your code....try that out...if it works, yay, if not...well...let us know

[dlconnor]

If this is an Access db, you need to use the pound sign (#) delimiter surrounding the date/time field values.
EX: VALUES ('text1','text2',#date1#,#date2#)

By using the CheckString you are enclosing them in single quotes (which you need to not do)

My suggestion would be send the SQL statement to a page as a string and display it. Then cut & paste it into the realtime query and let the DB show you where the errors are. Then go fix them one by one.

[gcapp]

dlconner,
So are you saying that I should put # signs in place of CheckString in my code in every spot?

Should it look like this?

SQL = SQL & #(txtEvent_Name,",")#

if not how would it look or do I have to change my code somewhere else?

I would do what you said about Access and finding the error, but I guess I really don't know what you mean.

[dlconnor]

No. Syntactically in Access data types have different "delimiters".

For a text column the delimiter is the single quote.

For a numeric column there is no delimiter.

For a datetime column the delimiter is the # sign.

I would change checkString to merely strip off any embedded single quotes in the entry and handle the delimiters in the SQL string. Like this:

sqlstr = "UPDATE mytable SET "
sqlstr = sqlstr & "TextVar = '" & valTextVar & "', "
sqlstr = sqlstr & "DateVar = #" & valDateVar & "#, "
sqlstr = sqlstr & "NumbVar = " & valNumbvar
sqlstr = sqlstr & "WHERE KeyVar = " & valKeyVar

Note that the last value does not have a comma before the WHERE keyword (that's another problem with the CheckString routine)

[dlconnor]

Try it like this:

<script LANGUAGE="VBScript" RUNAT="Server">
FUNCTION CheckString (s)
pos = InStr(s, "'")
While pos > 0
s = Mid(s, 1, pos) & "'" & Mid(s, pos + 1)
pos = InStr(pos + 2, s, "'")
Wend
END FUNCTION
</script>


'Submit updated/edited information to database

SQL = "UPDATE Events SET"
SQL = SQL & "Event_Name = '" & CheckString(txtEvent_Name) & "', "
SQL = SQL & "Location = '" & CheckString(txtLocation) & "', "
SQL = SQL & "City = '" & CheckString(txtCity) & "', "
SQL = SQL & "State = '" & CheckString(txtState) & "', "
SQL = SQL & "Zip_Code = '" & CheckString(txtZip_Code) & "', "
SQL = SQL & "Description = '" & CheckString(txtDescription) & "', "
SQL = SQL & "Start_Date = #" & CheckString(txtStart_Date) & "#, "
SQL = SQL & "End_Date = #" & CheckString(txtEnd_Date,) & "#, "
SQL = SQL & "Time = #" & CheckString(txtTime) & "#, " ' is time also a datetime field?
SQL = SQL & "Phone = '" & CheckString(txtPhone) & "', "
SQL = SQL & "Website = '" & CheckString(txtWebsite) & "', "
SQL = SQL & "Email = '" & CheckString(txtEmail) & "', "
SQL = SQL & "event_image = '" & CheckStringtxtevent_image) "' "
SQL = SQL & "WHERE ID = '" & txtID "'"

[gcapp]

dlconner,
Ok like i said I'm new. I changed my code the way you said in the first post and I get syntax errors at the lines where you put the # signs.

So if i change my code to the way you said in the 2nd post, I don't know how to change the entire code. This code was given to me and I'm not sure how to change the whole thing to accommodate your code.

I've tried getting this to work for a week and it's beginning to get frustrating.

Maybe is you could tell me how to fix my original code with the # signs errors. Or if you have an easier solution for a dummy, I'd appreciate it.

I attached the page with the code that I changed in your 1st post and the original page I have.

And by the way, Time is a text field.

Gary

[dlconnor]

Gary

I have started working on the file you posted, but it will take me some time to sort it all out. I tend to think that it is over complicated for what you are trying to do and am sending the attached page as an example of how I would implement the functionality you are looking for.

[gcapp]

dlconner,
Well I'm trying to follow the example you sent me but i am getting stuck. I don't have your table structure to follow plus I'm only using one table, not multiple like yours. And in my table I have a field called ID with an autonumber datatype.

So I'm trying to muddle through this but I don't think it's going to work.

If you can help along, I would appreciate it.

Gary

[dlconnor]

Send me an email from my profile and let's take this off-line. I'll see if I can help.