PDA

Click to See Complete Forum and Search --> : INSERT INTO command

grizla
06-11-2003, 11:50 PM
I have a primary key column called 'ID' in my Access database table which is of type autonumber. I am trying to collect data from a form and insert it into the DB table. In the INSERT INTO command of my ASP, I do not have the ID column, do I need to add this to my code, or is it dealt with Automatically? Code:

mySQL= "INSERT INTO webdatabase "
mySQL= mySQL & "(SchoolName,NameofContact,Address1,Address2,Address3,Country,TelephoneNumber,FaxNumber,Email,Website ,AgeofChildren,numberofchildren, PurposeofVisit,TryScuba,ScubaRangers,Snorkeling,DiveCourse,TransporttoTanjungLeman,PreferredMenu,Spe cialRequirements/Comments) "
mySQL= mySQL & "VALUES ('" & Request.Form("SchoolName") & "','"
mySQL= mySQL & Request.Form("NameOfContact") & "'"
mySQL= mySQL & ",'" & Request.Form("Address1") & "'"
mySQL= mySQL & ",'" & Request.Form("Address2") & "','"
mySQL= mySQL & Request.Form("Address3") & "','"
mySQL= mySQL & Request.Form("Country") & "')"
mySQL= mySQL & Request.Form("TelephoneNumber") & "')"
mySQL= mySQL & Request.Form("FaxNumber") & "')"
mySQL= mySQL & Request.Form("Email") & "')"
mySQL= mySQL & Request.Form("Website") & "')"
mySQL= mySQL & Request.Form("AgeOfChildren") & "')"
mySQL= mySQL & Request.Form("NumberOfChildren") & "')"
mySQL= mySQL & Request.Form("PurposeOfVisit") & "')"
mySQL= mySQL & Request.Form("TryScuba") & "')"
mySQL= mySQL & Request.Form("ScubaRangers") & "')"
mySQL= mySQL & Request.Form("Snorkeling") & "')"
mySQL= mySQL & Request.Form("DiveCourse") & "')"
mySQL= mySQL & Request.Form("TransportToTL") & "')"
mySQL= mySQL & Request.Form("PreferredMenu") & "')"
mySQL= mySQL & Request.Form("SpecialRequirements") & "')"
cmelnick
06-12-2003, 10:41 AM
Sorry, don't mean to be picky, but you could also clean this code up a bunch and save a couple CPU cycles... You can donate them to needy kids in China :D

Not really necessary or anything, but I have done a lot of large programming projects, and the nicer your code is to read, the easier it is to do EVERYTHING else... Might be a waste of your time, but I like it. It just ends up being a lot easier to track down errors if you can quickly and easily match up name and value pairs.


mySQL= "INSERT INTO webdatabase (" & _
"SchoolName, " & _
"NameofContact, " & _
"Address1, " & _
"Address2, " & _
"Address3, " & _
"Country, " & _
"TelephoneNumber, " & _
"FaxNumber, " & _
"Email, " & _
"Website, " & _
"AgeofChildren, " & _
"numberofchildren, " & _
"PurposeofVisit, " & _
"TryScuba, " & _
"ScubaRangers, " & _
"Snorkeling, " & _
"DiveCourse, " & _
"TransporttoTanjungLeman, " & _
"PreferredMenu, " & _
"SpecialRequirements/Comments) " & _
"VALUES (" & _
"'" & Request.Form("SchoolName") & "', " & _
"'" & Request.Form("NameOfContact") & "', " & _
"'" & Request.Form("Address1") & "', " & _
"'" & Request.Form("Address2") & "', " & _
"'" & Request.Form("Address3") & "', " & _
"'" & Request.Form("Country") & "', " & _
"'" & Request.Form("TelephoneNumber") & "', " & _
"'" & Request.Form("FaxNumber") & "', " & _
"'" & Request.Form("Email") & "', " & _
"'" & Request.Form("Website") & "', " & _
"'" & Request.Form("AgeOfChildren") & "', " & _
"'" & Request.Form("NumberOfChildren") & "', " & _
"'" & Request.Form("PurposeOfVisit") & "', " & _
"'" & Request.Form("TryScuba") & "', " & _
"'" & Request.Form("ScubaRangers") & "', " & _
"'" & Request.Form("Snorkeling") & "', " & _
"'" & Request.Form("DiveCourse") & "', " & _
"'" & Request.Form("TransportToTL") & "', " & _
"'" & Request.Form("PreferredMenu") & "', " & _
"'" & Request.Form("SpecialRequirements") & "')"
grizla
06-13-2003, 04:18 AM
thank you for sorting that code out, much appreciated, now I get the error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/Email.asp, line 143


