Little Help....

[courtjester]

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]

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]

...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]

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]

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]

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]

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]

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]

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]

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]

your missing some single quotes:

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

[courtjester]

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]

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]

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

[Smokie]

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

[Webforumz Staff]

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]

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

[courtjester]

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]

Glad to help.