sql

Query to find topics depending on the tag

Hi All, I want a search functionality in my application for the data like following topic_id tag 1 cricket 1 football 2 football 2 basketball 3 cricket 3 basketball 4 chess 4 basketball Now when i search for term cricket AND football o/p should be topic_id ...

Datetime format help

Hi all... I want only Hr and Min in time format so i did select convert(varchar(20),GETDATE(),108) ===>> output is "12:57:54" but i want only "12:57" this much so how i will do that in sql server ...

SQL String query

hey all i have this insert query im tryin to do but it isn't working. no matter how many variations, and variations of variations i try, it always has a problem with my code. Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES (<%=Request.QueryString...

How do I get this 2 SQL querys to be 1

Tables are: user_table(user_id, user_name) , votes_table(id,user_id, vote_date, votes) $sql = SELECT user_id, sum(votes) AS sumvotes, vote_date FROM votes_table WHERE vote_date > 1279749600 ORDER BY sumvotes DESC LIMIT 10 $result = query($sql) foreach user_id in $result $sql = SELECT user_id, username FROM ...

how to refresh table after adding a row

On one button click event i am inserting a row in a table after that i am generating a crystal report on that inserted row. My problem is that i am not getting that row in the same event handler for that i need to refresh that table before generating the report. Can any1 tell me how to refresh the table through c# code ...

Create database with bootstrapped SQL Express

I've written an app which bootstraps SQL Express 2008 with a Wix 3.5 installer, then attempts to CREATE DATABASE on first run of the app. The install of SQL Express is thus totally unattended. This all works fine when the account installing the app is an Administrator on the machine. However, somewhat unsurprisingly, problems arise whe...

SQL/ASP - Invalid column name 'Email'.

hey all, i am having trouble adding stuff into the Email column. I can add stuff into the Username column but for some reason i get the following error: Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid column name 'Email'. When I use this code: Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, Fi...

SQL Server 2005 simple update statment with join

I have 2 tables, one called dbo.dd and one called dbo.gt. where dbo.gt.v_products_model = dbo.dd.[Vendor Stock Code] I would like to update the field dbo.gt.v_products_price with the dbo.dd.[Dealer Ex] Sorry, forgot syntax of SQL 2005 and in a jam! ...

How to remove not null constraint in sql server using query

I am trying to remove not null constraint in sql server 2008 without losing data. ...

Formatting a Datetime returned from a SQL UD Function

Consider this simple user defined function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME) END whi...

How to get record in between time range

Hi all.. I want to fetch some result from table depending on time range so i did SELECT * FROM TABLE where convert(varchar(10),CountryTime,108) between '15:00' and '16:00' CountryTime- is of varchar type.. but iam not getting corect output plz suggets.. ...

SQL Compact 3.5 Ed, max length of SQL statement

Hi All, I'm developing a C++ application using OLEDB to communicate to SQL Server Compact Ed. 3.5. One feature of my application is a filter that helps the user narrow down data. Unfortunately, the selection criteria for the filter may become quite complex as the user may select data associated with various countries, keywords, categor...

MySQL + PHP (grouping by date)

I'm trying to output a list of different itmes grouped by the date they were stored in the database (unix timestamp). I'd need help with both MySQL (query) and PHP (output). MySQL table id | subject | time 1 | test1 | 1280278800 2 | test2 | 1280278800 3 | test3 | 1280365200 4 | test4 | 1280451600 5 | test5 | 1280451600 ...

Using a constant just once, in a SQL request

I am performing this request, and I get the results I want: UPDATE sales SET color = ( SELECT color FROM master WHERE productcode = "XJ2" ) WHERE productcode = "XJ2"; But now I use a BI transformation tool where I can enter the constant ("XJ2" here) only once. S...

NoNullAllowedException error when inserting data to a datagridview despite identity column is set to be auto-incremented

hi I have put a DataGridView in my program such that this DataGridView is corresponding to a dataset of one table and this table has a auto-incremented identity column(also,this column is set to be primary key). This identity column is not visible in DataGridView and when a user fills other cells and clicks "save",NoNullAllowedExceptio...

SQL View where value appears in one of two tables or both of them

I have two tables, both with the same columns. The first table contains original source data, the second contains revisions to that data OR values inserted where no original data was present. The tables are to be used in a view where if the data has a revision that is shown - if not then the original source data is shown. Normally thi...

Database design for email messaging system.

I want to make an email messaging system like gmail have. I would like to have following option: Starred, Trash, Spam, Draft, Read, Unread. Right now I have the below following structure in my database : CREATE TABLE [MyInbox]( [InboxID] [int] IDENTITY(1,1) NOT NULL, [FromUserID] [int] NOT NULL, [ToUserID] [int] NOT NULL, ...

Can someone please tell me what is wrong with this statement?

I am using this to insert a few things into my table and it keeps giving me this error: Microsoft VBScript compilation error '800a03ee' Expected ')' /thanks.asp, line 63 Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode) VALUES ('"Request.QueryString("payer_email") "', '" & Requ...

For each row in a table, get any one linked row in another table

Given the following (heavily simplified) tables: create table Tags ( TagId int Primary Key ) create table OrderLines ( Branch int, Station int, TransNo int, TagId int foreign key references Tags, primary key (Branch, Station, TransNo) ) I need a list of Tags along with an OrderLine which references each Tag. I am ex...

RowGuide and UNIQUE KEY

Hi, i need a GUID at row level in my table (name column RowGuid) datatype is uniqueIdentifier. Do I need also state it as a UNIQUE KEY (alternate key)? Ex: RowGuid uniqueIdentifier UNIQUE KEY Thanks ...