Easy Question

Jan 29, 2004

Hi,

I have a query that I need to pass as a string as the second parameter of the OpenQuery method. Here's my query:

SELECT * FROM mytable WHERE last_name = 'DOE'

Thing is that a string is set with apostrophies, so I don't know how to set my string since apostrophies are alse in my query. Normally, it would look like this:

DECLARE @CQUERY VARCHAR(100)
SET @CQUERY = 'SELECT * FROM mytable WHERE last_name = 'DOE''

But of course this fails. How can I do it then?

Thanks,

Skip.

View 5 Replies

I Know This Is Easy But...

Jun 9, 2004

I'm banging my head against the wall. It's probably pretty simple so here goes...

I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)

I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?

View 7 Replies View Related

C# Sql Easy Help

Sep 16, 2004

if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?

try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();

//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;

//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}

View 1 Replies View Related

Should Be Easy...Someone Has To Have Done This Already

Aug 16, 2006

This may not be the right place for this post...but my head is hurting and I can't think right now.

I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.

Can someone cure my headache?

View 1 Replies View Related

This Should Be Easy But It's Not.

Mar 3, 2006

I have written 2 custom connection mgr��s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.

But now I would like a good way to do this

I have 10 tables to copy from oracle. The only difference is just the name of the table.

static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
��
};

foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();

// dump out to raw file destination

}

I understand there are some pieces left out but that is the basic idea. It seems like I��m going to have write custom pieces for each step.

It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?

I could just write a specific piece for each table but the only difference is the table name.

View 13 Replies View Related

Hopefully An Easy One

Sep 5, 2006

I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.

View 4 Replies View Related

What Is The Easy Way To Do This?.

Oct 30, 2007

My table structure...
enddt datetime(8)
status varchar(20)

when enddt time equal to server time.

I want to change status field to expired.

What is the easy way to do this?.

enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live

View 6 Replies View Related

Easy ....If Else

Sep 18, 2007

I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.

so it would kind of be like this:

=IIF(Fields!DayOfWeek.Value=2,"Mon",

ELSE(Fields!DayOfWeek.Value=3,"Tues",

Else(Fields!DayOfWeek.Value=4,"Wed",

etc etc

Am i doing this right, or am i off??

View 4 Replies View Related

I Think This Is An Easy One...

Sep 5, 2007

If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.

View 3 Replies View Related

Has To Be Easy!

Oct 4, 2007

I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:

=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))

Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.

any suggestions? Everytime i try to put an aggregate in the box, its always tricky.

View 13 Replies View Related

SQL Query (ought To Be Easy)

Dec 21, 2006

hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)

View 6 Replies View Related

Easy Question

Oct 19, 2007

What is the equivalent of Double for SQL Server?

View 1 Replies View Related

An Easy One: The Need For Relationships?

Mar 16, 2008

Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?

View 1 Replies View Related

Another Easy One: Views

Mar 16, 2008

scenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?

View 3 Replies View Related

DataSet - Should Be Easy But I Don't Know How

Jan 6, 2004

I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.

Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)

Thanks,
Matt

View 5 Replies View Related

Easy SQL Question

Nov 21, 2004

I have a simple SQL table. The data looks like this:

products_ID----category----product_local

1----CORE----0

2----BASE----0

3----BRANCH----1

4----LEAF----3

I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.

TIA as I am completely stuck!

View 1 Replies View Related

Seems Like An Easy Task. :(

Nov 30, 2004

OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut

I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing

or

COL1 COL2
test pan

or

COL1 COL2
test ut

I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue

View 4 Replies View Related

Is There Easy Way To Bcp Ymd Format Into Mdy?

Mar 1, 2001

Hi there,

I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?

thanks a lot for your help!

Lena

View 2 Replies View Related

Easy Updates

Apr 5, 1999

Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.

View 3 Replies View Related

Easy Sql Question

Aug 2, 2002

Hi,

the following Insert statement is not acting like i would expect it. The final column (Average) divides two columns([Sum of Scores] / [Count Of Scores]) . The Average column is a numeric column with 5 decimal places. Yet, the result of the division is a whole number (no fractions). Why is this.

any help would be appreciated

Thanks,

Jim

Insert [Bankers_Scores_lead] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
SELECT [ID],
EqorMA,
Region,
Period,
IBFirm,
UndNeed,
QualIdea,
ProfAggr,
SpKnow,
negskill,
Perchem,
RepPri,
Repcomp ,
TranType,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
,
(ISNULL([UndNeed],0))
+(ISNULL([QualIdea],0))
+(ISNULL([ProfAggr],0))
+(ISNULL([SpKnow],0))
+(ISNULL([negskill],0))
+ (ISNULL([Perchem],0))
,
((
(ISNULL([UndNeed],0))+
(ISNULL([QualIdea],0))+
(ISNULL([ProfAggr],0))+
(ISNULL([SpKnow],0))+
(ISNULL([Perchem],0)) +
(ISNULL([negskill],0)
))
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
))
FROM tmp_Bankers_Scores

Here is the create table statement

CREATE TABLE [dbo].[Bankers_Scores_lead] (
[ID] [int] NOT NULL ,
[EqorMA] [smallint] NOT NULL ,
[Region] [smallint] NOT NULL ,
[Period] [int] NULL ,
[IBFirm] [int] NULL ,
[UndNeed] numeric(8,5) NULL ,
[QualIdea] numeric(8,5) NULL ,
[ProfAggr] numeric(8,5) NULL ,
[SpKnow] numeric(8,5) NULL ,
[negskill] numeric(8,5) NULL ,
[Perchem] numeric(8,5) NULL ,
[RepPri] numeric(8,5) NULL ,
[Repcomp] numeric(8,5)NULL ,
[TranType] [smallint] NULL ,
[Count Of Scores] numeric(8,5) NULL ,
[Sum of Scores] numeric(8,5) NULL ,
[Average] numeric(8,5) NULL
) ON [PRIMARY]

