Jump to content

ASP code for multiple row insert


Damodar

Recommended Posts

Dear All;I am designing student roll muster for my school using ASP. I know how to insert one record at a time in SQL table but this would not be convenient. What I need is like this....If there are X students in a given grade then this data should be fetched from the student master table and shown in a form with X radio buttons against names of each student. Teacher then will select "Absent" against the students who are absent on that day. After submitting this form ALL the rows with "Present" and "Absent" status should enter simultaneously in a table called "Student_roll_details".I know how to fetch this data from the table. select count(*) will give me the total number to loop. I do not know how to enter multiple rows at a time. I do have a sample code downloaded from internet and modified for my table, but time being I am traveling and can not test the code till mid July. The code is given below. Do you think this is a right code?==============================================================<% Option Explicit %> <html> <head> <title></title> </head> <body> <% Dim Conn,strSQL,objExec,i Set Conn = Server.Createobject("ADODB.Connection") Conn.Open "Driver={SQL Server};Server=localhost;Database=mydatabase;UID=sa;PWD=;" For i = 1 To Request.Form("studentCount") If Trim(Request.Form("studentID" & i)) <> "" Then strSQL = "" strSQL = strSQL &"INSERT INTO student_roll_details" strSQL = strSQL &"(StudentID,Name,Grade,Date,Status,Teacher) " strSQL = strSQL &"VALUES " strSQL = strSQL &"('"&Request.Form("studentID" & i)&"','"&Request.Form("studentName" & i)&"', '"&Request.Form("studentGrade" & i)&"' " strSQL = strSQL &",'"&Request.Form("TodayDate" & i)&"','"&Request.Form("stu_absent_present" & i)&"', '"&Request.Form("teacherName" & i)&"') " Set objExec = Conn.Execute(strSQL) End IF Next Response.write("Save completed. Click <a href='student_multiple_insert3.asp'>here</a> to view.") Conn.Close() Set objExec = Nothing Set Conn = Nothing %> </body> </html> =====================This is my first post. Please be kind enough to educate me.

Link to comment
Share on other sites

The code you have will loop through them and insert each one. If you want to use 1 query to add all rows, you can separate the value lists with a comma, e.g.:INSERT INTO table (col1, col2, col3) VALUES (val1a, val2a, val3a),(val1b, val2b, val3b),...

Link to comment
Share on other sites

The code you have will loop through them and insert each one. If you want to use 1 query to add all rows, you can separate the value lists with a comma, e.g.:INSERT INTO table (col1, col2, col3) VALUES (val1a, val2a, val3a),(val1b, val2b, val3b),...
Thanks a lot. I will surely let you know once I use your code. Thanks once again.
Link to comment
Share on other sites

The code you have will loop through them and insert each one. If you want to use 1 query to add all rows, you can separate the value lists with a comma, e.g.:INSERT INTO table (col1, col2, col3) VALUES (val1a, val2a, val3a),(val1b, val2b, val3b),...
Thanks a lot. I will surely let you know once I use your code. Thanks once again.
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...