which I believe is something to do with permissions? Any ideas?
grizla
06-14-2003, 12:17 AM
Ok, this is my entire script, it is based on a MS tutorial:

<%


Function ParseBody(strText)
strText = Replace(strText, Chr(13), "<br>")
ParseBody = strText
End Function



Dim myConnString
Dim myConnection
Dim mySQL


myConnString = Application("SeaGypsy_ConnectionString")



Set myConnection = Server.CreateObject("ADODB.Connection")


myConnection.Mode = 3 '3 = adModeReadWrite

myConnection.Open myConnString


mySQL= "INSERT INTO SchoolInfo (" & _
"SchoolName, " & _
"NameofContact, " & _
"Address1, " & _
"Address2, " & _
"Address3, " & _
"Country, " & _
"TelephoneNumber, " & _
"FaxNumber, " & _
"Email, " & _
"Website, " & _
"AgeofChildren, " & _
"numberofchildren, " & _
"PurposeofVisit, " & _
"TryScuba, " & _
"ScubaRangers, " & _
"Snorkeling, " & _
"DiveCourse, " & _
"TransporttoTanjungLeman, " & _
"PreferredMenu, " & _
"SpecialRequirements_Comments) " & _
"VALUES (" & _
"'" & Request.Form("SchoolName") & "', " & _
"'" & Request.Form("NameOfContact") & "', " & _
"'" & Request.Form("Address1") & "', " & _
"'" & Request.Form("Address2") & "', " & _
"'" & Request.Form("Address3") & "', " & _
"'" & Request.Form("Country") & "', " & _
"'" & Request.Form("TelephoneNumber") & "', " & _
"'" & Request.Form("FaxNumber") & "', " & _
"'" & Request.Form("Email") & "', " & _
"'" & Request.Form("Website") & "', " & _
"'" & Request.Form("AgeOfChildren") & "', " & _
"'" & Request.Form("NumberOfChildren") & "', " & _
"'" & Request.Form("PurposeOfVisit") & "', " & _
"'" & Request.Form("TryScuba") & "', " & _
"'" & Request.Form("ScubaRangers") & "', " & _
"'" & Request.Form("Snorkeling") & "', " & _
"'" & Request.Form("DiveCourse") & "', " & _
"'" & Request.Form("TransportToTL") & "', " & _
"'" & Request.Form("PreferredMenu") & "', " & _
"'" & Request.Form("SpecialRequirements") & "')"


myConnection.Execute mySQL


myConnection.Close

Set myConnection = Nothing

Dim myCDONTSMail
Dim strFrom
Dim strTo
Dim strSubject
Dim strBody


strFrom="enquiries@siburesort.com.com"

strTo=Request.Form("EMail")

strSubject = "School Trips Form Submission"

strBody="The following information was submitted:" & Chr(13)
strBody = strBody & Request.Form("SchoolName") & " "
strBody = strBody & Request.Form("NameofContact")
strBody = strBody & Chr(13) & Request.Form("Address1") & Chr(13)
strBody = strBody & Request.Form("Address2") & Chr(13)
strBody = strBody & Request.Form("Address3") & Chr(13)
strBody = strBody & Request.Form("Country") & Chr(13)
strBody = strBody & Request.Form("TelephoneNumber") & Chr(13)
strBody = strBody & Request.Form("FaxNumber") & Chr(13)
strBody = strBody & Request.Form("Email") & Chr(13)
strBody = strBody & Request.Form("Website") & Chr(13)
strBody = strBody & Request.Form("AgeofChildren") & Chr(13)
strBody = strBody & Request.Form("PurposeofVisit") & Chr(13)
strBody = strBody & Request.Form("TryScuba") & Chr(13)
strBody = strBody & Request.Form("ScubaRangers") & Chr(13)
strBody = strBody & Request.Form("Snorkeling") & Chr(13)
strBody = strBody & Request.Form("DiveCourse") & Chr(13)
strBody = strBody & Request.Form("TransporttoTanjungLeman") & Chr(13)
strBody = strBody & Request.Form("PreferredMenu") & Chr(13)
strBody = strBody & Request.Form("SpecialRequirements/Comments") & Chr(13)


