DataReader with SQL Join

C Coudou

Is there any workarounds on how to read the data from sql join using sqldatareader.

query ="SELECT TABLE1.FID FROM TABLE2 WHERE TABLE1.ID = TABLE2.ID" ---StoredProcedure

Dim sqlRead As SqlDataReader

cmd.CommandType = CommandType.StoredProcedure
sqlRead = cmd.ExecuteReader

While sqlRead .Read()
Dim str as String = sqlRead("FID") '-------> error here!
End While

Yes, I know that the good solution is to put "AS" clause on query. Let say, TABLE1.FID AS FID. But the problem is I have 100 StoredProcedures and each has no alias. It's very difficult if I will modify all the SP just to put that clause. Thanks in advance

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Wayne Gaylard

You can retrieve datareader results by using the index, like this:-

While dr.Read
'if you just want a string representation
Dim id As String = dr(0).ToString()
'if you know the result is stored as string
id = dr.GetString(0)
'can also retrieve ints, decimals, dates etc
id = dr.GetInt32(0)
id = dr.GetDateTime(0)
id = dr.GetDecimal(0)
End While

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

C Coudou

thank you for quick reply. i found out also that if i will not put dr.read in an if statement, it causes an error. weird. like this... if dr.read then while.... endif

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Simon_Whale

I would change your

if dr.read then

to

if dr.hasrows then

This is a check to see if the datareader has any rows in its collection. further reading Datareader.hasrows[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

C Coudou

how can i read the datareader backward. let say i have data number=1,2,3,4,5,6,7,8,9,10.

dr(number) 'i want result start at 10.

in dataset,

ds.Tables(0).Rows.Count - 1 To 0 Step -1

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Wayne Gaylard

You can't read a datareader backward as it is a forward only reader. You will have to do an order by on your sql query to return the rows in the order you wish them. i.e

SELECT id, name FROM employees ORDER BY id DESC

or something like that.

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Johan Hakkesteegt

Hi, Wayne's suggestion is viable, but using index numbers instead of field names is not recommended, because your code will become harder and harder to maintain, when you add more fields to your query. Generally speaking you may want to adapt your code thus:

If sqlRead.HasRows Then
Dim str as String = String.Empty
Do While sqlRead.Read()
str = sqlRead.Item("FID").ToString
' Do something with str here or outside the Do loop, when you expect only a single result
End While
End If

If your query has only a single field with the name FID, the DataReader will automatically translate FID to TABLE1.FID. Just as a matter of interest, are you using MS SQL or some other DB, because I can't get your query to work? In other words, what I am wondering is, the problem may simply be the query, and not so much the code. Cheers, Johan

My advice is free, and you may get what you paid for.

Dave Kreskowiak

Ummm, If query is actual code in your app, it is not a stored procedure. That's straight up CommandType.Text. A stored procedure in your code would be the name of a stored proc in the database itself.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak