Database Discussion Boards

Member 1116162519-Dec-15 17:13

19-Dec-15 17:13

Hi,

I want to know that while creating suqueries in SQL Server,it is mandatory to have Primary key and Foreign key relationships between the tables???

In short:-I want to know if we create subqueries without p.k and F.k realtionships,then it will work???

What is the main purpose of Subqueries???

Thanks...

Re: Subqueries

Mycroft Holmes20-Dec-15 11:58

Mycroft Holmes

20-Dec-15 11:58

In Short: Yes

The relationship structure is an integral part of the data structure but is not required for sub queries. Sub queries are just another tool for getting your data from the database the way you want it. Like CTEs and the various joins they are all aspects of TSQL.

Never underestimate the power of human stupidity
RAH

Re: Subqueries

Richard Andrew x6420-Dec-15 15:02

Richard Andrew x64

20-Dec-15 15:02

I have often wondered this myself. Since I can perform a join simply by saying "WHERE a.field = b.field", what is the ultimate purpose of setting up a relationship?

The difficult we do right away...
...the impossible takes slightly longer.

Re: Subqueries

Wonde Tadesse20-Dec-15 15:28

Wonde Tadesse

20-Dec-15 15:28

Richard Andrew x64
what is the ultimate purpose of setting up a relationship?

Referential Integrity[^]

Wonde Tadesse

Re: Subqueries

Mycroft Holmes20-Dec-15 19:15

Mycroft Holmes

20-Dec-15 19:15

Wonde nailed it, IMHO referential integrity and constraints are the last line of defense to keep the garbage in. They also help with optimisation of course.

Never underestimate the power of human stupidity
RAH

Synchronising number of disconnected databases to a central DB.

srinandan..14-Dec-15 17:45

srinandan..

14-Dec-15 17:45

There are number of laptops installed a window application connected with local database and all these machines works in remote area where no internet connectiviy is there. we have another web application connected to a central db.the Idea to synchroze database is that every machines export there data at the end of day and save the exported file in removable disk and place all the exported file to a specific location using FTP.

My requirement is to import all the exported files into central database and vice-versa. there are around 15 table out of 100 on which need to keep the synchronization.

Please suggest.

Re: Synchronising number of disconnected databases to a central DB.

Mycroft Holmes14-Dec-15 18:52

Mycroft Holmes

14-Dec-15 18:52

There may be a method to do this using replication but I have never explored that avenue, I have used a brute force method by designing my database to used GUIDs for the primary key and therefore the tables should be mergable.

Another way is to add a machine id to each table/record so the records are unique across databases.

Never underestimate the power of human stupidity
RAH

Re: Synchronising number of disconnected databases to a central DB.

srinandan..14-Dec-15 22:20

srinandan..

14-Dec-15 22:20

Can we achieve this using Microsoft sync framework or some framework?

modified 15-Dec-15 5:07am.

Re: Synchronising number of disconnected databases to a central DB.

Mycroft Holmes15-Dec-15 11:44

Mycroft Holmes

15-Dec-15 11:44

I doubt it, I have not worked with any of them but I have never heard of of them synching database content. You would still need to identify each record uniquely across all the databases.

Never underestimate the power of human stupidity
RAH

Re: Synchronising number of disconnected databases to a central DB.

Eddy Vluggen15-Dec-15 11:53

Eddy Vluggen

15-Dec-15 11:53

There would be some examples on how to synchronize Sql Server Express/CE with Sql Server, so yes. Then again, these samples are built specifically to show some specific functionality.

Means it is no silver bullet; you may want to print the documentation and set up a test-environment.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

SQL Server deadlocks

Member 1115415113-Dec-15 6:42

Member 11154151

13-Dec-15 6:42

I'm dealing with a strange (for me) situation in SQL Server.
I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal?
I wrote a script and run it twice from different connections.
Here is the script:

SQL

begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())	--remove for the second process (victim)
update anyTable set anyColumn = anyColumn												--remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())		--remove for the second process (victim)

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	print 'Waiting. Time = ' + convert(varchar(40), @exec)
	set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

drop table anyTable

commit tran myTran

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 7:17

Kornfeld Eliyahu Peter