here is the output

ID EqorMA Region Period IBFirm UndNeed QualIdea ProfAggr SpKnow negskill Perchem RepPri Repcomp TranType Count Of Scores Sum of Scores Average
----------- ------ ------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- --------------- ------------- ----------
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000

75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000

79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000

79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000

View 1 Replies View Related

Easy Query Help

Aug 15, 2007

Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.

I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!

eg.

select Sum(column1)

where column1 = 'x'

devided by

select Sum(column1)

where column1 = 'y'

View 2 Replies View Related

Easy Stuff

Jul 1, 2004

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col.... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??????

View 3 Replies View Related

Easy Question... I Think

Jun 1, 2006

I have a ASP page where the user posts part of the name field. Then i need to provides the matches. (providing partial comparision of the field & correcting bad spelling)

example:

User posts 'softwair' (should be software)

the query look like this
Select Name from A where Name is like '%softwair%' <-- but appling soundex on it in the like field, or something like it.

I could make a copy of the column and update all info in the field to the soundex value, but then the like statement would not get it if the value was only part of it. for example say the data values where like 'Microsoft Software', or 'My Software Solutions', ect...

View 1 Replies View Related

Probably An Easy JOIN. I But I Don't Know

Jun 22, 2007

I'm trying to find records in the 'user' table that DO NOT have a record in the 'stuff' table. So basically the statement should only return record 3 from the user table since there is not record in the stuff table for it. I think it a join, but I'm second guessing my self. Can some one please assist me. Thanks in advance.

user
uid name
1 James
2 Erick
3 Todd

stuff
userid thing
1 car
1 house
2 mansion

View 1 Replies View Related

Easy BCP Question ;)

Sep 13, 2007

Hey all,

A nice easy BCP question for y'all...
I'm currently running:

DECLARE @sql varchar(2000)

SET @sql = 'BCP master..sysobjects OUT C:sysobjects.csv -c -t, -T -S' + @@ServerName

EXEC master..xp_cmdshell @sql

Which works fine!
But how on earth do I save it to my local PC?
May sound silly, but this is one of my first adventures into BCP and I'd rather not "pollute" the server with my test files!

Thankyall!

View 8 Replies View Related

Easy Question

Feb 20, 2008

I'm used to forming Select statements for IBM System i imbedded SQL in High Level source programs. I now find myself converting System i RPG programs to VB.NET classes. What I want to know is how to insert the value of a class variable into a SQL Select string. In the IBM world, I would do:

Select STCTYC,STCRID,STTITL,STSTTY From CSLSTT01 Where STCTYC = :County

where County is a program defined variable. How do I get the value in the County variable into the Select string? I tried concatenation but that didn't work either. Thanks.

View 7 Replies View Related

Easy Trigger

Jan 5, 2004

I create trigger no table person. When I invoke sql command
insert into person values (.... bla bala bal)
trigger is autommaticaly fired.
The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (.... bla bala bal).
How I can do it?

View 5 Replies View Related

Easy Question

Feb 5, 2004

How can I output the result of a query in a text file.
(I want to use SP)

View 1 Replies View Related

Another Easy Question

Feb 6, 2004

I want to use a trigger (FOR DELETE)
For example

CREATE TRIGGER test ON [Table_1]
FOR DELETE
AS
...... export to a text file someting like this:
Date + TableName + UserName + Record with will be deleted

How can I export a single record to a text file ?

Sorry for my english

View 3 Replies View Related

Easy Question

Apr 1, 2004

I've read some post that highly recommend not putting a clustered pk on identity columns. Rather use a non-clustered pk for identity pk columns. Does anyone have opinions about this? I have several reference tables that have the following structure. Since joins are made on the pk column I would guess that it would be beneficial to make the pk the clustered index, then again maybe it's better to make one of my unique indexes clustered. Any thoughts?

CREATE TABLE [company] (
[company_id] [int] IDENTITY (1, 1) NOT NULL ,
[co_abbrev] [char(10)] NOT NULL ,
[co_name] [varchar(50)] NOT NULL ,
CONSTRAINT [pk_company] PRIMARY KEY CLUSTERED
(
[company_id]
) WITH FILLFACTOR = 90
)

CREATE UNIQUE INDEX [ux_company_01] ON [dbo].[company]([co_name]) WITH FILLFACTOR = 90

CREATE UNIQUE INDEX [ux_company_02] ON [dbo].[company]([co_abbrev]) WITH FILLFACTOR = 90

View 1 Replies View Related

Easy Question

Apr 6, 2004

Original Table

Col_1 ---Col_2
DE
DE
XB
DE
C
XB
D
XB

I want to obtain this

Col_1 --Col_2
DE----------1
DE----------1
XB----------2
DE----------1
C-----------3
XB----------2
D ----------4
XB----------2

or this

Col_1 ----Col_2
DE---------- 1
XB-----------2
C------------3
D------------4

Is possible without use cursors ?

View 4 Replies View Related

Hopefully An Easy Question

Mar 1, 2007

I'm creating a report from my table and I would like to take the last name field and only display the first two characters. I can't find out how to do that.

Any ideas?