insert the contents of dynamically generated textbox in a table. 2

[GoldPearl]

(asp 3.0 + sql server 2K)

i want to insert the contents of dynamically generated textbox in a table.

in my first web page, a user can make a series of selection (eg select a position - let's say 'engineer').

when the user clicks the next button(next1), a table is displayed with the employeeid, duty and a blank textbox where the user can input the scores.(same format as below: user-duty-score)

after filling in the scores, the user clicks the next button(next2) to input the data in the database, but the insert statement is not doing what i want it to do. it is inserting the scores on the same line as 2,3(eg of scores) and i want it to display separately in the table as:

user1 duty1 score1
user1 duty2 score2
user1 duty3 score3
etc...

enclosed is the code for the next2 button:

Sub Next2
dim rs
dim cn
Dim sql, sql1
dim i

date1=request.form("txtdate")
per=request.form("lstper")
emp=request.form("lstemp")
pos=request.form("lstpos")
dept=request.form("lstdept")
pjt=request.form("lstpjt")
response.Write(emp)
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)

Set rs = Server.CreateObject("ADODB.Recordset")

txtmscore = request.Form("txtmscore")
'for i = 0 to txtmscore
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & txtmscore & "' )"
'response.Write(sql)
'response.End()

cn.Execute(sql)
'next

end sub


anybody can help me out. it's urgent.
Tks
Gold Pearl.

 

[DotNetGnat]

post back your complete code and the errors you are getting...

-DNG

 

[GoldPearl]

hello people...

sorry had a small accident and could not come online.

i found the "mistake" and corrected it. now it works fine! i used the hidden text box to retrieve the name. in fact the only modification made was to put the hidden textbox outside the table.

code for button 1 (next1):

Sub Next1

dim rs
dim cn
dim sql
Dim fPerformUpdate
Dim strUpdated

date1=request.form("txtdate")
per=request.form("lstper")
emp=request.form("lstemp")
pos=request.form("lstpos")
dept=request.form("lstdept")
pjt=request.form("lstpjt")

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)

Set rs = Server.CreateObject("ADODB.Recordset")


' Now, the table may be loaded afresh
sql = "Select duty from posduty where position = '" & pos & "'"
rs.Open sql, cn
If rs.BOF And rs.EOF Then
%>
<FONT size=3 color=red><font color="#FFFFFF" size="2" face="Arial">No Records
Found</font><STRONG><BR>
<BR>
</STRONG></FONT>
<%
ELSE
' Records Exist. Build our Datasheet
%>

</p>
<p>&nbsp;</p>
<table width="52%" border="2" cellpadding="4" bordercolor="#CCCCCC" bgcolor="#CCCCCC" nowrap>
<tr bordercolor="#CCCCCC">
<!-- Table headers: -->
<td width="40%" align="center"><strong><font color="#000000" size="2" face="Arial">Employee
ID</font></strong></td>
<td width="51%" align="center"><strong><font color="#000000" size="2" face="Arial">Duty</font></strong></td>
<td width="9%" align="center"><strong><font color="#000000" size="2" face="Arial">Score</font></strong></td>
</tr>
<%
' Now, create the INPUT Boxes that make up our
' "DataSheet" and populate them
Do Until rs.EOF
%>

<tr bordercolor="#CCCCCC">

<td><div align="center">
<font color="#000000" size="2" face="Arial"><%=request.Form("lstemp")%></font></div></td>

<td><div align="left"><font color="#000000" size="2" face="Arial"><%=rs("duty")%></font></div></td>
<td> <div align="center">
<input name="txtmscore" type="text" id="txtmscore" size="5">
</div></td>
</tr>
<%
rs.MoveNext
loop
end if

%>
</table>
<input type='hidden' name='txtemp' value='<%=request.Form("lstemp")%>'>
<p>
<input name="btnnext2" type="submit" id="btnnext2" value="Next">
</p>
<p><Font size=2><%=strUpdated%></Font> </p>
<TD colspan=4 align="right">
</form>
<%
cn.close
set rs=nothing
set cn=nothing
end sub

code for button 2 (next2):

Sub Next2
dim rs
dim cn
Dim sql, sql1
dim i,score

