Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you're able to, a redesign of the tables would really help. Depending on how many properties there are, and reasons for partitioning the days into different tables, you could have a single table called DayData which has columns Date (maybe stored as an int or using SQL 2008 R2's DATE data type) Field Value</p> <p>If position matters (eg author1 is somehow treated differently from author2) then you could also have an index field (type integer). It depends on whether you always have exactly 5 values of each type, or if you may have 0, 1 or N, and whether or not the types can change over time.</p> <p>Anyway, if you're stuck with what you've got, you could try full text search, or if that's a bridge too far, try</p> <pre><code>"SELECT * from (select * from D1Table UNION D2Table) D1D2 WHERE Synopsis LIKE '%" + txtBText + "%' OR Author1 LIKE '%" + txtBText + "%' OR Author2 LIKE '%" + txtBText + "%' OR Author3 LIKE '%" + txtBText + "%' OR Author4 LIKE '%" + txtBText + "%' OR Author5 LIKE '%" + txtBText + "%' OR Biography1 LIKE '%" + txtBText + "%' OR Biography2 LIKE '%" + txtBText + "%' OR Biography3 LIKE '%" + txtBText + "%' OR Biography4 LIKE '%" + txtBText + "%' OR Biography5 LIKE '%" + txtBText + "%' OR Title LIKE '%" + txtBText + "%' OR Position1 LIKE '%" + txtBText + "%'OR Position2 LIKE '%" + txtBText + "%' OR Position3 LIKE '%" + txtBText + "%' OR Position4 LIKE '%" + txtBText + "%' OR Position5 LIKE '%" + txtBText + "%' "; </code></pre> <p>Note the derived table called D1D2: (select * from D1Table UNION D2Table)</p> <p>However, you have another problem - building the statement like you are is vulnerable to a SQL injection attack (search this site or Google). Someone could put </p> <pre><code>'--delete * from D1Table; 'select * from D1Table where ''1= </code></pre> <p>and delete the data from your D1Table. Major websites get hacked using similar techniques - rather than deleting data the queries are manipulated to generate error messages or deliberate timing delays, leaking information about the database design and usernames, passwords, etc.</p> <p>Hope that helps :)</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL querying two tables lots of columns
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaron Bertrand
    1. USIan Yates
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI might redesign the table. I don't know why I seperated them, I obviously didn't think it through. I will have a look at SQL injection stuff as well I think I need to know more about that. A quick question, when I take the text box value I use HttpUtility.HtmlEncode(); so the string I am using is: string txtBText = HttpUtility.HtmlEncode(TextBoxSearch.Text);, I am wondering is that not really enough to prevent SQL attacks from the user input? (I am assuming not)
      singulars
      1. PO
      1. USOverHere
    2. COAh I see I have read a bit more about SQL injection, I should use parameters instead so I will look into that.
      singulars
      1. PO
      1. USOverHere
    3. CO@Aaron Bertrand - thanks for the tidy up of formatting. I'm still getting used to StackOverflow :) (BIG fan of your blog by the way!)
      singulars
      1. PO
      1. USIan Yates
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload