Maybe a try/catch solution

hello

i create a dynamic table in a database access. When i read the database i don't know the exact number of columns. my code is like that

Dim

v0AsObject = dbDataReader("t1")Dim t0AsString =String.Empty

t0 = v0.ToString().Trim()

Literal1.Text = t0

Dim v2AsObject = dbDataReader("t2")Dim t2AsString =String.Empty

t2 = v2.ToString().Trim()

Literal2.Text = t2

and so on...

How can avoid to get the database error if t2 column doesn't exist?

Mario,

Presumably, v2 would be Nothing or Null, right (if t2 doesn't exist)? So do a check like:

If IsNothing(v2) or IsDBNull(v2) Then

Whatever

Else

Dosomething

End If

If the v2 Dim statement is bombing, then you need to use the Try/Catch for that statement and bypass as needed.

Good luck!

Here are two ways to look into the SqlDataReader to see what columns are in there. First, the simple approach is to use obDataReader.FieldCount to get the number of returned fields.

There is also a GetSchemaTable() method on the DataReader that will return a DataTable of the columns in the reader.

DataTable columnTable = obDataReader.GetSchemaTable();
string column1Name = columnTable.Rows[0]["ColumnName"].ToString();
string column2Name = columnTable.Rows[1]["ColumnName"].ToString();

So you could use this to determine what columns were returned through the reader. In most cases it is never good to rely on an exception when there is a preventative measure than can be taken.

Use dbDataReader.FieldCount which will give you the number of columns.

NC...

I agree with BillRob's general design philosophy about relying on exceptions...his example also needs to be checked for exceptions.

...I'd question the use of a DataReader (vs DataSet) related to schema info...but hey, if it works...

Hello,

You can get total numbers of columns by using count in datareader object. Then just use variable in loop upto total number of column's count.

I think this is better than try..catch. Because your code is doing only right thing. It knows exact no. of columns.

Regards

Kuldeep Deokule

LOL...yes there is some error checking that could be put into the snippet, but for brevity, I left it out.

As far as DataReader vs DataSets there is a long standing discussion about which to use and when to use it. Most beginning text on the subject says when you want fast forward readonly access to data use a reader. When you want to persist the data as a DataTable and need to perform more than just quick looping logic, use a DataSet (DataTable).

A DataReader is used to fill a DataSet in the framework. (confirmed)

The DataSet uses the DataReader's schema info to build itself. (speculation)

BillRob,

If Mario doesn't figure it out now, we need to go on a road trip to Portugal.

LOL

Thank you everybody

One more help

I choose the following solution:

quantos = dbDataReader.FieldCount

For i = 1To quantosWhile dbDataReader.Read()Dim v0AsObject = dbDataReader("t" + i.ToString)Dim t0AsString =String.Empty

t0 = v0.ToString().Trim()

Literal1.Text = t0

Dim v1AsObject = dbDataReader("r" + i.ToString)Dim t1AsString =String.Empty

t1 = v1.ToString().Trim()

TextBox1.Text = t1

EndWhileNext

what i need more:

i want something like replace the values t1 for something like

dim v+i.tostring as object =dbDataReader("r" + i.ToString)

t+i.tostring =v+i.tostring.tostring.trim()

Any ideas?

And BillRob it's raining now in Portugal. Not good time to came here. (By the way do you know the English time choosed Algarve for the concentration before the Mundial of Soocer?)

Last version

quantos =dbDataReader.FieldCount

Dim t0AsString =String.Empty
Dim t1AsString =String.Empty
Dim v0AsObject
Dim v1AsObject
dbDataReader.Read()
For i = 1To quantos
Try
v0 = dbDataReader("t" + i.ToString)
t0 = v0.ToString().Trim()
v1 = dbDataReader("r" + i.ToString)
t1 = v1.ToString().Trim()
Literal1.Text = t0
TextBox1.Text = t1
Catch exAs Exception
ExitFor
EndTry

i need help to do the following:

Literal ?.text=t0
textbox?.text=t1

Any ideas?

You can use Page.FindControl( controlID ) to dymically get a reference to a control on the page.

Dim literal1 as Literal
Dim textbox1 as TextBox
literal1 = CType( Page.FindControl( "literal" + i.ToString() ), Literal )
textbox1 = CType( Page.FindControl( "textbox" + i.ToString() ), TextBox )

Just what i need !!!!

Thank you