date1=request.form("txtdate")
per=request.form("lstper")
pos=request.form("lstpos")
dept=request.form("lstdept")
pjt=request.form("lstpjt")
emp2 = request.form("txtemp")
'response.Write(emp)
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)

Set rs = Server.CreateObject("ADODB.Recordset")

txtmscore = request.Form("txtmscore")
score = split(txtmscore,", ")

for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore ([userid], mdscore) VALUES ('" & request.Form("txtemp")& "' ,'" & score(i) & "' )"

cn.Execute(sql)
next

end sub


once more thnx a lot DotNetGnat & Kendel for the excellent support.

Cheers,
Gold Pearl.

 

[GoldPearl]

hello again.

now i have a new problem.problem still concerns the 2 pages. to recap, i have a 1st page where the user is allowed to make a series of selection from different lists. one of the selection is "postion" (eg engineer). then the user clicks the next(next1) button and a 2nd page is displayed displaying a table in the format:

employeeid duty score

x duty1 score1
x duty2 score2
x duty3 score3

in fact the duties corresponds to the duty of an engineer(retrieved from a table from the db based on the 'position' specified by the user in page 1.

the scores are dynamically generated textboxes where the user enters scores for the different duties.

now i want to insert these data in a table in the database but am unable to retrive the data for the duties which are retrieved from the database.

number of duties are different for different positions.

can anybody help me?

Gold Pearl.

 

[DotNetGnat]

that is because you are losing your rs("duty") value on the next page... may be you could create another hidden form variable or create a new session variable

option 1:

<form type="hidden" name=dutydesc value="<%=rs("duty")%>"

and on the next page you can have request.form("dutydesc) in your insert query

option 2:
session("dutydesc")=rs("duty")

and on the next page you can have session("dutydesc") in your insert query

[/code]

-DNG

 

[GoldPearl]

oops sorry, i already did that.forgot to post my code but am unable to retrieve the value from the hidden textboxes because unlike name, the have different values and different respective scores.

next1 button:

Sub Next1

dim rs
dim cn
dim sql
Dim fPerformUpdate
Dim strUpdated
dim date1, per, pos, dept, pjt

date1 = request.form("txtdate")
per = request.form("lstper")
pos = request.form("lstpos")
dept = request.form("lstdept")
pjt = request.form("lstpjt")

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)

Set rs = Server.CreateObject("ADODB.Recordset")


' Now, the table may be loaded afresh
sql = "Select duty from posduty where position = '" & pos & "'"
rs.Open sql, cn
If rs.BOF And rs.EOF Then
%>
<FONT size=3 color=red><font color="#FFFFFF" size="2" face="Arial">No Records
Found</font><STRONG><BR>
<BR>
</STRONG></FONT>
<%
ELSE
' Records Exist. Build our Datasheet
%>

</p>
<p>&nbsp;</p>
<table width="52%" border="2" cellpadding="4" bordercolor="#CCCCCC" bgcolor="#CCCCCC" nowrap>
<tr bordercolor="#CCCCCC">
<!-- Table headers: -->
<td width="40%" align="center"><strong><font color="#000000" size="2" face="Arial">Employee
ID</font></strong></td>
<td width="51%" align="center"><strong><font color="#000000" size="2" face="Arial">Duty</font></strong></td>
<td width="9%" align="center"><strong><font color="#000000" size="2" face="Arial">Score</font></strong></td>
</tr>
<%
' Now, create the INPUT Boxes that make up our
' "DataSheet" and populate them
Do Until rs.EOF
%>

<tr bordercolor="#CCCCCC">

<td><div align="center">
<font color="#000000" size="2" face="Arial"><%=request.Form("lstemp")%></font></div></td>

<td><div align="left"><font color="#000000" size="2" face="Arial"><%=rs("duty")%></font></div></td>
<input type='text' name='txtduty' value='<%=rs("duty")%>'>

<td> <div align="center">
<input name="txtmscore" type="text" id="txtmscore" size="5">
</div></td>
</tr>
<%
rs.MoveNext
loop
end if

%>

</table>
<input type='hidden' name='txtemp' value='<%=request.Form("lstemp")%>'>
<input type='hidden' name='txtper' value='<%=request.form("lstper")%>'>
<input type='hidden' name='txtdate' value='<%=request.form("txtdate")%>'>
<input type='hidden' name='txtpos' value='<%=request.form("lstpos")%>'>
<input type='hidden' name='txtdept' value='<%=request.form("lstdept")%>'>
<input type='hidden' name='txtpjt' value='<%=request.form("lstpjt")%>'>

<p>
<input name="btnnext2" type="submit" id="btnnext2" value="Next">
</p>
<p><Font size=2><%=strUpdated%></Font> </p>
<TD colspan=4 align="right">
</form>
<%
cn.close
set rs=nothing
set cn=nothing
end sub


page2:

Sub Next2
dim rs
dim cn
Dim sql, sql1
dim i,j, dut, score

date1=request.form("txtdate")
per=request.form("lstper")
pos=request.form("lstpos")
dept=request.form("lstdept")
pjt=request.form("lstpjt")

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open(Conn)

Set rs = Server.CreateObject("ADODB.Recordset")

txtmscore = request.Form("txtmscore")
score = split(txtmscore,", ")

txtduty = request.Form("txtduty")
dut = split(txtduty,", ")


for i = 0 to UBound(score)

sql = "INSERT INTO mdutyscore ( userid, managerid, periodid,dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"

cn.Execute(sql)
next


end sub


Gold Pearl

 

[GoldPearl]

anybody out there?

 

[GoldPearl]

no one can give me a solution about this. it's really urgent.

 

[DotNetGnat]

i am here..but could not understand your problem..are you getting any errors...what actually are you trying to do...

-DNG

 

[GoldPearl]

hey thnx for replying...

let me start all over again.

i have 2 web pages where in the first page, the user is allowed to make a series of selections from combo boxes (one of which is position eg programmer) and when the user clicks next(next1 button), a 2nd page appears with a table in it in the format below (the user is being rated on the different duties he performs in this page and the duties are retrieved dynamically based on the position the user selected in page 1):

userid duty score

user1 duty1 score1
user2 duty2 score2
user3 duty3 score3
etc

when the user has filled in the scores, he clicks next (next2 function) to save the data in a table in the db. i am able to retrive all data except the duty.

code is as above except the rectified sql stmt in next2:


sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"', '" & request.Form("txtduty") & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"


the response.write of the above is (both duties are being retrieved):

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs, Maintains programs','40', '8/15/2005', 'programmer', 'BPO', 'Volvo')

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs, Maintains programs','75', '8/15/2005', 'programmer', 'BPO', 'Volvo')

but what i want is (separated duties):

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs,'40', '8/15/2005', 'programmer', 'BPO', 'Volvo')

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', Maintains programs','75', '8/15/2005', 'programmer', 'BPO', 'Volvo')


