|
 |
access_asp thread: Newbie to ASP
Message #1 by "Brooke Alexander" <noni_j@h...> on Wed, 24 Oct 2001 20:41:03
|
|
I am trying to insert data into 2 separate tables. I know there is a
simplier way to doing this instead of creating the connection twice, but I
keep running into road blocks. If someone can help me I would really
appreciate it. Below is the code and astericks by the area in question.
Thank you in advance.
******HERE:**********
If Request.Form("Action") = "Add" Then
'Start building the SQL strings with the required fields
strInsert = "Insert into People (PeopleNameLast,PeopleNameFirst"
strValues = "Values('" & CStr(Request.Form("txtPeopleNameLast")) &
_
"','" & CStr(Request.Form("txtPeopleNameFirst")) & "'"
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=Sailors"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
Response.Write "The following insert string was executed and " & _
"the values inserted into the People table.<P>"
Response.Write strInsert & ") " & strValues & ")"
End If
****AND HERE:******
If Request.Form("Action") = "Add" Then
'Start building the SQL strings with the required fields
strInsert = "Insert into Clubs (ClubCode,ClubName"
strValues = "Values('" & CStr(Request.Form("txtClubCode")) & _
"','" & CStr(Request.Form("txtClubName")) & "'"
'Add meeting date if present
If Len(Request.Form("txtMeetingDate")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubAnnualMeeting"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtMeetingDate")) & "'"
End If
'Add club dues if present
If Len(Request.Form("txtClubDues")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubDues"
'Add the value to the value string
strValues = strValues & "," & _
CCur(Request.Form("txtClubDues"))
End If
'Add web site URL if present
If Len(Request.Form("txtWebSite")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubWWWSite"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtWebSite")) & "'"
End If
'Add cranes if cheked
If Request.Form("chkCrane") = 1 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubHasCranes"
'Add True to the value string
strValues = strValues & ",True"
End If
'Add club membership number if present
If Len(Request.Form("txtMembers")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubMembership"
'Add the value to the value string
strValues = strValues & "," & _
CLng(Request.Form("txtMembers"))
End If
'Add notes if present
If Len(Request.Form("txtNotes")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubNote"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtNotes")) & "'"
End If
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=Sailors"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
'Display the insert string
Response.Write "The following insert string was executed and " & _
"the values inserted into the Clubs table.<P>"
Response.Write strInsert & ") " & strValues & ")"
End If
'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
Message #2 by "Zimmer Computer Consulting" <zee@t...> on Wed, 24 Oct 2001 16:05:20 -0700
|
|
Just create the connection and command objects once at the top of your
code -- they should be reusable. Let me know if this works.
I have marked with five apostrophes the statements in this section of your
code to remove or comment out:
'Add notes if present
If Len(Request.Form("txtNotes")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubNote"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtNotes")) & "'"
End If
''''' 'Create and open the database object
''''' Set objConn = Server.CreateObject("ADODB.Connection")
''''' objConn.Open "DSN=Sailors"
''''' 'Create the command object
''''' Set objCmd = Server.CreateObject("ADODB.Command")
' Set the command object properties
''''' Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
objCmd.CommandType = adCmdText
' Execute the command
objCmd.Execute
----- Original Message -----
From: Brooke Alexander <noni_j@h...>
To: Access ASP <access_asp@p...>
Sent: Wednesday, October 24, 2001 8:41 PM
Subject: [access_asp] Newbie to ASP
> I am trying to insert data into 2 separate tables. I know there is a
> simplier way to doing this instead of creating the connection twice, but I
> keep running into road blocks. If someone can help me I would really
> appreciate it. Below is the code and astericks by the area in question.
> Thank you in advance.
>
> ******HERE:**********
> If Request.Form("Action") = "Add" Then
>
> 'Start building the SQL strings with the required fields
> strInsert = "Insert into People (PeopleNameLast,PeopleNameFirst"
> strValues = "Values('" & CStr(Request.Form("txtPeopleNameLast")) &
> _
> "','" & CStr(Request.Form("txtPeopleNameFirst")) & "'"
>
> 'Create and open the database object
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "DSN=Sailors"
>
> 'Create the command object
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> 'Set the command object properties
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = strInsert & ") " & strValues & ")"
> objCmd.CommandType = adCmdText
>
> 'Execute the command
> objCmd.Execute
>
> Response.Write "The following insert string was executed and " & _
> "the values inserted into the People table.<P>"
> Response.Write strInsert & ") " & strValues & ")"
>
> End If
> ****AND HERE:******
> If Request.Form("Action") = "Add" Then
>
> 'Start building the SQL strings with the required fields
> strInsert = "Insert into Clubs (ClubCode,ClubName"
> strValues = "Values('" & CStr(Request.Form("txtClubCode")) & _
> "','" & CStr(Request.Form("txtClubName")) & "'"
>
> 'Add meeting date if present
> If Len(Request.Form("txtMeetingDate")) > 0 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubAnnualMeeting"
> 'Add the value to the value string
> strValues = strValues & ",'" & _
> Cstr(Request.Form("txtMeetingDate")) & "'"
> End If
>
> 'Add club dues if present
> If Len(Request.Form("txtClubDues")) > 0 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubDues"
> 'Add the value to the value string
> strValues = strValues & "," & _
> CCur(Request.Form("txtClubDues"))
> End If
>
> 'Add web site URL if present
> If Len(Request.Form("txtWebSite")) > 0 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubWWWSite"
> 'Add the value to the value string
> strValues = strValues & ",'" & _
> Cstr(Request.Form("txtWebSite")) & "'"
> End If
>
> 'Add cranes if cheked
> If Request.Form("chkCrane") = 1 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubHasCranes"
> 'Add True to the value string
> strValues = strValues & ",True"
> End If
>
> 'Add club membership number if present
> If Len(Request.Form("txtMembers")) > 0 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubMembership"
> 'Add the value to the value string
> strValues = strValues & "," & _
> CLng(Request.Form("txtMembers"))
> End If
>
> 'Add notes if present
> If Len(Request.Form("txtNotes")) > 0 Then
> 'Add the column name to the insert string
> strInsert = strInsert & ",ClubNote"
> 'Add the value to the value string
> strValues = strValues & ",'" & _
> Cstr(Request.Form("txtNotes")) & "'"
> End If
>
> 'Create and open the database object
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "DSN=Sailors"
>
> 'Create the command object
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> 'Set the command object properties
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = strInsert & ") " & strValues & ")"
> objCmd.CommandType = adCmdText
>
> 'Execute the command
> objCmd.Execute
>
> 'Display the insert string
> Response.Write "The following insert string was executed and " & _
> "the values inserted into the Clubs table.<P>"
> Response.Write strInsert & ") " & strValues & ")"
>
> End If
>
> 'Close and dereference database objects
> Set objCmd = Nothing
> objConn.Close
> Set objConn = Nothing
> %>
Message #3 by "Charles Mabbott" <aa8vs@m...> on Sat, 27 Oct 2001 10:35:32 -0400
|
|
Here is page I did that user enters info and if does not supply a date
I build a date as a default for following year........
<%
Option Explicit
Dim strConnect, datarr(3)
%>
<!-- #include file="DataStore11.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>Adding member</TITLE>
</HEAD>
<BODY>
<center>
<h1>Adding member to database<br>
with all information supplied</h1>
<h3>Joe's Maintenance Page</h3>
</center>
<%
Dim objRS, objComm, intNoOfRecords, strfname, strlname, strcall, strclass
, strdate
strfname = Request.Form("sfn") ' first name
strlname = request.form("sln") ' last name
strcall = UCase(request.form("scl") ) ' FCC call sign
strclass = request.form("drop") ' license class
strdate = request.form("sRn") ' date if provided
if ( len(strdate) < 1) then ' no date passed fill in default
datarr(0) = "4/"
datarr(1) = "1/"
datarr(2) = year(date)+1
strdate = join(datarr,"")
end if
response.write "<p>Insert " & strfname & " " & strlname & "<br>"
response.write "Call sign= " & strcall & " License= " & strclass & "<
br>"
response.write "Renewal date: " & strdate & "<br>"
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.ActiveConnection = strConnect
objComm.CommandText = "INSERT INTO Members(Fname, lname, callid, class,
renew) VALUES ( '" & strfname & "', '" & strlname & "' , '" & strcall &
"', ' " & strclass & "', '" & strdate & "')"
objComm.CommandType = adCmdText
objComm.Execute intNoOfRecords
Response.Write "The INSERT command has been executed; " & _
" " & intNoOfRecords & " member has been added <HR>"
Set objComm = Nothing
%>
<center>
<a href="addi94.asp#top">Enter</a> page<br>
<a href="upkeep.asp#top">Maintence</a> page
</center>
<h3>Last update: 9/7/01</h3>
</BODY>
</HTML>
----- Original Message -----
From: Zimmer Computer Consulting
Sent: Wednesday, October 24, 2001 7:59 PM
To: Access ASP
Subject: [access_asp] Re: Newbie to ASP
Just create the connection and command objects once at the top of your
code -- they should be reusable. Let me know if this works.
I have marked with five apostrophes the statements in this section of your
code to remove or comment out:
'Add notes if present
If Len(Request.Form("txtNotes")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubNote"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtNotes")) & "'"
End If
''''' 'Create and open the database object
''''' Set objConn = Server.CreateObject("ADODB.Connection")
''''' objConn.Open "DSN=3DSailors"
''''' 'Create the command object
''''' Set objCmd = Server.CreateObject("ADODB.Command")
' Set the command object properties
''''' Set objCmd.ActiveConnection =3D objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
objCmd.CommandType = adCmdText
' Execute the command
objCmd.Execute
Message #4 by "Noni Jenkins" <noni_j@h...> on Wed, 31 Oct 2001 19:07:09
|
|
I tried your suggestion and I got the following error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/noniweb/example1.asp, line 109
Below is the revised code. I hope I put everything is the right places.
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=Sailors"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
objCmd.CommandType = adCmdText
If Request.Form("Action") = "Add" Then
'Start building the SQL strings with the required fields
strInsert = "Insert into People (PeopleNameLast,PeopleNameFirst"
strValues = "Values('" & CStr(Request.Form("txtPeopleNameLast")) & _
"','" & CStr(Request.Form("txtPeopleNameFirst")) & "'"
'Execute the command
'objCmd.Execute
Response.Write "The following insert string was executed and " & _
"the values inserted into the People table.<P>"
Response.Write strInsert & ") " & strValues & ")"
End If
If Request.Form("Action") = "Add" Then
'Start building the SQL strings with the required fields
strInsert = "Insert into Clubs (ClubCode,ClubName"
strValues = "Values('" & CStr(Request.Form("txtClubCode")) & _
"','" & CStr(Request.Form("txtClubName")) & "'"
'Add meeting date if present
If Len(Request.Form("txtMeetingDate")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubAnnualMeeting"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtMeetingDate")) & "'"
End If
'Add club dues if present
If Len(Request.Form("txtClubDues")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubDues"
'Add the value to the value string
strValues = strValues & "," & _
CCur(Request.Form("txtClubDues"))
End If
'Add web site URL if present
If Len(Request.Form("txtWebSite")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubWWWSite"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtWebSite")) & "'"
End If
'Add cranes if cheked
If Request.Form("chkCrane") = 1 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubHasCranes"
'Add True to the value string
strValues = strValues & ",True"
End If
'Add club membership number if present
If Len(Request.Form("txtMembers")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubMembership"
'Add the value to the value string
strValues = strValues & "," & _
CLng(Request.Form("txtMembers"))
End If
'Add notes if present
If Len(Request.Form("txtNotes")) > 0 Then
'Add the column name to the insert string
strInsert = strInsert & ",ClubNote"
'Add the value to the value string
strValues = strValues & ",'" & _
Cstr(Request.Form("txtNotes")) & "'"
End If
'Execute the command
objCmd.Execute <-------------------error occurs here!!!!!!!!!!!!!!!!!!!!!!
'Display the insert string
Response.Write "The following insert string was executed and " & _
"the values inserted into the Clubs table.<P>"
Response.Write strInsert & ") " & strValues & ")"
End If
'Close and dereference database objects
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
|
|
|
View Cart
