sql

Dynamic form builder using asp.net sqlserver

Hello, I would like to build a dynamic form builder using asp.net with jquery and a sqlserver as backend. The approach I wish to use is to modify the table structure based on user request so I have a database which is structured as one would normally store data in sqlserver i.e. mostly a single table (barring consideration of sub-table...

How can I manage a FIFO-queue in an database with SQL?

I have two tables in my database, one for In and one for Out. They have two columns, Quantity and Price. How can I write a SQL-query that selects the correct price? In example: If I have 3 items in for 75 and then 3 items in for 80. Then I have two out for 75, and the third out should be for 75 (X) and the fourth out should be for 80 (Y...

How to query on table returned by Stored procedure within a procedure.

I have a stored procedure that is performing some ddl dml operations. It retrieves a data after processing data from CTE and cross apply and other such complex things. Now this returns me a 4 tables which gets binded to various sources at frontend. Now I want to use one of the table to further processing so as to get more usefull inform...

Reported error code considered SQL Injection?

SQL injection that actually runs a SQL command is one thing. But injecting data that doesn't actually run a harmful query but that might tell you something valuable about the database, is that considered SQL injection? Or is it just used as part to construct a valid SQL injection? An example could be set rs = conn.execute("select headl...

Advanced SQL query with lots of joins

Hey fellow programmers Okay, first let me say that this is a hard one. I know the presentation may be a little long. But I how you'll bare with me and help me through anyway :D So I'm developing on an advanced search for bicycles. I've got a lot of tables I need to join to find all, let's say, red and brown bikes. One bike may come in ...

How to force a SQL Server 2008 database to go Offline

How do I force my Database to go Offline, in no regards to what or who is is already using it? I tried: ALTER DATABASE database-name SET OFFLINE; But it's still hanging of 7 min. I want this because I need to test the scenario. If it's even possible? ...

odp.net SQL query retrieve set of rows from two input arrays.

I have a table with a primary key consisting of two columns. I want to retrieve a set of rows based on two input arrays, each corresponding to one primary key column. select pkt1.id, pkt1.id2, ... from PrimaryKeyTable pkt1, table(:1) t1, table(:2) t2 where pkt1.id = t1.column_value and pkt1.id2 = t2.column_value I then bind the values...

I want to select a particular list of words containing certain characters using php

Hello, I want to select a particular list of words in a paragraph containing certain characters $query_search_text= "SELECT * FROM para where para_main='%text_words'"; I tried using the % sign, but it does not work. It works if I give in the full text. Thanks Jean ...

What is the purpose of ROWLOCK on Delete and when should I use it?

Ex) When should I use this statement: DELETE TOP (@count) FROM ProductInfo WITH (ROWLOCK) WHERE ProductId = @productId_for_del; And when should be just doing: DELETE TOP (@count) FROM ProductInfo WHERE ProductId = @productId_for_del; ...

Is there any optimizations I can do with this statement to get the count of used rows?

Is there any optimizations I can do with this statement to get the count of used rows. Note: The ProductOrderInfo table has over 40 million rows in it. SELECT @TotalUsed = COUNT(*) FROM ProductInfo WITH (NOLOCK) WHERE ProductInfoId IN ( SELECT ProductInfoId FROM ProductOrderInfo WITH (NOLOCK) ); ...

Should I sanitize EVERY form variable passed along?

I have a form with many fields... The action is set to a php page which queries mysql... Should I sanitize with mysql_real_escape_string every single variable? Or can I ignore sanitizing drop-lists and radios for instance? Also, besides mysql_real_escape_string, what else should I do to prevent attacks? Thanks ...

cloning a database with backing up possible?

I need to write a tool to clone a database, instead of detaching it and copying the .mdf and .ldf, wouldn't it be better to just back it up and restore a newly created db? Is there a way using SQL to create a database from a .bak? ...

select row from table and substitute a field with one from another column if it exists

I'm trying construct a PostgreSQL query that does the following but so far my efforts have been in vain. Problem: There are two tables: A and B. I'd like to select all columns from table A (having columns: id, name, description) and substitute the "A.name" column with the value of the column "B.title" from table B (having columns: id, t...

Select a record that has a duplicate

I'd like to select all records from a table (names) where lastname is not unique. Preferrably I would like to delete all records that are duplicates. How would this be done? Assume that I don't want to rerun one query multiple times until it quits. ...

Microsoft Access Query To Build Relationship

Hi, I have the following table: Table Parents with columns ID and FullName. ID is a primary key of type Auto-Number. Table Children with columns ID, ParentID and FullName. ID is a primary key of type Auto-Number. ParentID is of type Number. I want to use the following query to build a "cascade deletion" so that when a parent is del...

Select which satisfies at least N conditions from M available.

Hi, there is a question floated. It's set in basic SQL terms, but its nature is pure math (so maybe I should visit http://mathoverflow.com too). I have a table in some theoretical database with 6 fields, all are numbers. Also we have basic conditions, such as Field_1 > Field_5, Field_4 = 3 etc., 7 conditions total. I need to write a sel...

Speed up a query, simple inner join with one large table and one small table.

I have a table T1 with 60 rows and 5 columns: ID1, ID2, info1, info2, info3. I have a table T2 with 1.2 million rows and another 5 columns: ID3, ID2, info4, info5, info6. I want to get (ID1, ID2, info4, info5, info6) from all the rows where the ID2s match up. Currently my query looks like this: SELECT T1.ID1, T2.ID2, T2.info4,...

DuplicateKeyException on empty table

I get a System.Data.Linq.DuplicateKeyException when adding an entity to an empty table. grade g = new grade(); g.subject = dc.subjects.Single(x => x.subjectID == 5); g.student = aStudent; dc.grades.InsertOnSubmit(g); I dropped the entire table itself and recreated it with no luck. Grades is a join table with subjectID and studentID as...

Is this possible with Sql 2005 CTE?

I have been working on a query that will return a suggested start date for a manufacturing line based on due date and the number of minutes needed to complete the task. There is a calendar table(LINE_ID, CALENDAR_DATE, SCHEDULED_MINUTES) that displays per manufacturing line, the number of minutes scheduled for that day. Example: (Usu...

.tpl files and website problem

Apologies if the title is in lowercase but it's describing an extension format. I've started using Dwoo as my template engine for PHP, and am not sure how to convert my PHP files into .tpl templates. My site is similar to, but not the same as, http://library.digiguide.com/lib/programme/Medium-319648/Drama/ with its design (except colou...