Row Doesn't Show when Retrieving One Record With Datagrid, Data List or Repeater

[KevinM]

When Binding a Datagrid, Data List or Repeater control to a simple MS Access Query...."SELECT [MyID], [MyField] FROM MyQuery WHERE My ID=" & inMYID works fine if there is more than one record BUT if there is only ONE record retrieved it doesn't show anything!!
(Also tried 'SELECT *...' )

I've checked the criteria at the MS Access end and there is clearly one record in the query.

Why won't it show one record on it's own?

Many Thanks

 

[dan-cat]

When Binding a Datagrid, Data List or Repeater control to a simple MS Access Query...."SELECT [MyID], [MyField] FROM MyQuery WHERE My ID=" & inMYID works fine if there is more than one record BUT if there is only ONE record retrieved it doesn't show anything!!
(Also tried 'SELECT *...' )

I've checked the criteria at the MS Access end and there is clearly one record in the query.

Why won't it show one record on it's own?

Many Thanks

Are you using dynamic sql? If so, you're best bet is to response.write your sql statement onto the screen then run that sql in your DB. Check to see if your sql is definitely returning a record...

PS Don't use SELECT *

 

[KevinM]

Are you using dynamic sql? If so, you're best bet is to response.write your sql statement onto the screen then run that sql in your DB. Check to see if your sql is definitely returning a record...

PS Don't use SELECT *

What do you mean by 'dynamic sql'?

What else can I use instead of 'SELECT' as I need to to include a 'WHERE' condition to retrieve records from a 'Request.QueryString("intMYID").

 

[dan-cat]

What do you mean by 'dynamic sql'?

Are you building your sql statement from within your asp code? If so once you have built this statement. Output this statement to your browser by using response.write. Better yet post some of your code.

What else can I use instead of 'SELECT' as I need to to include a 'WHERE' condition to retrieve records from a 'Request.QueryString("intMYID").

You still use select but just select the fields you need. Don't use the * syntax becuase most likely you'll be querying more data than you need and thus slow the query down.

Post your code and I'll take a look...

 

[KevinM]

Hi Dan

Thanks for helping out again.

Here is my code....

objConnection.Open()

intHolidayPhotoID=Request.QueryString("HolidayPhotoID") ' USE

strSQL="SELECT LocationSiteAndPlace, HolPhotoLargePath, Notes FROM qryHolidayPhotosAndMovieTitles WHERE HolidayPhotoID=" & intHolidayPhotoID

on error resume next ' Skip NULL Values in 'Notes'
dbComm=New OleDbCommand(strSQL,objConnection)

dtr=dbComm.ExecuteReader()
dtr.Read()
strTitle=dtr("LocationSiteAndPlace")
lblHeader.text=strTitle
Response.Write("<title>" & strTitle & "</title>")
imgHolPhoto.ImageURL=dtr("HolPhotoLargePath")
imgHolPhoto.AlternateText=dtr("LocationSiteAndPlace")
ltLocationName.Text=dtr("LocationSiteAndPlace")
ltNotes.Text=dtr("Notes")

rpMoviesMadeHere.DataSource=dtr
rpMoviesMadeHere.Databind()
dtr.Close()

objConnection.Close

Here is the code for the offending Repeater control...

<asp:Repeater id="rpMoviesMadeHere"
runat="server"
>
<HeaderTemplate>
<strong>
 Movies Made Here:
 </strong>
 </HeaderTemplate>
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Title") %>,
</ItemTemplate>
</asp:Repeater>

All the controls work fine except the the Repeater above which now misses off the first record every time!

Here is the link
http://medusa/moviesmadeinmalta2/HolLocations.aspx

Click on a location link and then on a thumbnail to get to the 'offending page' 'HolPhoto.aspx'

