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.

 

[Kendel]

You will need to loop through the table row to do the insert. I saw you comment out the loop. Let put it back in.

Dim i, score 
txtmscore = request.Form("txtmscore")
score = split(txtmscore,", ")
for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & score(i) & "' )"
response.Write(sql)
cn.Execute(sql)
next

 

[Kendel]

You will need to split the request.Form("lstemp") too. Just like what we do with score.

 

[Kendel]

Or maybe all you need is:

sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & request.Form("txtmscore")
& "' )"
response.Write(sql)
cn.Execute(sql)

 

[GoldPearl]

well kendel, both possibilities work but am getting same problem with both:

output of response.write:

INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2' )

and this is not what i require. also it is executing 3 insert statement when i input only 2 marks (duty 1 : score =1 , duty 1 : score =2)

what i need is as follows:
INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1')
INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'2')

Gold Pearl

 

[DotNetGnat]

how about this:

INSERT INTO mdutyscore (userid, mdscore) VALUES ('" &emp& "'

also userid is a reserved word...use square braces around it...

-DNG

 

[Kendel]

This didn't work?

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

txtUID = request.Form("lstemp")
uid = split(txtUID,", ")

for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & uid(i) & "' ,'" & score(i) & "' )"
response.Write(sql)
cn.Execute(sql)
next

How come your userid value is empty?

 

[GoldPearl]

nop i made a mistake. in fact the code below:

Dim i, score
txtmscore = request.Form("txtmscore")
score = split(txtmscore,", ")
for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & score(i) & "' )"
response.Write(sql)
cn.Execute(sql)
next

generates this:

INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'5') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'3') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'3' )

and this code:

sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & request.Form("txtmscore")
& "' )"
response.Write(sql)
cn.Execute(sql)

generates this:

INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2') INSERT INTO mdutyscore (userid, mdscore) VALUES ('' ,'1, 2' )

the first code is closer to what i want but i don't understand y it is taking the score "3" twice and generating 3 insert statements when i rated only 2 duties.

thks,
Gold Pearl

 

[DotNetGnat]

and you have to say...

for i = 0 to txtmscore-1

so when txtmscore is 2 then the insert statement will be executed two times once for i=0 and then for i=1

-DNG

 

[GoldPearl]

hey ppl it work fine bcoz inspite of displaying 3 insert statement instead of 2, it is inserting only 2 records in the database.
but the problem now is that it is not retrieving the employee id.

updated code:

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

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")
lstemp=request.form("lstemp")
score = split(txtmscore,", ")
emp = split(lstemp,", ")
for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & score(i) & "' )"

cn.Execute(sql)
next
end sub

Gold Pearl

 

[DotNetGnat]

try this:

sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & [red]emp[/red] & "' ,'" & score(i) & "' )"

-DNG

 

[GoldPearl]

am getting type 'mismatch error' on sql line

 

[DotNetGnat]

oops...that should be

sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & [red]emp(i)[/red] & "' ,'" & score(i) & "' )"

-DNG

 

[GoldPearl]

i tried this when the other didn't work. got his error:

Error Type:
Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '0'

 

[DotNetGnat]

may be we both are confused here...

what happened when you tried this:

sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & request.Form("lstemp") & "' ,'" & score(i) & "' )"

in your code...you have this so many times...

emp=request.form("lstemp")
lstemp=request.form("lstemp")

what is the emp variable doing?? and in your query you dont use either of these variables....

-DNG

 

[GoldPearl]

rectified:

txtmscore = request.Form("txtmscore")
lstemp=request.form("lstemp")
score = split(txtmscore,", ")
emp = split(lstemp,", ")
for i = 0 to UBound(score)
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & emp(i) & "' ,'" & score(i) & "' )"
'response.Write(sql)
'response.End()
cn.Execute(sql)
next

 

[Kendel]

As I already said, you need to split the userId too.

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

txtUID = request.Form("lstemp")
uid = split(txtUID,", ")

for i = 0 to UBound(score)-1
sql = "INSERT INTO mdutyscore (userid, mdscore) VALUES ('" & uid(i) & "' ,'" & score(i) & "' )"
response.Write(sql)
cn.Execute(sql)
next

 

[DotNetGnat]

try :

for i = 0 to UBound(score)-1

-DNG

 

[DotNetGnat]

ok..i was late this time

-DNG

 

[GoldPearl]

Error Type:
Microsoft VBScript runtime (0x800A0009)
Subscript out of range: 'i'

error still on sql line

Gold Pearl