Little Help....

courtjester · #1 Apr 5th, 2004, 09:57

Ok, this is what I have gotten so far. I am working on the Registration form, and I know, I haven't even gotten this far without a problem. here is my code.

Code: Select all

<%
Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "DSN=Mydatabase"

Set recordSet =Server.CreateObject("ADODB.Recordset")
recordSet.Open "SELECT * FROM List" ,connectionToDatabase, 1,2

recordSet.AddNew
recordSet("First")=Request.Form("First")
recordSet("Last")=Request.Form("Last")
recordSet("User")=Request.Form("User")
recordSet("Password")=Request.Form("Password")
recordSet("Address")=Request.Form("Address")
recordSet("City")=Request.Form("City")
recordSet("State")=Request.Form("State")
recordSet("Country")=Request.Form("Country")
recordSet("Email")=Request.Form("Email")
recordSet("Date")=Request.Form("Date")
recordSet.Update

Do while Not recordSet.EOF
		recordSet.MoveNext
Loop

connectionToDatabase.close
Set connectionToDatabase=Nothing
%>

And I am getting this error.

Code: Select all

Provider error '80020005' 

Type mismatch. 

/ASPtests/Submit.asp, line 28

The problem is that it actually matches the content of the Database exactly and that of the form. So I am confused as to why I am getting this error. line 28 in the code is actually this recordSet("Date")=Request.Form("Date")

A couple of other things i wanted to ask about... Is there a better/Easier way of connecting to a database? Cause this way works fine and all, but I can only use 1 DSN with my current host.

Smokie · #2 Apr 5th, 2004, 11:48

I would never do it like that, but whatever floats your boat...

as you know, string, number and date are all different data types, if you have a field in your database called "Date", and it is of Date/Time datatype, then you need to convert the string (Request.Form("Date")) into date format using the CDate() function.

Trebz · #3 Apr 5th, 2004, 15:33

...but you'll need to make sure you validate the input otherwise you'll get an error with CDate()

Run the date through a function like this before you insert it:

Code: Select all

Function FixDBDates(dtmDate)

  If dtmDate = "" Then
    FixDBDates = dtmDate
    Exit Function
  End If

  Dim dtmTmp
  
  dtmTmp = Day(dtmDate) & "/" & MonthName(Month(dtmDate)) & "/" & Year(dtmDate) & " " & Hour(dtmDate) & ":" & Minute(dtmDate) & ":" & second(dtmDate)
  
  FixDBDates= dtmTmp
  
End Function

...

recordSet("Date") = FixDBDates(Request.Form("Date"))

If you gave this function 20/11/2004 it would give you 20/december/2004

Webforumz Staff · #4 Apr 5th, 2004, 18:14

You need to delimit the date to put it in a date field. For Access the delimiter is # so you'd do:

recordSet("Date")= "#" & Request.Form("Date")& "#"

courtjester · #5 Apr 5th, 2004, 19:50

It's ok Smokie... Like I have said before, Im very new at this, and this site is not for anyone, it's just to help me learn what I am doing. Cause you never get anything accomplished without practice :wink:.

Webforumz Staff · #6 Apr 5th, 2004, 20:51

Oh, I didn't see the end of your post with the questions. First , you can use a DSN-less connection. They're faster and don't need anything setup on the host.

Personally "connectionToDatabase" is a really long thing to keep typing even if it's very easy to read. I always just use "con" or "objCon" - just a cosmetic thing where you'll make less typos. Same with recordset... rs or objRs.

Finally I almost never use AddNew. I just add the whole record with one line using an INSERT INTO sql statement.

Trebz · #7 Apr 6th, 2004, 08:29

Always use Option Explicit to stop typos Catalyst :razz:

Sorry for forgetting to wrap the date in hashes...haha, or pound signs if your American!

courtjester · #8 Apr 6th, 2004, 09:15

well Im american and I call them hashes... Hrmm Am I just stupid? Well It's still not working even by taking all of yall's advice. So I think I am just going to go further and use some other methods of doing it. I thought this was the best way to do it, but I guess I might use something else.

Ok, Like I said I am a BIG NOOB so this is me trying to learn and is not for any real purpose besides that, but if I am using Access to do my database, can I still use SQL Statements? If so, then i can do this easily with the following.

Code: Select all

sqlStatement="INSERT INTO List(First,Last,User) Values ("& frmFirst & ",'" & frmLast &"'," & frmUser & ")"

Set recordSet=connectionToDatabase.Execute(sqlStatement)

connectionToDatabase.Close
Set connectionToDatabase=Nothing
%>

Trebz · #9 Apr 6th, 2004, 09:23

The Hash pound thing came from something I read regarding C# (C Sharp), i'm sure someone somewhere will know what I was talking about!

And yes, you can use SQL INSERT statements for access.

courtjester · #10 Apr 6th, 2004, 09:26

YAY!!! Well that's going to save me about a BILLION hours of strenuous work. :wink: . I just thought that SQL worked with MYSQL for some strange reason.. Guess cause in my book they use SQL statemenst with MYSQL databases only, and use the Recordsets with Access. Guess I read too much into it, lol.

Smokie · #11 Apr 6th, 2004, 09:36

your missing some single quotes:

sqlStatement="INSERT INTO List(First,Last,User) Values ('"& frmFirst & "','" & frmLast &"','" & frmUser & "')"

courtjester · #12 Apr 6th, 2004, 09:43

ohh.. lol, no wonder it wasn't working.. I almost posted back on here again.. Thanks for saving me some time there Smokie :wink:

Smokie · #13 Apr 6th, 2004, 10:18

for future reference, if you are inserting a string, it must be wrapped in single quotes, a number doesnt need any quotes and a date needs #'s (or single quotes if you are using MS SQL Server)

Trebz · #14 Apr 6th, 2004, 10:51

What knowledge....I can only dream of being as clever as Smokie

Smokie · #15 Apr 6th, 2004, 12:36

one day my son, your time will come...

Webforumz Staff · #16 Apr 6th, 2004, 18:44

Also, be careful about using reserved words for field names, sometimes things like 'Date' or 'Name' used as a field name will cause an error and drive you crazy.

Tim356 · #17 Apr 8th, 2004, 14:19

I'm glad Court Jester's asking these questions, I'm learning alot from just reading all of this... thanks guys.

courtjester · #18 Apr 8th, 2004, 19:03

Thanks Cata... I got soooo soo mad couldn't figure out what I was doing wrong. Forgot Date is a reserved word. haha.. Im going to rip that page out of my book and glue it to my forehead :wink:

Webforumz Staff · #19 Apr 8th, 2004, 20:25

Glad to help.

		Webforumz.com > Main Forums > Program Your Website > Classic ASP
Little Help....