argh! simple form to make database!! 1

scoobydope · May 31, 2000

Ok, this is going to seem trite to most of you i think.  Basically I am trying to ask the residents of a complex that I run an ISP for whether or not they want PC access to the network.  Its a small (500 host) lan that so far only allows these weak little proprietary TV computers a connection to the ethernet network.  Most people would probably like to hook up their own computer to the lan.  So I want to make an asp form that asks them all the following: 1) suite number 2) name 3) phone number 4) yes or no.... pc hookup. I tried to make one using javascript, but I could only get it to email me the results of the form, and the results are god-awful ugly. (you can check out the code at <A HREF="

http://www.theresidences.com/pchook.html"

TARGET="_new">

http://www.theresidences.com/pchook.html</A>) As

you can see, the fields are all there and just DYING for some asp/sql linkage. I simply want the "input" button to append their info onto a database table, so that i can access the table with another asp page that loads the current list of names and numbers.  Kinda like a membership list. A third party set this site up, and there is a database already in place using "Pervasive SQL server 7.0.  In the last month, I have purchased : "Active Server Pages Unleashed" "Beginning ASP Databases" "Teach yourself Visual Interdev in 21 days" and "MySQL and Msql" by O'rielly. Still lost. I KNOW this is a simple operation.  It would only take somebody about 15 minutes to do if they knew the software..... but I am at a total loss! Basically, I would like to use ASP to snag the IP address of the poster,(hidden field) to verify their information (all the IP addresses are unique to each suite address) and have this IP appended to the form submission. and I wouldn't even be too upset if this didn't link to a SQL database.  If the thing would just email me something that was a little more readable than what I am getting I would be happy. anybody have any ideas?

LuvASP · Jun 1, 2000

Actually this would take 7.5 mins to do. Just kidding! I grabbed the following from your site: <FORM NAME="PC_input_form"> you're missing a method and action attribute for this FORM tag. Use POST for Method, and ACTION would be an ASP page. So your modified FORM tag would look like: <FORM NAME="PC_input_form" Method="POST" Action="SomeASPPage.asp"> name field Suite field Phone number Select field Clear button and Send button Also you need a hidden field here which will capture the IP address of the user: <INPUT type="hidden" id=text1 name=text1 value="<%Response.write(Request.ServerVariables("REMOTE_HOST&quot

)%>"> </FORM> Once the user hits the SUBMIT button, s/he will be taken to "SomeASPPage.asp" where you need to retrieve the values of the name, suite, phone, selectbox, and the IP. Put these in variables: name = request.form("name&quot

