|
This thing bother me some time already
Say I have form that should display data about some student; one of thing it need to display is cityID which is just foreign key to some city in table city.
How can I make it display name of city instead of ID, but write ID to table student.
I tried to do this logical way but never worked, first if there is some data in base it'll show FK value instead on city name, second when I drop down list I get names of cities, but when I pick one from list I never can continue even though I selected value member as ID field not name field (in parent table).
I tried any combination available still doesn't work as expected.
all data is show in details mode and combo box is for cityID value...
Any idea?
|
|
|
|
|
Hi All,
I am trying to run an update query from Oracle SQL Plus, I need to change a reference ID (in table Equipment) from A to B where the ContractID (in table Contract) is 1234, however the link between Contract table and Equipment is done through 2 other tables, the query is:
UPDATE EQUIPMENT SET REF_ID = ‘B’
WHERE EXISTS
(SELECT CONTRACT.CONTRACT_ID, EQUIPMENT.REF_ID
FROM ((CONTRACT LEFT JOIN CONTRACT_PERIOD ON CONTRACT.CONTRACT_ID = CONTRACT_PERIOD.CONTRACT_ID) LEFT JOIN CONTRACT_EQUIP_PERIOD ON CONTRACT_PERIOD.CONTRACT_PERIODOID = CONTRACT_EQUIP_PERIOD.CONTRACT_PERIODOID) LEFT JOIN EQUIPMENT ON CONTRACT_EQUIP_PERIOD.EQUIPMENTOID = EQUIPMENT.EQUIPMENTOID
WHERE (((CONTRACT.CONTRACT_ID)=’1234’) AND ((EQUIPMENT. REF_ID)=’A’)));
when I run this query the rather than change 2 records, it changes about 233,000 of them (Good things I'm using the test Database)
If you could tell me where I'm going wrong, I would really appreciate it.
Many thanks
Sayian
Ignorance is darkness.
Knowledge is light.
|
|
|
|
|
I think you need the following:
UPDATE EQUIPMENT SET REF_ID = ‘B’
WHERE EQUIPMENTOID IN (
SELECT CONTRACT_EQUIP_PERIOD.EQUIPMENTOID
FROM CONTRACT
LEFT JOIN CONTRACT_PERIOD
ON CONTRACT.CONTRACT_ID = CONTRACT_PERIOD.CONTRACT_ID
LEFT JOIN CONTRACT_EQUIP_PERIOD
ON CONTRACT_PERIOD.CONTRACT_PERIODOID = CONTRACT_EQUIP_PERIOD.CONTRACT_PERIODOID
WHERE CONTRACT.CONTRACT_ID = 1234
)
AND EQUIPMENT.REF_ID= 'A' ;Your original SQL was saying "Do any equipment rows exist for the criteria? Right now update everything single equipment record!"
Regards
Andy
|
|
|
|
|
I need to grab data from a table not in my database.
The other database is on the Ms SQL server.
Now I know how to access it, but I was just thinking what would be the best method for accessing the data.
First I was thinks about just working with the tables in the other database and using sored procedures to insert and access the data.
The second option is to make views to the other database and use them as if there locale tables.
The second option makes it easy to make any change later, but this probably not happen and I could just act a an overhead.
Anyone a pointers on the best way to do this?
|
|
|
|
|
How can i convert a getdate() result to a style of yyyymmddhhmmss
Thanks
Javierarka
|
|
|
|
|
select replace(convert(varchar(10), GetDate(), 112) + convert(varchar(8), GetDate(), 114), ':', '')
Regards
Andy
|
|
|
|
|
select replace(replace(replace(convert(varchar(19),getdate(),120),'-',''),':',''),' ','')
|
|
|
|
|
Ok, I have a concurrency problem (I think) while trying to create a field using CDaoDatabase & CDaoRecordset.
Long and short of it is that I need to create a field in an access database while still keeping a recordset that is connected to the table open. When I create the field using the following code:
CDaoTableDef tableDef(&m_daoDB);<br />
tableDef.Open(sTableName);<br />
tableDef.CreateField(newField);
I also have a recordset open on the same table. The last line throws an exception with a message: The database engine could not lock table 'Test Datatypes' because it is already in use by another person or process.
Any help or thoughts on that would be wonderful. Thanks in advance.
|
|
|
|
|
Is there a way to increment a field that is an INT value, without having to query the existing value and then add one and write it back?
For example, if I have a table like this:
Column Name | Datatype
======================
ID | INT (Identity)
Count | INT Then is there a way I can do something like this?
INCREMENT Count FROM MyTable WHERE ID='1' I'm using Microsoft SQL Server 2005. Thanks in advance for any help!
|
|
|
|
|
UPDATE MyTable SET Count=Count+1 WHERE ID='1'
|
|
|
|
|
  Thanks!!!
|
|
|
|
|
Hi,
This is quick question regarding calling function from join.
In my project i am facing one situation where i need to use join
between one temporary table and database table.From that join statement
i need to call one function which taking 4 input parameters of temporary table and 4 input parameters of database table and returns one varchar2 value.
Now here is question
Let's say i creates one join statement like :
"Select temp_table.firstname,temp_table.lastname,temp_table.ssn,
db_table.firstname,db_table.lastname,db_table.ssn,
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
from temporary_table ,database_table
where
(
/*
some code in where clause which joins temporary table and database table.
*/
)
Now my question is that do my
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
function returns same value for value based on input parameter or it returns
different differnt value every time.
Or can some one say me that how my function works in join query???
Actually , my requirement is that depends on where clause of join,
function takes input parameters and return back to select query and
everytime function should return new value for one by one record.
Is this join works of not for that??
Can some body help me out for this??
Thankx.
regards,
montu3377
|
|
|
|
|
My first idea would be to try it and see, but I think i see what you mean and your function should produce a different value based on the row, as long as the input parameters are different, of course...
|
|
|
|
|
I have the Stored Procedure below which I run using the code below that but each time I run the procedure the row gets added to the table but the @Identity parameter is always the value 1. There is a column on the Invoice table called InvoiceID which has Identity set as Yes.
Am I doing something wrong?
Thanks
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertInvoice]
-- Add the parameters for the stored procedure here
@IClientID int
,@InvoiceNumber varchar(15)
,@InvoiceDate datetime
,@PaymentStatus tinyint
,@Description varchar(255)
,@InvoiceNotes text
,@ICustomCheck1 bit
,@ICustomCheck2 bit
,@ICustomCheck3 bit
,@ICustomCheck4 bit
,@ICustomCheck5 bit
,@ICustomCheck6 bit
,@IsAgent bit
,@Identity int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Invoice]
([IClientID]
,[InvoiceNumber]
,[InvoiceDate]
,[PaymentStatus]
,[Description]
,[InvoiceNotes]
,[ICustomCheck1]
,[ICustomCheck2]
,[ICustomCheck3]
,[ICustomCheck4]
,[ICustomCheck5]
,[ICustomCheck6]
,[IsAgent])
VALUES
(@IClientID
,@InvoiceNumber
,@InvoiceDate
,@PaymentStatus
,@Description
,@InvoiceNotes
,@ICustomCheck1
,@ICustomCheck2
,@ICustomCheck3
,@ICustomCheck4
,@ICustomCheck5
,@ICustomCheck6
,@IsAgent)
SET @Identity = SCOPE_IDENTITY()
END
SqlCommand cmd = dCon.CreateCommand();
cmd.CommandText = "InsertClient";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@HoldingNumber", SqlDbType.VarChar).Value = client.HoldingNumber;
cmd.Parameters.Add("@TradingTitle", SqlDbType.VarChar).Value = client.TradingTitle;
cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = client.Surname;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = client.ContactName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = client.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = client.Address2;
cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = client.Address3;
cmd.Parameters.Add("@Address4", SqlDbType.VarChar).Value = client.Address4;
cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = client.Postcode;
cmd.Parameters.Add("@Tel", SqlDbType.VarChar).Value = client.Tel;
cmd.Parameters.Add("@Tel2", SqlDbType.VarChar).Value = client.Tel2;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = client.Mobile;
cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = client.Fax;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = client.Email;
cmd.Parameters.Add("@Agent", SqlDbType.TinyInt).Value = client.Agent;
cmd.Parameters.Add("@CTSUsername", SqlDbType.VarChar).Value = client.CTSUsername;
cmd.Parameters.Add("@CTSPassword", SqlDbType.VarChar).Value = client.CTSPassword;
cmd.Parameters.Add("@LMeNTRY", SqlDbType.Bit).Value = client.LMeNTRY;
cmd.Parameters.Add("@FarmMatters", SqlDbType.Bit).Value = client.FarmMatters;
cmd.Parameters.Add("@ClientNotes", SqlDbType.VarChar).Value = client.ClientNotes;
cmd.Parameters.Add("@Support", SqlDbType.TinyInt).Value = client.Support;
cmd.Parameters.Add("@Licenes", SqlDbType.Int).Value = client.Licenes;
cmd.Parameters.Add("@IsBureau", SqlDbType.Bit).Value = client.IsBureau;
cmd.Parameters.Add("@OperatingSystem", SqlDbType.TinyInt).Value = client.OperatingSystem;
cmd.Parameters.Add("@SBI", SqlDbType.VarChar).Value = client.SBI;
cmd.Parameters.Add("@CustomCheck1", SqlDbType.Bit).Value = client.CustomCheck1;
cmd.Parameters.Add("@CustomCheck2", SqlDbType.Bit).Value = client.CustomCheck2;
cmd.Parameters.Add("@CustomCheck3", SqlDbType.Bit).Value = client.CustomCheck3;
cmd.Parameters.Add("@CustomCheck4", SqlDbType.Bit).Value = client.CustomCheck4;
cmd.Parameters.Add("@CustomCheck5", SqlDbType.Bit).Value = client.CustomCheck5;
cmd.Parameters.Add("@CustomCheck6", SqlDbType.Bit).Value = client.CustomCheck6;
cmd.Parameters.Add("@Identity", SqlDbType.Int);
cmd.Parameters["@Identity"].Direction = System.Data.ParameterDirection.Output;
if(isClosed) dCon.Open();
cmd.ExecuteNonQuery();
if(isClosed) dCon.Close();
return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
|
|
|
|
|
Stored procedure name as defined:
Howellsy wrote: ALTER PROCEDURE [dbo].[InsertInvoice]
Stored procedure called by your code:
Howellsy wrote: cmd.CommandText = "InsertClient";
Maybe that could be the problem? Or is it just a copy-past error (looks like it)?
It would be helpful to see the definition of the Invoice table.
Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash
Blog | Site | Articles
|
|
|
|
|
It was just a copy and paste error, that is the actually Stored Procedure, which is much the same but with different data fields. Below the Stored Procedure is the sql for creating the table. Not sure what the table definition is and how to get it, could you please tell me how to get it?
Kind Regards
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertClient]
-- Add the parameters for the stored procedure here
@HoldingNumber varchar(12)
,@TradingTitle varchar(30)
,@Surname varchar(30)
,@ContactName varchar(30)
,@Address1 varchar(30)
,@Address2 varchar(30)
,@Address3 varchar(30)
,@Address4 varchar(30)
,@Postcode varchar(30)
,@Tel varchar(20)
,@Tel2 varchar(20)
,@Mobile varchar(20)
,@Fax varchar(20)
,@Email varchar(255)
,@Agent tinyint
,@CTSUsername varchar(15)
,@CTSPassword varchar(15)
,@LMeNTRY bit
,@FarmMatters bit
,@ClientNotes varchar(255)
,@Support tinyint
,@Licenes int
,@IsBureau bit
,@OperatingSystem tinyint
,@SBI varchar(20)
,@CustomCheck1 bit
,@CustomCheck2 bit
,@CustomCheck3 bit
,@CustomCheck4 bit
,@CustomCheck5 bit
,@CustomCheck6 bit
,@Identity bit OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Client]
([HoldingNumber]
,[TradingTitle]
,[Surname]
,[ContactName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Postcode]
,[Tel]
,[Tel2]
,[Mobile]
,[Fax]
,[Email]
,[Agent]
,[CTSUsername]
,[CTSPassword]
,[LMeNTRY]
,[FarmMatters]
,[ClientNotes]
,[Support]
,[Licenes]
,[IsBureau]
,[OperatingSystem]
,[SBI]
,[CustomCheck1]
,[CustomCheck2]
,[CustomCheck3]
,[CustomCheck4]
,[CustomCheck5]
,[CustomCheck6])
VALUES
(@HoldingNumber
,@TradingTitle
,@Surname
,@ContactName
,@Address1
,@Address2
,@Address3
,@Address4
,@Postcode
,@Tel
,@Tel2
,@Mobile
,@Fax
,@Email
,@Agent
,@CTSUsername
,@CTSPassword
,@LMeNTRY
,@FarmMatters
,@ClientNotes
,@Support
,@Licenes
,@IsBureau
,@OperatingSystem
,@SBI
,@CustomCheck1
,@CustomCheck2
,@CustomCheck3
,@CustomCheck4
,@CustomCheck5
,@CustomCheck6)
SET @Identity = SCOPE_IDENTITY()
END
USE [BreedSociety]
GO
/****** Object: Table [dbo].[Client] Script Date: 01/26/2007 08:39:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[HoldingNumber] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TradingTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Surname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address4] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Postcode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Mobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Agent] [tinyint] NOT NULL,
[CTSUsername] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CTSPassword] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LMeNTRY] [bit] NOT NULL,
[FarmMatters] [bit] NOT NULL,
[ClientNotes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Support] [tinyint] NOT NULL,
[Licenes] [int] NOT NULL,
[IsBureau] [bit] NOT NULL,
[OperatingSystem] [tinyint] NOT NULL,
[SBI] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomCheck1] [bit] NOT NULL,
[CustomCheck2] [bit] NOT NULL,
[CustomCheck3] [bit] NOT NULL,
[CustomCheck4] [bit] NOT NULL,
[CustomCheck5] [bit] NOT NULL,
[CustomCheck6] [bit] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
|
|
|
|
|
Howellsy wrote: ,@Identity bit OUTPUT
A BIT can only have a value of 1 or 0 (or NULL). Change the type to INT.
|
|
|
|
|
Thank you very much, what a silly mistake to make.
|
|
|
|
|
Hi
i want to select the values which are not in the other table mentioned below:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
the following error occuring:
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
plz help this.
SHAHZAD ASLAM
Software Engineer
shehzadaslam@hotmail.com
|
|
|
|
|
Try these steps........
First u make inner join with both table
and check all fields seperately with not equel operator ( add condition with And).
Or,
U can also use outer join. plz refer outer join...........
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
The error you are getting here is because you have not defined boolean conditions for your AND statements. For instance, AND new_account, "AND new_account what"? Does this mean AND new_account = 1?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
if i remove the boolean field new_account from query still same error is occuring.
|
|
|
|
|
AND new_account AND rsp_code_rsp
But what about rsp_code_rsp - That is exactly the same thing.
Also your IN(SELECT...) statement isn't right either. You should only be specifying a single column in the SELECT statement in the IN() expression.
|
|
|
|
|
rsp_code_rsp is not boolean field the only boolean field is new_account but if i remove this new_account from query the same error is occuring.
|
|
|
|
|
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Right - let's analyze this statement:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type What does this equate to?
AND tran_type Same here...
AND new_account And here...
AND rsp_code_rsp And here...
AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Change to reference only one attribute here.
It is obvious from the above that you need to supply the other side of the test in each case highlighted in bold. For instance, you may want to specify AND tran_type = 2.
However, I think what you are trying to do in your statement is to test to retrieve records from transactions where there is not a corresponding entry in tran_types_dimension. Is this correct?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
