Data Retrieval Best Practices type of Question

  • Thread starter Thread starter Blake Weaver
  • Start date Start date
B

Blake Weaver

I'm trying to load all the 'Ticket' Data that may be associated with a
particular AccountID. I'm fairly satisfied with the way I get the AccountID
and I'm storing it in the CurrentAccountID variable. So, I build my select
statement to retrieve all the Tickets into the "Tickets" table of the
dsTicketMain DataSet.



..

dascTicketsMain.SelectCommand.CommandText = "SELECT TicketID, AccountID,
CreationDate, CSRID FROM scTickets WHERE AccountID = " &
CurrentAccountID.ToString

dascTicketsMain.Fill(dsTicketMain, "Tickets")

..



Now, each AccountID will most likely have more than one Ticket associated
with it. And each Ticket will have many line Items associated with it. I
want to load all the line items for all the tickets so I can let the
DataRelation handle my navigation. That way I don't have to load the Item
info each time I switch tickets. So, should I build my 'Items' Select
statement to do something like this.?



..

dascItemsMain.SelectCommand.CommandText = "SELECT ItemID, TicketID, BookID,
Description, QtyOrdered, UnitPrice, QtyReceived, FROM scItems WHERE TicketID
IN (SELECT TicketID FROM scTickets WHERE AccountID = " &
CurrentAccountID.ToString

dascItemsMain.Fill(dsTicketMain, "Items")

..



Or should I iterate through the Tickets DataTable and retrieve each TicketID
like the following, then merge each time?



..

CurrentTicketID = CInt(dsTicketMain.Tables("Tickets").Rows(0).Item(0))

"SELECT ItemID, TicketID, BookID, Description, QtyOrdered, UnitPrice,
QtyReceived, FROM scItems WHERE TicketID = " & CurrentTicketID.ToString

..



Or is there another, better, way that I'm not thinking of?



Hope I included all the necessary info for the question. let me know if I
need to provide more.



Thanks guys,

Blake



PS, the last couple posts I made to this group and showing up for me. I got
a reply to one of them but it showed up its own thread. Maybe its just one
of those things and this one will show up, but anyone got any idea what
would cause that?
 
Blake,

That should work in terms of the SQL select statement

However, if I have access to your sytem as a user I can drop any table I
know the name of. It's called SQL injection. If you also have code like

sqlCommand = "SELECT * FROM User WHERE UserID = " & txtUserName.Text

to allow users to log into your system, a hacker can also gain immediate
access to your system.

You must parameterize dynamic SQL. If you concatonate SQL strings for data
access, you are open for SQL injection. Yes, sanitation helps a little bit
(if you are validating that the textbox contains no special characters) you
gain a some protection.
 
Back
Top