suite = request.form("suite&quot

phone = request.form("phone&quot

ip = request.form("ip&quot

Now consult your books to create a DSN to the database via the control panels and then use standard ADO to open the connection to the database. Create a simple INSERT statement and execute it via the Recordset object of ADO to finish writing to the database. I think thats it!

scoobydope · Jun 1, 2000

I know it should be simple, but I have NEVER written an asp page. Set Conn = Server.CreateObject("ADODB.Connection&quot

is what i assume you  mean... I can figure the rest of this out from the books i guess.  My main problem is that all my books are referring to Microsoft SQL server 7.0, and the original company that set this server up installed Pervasive SQL server.  None of my docs really pertain to it. So my question for you goes back to your form: <INPUT type="hidden" id=text1 name=text1 value="<%Response.write(Request.ServerVariables("REMOTE_HOST&quot

)%>">                            </FORM> you have 'name=text1' and then later in the second .asp page, you have ip = request.form("ip&quot

would the line in the form that pulls the ip address have to be 'name=ip' ? or 'id=ip' I am assuming the someASPPage.asp is pulling the data from the form,  but I am not sure if it is the "id" tag that you are using, or the "name" tag that i am using that the request.form("_variable_&quot

would be referring to. For example I have in my form this: <input type="text" name="nameField"> so in the second asp page, would i use this: name = request.form("nameField&quot

or would i have to use my form like this, with the added id tag: <input type="text" name="nameField" id=nameField> did that make sence? Thanx for your help.... i am beginning to see the clouds clearing.

LuvASP · Jun 2, 2000

<INPUT type="hidden" id=text1 name=text1 value="<%Response.write(Request.ServerVariables("REMOTE_HOST&quot

)%>">                            </FORM> you have 'name=text1' and then later in the second .asp page, you have ip = request.form("ip&quot

would the line in the form that pulls the ip address have to be 'name=ip' ? or 'id=ip' ANSWER: Yes, change the id and name to ip. I am assuming the someASPPage.asp is pulling the data from the form,  but I am not sure if it is the "id" tag that you are using, or the "name" tag that i am using that the request.form("_variable_&quot

would be referring to. For example I have in my form this: <input type="text" name="nameField"> so in the second asp page, would i use this: name = request.form("nameField&quot

ANSWER: Yes, that is correct. All the text, selectbox fields on the first page need to have a 'name' argument in the HTML tag. And thats what you refer to in the SomeASPPage.asp to pull the data. Set Conn = Server.CreateObject("ADODB.Connection&quot

ASNWER: Yes, this is it. The steps would be Set Conn = server.createobject("ADODB.connection&quot

conn.open "DSN=DSNYoucreate","Username","Password" (where username and password are what you specify when creating the DSN.) Set rs = server.createobject("ADODB.recordset&quot

rs.open "Recordsetsource","conn" (where Recordsetsource could be a SQL Statement or a name of a table within the database.) As far as creating the DSN you need to check with Pervasive SQL Server or the documentation as to which provider it supports, OLE DB or ODBC, depending on the answer you choose the right provider when creating the DSN through windows(control panel ODBC 32 source).

scoobydope · Jun 2, 2000

OK!!!!! I think i got it!  It has taken me almost 14 hours of scouring, but I think i have finally started to see the LIGHT!!!! I created a new DSN called "suites" with pervasive... in that DSN, I created a Table called PChookup.  In that table, i made 6 columns.  I called them : firstName lastName IP_addr phone suite PChook_ans This is what I have so Far: ok, first page, this is the page with the form on it that asks for the input of the suite's information: <TABLE CELLPADDING=2 CELLSPACING=5 BORDER=0 WIDTH=480 height=250 bgcolor="white"> <form action=pc_add_list.asp method=post name=PC_addfrm> <INPUT type=hidden name=Action VALUE=Add> <INPUT type=hidden  name=IP_num value="<%Response.write(Request.ServerVariables("REMOTE_ADDR&quot

)%>">                                <TR VALIGN="top">                                                            <td>First Name:</td>                     <td><input type=text name=first_nameField></td></tr>                                          <tr><td>Last Name:</td>                     <td><input type=text name=last_nameField></td></tr>                                                                                                                 <tr><td> Suite Number (eg. W1204, E1204)</td>                      <td> <input type=text name=suiteField size=22></td></tr>                                                                <tr><td> Phone Number </td>                       <td><input type=text name=phoneField  size=22 maxlength=10 ></td></tr>                                                                   <tr>                     <td colspan="2">                       <select name=hook> <option selected value="yes"> Yes I would like to connect my PC <option value="no_pc"> No, Thankyou, I don't have a PC <option value="no_way"> No, Thankyou, I prefer to keep my existing provider </select> </td></tr>                                             <tr><td colspan="2"><center><input type="reset" value="clear"></center></td></tr>                      <tr><td colspan="2"><center><input type=button name=btnSubmit value=Submit></center></td></tr>                      </FORM>                                </TABLE>                               <script language=vbscript> Sub btnSubmit_OnClick() 'The only required fields at this point are 'last name and phone number If Len(PC_addfrm.first_nameField.value) = 0 Then Alert "You must enter your first name" PC_addfrm.first_nameField.focus Exit Sub ElseIf Len(PC_addfrm.last_nameField.value) = 0 Then Alert "You must enter your last name" PC_addfrm.last_nameField.focus Exit Sub ElseIf Len(PC_addfrm.suiteField.value) < 4 Then Alert "You must enter your suite number (ie: E1205" PC_addfrm.suiteField.focus Exit Sub ElseIf Len(PC_addfrm.phoneField.value) < 8 Then Alert "You must enter your full Phone number (ie: 555-1234)" PC_addfrm.phoneField.focus Exit Sub End If 'If we make it this far then submit the form Call PC_addfrm.submit() End Sub </script> </BODY> </HTML> then my second page comes up... the one that is called from the first one: pc_add_list.asp: I only have the begining of this page, because the stupid "underscore" thing is screwing me up!!!! check this out: <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Basic 6.0"> <TITLE>Chapter 10 - SQL Statements That Modify Data</TITLE> </HEAD> <BODY> The final page <% 'Declare variables needed Dim strInsert Dim strValues Dim adCmdText 'Set required variables adCmdText = 1 '*********************************************************** '* If an Add was requested, add the new club to the database '*********************************************************** If Request.Form("Action&quot

= "Add" Then 'Start building the SQL strings with the required fields strInsert = "Insert into PChookup (IP_addr,lastName,firstName,phone,suite,pchook_ans) strValues = "Values('" & CStr(Request.Form("IP_num&quot

) & _ "','" & CStr(Request.Form("last_nameField&quot

) & "'" 'Add meeting date if present If Len(Request.Form("txtMeetingDate&quot

) > 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&quot

) & "'" End If 'Add club dues if present If Len(Request.Form("txtClubDues&quot

) > 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&quot

) End If 'Add web site URL if present If Len(Request.Form("txtWebSite&quot

) > 0 Then 'Add the column name to the insert string strInsert = strInsert & ",Club

http://WWWSite"

'Add the value to the value string strValues = strValues & ",'" & _ Cstr(Request.Form("txtWebSite&quot

) & "'" End If 'Add cranes if cheked If Request.Form("chkCrane&quot

= 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&quot

) > 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&quot

) End If 'Add notes if present If Len(Request.Form("txtNotes&quot

) > 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&quot

) & "'"     End If 'Create and open the database object Set objConn = Server.CreateObject("ADODB.Connection&quot

