asp.net 2 assign queryresultset to array

asp.net 2 assign queryresultset to array

• MadGerbil

  you can read a specific column on the current row in a while loop on a dataReader. Just read it and add to the array the values you want. If you like, but could may well be overkill if you are reading 8 values only, is to fill a dataset/datatable with data and access it via the Row/Column array collection.

  you could also use the GetValues() method of the dataReader to return you the entire row in an array.

  going with the first example of using a SqlDataReader.....

  ArrayList myData = new ArrayList();

  SqlCommand command = new SqlCommand("SELECT * FROM [myTable]", new SqlConnection(connectionString));

  command.Connection.Open();

  SqlDataReader reader = command.ExecuteReader(CommandBehavior.ConnectionClose);

  while (reader.Read())

  {

  myData.Add(reader[ColumnName].ToString());

  myData.Add(reader[ColumnName2].ToString());

  //and so on

  }

  command.Connection.Close();

  going with the GetValues....

  object[] myObject = new object[reader.FieldCount];

  while (reader.Read())

  {

  reader.GetValues(myObject);

  }

  of course this code will overwrite any existing contents of the myObject array but you can change this to your needs.

  does this help

  
  
  

• Steve Strong

  I see, this line
  dbReader2.Read()
  reads the first value then the while loop takes over
  and reads the rest.
  I initalised the datareader to soon.
  
  While dbReader2.Read
  Request(index) = CStr(dbReader2("request").ToString())
  index += 1
  End While
  
  Thanks for your help, all values now getting displayed.
  
  

• Hyde

  I'm not sure since I dont know your data but I'm guessing its because you read the data early without storing it...

  dbReader2 = myCommand2.ExecuteReader
  
  
  While dbReader2.Read
  Request(index) = CStr(dbReader2("request").ToString())
  index += 1
  End While

  what happens now

  
  
  

• UsingBytes

  I thought the point of the while loop was to store the data from the reader in the array 'result()'
  
  

• vinclaro001

  well yes but from what I understood you were perhaps reading 1 row ahead and the reason being because you placed a read() command before the while loop. That was taken out in the above response. Also check to see what values it is reading through the debugger or with the aid of messagebox's in your while loop

  so are you not able to use GetValues()

  
  
  

• StevenR2

  I tried something similar to what you suggest which works to a point.
  the query returns 8 values I have tested it on server.
  
  However  the first value for the field is not displayed on the page,
  txtreq0.Text = CStr(Request(0))------> holds value 1 not 0
  
  
  Using myConnection As New SqlConnection(conn2)
  Dim myCommand2 As New SqlCommand(Sql2, myConnection)
  myConnection.Open()
  
  myCommand2.ExecuteNonQuery()
  Dim index As Integer = 0
  Dim Request(8) As String
  Dim dbReader2 As SqlDataReader
  
  dbReader2 = myCommand2.ExecuteReader
  dbReader2.Read()
  
  While dbReader2.Read
  Request(index) = CStr(dbReader2("request").ToString())
  index += 1
  End While
  
  txtreq0.Text = CStr(Request(0))
  txtreq1.Text = CStr(Request(1))
  txtreq2.Text = CStr(Request(2))
  txtreq3.Text = CStr(Request(3))
  txtreq4.Text = CStr(Request(4))
  txtreq5.Text = CStr(Request(5))
  txtreq6.Text = CStr(Request(6))
  txtreq7.Text = CStr(Request(7))
  
  If (Not conn2 Is Nothing) Then
  myConnection.Close()
  myConnection.Dispose()
  End If
  End Using
  
• asp.net 2 assign queryresultset to array