13-Dec-15 7:17

Are you executing this exact code twice?

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 19:41

Member 11154151

13-Dec-15 19:41

Not this exact code twice. But similar yes. The only difference is the three lines on top which are commented for the process I would like to be the deadlock victim.
These lines:
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
update anyTable set anyColumn = anyColumn --remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
The only alternative that I can think of is to check if the table is locked by my SPID before try to lock it for the second time.

Thanks for your posts. If can anyone come with any idea I would appreciate it.

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 20:23

Kornfeld Eliyahu Peter

13-Dec-15 20:23

It was much better two post the code you have problem with - including the way you are running it...
The reason I say this, is that I took your code and immediately saw that it can not be run twice as every instance will try to create a table, and that can be a serious problem...
Other thing is the transaction - do not create a transaction if you need not (and from the sample you had here you need not, as you are updating the very same table twice)...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 20:36

Member 11154151

13-Dec-15 20:36

You are right. The second process should run something like this:
begin tran myTran

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

commit tran myTran

But the reason that I lock the same table twice is much more complicated that you think. This is not a simple process. The two locks are coming from different updates. I have just tried to make it simple.

Thanks any way.

Re: SQL Server deadlocks

Member 1115415114-Dec-15 0:00

Member 11154151

14-Dec-15 0:00

The situation is much more complex than I thougth it was.
The deadlock happened between the primary key of the table and the index of the same table. I really do not know why. A workaround that I found is to use SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK) insted of UPDATE EDEFADDERS SET ID = ID.
Here is the scripts that you need to reproduced it. (I believe complete and correct!!!)
Script to create the table:

SQL

CREATE TABLE EDEFADDERS(
	id varchar(32) NOT NULL,
	adderkind int NOT NULL,
	description varchar(30) NOT NULL,
	formula text NOT NULL,
	transkind int NOT NULL,
	addedset varchar(255) NULL,
	removedset varchar(255) NULL,
	adderdecs int NOT NULL,
PRIMARY KEY CLUSTERED (id ASC) 
)

CREATE NONCLUSTERED INDEX EDEFADDERS_I1 ON EDEFADDERS (adderkind ASC)