Example...click on 'Anchor Bay'. Click on any thumbnail and at the bottom of the larger pic under 'Movies Made Here', the movie Popeye should be listed (it is if you replicate the same filtering on the same query within MS Access 2003.

Also if you select Valletta from the first page and then click on any of the 'Fort St Elmo' thumbnails (e.g Row 1, last thumb) then iit lists Cutthroat Island, Midnight Express but misses out Clash of the Titans which again is in the query and is listed first!.

It does this whether it's a Repeater, Datagrid or Datalist control!

Many Thanks

 

[dan-cat]

objConnection.Open()

intHolidayPhotoID=Request.QueryString("HolidayPhotoID") ' USE

strSQL="SELECT LocationSiteAndPlace, HolPhotoLargePath, Notes FROM qryHolidayPhotosAndMovieTitles WHERE HolidayPhotoID=" & intHolidayPhotoID

on error resume next ' Skip NULL Values in 'Notes'
dbComm=New OleDbCommand(strSQL,objConnection)

dtr=dbComm.ExecuteReader()
[COLOR="Red"]dtr.Read()[/COLOR]
strTitle=dtr("LocationSiteAndPlace")
lblHeader.text=strTitle
Response.Write("<title>" & strTitle & "</title>")
imgHolPhoto.ImageURL=dtr("HolPhotoLargePath")
imgHolPhoto.AlternateText=dtr("LocationSiteAndPlace")
ltLocationName.Text=dtr("LocationSiteAndPlace")
ltNotes.Text=dtr("Notes")

rpMoviesMadeHere.DataSource=dtr
rpMoviesMadeHere.Databind()
dtr.Close()

objConnection.Close

I've highlighted the offending line.

What you've done is created your datareader and then read a single record. A datareader is a forward-only reader. This means once you've read one line - the reader will automatically go to the next record. That's why it seems like its skipping a record but actually it isn't. You've bound your datagrid to the reader afterwards but the reader starts at the second row because of your code that I have highlighted.

P.S - That link didn't work

 

[KevinM]

Thanks Dan

I added a dtr.Close() line just before the Repeater lines and it now works fine.

Incidentally, I still needed to keep the original dtr.Read() line in for the literal controls.

Try these links...

http://medusa.umist.ac.uk/moviesmadeinmalta2/HolLocations.aspx

http://www.medusa.umist.ac.uk/moviesmadeinmalta2/HolLocations.aspx

Cheers

KevinM

 

[dan-cat]

Thanks Dan

I added a dtr.Close() line just before the Repeater lines and it now works fine.

Yep, that'll do it. Now the databind event has to repopen the datareader from the beginning.

Incidentally, I still needed to keep the original dtr.Read() line in for the literal controls.

Ok - as long as you understand that that will only return the first record of your query even though there may be more records contained within the datareader.

Try these links...

http://medusa.umist.ac.uk/moviesmadeinmalta2/HolLocations.aspx

http://www.medusa.umist.ac.uk/moviesmadeinmalta2/HolLocations.aspx

Sorry, still no joy...

 

[KevinM]

Ok, i've copied the web over to my proper domain.

This SHOULD now work...

http://www.moviesmadeinmalta.com/HolLocations.aspx

BTW, it's my personal web page about 'Movies Made in Malta' and being half Maltese, a regular visitor to the island and a BIG movie fan, then I just had to combine them all into a web page.

Last year whilst I was there on holiday I stumbled upon Spielberg filming Munich there (photos are on the web page)!

Your expert opinion on any web/aspx issues are more than welcome.

Known issues are when viewing any of the 'thumbnail type' pages then it sometimes reports an extra page when there isn't one, at the bottom near the page navigations

 

[dan-cat]

Yep got through on that one.

I went on a family vacation once to Malta. We hired a Fiat Panda with plastic seats (this was back in the '80s). You couldn't get in it with shorts on because otherwise the fabric would burn your ass off

The site looks good - didn't get any errors or anything when browsing.

Images are great, some of those movies are dead cool. Especially Clash of the Titans