Logo 
Search:

Asp.net Forum

Ask Question   UnAnswered
Home » Forum » Asp.net       RSS Feeds

Can Datareader be used inside a datareader

  Asked By: Seth    Date: Mar 04    Category: Asp.net    Views: 1079
  


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"))

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Hubba Akhtar     Answered On: Mar 04

Some suggestions:

You can eliminate 2 DataReaders and speed up your program a lot.

#1
The segment below is best replaced with ExecuteScalar. see:
http://www.learnasp.com/freebook/learn/executescalar.aspx

ExecuteScalar is lightning fast at grabbing 1 value.


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()


In this segment below why are you fetching * you are only using Field 0. Replace it with executeScalar as well.

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"))

I personally think you should learn about DataTables as well particularly for your outermost loop, see:
http://www.learnasp.com/freebook/learn/datatable.aspx

datareaders need 1 connection  each. 6 datareaders in a loop, 6 connections needed. As long as they have their own connections you can have as many as you want. DataTables fill and ditch their connection, so you could have 6 datatables all from 1 connection.

 
Answer #2    Answered By: Sumitra 2004     Answered On: Mar 04

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"))

 
Didn't find what you were looking for? Find more on Can Datareader be used inside a datareader Or get search suggestion and latest updates.




Tagged: