|
Comparing two tables from different databases
I need to compare two tables from two different databases to show records in one table that are not in another. I am using ADO.NET and C#. I don’t wish to use a database link in MSSQL because it’s too slow. All that is left is code. In case you are curious, one database is MS SQL 2k and the other is Pervasive2000i.
Lets use DatabaseA TableB And DatabaseC TableD
If I where able to write a simple sql statement to show records in AB not in CD could be:
SELECT AB.ID, AB.Name
FROM DatabaseA.TableB as AB Left Join DatabaseC.TableD as CD on AB.ID = CD.ID
WHERE CD.ID is null
I am looking for a way to do this in C#. Using data relations between DataTables in a DataSet I think I can set up a filter to achive the same information, but I have ran out of time to experiment. So I look to you for some assistance. Thank you for any contributions.
Brett Slaski
|
|
|
|
|
Not sure if this is the right place to post this but I'm using Asp.net to design a report system. I want to show the clients what the final report will look like after exporting it as a crystal report but i have never used crystal reports before and dont know what the output looks like. Obviously it will look different depending on the information to be displayed but i just want to get an idea of how it is layed out. I have looked for a sample of a crstal report output but cant find any, so if anyone could post up an example it'd help alot
Thanks
Colum
|
|
|
|
|
right well now im looking to store information in a dataset (hard coded in, not extracted from a database or anything) and display it as a crystal report... anyone?
|
|
|
|
|
Hi,
I had developed a setup project which attaches a database while installing it & detaches it when uninstalled. Installation works fine. While uninstalling it throws an exception:"Cannot detach the database ,it is currently in use".
How do I forcefully detach a database (even though it is connected to any appln)?
Thankx
Priya
|
|
|
|
|
WHen you detach a datatabse , no connection should be exist to database. Do you close connections in your appliications before detaching , or do you logged into that databe while you are uninstalling your application?
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Hi,
I have 3 PC reside in a LAN, my VC++ application install in all 3 PC that must access the same database Main_database.mdb to do a multi-tasks job.
Because of that I have placed my Main_database.mdb in a Map Network drive Z: in order all 3 PC can access, my question is:
1. When one application connects to Main_database.mdb and using it ... is that the whole database is copied temporary into its memory then travels back & forward (between this PC & Z: ) in order to update data inside the z:Main_database.mdb?
2. I verified the speed of update Main_database.mdb is not slow at all, but if the same PC, I use another application which also uses the same Main_database.mdb at the same time ... then both of them run very slow! Can someone explains why?
*) I have known that if I use XML to acess database it will be safe & faster method!
Thanks for any help for understand more about acess database method!
Anh
|
|
|
|
|
Hi Anh. I don't know if I can answer either of the two questions you've posed, but I can offer a suggestion. Although Access databases can be used the way you've described (back-end data store on a shared network drive) you will probably see several advantages to migrating the back-end to run on a true client-server RDBMS. You could migrate the data to the MSDE for example - it functions well for a small workgroup database without the limitations of file-sharing databases. And - someone correct me if I'm wrong - I think an Access/Office license allows you to use the MSDE this way at no extra cost.
|
|
|
|
|
i've make a column named "PIC" in my DB & made it of tiype "IMAGE" put when i'm goin to fill tables i can't put an image " hyper link" how do i put it??? thnx 4 all
|
|
|
|
|
|
I have a date database field, but I am storing the field values as string in front end. I want to covert that value to date field while inserting into database. But the problem is converting a null value to date field.
Please help
|
|
|
|
|
Your question is nebulous. In the case of a null value, what do you want to happen? Store a null value, or some default value? If the former, pass NULL. If the latter, pass the default (or set up the table column to have a default value and don't pass anything at all).
Regards,
Jeff Varszegi
EEEP! An Extensible Expression Evaluation Package
|
|
|
|
|
I use the following functions in conjunction with a datatable to retrieve and set the date values with the front end.
Friend Function DateToDb(ByVal val As Object) As Object
'Validates a datevalue before saving to db
If IsDate(val) Then
Return val
Else
Return DBNull.Value
End If
End Function
Friend Function DateFromDb(ByVal val As Object, Optional ByVal DateFormat As String = "MM/dd/yyyy") As Object
'Validate the date from the database. Return "" if null
If TypeOf val Is Date Then
Return Format(val, DateFormat)
End If
Return C_ES
End Function
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hello,
I have a problem in preserving the format of the data...
Consider this live example...
when i insert this message, the reply will appear as it is i type..
How to preserve that format?
|
|
|
|
|
i have a XML file contaning data. this data contains a feild name paymentmode. on the basis of paymentmode i have to generate different reports. how i can filter all these records if i am using ADO.net based on a criteria on paymentmode. can i generate different XML files from the Main Xml File on the basis of paymentmode
Dinesh Rana
|
|
|
|
|
Question: What's the right order so the connection pool is reused?
<br />
SqlConnection myConnection = new SqlConnection(m_sConnectionString);<br />
<br />
SqlCommand myCommand = new SqlCommand(sQuery, myConnection); <br />
<br />
myConnection.Open();<br />
myCommand.ExecuteNonQuery();<br />
<br />
myConnection.Close(); <br />
myConnection.Dispose();<br />
or
<br />
myConnection.Dispose();<br />
myConnection.Close(); <br />
<br />
??? Anybody?
Al
|
|
|
|
|
myConnection.Dispose();
myConnection.Close();
Dispose and then close
|
|
|
|
|
|
It's a bit odd that it has both. Close comes from IDbConnection, while Dispose is inherited from System.ComponentModel.Component.
A bit of digging with Reflector[^] shows that Dispose simply calls Close, if the connection is in the Open state, then sets the ConnectionString property to null.
My rule of thumb is that if you're planning to call Open on this object again, use Close, but if you're not going to use this object again, use Dispose.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
That's good information, but I don't think that using Dispose with the built-in connection pooling will do anything but incur a slight performance penalty for the extra work, although I haven't tested yet. It seems that the actual underlying connection is just mapped to IDbConnection instances, and associated upon Open() and disassociated upon Close. From the docs, it seems logical to assume that resetting ConnectionString, in correctly implemented code, would result in the IDbConnection being disassociated from its pool, but why bother if you're just throwing the object away?
One good thing that you bring up is that it's probably a good idea to hang onto your IDbConnection if you're going to need a connection again, even after Close(). This'll get you out of some creation and destruction overhead. Bright bright bright
-Jeff
here, bloggy bloggy
|
|
|
|
|
I have a service that connects to SQL Server 2K via ODBC.
I need the service to know when certain tables have been modified.
Right now the plan is :
- create trigger(s) for the appropriate modifications
- have the trigger(s) call an extended stored proc with the name of the server, port and message
- the esp will connect to the specified service and send the message
Questions :
Is there a way to do this already that i've missed ?
If not, when does the dll with the esp get loaded/unloaded.
In particular, i don't want the esp to create a socket each time it's called, i'd like to have 3 esp's : an init, one called by the trigger, and a term. The init would open the socket, the term would close it, and the one called by the trigger would just send the message. Of course this won't work if the dll is loaded/unloaded on each call (doubt it works this way, just want to make sure).
...cmk
Save the whales - collect the whole set
|
|
|
|
|
Hey guys,
I need a stored procedure which could read data from all types of files and then print the data or store it in a table. i have tried bulk insert but when bulk insert reads the data and stores it in a table it creates many rows (.i.e rows = number of lines in the files). say if there are 20 lines then 20 rows and if 700 lines 700 rows. I want to store the data in one string and print it. Also the files i need to read are generated on runtime. If u want to take a look at the Sto. Proc. here it is. Please note files culd be of any size.
**********************************************************
1. Bulk insert
CREATE PROC sp_readTextFile @filename sysname
as
BEGIN
SET nocount ON
EXEC ('bulk INSERT WilTemp FROM "' + @filename + '"')
SELECT * FROM WilTemp
END
GO
***********************************************************
2. Using a filesytem object
CREATE PROCEDURE [dbo].[readFromFile] (@FileName varchar(255))
AS
DECLARE @FS int, @OLEResult int, @FileID int, @Text1 varchar(8000)
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--read a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'
--Read Text1
execute @OLEResult = sp_OAMethod @FileID, 'ReadAll', @Text1 OUT
IF @OLEResult <> 0 PRINT 'ReadAll'
--insert into tfromfile values (@Text1)
Print @Text1
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
GO
|
|
|
|
|
Does anyone know how to insert commas in database? like if i want to insert:
Hi,How are you? I am fine, ThanQ.
??
|
|
|
|
|
This should work:
INSERT myTable(myColumn)
VALUES('Hi,How are you? I am fine, ThanQ')
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
Coming soon: The Second EuroCPian Event
|
|
|
|
|
Thats wrong syntax.
What about multicolumns?
Insert into Table1(col1,col2,col3) values('Hi, how are you?','I'm fine thanQ',I guess, this SQL will fail, for sure');
See, so many quotes and commas in the SQL statement.
Do I need to parse and eleminate all those quotes and commas, do u have any other trick?
|
|
|
|
|
Anonymous wrote:
Thats wrong syntax
Sorry, I missed the INTO 
Anonymous wrote:
What about multicolumns?
They are no different.
INSERT INTO Table1(col1, col2, col3)
values('0,23','2,49','4,99')
Anonymous wrote:
See, so many quotes and commas in the SQL statement
Ah! Now... This is different - You never mentioned anything about quotes before.
Anonymous wrote:
Do I need to parse and eleminate all those quotes and commas, do u have any other trick?
No tricks, just the SQL Server Books online that come with SQL Server 2000.
SET QUOTED_IDENTIFIER OFF
GO
Insert into Table1(col1,col2,col3)
values("Hi, how are you?","I'm fine thank you","I guess this SQL won't fail!");
GO
SET QUOTED_IDENTIFIER ON
I actually tested this on a real live SQL Server so I wouldn't mess up the syntax again by just writing it in directly to the web site
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
Coming soon: The Second EuroCPian Event
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
