|
Thanks for all your help. have another question.
I have a database name star. My star database have 4 tables contact, units invoices and payments
in my contact table I have columns contact id Fname, LName, Address, City, State, zip, email.
in the invoice table I have columns invoice id, contact id, startdate, enddate, subtotal, total,
I wrote a little app from the help from here to migrate my data because SSIS 2005 was having problems with null value data in the contact table import perfectly and invoice imports as well but in but my contact id in the Contact table is not matching the contact id in the invoice table.
if I can get the a sample of some code in sql I can do it in c#
Learning to Code
|
|
|
|
|
It is called referential integrity and is a fundamental of database design, you need to understand it before you will be able to progress with this. You need to understand the use of foreign keys and identity fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there way for me to do this in c#.
Learning to Code
|
|
|
|
|
Hi to all,
In My SP i created this table
DECLARE @inc_table TABLE(ID INT identity(1,1),ModelName VARCHAR(20),Region VARCHAR(20),Tot VARCHAR)
BEGIN
INSERT INTO @inc_table (ModelName)
SELECT ModelName FROM SRO_ModelMaster
END
NOw my table is fill with all modelname say,
1 PPM
2 CCP
3 MMM
NOw in same table i want to add
RegionCode using following statment
INSERT INTO @inc_table (Region)
(SELECT RegionCode FROM cmp_RegionMaster WHERE regionCode<>'TR' )
but it is inserting at the 4,5,6, id levelI want to append it to
on same id that is 1,2,3
so that it gives me
1 PPM ER
2 CCP NR
3 MMM SR
How i can achive this in my sp
Thanks for all for yours kind suggestions.
|
|
|
|
|
If you want to update existing records, use UPDATE[^] statement, not INSERT.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi everyone,
It seems there is next to no help online anywhere for using the Odbc classes in the .Net framework with Oracle Lite. There is plenty of online info for full blown Oracle but that appears to be no use to me. Has anybody had any experience with this? My connection string as it stands is:
Driver={Oracle Lite 40 ODBC Driver};dsn=faultdb9i;UID=system;PWD=XXXXXXXXX;
but I get "POL-3203 Error: Database does not exist". It does exist, and I can connect to it fine using Oracles MSQL application. I have tried many different variations of connection string with no success - I've stuck with this one as the .net framework is at least catching an Oracle error so I must be getting close.
Is there some magic connection string parameter for use with Oracle Lite?
Many thanks,
Chris Chambers.
|
|
|
|
|
Are you sure about the error? Based on documentation POL-3203 means "Bad BLOB block address".
Also you could could try to use connection string like:
dsn=faultdb9i;UID=system;PWD=XXXXXXXXX (without driver info) or
Driver={Oracle Lite 40 ODBC Driver};server=faultdb9i;UID=system;PWD=XXXXXXXXXMika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Thanks for looking into that for me. The error was my mistake - a typo. I meant POL-3023 which really is "The database does not exist".
The Microsoft ODBCConnection class won't accept a connection string without a driver and I tried using the server item too but that didn't work either - still got POL-3023. After spending the best part of 6 hours researching and trying dozens of different variations of connection string I decided to give up on the System.Data.ODBC namespace and opt for the Oracle.DataAccess.Lite DLL direct from Oracle instead. Unfortunately you can only get your hands on it by downloading the 400MB+ Oracle 10g Lite installation package but, once I'd done that, it worked first time with:
dsn=faultdb9i;UID=system;PWD=XXXXXXXXX
Oh well, you win some, you lose some.
Cheers,
Chris.
|
|
|
|
|
That's odd. Normally it's enough that you specify a DSN if it's correctly defined in Control Panel. Driver is typically needed only if you create a DSN on-the-fly.
However, I believe that native client will be much better for you since it's more efficient and allows you to better use Oracle specific features.
About the client. I typically use full Oracle client when connecting to Oracle and I'm under the impression that it works against any Oracle database edition (starting from version 9i). Have you tried using Oracle Data Access Components (ODAC). If you have time, I think it's worth checking out. The download size is less than 50 megabytes and it supports xcopy deployment.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Mika Wendelius wrote: That's odd. Normally it's enough that you specify a DSN if it's correctly defined in Control Panel. Driver is typically needed only if you create a DSN on-the-fly.
That's exactly what I thought, but I always got an Invocation exception when I didn't specify a driver. I know that the datasource is set up correctly because I have an older C++ application that uses good old-fashioned ADO that connects fine with just the DSN, the UID and the PWD.
Mika Wendelius wrote: Have you tried using Oracle Data Access Components (ODAC). If you have time, I think it's worth checking out.
I haven't, but I will certainly investigate it.
Thanks for your help Mika.
Regards,
Chris Chambers.
|
|
|
|
|
You're welcome 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I just got some SQL Query to get updated cell like below:
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2
INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3
And, I want to change it into dynamic SQL since it is too much to type the column one by one:
DECLARE @COLUMN_NUMBER AS integer
DECLARE @COLUMN_TOTAL AS integer
DECLARE @COLUMN_NAME AS nvarchar(100)
DECLARE @SQL AS nvarchar(1024)
SET @COLUMN_NUMBER = 1
SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')
WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
BEGIN
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
SET @SQL =
'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
FROM Inserted
INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
EXEC(@SQL)
SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
END
And it got an error message saying Invalid object name 'Inserted'
Can anyone help me?
Thanks
- No Signature Available -
|
|
|
|
|
AFAIK you cannot use inserted or deleted tables via dynamic sql in a trigger. Perhaps you could use COLUMNS_UPDATED or UPDATE functions instead.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Error Message:-
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
I have dotnet application and using sql server 2000 for database. This application installed at client server is working fine since last release, but from last 2 days all users, logged in to appliction. Application start sucessfully and after some time (not fixed) as application fetch the data this error is generate and its continue till user close the application.
I am not able to find out the actual root of the error.
I am feeling that code dont have any problem, since same application running fine before this error message and we don' t
do any change there.
If you required any more information please let me know about that.
Thanks in advance for the your feedback and help..
I am wating for reply...
Thanks and regard,
Nitin
|
|
|
|
|
You may be using a closed connection object. That is the most common cause.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
Hi to all
select ModelName from SRO_ModelMaster
select RegionCode from cmp_RegionMaster
SELECT Count(*) as 'TOT' FROM SRO_SurveySelectedModel,cmp_RegionMaster WHERE
modelno in (Select ModelNo From SRO_ModelMaster where ModelName='FNRO')
AND approved='Y' AND RosapCode in (
SELECT ROsapCode FROM SRO_ROMaster where region='ER')
Please tell me how i can combine these three statments into Single SQL
Statment
I want to develop Stored procedure From this without using temp table
Thanks & regard
Sonia
|
|
|
|
|
Basically you need to join those tables and then group by ModelName and RegionCode but to get this statement further: what is the relationship between those four tables?
Your query should look something like:
SELECT SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCode,
Count(*) as 'TOT'
FROM SRO_SurveySelectedModel,
cmp_RegionMaster,
SRO_ModelMaster,
SRO_ROMaster
WHERE ???.modelno = SRO_ModelMaster.ModelNo
AND SRO_ModelMaster.ModelName='FNRO'
AND ???.approved='Y'
AND ???.RosapCode = SRO_ROMaster.ROsapCode
AND SRO_ROMaster.region='ER'
AND SRO_ROMaster.??? = ???.???
GROUP BY SRO_ModelMaster.ModelName,
cmp_RegionMaster.RegionCode
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hello All,
I am getting this error "Conversion failed to when converting datetime string to smalldatetime data type" in Sql Server 2008 and my string is 20081110072938 .....I also tried with datetime data type in Sql server but same error.
Exception comes at query
sql.Format("Insert into trade values('%s','%s','%s','%s','%s','%ld')",
m_sTimeStamp,m_sTradeStatus, m_sTradeType, m_sTradeXML, m_sTradeKcc, m_nTrdNumber);
here m_sTimeStamp has the value 20081110072938 .
query is like
insert into trade values('20081110072938','yes','Regular','xml','kcc','1');
first column is of datetime type.. it gives the conversion error in SQL Server 2008 .
.. whereas same string is getting converted to datetime format in MySQL ... and working fine with MySQL...
I am doing all this in VC++.
Can anybody help me out of this...
Thanks
|
|
|
|
|
Yes I could able to insert now by converting string 20081110110223 to 2008/11/10 11:02:23 and then using into insert query.. now both sqlserver as well as MySql could abto insert this into datetime format..
insert into trade values('20081110072938','yes','Regular','xml','kcc','1');
To
insert into trade values('2008/11/10 11:02:23','yes','Regular','xml','kcc','1');
Regards,
|
|
|
|
|
So are you still having problems with this?
Each database server typically has a different 'native' format for dates and times. The easiest (and a correct) way to overcome this is to use parameters for operations instead of literal values. For example if you're using OdbcConnection, you should use OdbcParameter class to define values for the operations.
If you still need to use literal, perhaps you could use native conversion functions such as CONVERT or CAST in SQL Server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
So whats the problem? Where's the code thats failing?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi, how can i create a database script? so that i dont have to copy paste my declarations of creating the database and tables from notepad.
Im using MySQL Server 5.0
Thank you!
|
|
|
|
|
Can you simply save it as a text file and then use source command in mysql command line client?
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don't write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
really? so i can just save it as db.txt and inside it it will contain:
create database myDatabase;
use myDatabase;
etc.
etc....
then i will just type:
source db.txt
?
Thank you! 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