objConn.Open "DSN=Sailors" 'Create the command object Set objCmd = Server.CreateObject("ADODB.Command&quot

'Set the command object properties Set objCmd.ActiveConnection = objConn objCmd.CommandText = strInsert & &quot

" & strValues & &quot

" 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." Response.Write strInsert & &quot

" & strValues & &quot

" End If 'Close and dereference database objects Set objCmd = Nothing objConn.Close Set objConn = Nothing %> </BODY> </HTML> Ok, obviously I am half way into modifying a pre-existing tutorial  page.... but what is screwing me up is that damned UNDERSCORE!!! strInsert = "Insert into PChookup (IP_addr,lastName,firstName,phone,suite,pchook_ans) strValues = "Values('" & CStr(Request.Form("IP_num&quot

) & _ "','" & CStr(Request.Form("last_nameField&quot

) & "'" The ^strValues^ section says: strValues = "Values(" & CStr(Request.Form)"IP_num&quot

) & _ What is with that underscore??????  I can't figure out how to finish the whole statement.  I don't need all the "IF "variable=0" statements,  I just need to have a : strInsert = "Insert into PChookup (IP_addr,lastName,firstName,phone,suite,pchook_ans) strValues = "Values('" & CStr(Request.Form("IP_num&quot

) & _ "','" & CStr(Request.Form("last_nameField&quot

) & "'" etc and so on... but I can't re-write the code without knowing what that damn underscore is supposed to be there for.  I don't requre any "if's" all of my variables are required, so the final page should only need two lines for the SQL.  One for the strInsert, and one for the strValues.   But that underscore is really confusing me! all of my columns in my table are of the "char" type, god ... i felt so GREAT about four hours ago... thought I had IT! now I am lost again. thanx in advance.

Guest_imported · Jun 4, 2000

No hay nadie que hable castellano

Guest_imported · Jun 4, 2000

You're almost there. The underscore is simply a line continuation character, for when a line is longer than the width of your screen. You can simply erase it and type your code all on one line. A bit of advice - put your request.form stuff into variables, then use them in your code. It will make your code cleaner and easier to read, and it's good programming style. So.. ip_addr = request.form("ip_num&quot

lastName = Request.Form("last_nameField&quot

then.. strInsert = "Insert into PChookup (IP_addr,lastName,firstName,phone,suite,pchook_ans) strValues = "Values('" & ipaddr & "','" & lastName & "'," etc... Bill Burns

scoobydope · Jun 5, 2000

GOT IT! 'Start building the SQL strings with the required fields strInsert = "Insert into PChookup (IP_addr,lastName,firstName,phone,suite,pchook_ans" strValues = "Values('" & CStr(Request.Form("IP_num&quot

) & _ "','" & CStr(Request.Form("last_nameField&quot

) & "'" strValues = strValues & ",'" & _ Cstr(Request.Form("first_nameField&quot

) & "'" strValues = strValues & ",'" & _ Cstr(Request.Form("phoneField&quot

) & "'" strValues = strValues & ",'" & _ Cstr(Request.Form("suiteField&quot

) & "'" strValues = strValues & ",'" & _ Cstr(Request.Form("hook&quot

) & "'" 'Create and open the database object Set objConn = Server.CreateObject("ADODB.Connection&quot

objConn.Open "suites", "usrname", "password" 'Create the command object Set objCmd = Server.CreateObject("ADODB.Command&quot

'Set the command object properties Set objCmd.ActiveConnection = objConn objCmd.CommandText = strInsert & &quot

" & strValues & &quot

" objCmd.CommandType = adCmdText 'Execute the command objCmd.Execute 'Display the insert string Response.Write "Thank you for your input.  You will be contacted within the next few days " End If 'Close and dereference database objects Set objCmd = Nothing objConn.Close Set objConn = Nothing %> __________________________________________________ Then the page I use to pull the values up is this: <% 'Dim oRSs Dim oConn Set oConn = Server.CreateObject ("ADODB.connection&quot

'Set oRSs = Server.CreateObject("ADODB.recordset&quot

'oRSs.Open "PChookup", "DSN==suites", "sa", "" oConn.Open "suites", "usrname", "password" 'strSQL = "SELECT IP_addr, pcHook_ans, phone, lastName, firstName, suite FROM PCHookup ORDER BY IP_addr DESC;" strSQL = "SELECT IP_addr, pcHook_ans, phone, lastName, firstName, suite FROM PCHookup" Set rsSuite = oConn.Execute(strSQL) If rsSuite.EOF Then    Response.Write "Sorry, nobody has filled out the form" Else %>   <TR>     <TD>Suite # </TD>     <TD>(IP ADDRESS) </TD>     <TD>First Name </TD>     <TD>Last Name </TD>     <TD>Phone Number </TD>     <TD>Answer</TD></TR>          <%     While NOT rsSuite.EOF          'Session("IP_addr&quot

= rsSuite.Fields(0) 'Session("answer&quot

= rsSuite.Fields(1) 'Session("phone&quot

= rsSuite.Fields(2) 'Session("lName&quot

= rsSuite.Fields(3) 'Session("fName&quot

= rsSuite.Fields(4) 'Session("suite&quot

= rsSuite.Fields(5)   Response.Write "<TR><TD>" & rsSuite(5)&  "</TD>"   Response.Write  "<TD>" & rsSuite(0)&  "</TD>"   Response.Write  "<TD>" & rsSuite(4)&  "</TD>"   Response.Write  "<TD>" & rsSuite(3)&  "</TD>"   Response.Write  "<TD>" & rsSuite(2)&  "</TD>"   Response.Write  "<TD>" & rsSuite(1)&  "</TD></TR>"      rsSuite.MoveNext   WEND   Response.Write "</table>"   end if   %></TABLE> >>>  Notice how i had to comment out the object.recordset?  For some reason, the server wouldn't recognise it.  I kept getting "type mismatch error: open" when trying to run the page.  The book I have covers the first 4 chapters only using the recordset parameter.. not the connection parameter.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

argh! simple form to make database!! 1

scoobydope

ISP

LuvASP

Programmer

scoobydope

ISP

LuvASP

Programmer

scoobydope

ISP

Guest_imported

New member

Guest_imported

New member

scoobydope

ISP

Similar threads

Part and Inventory Search

Sponsor