strBody = strBody & Chr(13) & "Thank you for submitting your data."

Set myCDONTSMail = CreateObject("CDONTS.NewMail")

myCDONTSMail.Send strFrom,strTo,strSubject,strBody


Set myCDONTSMail = Nothing



%>
<p><font face="Verdana" color="#FF0000"><b>Thank you for submitting your
information!<br>
</b></font><font face="Verdana" size="2">You will receive an e-mail
shortly.&nbsp; The e-mail was sent using the following information:</font></p>
<b><font face="Verdana" size="2">Sent To: <%
Response.Write Request.Form("EMail")

%>
<br>
From&nbsp;&nbsp;&nbsp; : Microsoft PSS Sample Page</font>
<p><font face="Verdana" size="2">Subject: Send to Database and E-mail</font></p>
<p><font face="Verdana" size="2">Content: <%
Response.Write(ParseBody(strBody))

%>
</font></p>
<hr noshade size="1" style="color: #000000">
<p>&nbsp;</p>
</b>
grizla
06-15-2003, 01:02 AM
Hi, tried the code above but have hit another problem, get this error message when I run the code:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/Email.asp, line 14

Line 14 is:

myRecordSet.Open mySQL, myConnection, 2, 3
grizla
06-15-2003, 09:47 AM
Ok, I will have a look at that, thank you very much for all of your help!

Grizla
grizla
06-15-2003, 09:14 PM
Ok, hopefully this will be the last thing I need, but do i put the quotes in the SQL statement here, If so where do they go? I only have character and numeric data, I also have a field ID, which is the PK and is an autonumber but I presume this will be dealt with automatically:

mySQL= "INSERT INTO SchoolInfo (" & _
"SchoolName, " & _
"NameofContact, " & _
"Address1, " & _
"Address2, " & _
"Address3, " & _
"Country, " & _
"TelephoneNumber, " & _
"FaxNumber, " & _
"Email, " & _
"Website, " & _
"AgeofChildren, " & _
"numberofchildren, " & _
"PurposeofVisit, " & _
"TryScuba, " & _
"ScubaRangers, " & _
"Snorkeling, " & _
"DiveCourse, " & _
"TransporttoTanjungLeman, " & _
"PreferredMenu, " & _
"SpecialRequirements_Comments) " & _
"VALUES (" & _
"'" & Request.Form("SchoolName") & "', " & _
"'" & Request.Form("NameOfContact") & "', " & _
"'" & Request.Form("Address1") & "', " & _
"'" & Request.Form("Address2") & "', " & _
"'" & Request.Form("Address3") & "', " & _
"'" & Request.Form("Country") & "', " & _
"'" & Request.Form("TelephoneNumber") & "', " & _
"'" & Request.Form("FaxNumber") & "', " & _
"'" & Request.Form("Email") & "', " & _
"'" & Request.Form("Website") & "', " & _
"'" & Request.Form("AgeOfChildren") & "', " & _
"'" & Request.Form("NumberOfChildren") & "', " & _
"'" & Request.Form("PurposeOfVisit") & "', " & _
"'" & Request.Form("TryScuba") & "', " & _
"'" & Request.Form("ScubaRangers") & "', " & _
"'" & Request.Form("Snorkeling") & "', " & _
"'" & Request.Form("DiveCourse") & "', " & _
"'" & Request.Form("TransportToTL") & "', " & _
"'" & Request.Form("PreferredMenu") & "', " & _
"'" & Request.Form("SpecialRequirements") & "')"


myConnection.Execute mySQL