Heres is your larger program rewritten with Utility Belt to reduce code size, simplify nesting and improve error trapping. This sort of addresses the issue of using datatables instead of datareaders for simplifed nesting.
http://www.learnasp.com/freebook/learn/utilitybelt.aspx
Your old program was 166 lines.
DIM strConn as string = "...."
DIM strSQL as string
Dim fieldId As Integer
strSQL = "select max(eField_ID) From eForm_Table_Master where eForm_ID ="
strSQL &= request("tID")
fieldId=ub1.DBScalar(strSQL)
strSQL=""
strSQL = "select * From eForm_Table_Master where eForm_Id ="
strSQL &= txtTemplateName.Text & " and eField_ID in(" & Request("cbx") & ")"
Dim dtMaster as new dataTable
dtMaster=ub1.DBPopulate(strSQL)
strSQL=""
NoOfQuestions = 0
dim QuestionText As String
Dim QuestionType As Integer
Dim QuestionTypeText As String
Dim QuestionNoOfChoices As Integer
Dim QuestionFieldID As Integer
Dim tempQry As String
Dim validornot As Boolean
DIM strSQLChoiceMaster as string
dim dtrow1 as datatablerow
FOR EACH dtrow1 IN dtMaster
validornot = False
fieldId = fieldId + 1
QuestionFieldID =CInt(dtrow1(1))
QuestionText =dtrow1(2)
QuestionType =CInt(dtrow1(3))
QuestionNoOfChoices =CInt(dtrow1(4))
strSQL = "Select * From eForm_Table_Master where eForm_Id = "
strSQL &= Request("tID") & " and DE_Caption = '"
strSQL &= QuestionText & "'"
ValidOrNot=ub1.DBExecuteScalar(strSQL)
IF ValidOrNot
strSQL = "Insert into eForm_Table_Master values(" & Request("tID") & ","
strSQL &= fieldId & ",'" & QuestionText & "'," & QuestionType & "," & QuestionNoOfChoices & ")" & "$"
DIM strSQLTableAlter as string
strSQLTableAlter = "Alter table " & Chr(34) & lblTemplateName.Text & Chr(34) & " add "
If QuestionType = 5
strSQLTableAlter &= Chr(34) & QuestionText & Chr(34) & " numeric "
Else
strSQLTableAlter &= & Chr(34) & QuestionText & Chr(34) & " varchar(255)"
End If
strSQLTableAlter &= tempQry & "$"
If QuestionNoOfChoices > 1 Then
strSQLChoiceMaster = "select * From eForm_Table_Choice_Master where eForm_Id ="
strSQLChoiceMaster &= txtTemplateName.Text & " and eField_Id = " & QuestionFieldID
END IF
END IF
NEXT
Dim dtChoiceMaster as new dataTable
dtChoiceMaster=ub1.DBPopulate(strSQLChoiceMaster)
DIM dtrwChoiceMaster as datarow
DIM strSQLinsert as string
FOR each dtrwChoiceMaster IN dtChoiceMaster
strSQLTableAlter &= "Insert into eForm_Table_Choice_Master Values(" & Request("tID")
strSQLTableAlter &= "," & fieldId & ",'" & dtrwChoiceMaster(2) & "')$"
NEXT
Dim Conn1 As Object ' SqlConnection
Dim Comm1 As Object 'SqlCommand
Dim DataRdr1 As Object 'SqlDataReader
InitializeDBObjects(Conn1, Comm1)
Comm.CommandText = "executeSetOfStatement"
Comm.CommandType =CommandType.StoredProcedure
Dim PMThreadDesc As SqlParameter
PMThreadDesc = New SqlParameter("@whichStatement", SqlDbType.VarChar,2555)
PMThreadDesc.Value = Trim(strSQLTableAlter)
Comm.Parameters.Add(PMThreadDesc)
Dim PMEmpID As SqlParameter
PMEmpID = New SqlParameter("@delimeter",SqlDbType.VarChar, 1)
PMEmpID.Value = "$"
Comm.Parameters.Add(PMEmpID)
Comm.ExecuteNonQuery()
Comm.Dispose()
Response.Redirect("viewAllTemplateQuestions.aspx?tID=" & Request("tID"))
At 01:29 AM 2/19/2003 -0800, you wrote:
Dim Conn As Object ' SqlConnection
Dim Comm As Object ' SqlCommand
Dim DataRdr As Object 'SqlDataReader
Dim ConnString As String
Dim NoOfQuestions As Integer
Dim mQry As String
InitializeDBObjects(Conn, Comm)
Conn.Open()
Comm.Connection = Conn
Dim Conn1 As Object ' SqlConnection
Dim Comm1 As Object 'SqlCommand
Dim DataRdr1 As Object 'SqlDataReader
InitializeDBObjects(Conn1, Comm1)
Conn1.Open()
Comm1.Connection = Conn1
Dim Conn2 As Object 'SqlConnection
Dim Comm2 As SqlCommand
InitializeDBObjects(Conn2, Comm2)
Conn2.Open()
Comm2.Connection = Conn2
Dim fieldId As Integer
Comm.CommandText = "select max(eField_ID)
>From eForm_Table_Master where eForm_ID = " &
Request("tID")
DataRdr =
Comm.ExecuteReader(CommandBehavior.SequentialAccess)
If DataRdr.Read() Then
If DataRdr.IsDBNull(0) = True Then
fieldId = 0
Else
fieldId = CInt(DataRdr.Item(0))
End If
Else
fieldId = 0
End If
DataRdr.Close()
Comm.Dispose()
Comm.CommandText = "select * From
eForm_Table_Master where eForm_Id =" &
txtTemplateName.Text & " and eField_ID in(" &
Request("cbx") & ")"
DataRdr =
Comm.ExecuteReader(CommandBehavior.SequentialAccess)
NoOfQuestions = 0
While DataRdr.Read
Dim QuestionText As String
Dim QuestionType As Integer
Dim QuestionTypeText As String
Dim QuestionNoOfChoices As Integer
Dim QuestionFieldID As Integer
Dim tempQry As String
Dim validornot As Boolean
validornot = False
fieldId = fieldId + 1
QuestionFieldID =
CInt(DataRdr.Item(1))
QuestionText =
DataRdr.Item(2).ToString()
QuestionType =
CInt(DataRdr.Item(3).ToString())
QuestionNoOfChoices =
CInt(DataRdr.Item(4).ToString())
tempQry = "Select * From
eForm_Table_Master where eForm_Id = " & Request("tID")
& " and DE_Caption = '" & QuestionText & "'"
'On Error Resume Next
Comm2.CommandText = tempQry
Dim DataRdr2 As SqlDataReader
DataRdr2 =
Comm2.ExecuteReader(CommandBehavior.SequentialAccess)
If DataRdr2.Read() Then
If DataRdr2.IsDBNull(0) = True
Then
validornot = True
Else
validornot = False
End If
Else
validOrNot = True
End If
'Here is the place
'DataRdr2.Close()
'Comm2.Dispose()
If validornot = True Then
mQry += "Insert into
eForm_Table_Master values(" & Request("tID") & "," &
fieldId & ",'" & QuestionText & "'," & QuestionType &
"," & QuestionNoOfChoices & ")" & "$"
tempQry = "Alter table " & Chr(34)
& lblTemplateName.Text & Chr(34) & " Add "
If QuestionType = 5 Then
tempQry = tempQry & Chr(34) &
QuestionText & Chr(34) & " numeric "
Else
tempQry = tempQry & Chr(34) &
QuestionText & Chr(34) & " varchar(255)"
End If
mQry += tempQry & "$"
If QuestionNoOfChoices > 1 Then
Comm1.CommandText = "select *
>From eForm_Table_Choice_Master where eForm_Id =" &
txtTemplateName.Text & " and eField_Id = " &
QuestionFieldID
DataRdr1 =
Comm1.ExecuteReader(CommandBehavior.SequentialAccess)
While DataRdr1.Read
mQry += "Insert into
eForm_Table_Choice_Master Values(" & Request("tID") &
"," & fieldId & ",'" & DataRdr1.Item(2).ToString() &
"')" & "$"
End While
DataRdr1.Close()
Comm1.Dispose()
End If
End If
End While
DataRdr.Close()
Comm.Dispose()
Comm.CommandText = "executeSetOfStatement"
Comm.CommandType =
CommandType.StoredProcedure
Dim PMThreadDesc As SqlParameter
PMThreadDesc = New
SqlParameter("@whichStatement", SqlDbType.VarChar,
2555)
PMThreadDesc.Value = Trim(mQry)
Comm.Parameters.Add(PMThreadDesc)
Dim PMEmpID As SqlParameter
PMEmpID = New SqlParameter("@delimeter",
SqlDbType.VarChar, 1)
PMEmpID.Value = "$"
Comm.Parameters.Add(PMEmpID)
Comm.ExecuteNonQuery()
Comm.Dispose()
Response.Redirect("viewAllTemplateQuestions.aspx?tID="
& Request("tID"))