toys032498
asked on
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("txtMiddleNam e")
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.OL EDB.4.0;" & _
"Data Source=\\p350\wwwroot\Anth ony\Test\w w\gene.mdb ;" & _
"User Id=admin;" & _
"Password=;"
' Create Connection/Recordset objects
Set adoConn = CreateObject("ADODB.Connec tion")
Set adoRec = CreateObject("ADODB.Record set")
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 ")=strFiel ds(0)
adoRec.Fields("strFirstNam e")=strFie lds(1)
adoRec.Fields("strMidName" )=strField s(2)
adoRec.Fields("strLastName ")=strFiel ds(3)
adoRec.Fields("dtmBirthday ")=strFiel ds(4)
adoRec.Fields("strEmail")= strFields( 5)
adoRec.Fields("strHomePage ")=strFiel ds(6)
adoRec.Fields("strPassword ")=strFiel ds(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(str Fields(0)) %></TD></T R>
<TR><TD>First Name:</TD><TD><%Response.W rite(strFi elds(1))%> </TD></TR>
<TR><TD>Middle Initial:</TD><TD><%Respons e.Write(st rFields(2) )%></TD></ TR>
<TR><TD>Last Name:</TD><TD><%Response.W rite(strFi elds(3))%> </TD></TR>
<TR><TD>Birthday:</TD><TD> <%Response .Write(str Fields(4)) %></TD></T R>
<TR><TD>Email::</TD><TD><% Response.W rite(strFi elds(5))%> </TD></TR>
<TR><TD>Homepage:</TD><TD> <%Response .Write(str Fields(6)) %></TD></T R>
<TR><TD>Password:</TD><TD> <%Response .Write(str Fields(7)) %></TD></T R>
</TABLE>
</body>
</html>
Thank you
Anthony
cgsabol@seanet.com
==========================
<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.
<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("txtMiddleNam
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.OL
"Data Source=\\p350\wwwroot\Anth
"User Id=admin;" & _
"Password=;"
' Create Connection/Recordset objects
Set adoConn = CreateObject("ADODB.Connec
Set adoRec = CreateObject("ADODB.Record
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
adoRec.Fields("strFirstNam
adoRec.Fields("strMidName"
adoRec.Fields("strLastName
adoRec.Fields("dtmBirthday
adoRec.Fields("strEmail")=
adoRec.Fields("strHomePage
adoRec.Fields("strPassword
'-------------------------
adoRec.Update
adoRec.Close
Set adoConn = Nothing
Set adoRec = Nothing
%>
<BR>
<H1 ALIGN="CENTER">Thank you <%Response.Write(strFields
<BR>
<TABLE>
<TR><TD>UserName:</TD><TD>
<TR><TD>First Name:</TD><TD><%Response.W
<TR><TD>Middle Initial:</TD><TD><%Respons
<TR><TD>Last Name:</TD><TD><%Response.W
<TR><TD>Birthday:</TD><TD>
<TR><TD>Email::</TD><TD><%
<TR><TD>Homepage:</TD><TD>
<TR><TD>Password:</TD><TD>
</TABLE>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops didn't mean to duplicate mbrumen's
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\Anthon y\Test\ww\ gene.mdb;U ID=admin;P WD=;Driver ={Microsof t Access Driver (*.mdb)};DriverId=25;FIL=M S Access;}"
Hope this helps,
good luck (Some seem to interpet this wrong)(I mean good luck in general)
Martijn
example:
set Cmd1 = Server.CreateObject("adodb
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\Anthon
Hope this helps,
good luck (Some seem to interpet this wrong)(I mean good luck in general)
Martijn
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
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
ASKER
thanks
adoRec.AddNew
i=0
Do Until i = Ubound(strFields) + 1
adoRec.Fields(i)= strFields(i)
i = i + 1
Loop