hope is clearer now. if u need some more clarification, please ask bcoz am waiting for a solution urgently.

Regards,
Gold Pearl

 

[Kendel]

sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"',[b] '" &  dut(i) & "'[/b],'" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"

You split the duty but in you SQL, you didn't pass it in.

 

[DotNetGnat]

hey thnx for replying...
[red]No Problem...glad to be of help...[/red]

let me start all over again.

[red]ok[/red]

i have 2 web pages where in the first page, the user is allowed to make a series of selections from combo boxes (one of which is position eg programmer) and when the user clicks next(next1 button), a 2nd page appears with a table in it in the format below (the user is being rated on the different duties he performs in this page and the duties are retrieved dynamically based on the position the user selected in page 1):

[red]as a user i entered my user id and selected my position(say: programmer)...fine up till now...[/red]

userid duty score

user1 duty1 score1
user2 duty2 score2
user3 duty3 score3
etc

[red]here is where i am confused...when i do the above step..is this how it looks on page2??
userid |duty1 | score1
myuserid |test programs |
myuserid |maintain programs |

ok now after i get this i enter the scores..is that correct...something like this:
userid |duty1 | score1
myuserid |test programs | 8
myuserid |maintain programs | 4

i am assuming the above is a form...

[/red]

when the user has filled in the scores, he clicks next (next2 function) to save the data in a table in the db. i am able to retrive all data except the duty.

[red]ok we can do this...[/red]

