• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

ADODB AddNew method

I am trying to add a new record and populate the fields using a Loop, but its not working, any ideas?  Its listed under the comments DEBUG.

Thank you
Anthony
cgsabol@seanet.com

========================================<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Registration</title>
</head>

<body>
<%
      Dim adoConn                  ' ADODB Connection Object
      Dim adoRec                  ' ADODB Recordset Object
      Dim strFields(8)      ' Data from Registration Form
      Dim strColumn(8)      ' Column Names
      Dim strConnString      ' SQL Statement
      Dim i                        ' Counter            
      
      strFields(0)= Request.Form("txtUserName")
      strFields(1) = Request.Form("txtFirstName")
      strFields(2) = Request.Form("txtMiddleName")
      strFields(3) = Request.Form("txtLastName")
      strFields(4) = Request.Form("txtBirthDay")
      strFields(5)= Request.Form("txtEmail")
      strFields(6)= Request.Form("txtHomePage")
      strFields(7) = Request.Form("txtPassword")
      
      strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=\\p350\wwwroot\Anthony\Test\ww\gene.mdb;" & _
       "User Id=admin;" & _
       "Password=;"
       
   ' Create Connection/Recordset objects
      Set adoConn = CreateObject("ADODB.Connection")
      Set adoRec = CreateObject("ADODB.Recordset")
      
      adoConn.Open strConnString
      adoRec.Open "tblUserInfo", adoConn, 3, 3
      
      '-------------------------------------------------------------------------
      ' Check for null Values in the data being sent from the Registration Form.
      ' If we have a null value change it to a empty string
      Do Until i = Ubound(strFields) + 1
            If IsNull(strFields(i)) Then strFields(i) = ""
            i = i + 1
      Loop
      '--------------------------------------------------------------------------
      i = 0 ' Recycle variable

      adoRec.AddNew ' Add a new record
      
      ===DEBUG===================================================================
      '----DOES NOT WORK!
      'Do Until i = Ubound(strFields) + 1
            'adoRec.Fields(i).Value = strFields(i)
            'i = i + 1
      'Loop
      
      '----DOES NOT WORK!
      'Do Until i = Ubound(strFields) + 1
            'adoRec(i) = strFields(i)
            'i = i + 1
      'Loop
      
      '===========================================================================
      
      '----DOES WORK------------------------------------------------------------
      ' Write data to the Columns in the database
      adoRec.Fields("strUserName")=strFields(0)
      adoRec.Fields("strFirstName")=strFields(1)
      adoRec.Fields("strMidName")=strFields(2)
      adoRec.Fields("strLastName")=strFields(3)
      adoRec.Fields("dtmBirthday")=strFields(4)
      adoRec.Fields("strEmail")=strFields(5)
      adoRec.Fields("strHomePage")=strFields(6)
      adoRec.Fields("strPassword")=strFields(7)
      '-----------------------------------------------------------------------------
      
      adoRec.Update
      adoRec.Close
      
      Set adoConn = Nothing
      Set adoRec = Nothing
%>
      
<BR>
<H1 ALIGN="CENTER">Thank you <%Response.Write(strFields(0))%></H1>
<BR>

<TABLE>
      <TR><TD>UserName:</TD><TD><%Response.Write(strFields(0))%></TD></TR>
      <TR><TD>First Name:</TD><TD><%Response.Write(strFields(1))%></TD></TR>
      <TR><TD>Middle Initial:</TD><TD><%Response.Write(strFields(2))%></TD></TR>
      <TR><TD>Last Name:</TD><TD><%Response.Write(strFields(3))%></TD></TR>
      <TR><TD>Birthday:</TD><TD><%Response.Write(strFields(4))%></TD></TR>
      <TR><TD>Email::</TD><TD><%Response.Write(strFields(5))%></TD></TR>
      <TR><TD>Homepage:</TD><TD><%Response.Write(strFields(6))%></TD></TR>
      <TR><TD>Password:</TD><TD><%Response.Write(strFields(7))%></TD></TR>
</TABLE>

</body>

</html>
0
toys032498
Asked:
toys032498
1 Solution
 
mberumenCommented:
have you tried this statement?

===DEBUG===================================================================
                     i=0
                     Do Until i = Ubound(strFields) + 1
                      adoRec.Fields(i) = strFields(i)
                      i = i + 1
                      Loop
                     '===========================================================================
0
 
HencahCommented:
have tried this ??

adoRec.AddNew
i=0
Do Until i = Ubound(strFields) + 1
  adoRec.Fields(i)= strFields(i)
  i = i + 1
Loop


0
 
HencahCommented:
oops didn't mean to duplicate mbrumen's
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mtielandCommented:
I recommand specifying a sql statement instead of a tablename so you can manage the order the fields are presented to you. And a command object could in some strange cases be a solution as wel.

example:

set Cmd1 = Server.CreateObject("adodb.command")
Cmd1.ActiveConnection = adoConn
Cmd1.CommandText = "select strUserName, strFirstname, strMidName, strLastName, dtmBirthday, strEmail, strHomepage, strPassword from tblUserInfo"
adoRec.Open Cmd1, , 3, 3

I do find your connectionstring somewhat strange too, you should maybe try this (if this is a access file...):

adoDb.ConnectionString = "DBQ=\\p350\wwwroot\Anthony\Test\ww\gene.mdb;UID=admin;PWD=;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;}"

Hope this helps,

good luck (Some seem to interpet this wrong)(I mean good luck in general)

Martijn
0
 
mtielandCommented:
ps.

You should use this kind of loop:
adorec.addnew
for i = 0 to Ubound(strFields)
 adorec(i)=strFields(i)
next
adorec.update

One other thing I found out is:
When a new record is inserted you can start reading the values from the record only after requerying the table.
(Access only)

Martijn
0
 
toys032498Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now