INSERT INTO EDEFADDERS VALUES ('088162994983574692C347CE326582BD', 0, 'Πληρ. Μετρητοίς', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('0DCE2805DF7E4B4C875649128C16A14C', 0, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('1072EB880FABF8459B99EC38058B6CA1', 2, 'Εισπράξεις Γραμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('142F006F40F0DA4C80ACEA477E2B5662', 0, 'Εκπτώσεις Χονδρ.', '"Αξία Εκπτωσης" + "Πιστωτικό Εκπτωσης" * "Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('1D4E3250AFD52343A1680A4387BA1490', 0, 'Εισπρ. Μετρητοίς', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('22A60A4EC35EC14E8AD82B97A5906572', 2, 'Εισπράξεις Επιτ.', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('24B3B31AD780124B888C5D935966F679', 3, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 2, 49, , 0)
INSERT INTO EDEFADDERS VALUES ('273FED73284E5E4689BAA7F913173086', 0, 'Εκπτ. Ειδών Χ.', '"Αξία Εκπτωσης Είδους"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('3034E6BD63AF114A8DEB61B442C8D873', 0, 'Πωλήσεις Π.Υ. Λ.', '"Καθαρή Αξία"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('349B9E68AE2D104689B6E2E67C618A8E', 0, 'Εκπτώσεις Π.Υ.Χ.', '"Αξία Εκπτωσης"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('3708771B165CD74E81323BCBDE14CEC7', 2, 'Εισπράξεις Μετρ.', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('38570F0087D3D94D838C4219E713F0FA', 0, 'Εισπρ. Πιστ.Καρτ', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('3AC77B713D3FB546947FE018C7B2BF7E', 0, 'Πληρ. Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('4259AB5FE2C25C4DA9C975E24F42BA4F', 0, 'Πωλήσεις Λιαν.', '"Καθαρή Αξία"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('4AF5EDBA1B0BED4CB4085F3B8D567AA1', 0, 'ΠΩΛ.ΑΝΑ ΚΑΤΗΓΟΡΙ', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('4BD015E24E739C4292009DB18DA2AED3', 1, 'Σύν.Εκπτ.Πελατών', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('5390C0D7C85DE44797C2AF36B46FD441', 0, 'Ποσοτ. Εκπτ. Χ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('5986CE3CE649D14BA0E7DC9138DC24C9', 0, 'Πωλήσεις Χονδρ.', '"Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('5A2A9016A3C57A4CB6FD3FD9D3B5E40E', 0, 'Πιστωτικά Εκπτ.', '"Καθαρή Αξία"', 5, 74, , 0)
INSERT INTO EDEFADDERS VALUES ('5AC130A23482C345B581091BA5504859', 0, 'Αθρ. Επιχ. 31', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('5B61F8A2D361014484234E50AEC99045', 0, 'Λοιπές Εξαγωγές', '"Καθαρή Αξία"', 5, 11, , 0)
INSERT INTO EDEFADDERS VALUES ('5D59097BF887F240801FFAF200B427A6', 0, 'Αγορές Χονδρικής', '"Καθαρή Αξία"', 4, 40,42, 43,44,49, 0)
INSERT INTO EDEFADDERS VALUES ('604F30B7A1CB434D9F7DADD59A9B9A74', 0, 'Εκπτώσεις Πελ. Χ', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('641899F5AFF050479F9454573841EC8E', 3, 'Πληρωμές Αντικ.', '"Συνολική Αξία"', 2, 45, , 0)
INSERT INTO EDEFADDERS VALUES ('6523E3A73BAD414EA534954D1C21106E', 0, 'Πληρ. Γραμματ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('666D15457C526B4FBF96539E20BBE802', 2, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('706E587CF5A1E648939391046C5FA117', 3, 'Πληρωμές Επιταγ.', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('714F6D3207A8BD4BACDAD6FCB86F774B', 1, 'Σύν.Ειδικών Εκπτ', '"Ειδική Εκπτωση"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('73C057D1EFC0C440AB42AB2E43E59ED7', 0, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('77D6C0A98A289F4F92FCBA462DC78449', 0, 'Ειδικές Εκπτ. Χ.', '"Ειδική Εκπτωση"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('7CC5CEDDAB8D8746AD7B1D7E315B75DE', 0, 'ΠΩΛΗΣΕΙΣ', '"Καθαρή Αξία"', 5, 70,72,76,78,82,84, 73,74,79,83, 0)
INSERT INTO EDEFADDERS VALUES ('82A8E1B3BF46D04EB1DC0CB178FE15BB', 2, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('8F8A505724081E4AB510EEE90A4F58C1', 1, 'Σύν.Εκπτ. Ειδους', '"Αξία Εκπτωσης Είδους"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('90499A1A8954D741A1C076735E4C4B6E', 3, 'Πληρωμές Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('97CCD23F1432954EBABBE391B0945CE9', 0, 'Εκπτώσεις Λιαν.', '"Αξία Εκπτωσης"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('9E0D358AECAE0A47BD0D27CE8A8FFCF3', 0, 'Εκπτώσεις Π.Υ.Λ.', '"Αξία Εκπτωσης"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('A084AC5553C52544B4B053DF77817265', 2, 'Εισπρ. Πιστ. Κ.', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('A0CDBA5347B1CD48B2119BE5C007F3C5', 0, 'Εισπρ. Επιταγών', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('A329C05004756C4EBAACA1B5F269DE1F', 0, 'Αθρ. Επιχ. 32', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' ))*( inset ( "Κατηγορία Είδους" , '1 - 2' )))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('B175031C0CA3364193E39C41FC29A262', 2, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 1, 29, , 0)
INSERT INTO EDEFADDERS VALUES ('B54944B81669EF498F3E2D55B5F8ECD5', 1, 'Σύν.Ποσοτ. Εκπτ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('B6527AFBC4B345469C4B81D1B768504F', 0, 'Πληρ. Επιταγών', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('BE5E6E822BF0D946918436C3F9F28737', 0, 'Αγορές Παρ.Υπηρ.', '"Καθαρή Αξία"', 4, 46, 47, 0)
INSERT INTO EDEFADDERS VALUES ('C5D6BFD587A11948949446161E501015', 3, 'Πληρωμές Μετρητ.', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('CABA8202B260F046945AD56CCAD51A56', 0, 'Εκπτ. Τρ.Πληρ. Χ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('CDEAFE4EF039DD4A9A7721A35BC949AA', 1, 'Σύν.Εκπτ.Τρ.Πληρ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('CE97BE8509223145B274049AE6EABBF4', 0, 'Πωλήσεις Π.Υ. Χ.', '"Καθαρή Αξία"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('D051DCEB86DFAB4BA3B564A1B4570CED', 3, 'Πληρωμές Γραμ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('EC08552E3FDBA14189D39418D5571545', 0, 'Εισπρ. Γραμμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('FEB6C6CE84571F4A98558A515A822C47', 0, 'Πληρ. Αντικατ.', '"Συνολική Αξία"', 2, 45, , 0)

Script to run it twice (simultaneously) that will produce the deadlock:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Script to workaround the problem:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Sorry to anyone I mislead.

Re: SQL Server deadlocks

Richard Deeming14-Dec-15 2:50

Richard Deeming

14-Dec-15 2:50

Member 11154151 wrote:
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

Rather than sitting in a tight loop waiting for a particular time, it would be better to use WAITFOR[^]. That will let SQL free up the processor to do other things, rather than constantly querying the current time and shouting "are we nearly there yet?" until the target time arrives.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server deadlocks

Member 1115415116-Dec-15 2:28

Member 11154151

16-Dec-15 2:28

The answer to my question was given to me by someone from Microsoft.
The reason why this deadlock happens is that the optimiser is choosing the second index for this SQL:
Update anyTable set anyColumn = anyColumn mak
So, it puts a lock in order to do the update and then SQL server is putting an exclusive lock to the primary key for the update and release the first lock of the second index.
At this time the second process is putting a lock for the second index and waits for the 1st process to finish in order to get an exclusive lock for the primary key.
The 1st process now is trying to reupdate the same table and to put a lock for the second index but this is already locked by the 2nd process (who waits the 1st).
So, the deadlock is inevitable.

need to consult about project discussion

divinity technologies10-Dec-15 22:14

divinity technologies

10-Dec-15 22:14

JavaScript

hello
                  i want some clarification on some database related issue can you please suggest us.
 
please find attachment
1.this excel sheet we have to upload in database  
2.how can be database table structure

in these excel sheet variable columns and variable rows will be there .

Re: need to consult about project discussion

Mycroft Holmes11-Dec-15 1:26

Mycroft Holmes

11-Dec-15 1:26

Hah hah your screwed, you will now spend the next 8 months working around edge cases on excel.

You are looking at one of the classic development nightmares. Using Excel as a datasource for a database application.

If the format is stable you can map the cells to fields in your table, a REALLY tedious job that will need to be repeated every time some fool of a use inserts a row or column.

Never underestimate the power of human stupidity
RAH

Creating Access database with VB6

Member 121949778-Dec-15 18:05

Member 12194977

8-Dec-15 18:05

I am creating a database in Access with 3 tables. Each table has different number of columns or fields. I get an error when creating more than 8 fields. If I comment out the other fields, then everything works great. Can anyone help? Code is as follows:

Dim FileName As String, strConn As String
Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table
Dim tblCollection As Collection
Dim Script As Object

'check and/or build DB
Set Script = CreateObject("Scripting.filesystemobject")
If Script.FolderExists("C:\Business Pro") Then
'folder is in place...move on
If Dir("C:\Business Pro\BusinessPro.mdb") = "" Then
'dir not in place...build
lblPB.Caption = "No database found. Creating database..."
FileName = "C:\Business Pro\BusinessPro.mdb"
'create instance of ADOX-object
Set adoCat = New ADOX.Catalog
'creat instance of Collection-object
Set tblCollection = New Collection
Set adoTable = New ADOX.Table
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FileName & ";"
'create the db
adoCat.Create (strConn)
'name table
tblCollection.Add "Customer_Contacts"
With adoTable
.Name = "Customer_Contacts" 'name the table
.Columns.Append "CustID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("CustID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key
'add rest of fields
.Columns.Append "FirstName", adWChar
.Columns.Append "LastName", adWChar
.Columns.Append "Address", adWChar
.Columns.Append "City", adWChar
.Columns.Append "State", adWChar
.Columns.Append "Zip", adLongVarWChar, 5
.Columns.Append "PhoneNumber", adLongVarWChar, 10
End With
'add table to mdb
adoCat.Tables.Append adoTable

'create 2nd table
Set adoTable = New ADOX.Table
'name table
tblCollection.Add "Labor_Rates"
With adoTable
.Name = "Labor_Rates" 'name the table
.Columns.Append "ID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("ID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "ID" 'append primary key
'add rest of fields
.Columns.Append "dateFiled", adDate
.Columns.Append "NumberCode", adWChar
.Columns.Append "Description", adWChar
.Columns.Append "LaborRate", adWChar
.Columns.Append "Frequency", adWChar

End With

'add table to mdb
adoCat.Tables.Append adoTable

'create 3rd table
Set adoTable = New ADOX.Table
'name table
tblCollection.Add "New_Contract"
With adoTable
.Name = "New_Contract" 'name the table
.Columns.Append "CustID", adInteger 'create a field in primary key
.ParentCatalog = adoCat 'set Parent catalog
.Columns("CustID").Properties("AutoIncrement").Value = True
.Keys.Append "PrimaryKey", adKeyPrimary, "CustID" 'append primary key
'add rest of fields
.Columns.Append "dateFiled", adDate
.Columns.Append "ProjectCode", adWChar
.Columns.Append "Name", adWChar
.Columns.Append "Address", adWChar
.Columns.Append "City", adWChar
.Columns.Append "State", adWChar
.Columns.Append "Description", adWChar
.Columns.Append "LaborRate", adWChar
'error is here. when the last 2 columns are commented out, the code works fine. When uncommented, I get an error of file being too large...
' .Columns.Append "DaysOnJob", adLongVarWChar
' .Columns.Append "Remarks", adWChar

End With
'add table to mdb
adoCat.Tables.Append adoTable

'release objects from memory
Set adoTable = Nothing
Set tblCollection = Nothing
Set adoCat = Nothing

Else:
lblPB.Caption = "Database exists. Loading software..."
tmrLoad.Enabled = True
tmrLoad.Interval = 1000

End If
Else
'make directory
MkDir "C:\Business Pro"
'file is created. Create db...run through process again
chkForDB
End If
End Sub

Re: Creating Access database with VB6

Mycroft Holmes8-Dec-15 20:46

Mycroft Holmes

8-Dec-15 20:46

I'm afraid you are really pushing your luck trying to get support for a language that has not been supported for more than a decade. You should download and use the free VB.net express.

Never underestimate the power of human stupidity
RAH

Re: Creating Access database with VB6

Member 121949778-Dec-15 21:03

Member 12194977

8-Dec-15 21:03

I was just thinking of learning the .Net language and converting over and rewrite my whole program. I guess now is as good a time as any to do it. Thanks!

Re: Creating Access database with VB6

Mycroft Holmes9-Dec-15 1:23

Mycroft Holmes

9-Dec-15 1:23

I would recommend converting to c#, the learning curve will be marginally steeper but the learning resources are 2-3 time the amount of VB.net code.

Never underestimate the power of human stupidity
RAH

Re: Creating Access database with VB6

CHill609-Dec-15 5:29

CHill60

9-Dec-15 5:29

Some points that might help ...
You're not defining the maximum size of the text columns so they are all defaulting to 255.
Did you really want a Memo field for DaysOnJob?
For that matter, shouldn't LaborRate be adDouble not adWChar? Or an integer - linking to Labor_Rates.ID
Consider using the DAO library rather than the ADO extension library (which is not stable).

On the subject of using VB6 - now is definitely the time to stop using it! But don't assume that VB.NET is the natural progression into .NET. C# is really worth considering. If not then MacDonald publish a book for programmers transitioning from VB6 (The one I had was "The Book of Visual Basic 2005", but that's well out of date now).

Last Visit: 31-Dec-99 18:00 Last Update: 17-May-24 8:21

Refresh

ᐊ Prev 1...117 118 119 120121122 123 124 125 126 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.