code is as above except the rectified sql stmt in next2:


sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"', '" & request.Form("txtduty") & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"


the response.write of the above is (both duties are being retrieved):

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs, Maintains programs','40', '8/15/2005', 'programmer', 'BPO', 'Volvo')

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs, Maintains programs','75', '8/15/2005', 'programmer', 'BPO', 'Volvo')

[red]so your request.form("textduty") is Develop and tests programs, Maintains programs...
so you need to do:

myduties=split(request.form("textduty"),",")

then...
[/red]

but what i want is (separated duties):

[red]do this....
for i=0 to UBOUND(myduties)
'do your sql here
next

to result in the below format...
[/red]
INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs,'40', '8/15/2005', 'programmer', 'BPO', 'Volvo')

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', Maintains programs','75', '8/15/2005', 'programmer', 'BPO', 'Volvo')


hope is clearer now. if u need some more clarification, please ask bcoz am waiting for a solution urgently.

[red]post back if you have more questions[/red]
Regards,
Gold Pearl

-DNG

 

[GoldPearl]

well u r right on all tracks but i already have a for loop:

for i = 0 to UBound(score)


sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"', '" & request.Form("txtduty") & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"

cn.Execute(sql)
next

wat to do then?

 

[DotNetGnat]

if that is the case then do this:

for i = 0 to UBound(score)
for j = 0 to UBound(myduties)

'your sql here with score(i) and myduties(j)

next
next

actually if i get understand you correctly...your # scores should be equivalent to # duties...so just do this...

upperbound=UBOUND(score) or upperbound=UBOUND(myduties)..both should be the same number...then do this:

for i=0 to upperbound
sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"', '" & myduties(i) & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"

cn.Execute(sql)
next

hope that solves your problems...
-DNG

 

[Kendel]

Oh man, you didn't understand what I was trying to say there:

CODE
sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"','" & dut(i) & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"
[/code]
Do the the same thing as score(i)

 

[GoldPearl]

aha!

we both got trapped same way.

in fact i already did this but i made same mistake as u and i as i was getting errors, i thought it was the wrong way aand commented it.

& myduties(i) & "','" & score(i) & "',

it should have been

& myduties(j) & "','" & score(i) & "',

i didn't realise my mistake. now that am looking at it, i saw the mistake. lolz.


now am not getting any errors. the insert statement is working but am getting 4 table entry instead of 2

INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs','55', '8/15/2005', 'programmer', 'BPO', 'Honda')
INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Maintains programs','55', '8/15/2005', 'programmer', 'BPO', 'Honda')
INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Develop and tests programs','55', '8/15/2005', 'programmer', 'BPO', 'Honda')
INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('tom.hill' , 'jane.lake', 'Jul - Dec 05', 'Maintains programs','55', '8/15/2005', 'programmer', 'BPO', 'Honda')


y is that so?

 

[DotNetGnat]

dont use the loop two times...just use it once...like this:

[blue]
upperbound=UBOUND(score)
for i=0 to upperbound
sql = "INSERT INTO mdutyscore ( userid, managerid, periodid, dutyid, mdscore, [date], position, department, project) VALUES ('" & request.Form("txtemp")& "' , '" & userid1 &"', '" & request.Form("txtper") &"', '" &  myduties(i) & "','" & score(i) & "', '" & request.Form("txtdate") &"', '" & request.Form("txtpos") &"', '" & request.Form("txtdept") &"', '" & request.Form("txtpjt") &"')"

cn.Execute(sql)
next[/blue]

-DNG

 

[Kendel]

You do from 0 to i, if you j in there, how does it know what's the value of j???

 

[GoldPearl]

chapeau! like we say in french!

u r terrific! thnx a lot. i finally achieved what i wanted to do...though i got a bit mixed up in the way. the guidance have been wonderful and the response very quick.

thnx guys!

Cheers,
Gold Pearl.

 

[DotNetGnat]

uff...that was one long thread...anyways got it fixed in the end...

-DNG

 

[GoldPearl]

yep...but am not done wiz it yet...lolz. now got to refine the "look" of the page. then i'l b done.

DNG...ur patience has been remarkable. next time i have a question , hopefully u'l be around.