sql

How to bind literal text to an SQLite query?

I'm trying to use the SQLite C API in my iPhone app. I'm trying to query an SQLite database for the number of records that have been completed after a certain date. The database saves the completed date as text in YYYY-MM-dd format. For example the text 2009-04-10 might appear as a completed date. When I query the database from the comm...

How do I get the next item in a subset of an ordered table using linq?

I have two tables: Topic ( TopicID: int, (primary key) TopicName: varchar ); Example ( ExampleID: int, (primary key) TopicID: int, (foreign key to Topic table) ExampleOrder: int, ExampleName: varchar ); Using Linq, given an ExampleID, I want to get the next example in the same topic (with the same TopicID). Here's how it ...

Tools to Replay Load on a SQL Server

Has anyone come across any good tools (preferably but not necessarily FOSS) that can read a SQL Server (2005/2008) trace file and execute the commands against another database. We are attempting to perform some performance testing on our SQL servers and would like to replicate an actual load. I have come across but not yet used: JMet...

SQL Server index question - address lookup

I have a table which may have anywhere from 10,000 to 10 million rows during the life of the application. This table contains NACSZ info, among other things, and I need to write a query that checks for matching NACSZ like so: select * from Profiles where FirstName = 'chris' and LastName = 'test' and Address1 = '...

Add row to query result using select

Hey guys, is it possible to extend query results with literals like this? Select name from users union select name from ('JASON'); or Select age, name from users union select age, name from (25,'Betty'); so it returns all the names in the table plus 'JASON', or (25,'Betty'). Thanks! ...

Updating Nested Set

I was hoping someone could explain how one goes about updating a nested set. I've looked at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html, but it really only deals with adding and deleting nodes. I need to be able to move nodes with and without child nodes. Any help would be appreciated. ...

Updating/Saving to a SQL Database using C#?

I can't find how to do this on google anywhere. How do you save to a SQL DB using just C# code? Is it possible? The save method that comes default when you create a DB using the wizard dosen't actually save the DB: this.Validate(); this.studentsBindingSource.EndEdit(); this.studentsTableAdapter.Update(this.studentsDataSet.FirstClass); ...

Google App Engine: Intro to their Data Store API for people with SQL Background?

Hi All, Does anyone have any good information aside from the Google App Engine docs provided by Google that gives a good overview for people with MS SQL background to porting their knowledge and using Google App Engine Data Store API effectively. For Example, if you have a self created Users Table and a Message Table Where there is a ...

SQL query on two tables - return rows in one table that don't have entries in the other

I have two database tables, Categories and SuperCategories, for an inventory control system I'm working on: Categories: ID_Category, CategoryName SuperCategories: ID_SuperCategory, CategoryID, SuperCategoryID I'm putting category-subcategory relationships into the SuperCategories table. I'm putting all categories into the Cat...

Problem connecting VWD Express 2008 to SQL Server Express 2005

Hi, WHen i try to connect to SQL Express 2005 from Visual Web Developer Express 2008, i was getting errors like 'Could not load file or assembly Microsoft.SqlServer.Management.Sdk.Sfc' . I read some posts which advised me to download and install 3 applications to address above issue ( SharedManagementObjects.msi, sqlncli.msi,SQLSysClr...

Easy way to retrive timestamp from SQL Server db

I have a timestamp column (updated) in my Microsoft Sql Server Database and wish to retrieve the value as a DateTime object using SqlConnection and SqlCommand. What is the easiest way to do this? ...

SQL Find most recent date from 2 columns, AND identify which column it was in

I have a table called tblAssetsInUse with the following structure: intPK intAssetID datCheckedOut datCheckedIn 1 450 1/5/2009 10/5/2009 2 300 2/5/2009 <NULL> 3 200 2/5/2009 <NULL> 4 450 12/5/2009 5/7/2009 ...

SQl Delete from more than one table

Say, I have two tables Courses and faculty_courses - each has a primary key course_ID that is varchar(50) not null. I am trying to delete a row from the Courses table - so assume have to delete that row from both tables since they are linked by a relationship. I wrote this - doesn't work - says Incorrect syntax near the keyword 'JOIN'...

Is there a way to distinct more than 1 field

I need a report that has office, date and order count. I need the total count of orders per month, but only 1 order count per day. e.g. West 1/1/2009 1 order West 1/1/2009 1 order West 1/2/2009 1 order on my report I would see West 1/1/2009 1 order West 1/2/2009 1 order and my total or...

MYSQL one to many JOIN

I have this query and I need to include another join on a table called "likes" where updates.id = likes.update_id. There will be 0 or more matches on this join. "SELECT * FROM users INNER JOIN updates ON users.remote_id=updates.owner_id ORDER BY updates.status_time DESC LIMIT 50" This is probably fairly simple, but I haven't been a...

Help with Rails find_by queries

Say if @news_writers is an array of records. I then want to use @news_writers to find all news items that are written by all the news writers contained in @news_writers. So I want something like this (but this is syntactically incorrect): @news = News.find_all_by_role_id(@news_writers.id) Note that class Role < ActiveRecord::Base ...

How do I provide a string of Ids in a Select statement's IN clause even though the column is an integer

I'm paging data using an ObjectDataSource and I have the following method: public int GetNumberOfArticles(string employeeIds) { System.Data.DataTable dataTable; System.Data.SqlClient.SqlDataAdapter dataAdapter; System.Data.SqlClient.SqlCommand command; int numberOfArticles = 0; command = new System.Data.SqlClient.S...

Querying data from multiple clustered tables as one unified

Hello, I've recently started work on a project which involves creating a web-based reporting interface for a fairly old software responsible for managing some access control hardware like electronic door locks for example. I have chosen CakePHP for the task and all it involves is querying the database for the log records and displaying ...

TSQL make EXECUTE statement synchronous

I have two TSQL EXEC statements EXECUTE (N'MyDynamicallyGeneratedStoredProcedure') -- return 0 on success SELECT @errCode = @@ERROR ; IF (@errCode = 0) BEGIN EXEC 'A Sql Statement using ##temptable created from first', @returnValue END How do I make the two EXEC's synchronous? ; Right now the second EXEC does not wait for the first...

C# - What is good SQLException Error Handling for Time-outs and failed connections

I use EntitySpaces for all of my Database related coding. As a sole developer I don't have time to sit and write the SQL for each and every application I write, so using a generator such as ES Suites me down to the ground and saves me days, if not weeks, of time. I generally write a series of helper classes for each of the tables that m...