|
Subject:
|
Help Please with my code to insert data
|
|
Posted By:
|
shopgirl
|
Post Date:
|
2/12/2004 5:01:42 AM
|
Hi, I have been trying to figure out what's wrong with my coding, but i could not figure out why i cant insert my data to the table? i have 2 form which first form for insert data and after submit button the viewdata form will be show. i have a lot of fields in my table, so i will only show 9 column. the database i'm using is Access, and for the connection DSN.in my table i'm using 2 fileds of Number,
one with auto number the other with number i dont know this have affect or not.
these are my code for first form
<! --#include file="Source.asp" -->
<%
Dim intAngka,intNomer, varEmployeeNo, varSurname, varGivenName
Dim varStatus, varSection, varAddress, varHired_date
Dim strConnect, rsData, Conn, strSQl
intAngka = Request.Form ("Angka") --the data type is auto number in access
intNomer = Request.Form ("Nomer") -- the data type is number in access
varEmployeeNo = Request.Form ("EmployeeNo")
varSurname = Request.Form ("Surname")
varGivenName = Request.Form ("GivenName")
varStatus = Request.Form ("Status")
varSection = Request.Form ("Section")
varAddress = Request.Form ("Address")
Hired_date = Request.Form ("DateofHired")
Session ("Angka") = intAngka
Session ("Nomer") = intNomer
Session ("EmployeeNo") = varEmployeeNo
Session ("Surname") = varSurname
Session ("GivenName") = varGivenName
Session ("Status")= varStatus
Session ("Section") = varSection
Session ("Address") = varAddress
Session ("DateofHired") = Hired_date
If Request.Form("submit1")= "submit1" Then
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "DSN=Insert"
strsql ="INSERT INTO Expat_EmployeeReg " &_
"(Nomer,Surname,GivenName,Status,EmployeeNo " & _
"Section,DateofHired,Address)" & _
"VALUES (" &_
"'"&intNomer&"','" & varSurname & "','" & varGivenName & "','" & varStatus & "','" & varEmployeeNo & "', " & _
"'" & varSection & "', '" & Hired_date & "','" & varAddress & "')"
Conn.Execute strSql
If Conn.State then
Conn.Close
set Conn= Nothing
end if
%>
code for my second form:
<%
Dim intAngka,intNomer, varEmployeeNo, varSurname, varGivenName
Dim varStatus, varSection, varAddress, varHired_date
Dim strConnect, rsData, Conn, strSQl, i, lngRecordNo
lngRecordNo = (Request.QueryString("Nomer"))
intAngka = Session("Angka")
intNomer = Session("Nomer")
varEmployeeNo = Session ("EmployeeNo")
varSurname = Session ("Surname")
varGivenName = Session ("GivenName")
varStatus = Session ("Status")
varSection = Session ("Section")
varAddress = Session ("Address")
Hired_date = Session ("DateofHired")
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "DSN=Insert"
Set rsData = Server.CreateObject ("ADODB.Recordset")
strSQL= "SELECT * FROM Expat_EmployeeReg"
rsData.Open strSQl, Conn
i=1
%>
<%
Do While Not rsData.EOF
%>
<TD><%=i%></TD>
<TD><%=rsData("Nomer")%></TD>
<TD><%=rsData("EmployeeNo")%></TD>
<TD><%=rsData("Surname")%></TD>
<TD><%=rsData("GivenName")%></TD>
<TD><%=rsData("Status")%></TD>
<TD><%=rsData("Section")%></TD>
<TD><%=rsData("Address")%></TD>
<TD><%=rsData("DateofHired")%></TD>
<%
i = i + 1
rsData.MoveNext
Loop
rsData.Close
Conn.Close
Set rsData= Nothing
Set Conn= Nothing
%>
maybe someone can help me with the coding i really Appreciate,
thank you
|
|
Reply By:
|
Greg Griffiths
|
Reply Date:
|
2/12/2004 5:26:02 PM
|
Can youi print the SQL String before you execute it and then try running that in Query Analyser to see if that reports an error.
|
|
Reply By:
|
jrwlkn
|
Reply Date:
|
2/12/2004 5:33:09 PM
|
Q1. Is there currently data in the table?
q2. If so, does your second form connect with and display that data.
John
|
|
Reply By:
|
shopgirl
|
Reply Date:
|
2/12/2004 9:32:37 PM
|
thanks for your replay...Could you tell me how to do the query analyser, because i never use it.
yes in my table there is a current data and in my second form is connect and can display the current data where i entry the data from access. but when i run my code to insert data from web that's the problem...
|
|
Reply By:
|
jrwlkn
|
Reply Date:
|
2/13/2004 11:25:40 AM
|
IT MAY BE A SYNTAX ISSUE WITH ALL THE APOSROPHES, QUOTES, ETC.
TRY REMOVING ALL INSERT FIELDS AND VALUES EXCEPT FOR ONE
IN OTHER WORDS, ONLY INSERT ONE VALUE INTO ONE FIELD,
THEN ADD A COUPLE MORE AND TEST, AND A COUPLE MORE AND TEST.
TEDIOUS, I KNOW BUT IT IS AN EFFECTIVE WAY TO TROUBLESHOOT.
THE FIRST ATTEMPT WILL CONFIRM PROPER CONNECTION WITH THE DB AND THAT YOUR INSERT SYNTAX IS CORRECT
LET ME KNOW HOW IT GOES
JOHN
|
|
Reply By:
|
shopgirl
|
Reply Date:
|
2/14/2004 12:01:58 AM
|
I try your suggestion, i input some of my data in my first form and submit the second form come up and the new values doesn't show in the table.
the things is there are no error message until i refresh the second form and i got error message like this:
Error Type:
Provider (0x80004005)
Unspecified error
/Nur/Project_HRD/ViewData.asp, line 23
Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
Page:
POST 733 bytes to /Nur/Project_HRD/ViewData.asp
POST Data:
Nomer=2&EmployeeNo=234&Surname=Sena&GivenName=Daulay&Status=Permanent+Employee&Section=COR&Address=Jakarta&WorkLocation=Jakarta&DateofHired=02%2F03%2F2003&ProjectNo=123&Citizen=Indonesia&PlaceofBirth= . . .
is that mean my insert syntax still have an error?
|
|
Reply By:
|
DaveGerard
|
Reply Date:
|
2/20/2004 3:48:34 PM
|
Here you go shopgirl. You don't need to request Angka from the form as that field is an auto-number field and you cannot insert into it. You also don't need to set session variables for these values. Let me know if this helps.
'==== Page 1 ====
<%
Nomer = Request("Nomer")
EmployeeNo = Request("EmployeeNo")
Surname = Request("Surname")
GivenName = Request("GivenName")
Status = Request("Status")
Section = Request("Section")
Address = Request("Address")
DateofHired = Request("DateofHired")
set rs = server.createobject("adodb.recordset")
sql = "select * from Expat_EmployeeReg"
rs.open sql, "dsn=Insert", 3, 3
rs.addnew
rs("Nomer") = Nomer
rs("Surname") = Surname
rs("GivenName") = GivenName
rs("Status") = Status
rs("EmployeeNo") = EmployeeNo
rs("Section") = Section
rs("Address") = Address
rs("DateofHired") = DateofHired
rs.update
%>
'==== Page 2 ====
<table width="100%">
<tr>
<%
set rs = server.createobject("adodb.recordset")
sql = "select * from Expat_EmployeeReg"
rs.open sql, "dsn=Insert"
if not rs.eof then
do while not rs.eof
%>
<td><%=rs("Angka")%></td>
<td><%=rs("Nomer")%></td>
<td><%=rs("Surname")%></td>
<td><%=rs("GivenName")%></td>
<td><%=rs("Status")%></td>
<td><%=rs("EmployeeNo")%></td>
<td><%=rs("Section")%></td>
<td><%=rs("Address")%></td>
<td><%=rs("DateofHired")%></td>
<%
rs.movenext
loop
else
%>
<td colspan="9">There are no records for the criteria that you have selected.</td>
<%
end if
rs.close
set rs = nothing
%>
</tr>
</table>
|
|
Reply By:
|
shopgirl
|
Reply Date:
|
2/24/2004 12:58:54 AM
|
I did try your code but my computer doesn't suppport Addnew method thank's DaveGerard and I did figure out how to insert datas by using SQL syntax I put my Access table in the wrong directory and I also make some changes with my database connection. instead using DSN Know I'm using Microsoft Jet OLEDB 4.0 and it's work.
thank's guys
|
