|
iskaza wrote:
1)
Yes.
2) It's better to do this with a database table - you can change the wording of the string values (for instance translate to another language) or extend the enumeration without recoding/recompiling - and it makes the database more self-documenting. No clear cut rule, but I prefer to keep everything thats about the data in the database. It's also a storage space saving device - the integer id takes less space than the equivalent string in the tables where it is used.
|
|
|
|
|
iskaza wrote: 2) About lookup tables; if it's just a matter of avoiding misspelling errors ...
The golden rule for all databases: Keep all instances of data in one place, and one place only. Refer to it if necessesary. The guy who ends up with the maintenance will thank you. 
--
-= Proudly Made on Earth =-
|
|
|
|
|
Hi i am using access in my webapplication.I am facing a lot of problems like connection already open.My question is why MS access is not suitable for web application ?
Thanks and regards
|
|
|
|
|
sishya wrote: My question is why MS access is not suitable for web application ?
Because it is fundamentally a single user file based database, not a multiuser server.
|
|
|
|
|
Do you have the 'Default Open Mode' set to 'shared' in the Database Options?
Steve
|
|
|
|
|
Hi Rob and all,
I have a recruitment site which is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's. and there's a couple of other databases. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask him again to sign in to search jobs or modify his resume. Now, with that said I was wondering can I create a FK say UserID in the resumes table in the (Recruitment) database whose value is the PK of the Users table of the (portal) database. So, is that possible?? I mean referencing a PK from a table in another database?? If not, what is the best approach to integrate multiple databases within that portal and to implement the above functionality.
Thanks
|
|
|
|
|
JobSeeker is in the MyTestDB database and Resumes is in the MyTestDB2 database.
select * from MyTestDB..JobSeeker js inner join MyTestDB2..Resumes re on js.JobSeekerID = re.JobSeekerID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Greetings:
I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though:
Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why:
1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records.
2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically).
What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case?
I'm not seeing anything in the literature that I have that address these questions.
Thanks in advance to anyone that responds,
Mark
|
|
|
|
|
Using stored procedures is the preferred and recommended way access your data. If the tables have been properly constructed and have good indexes then in general it would be more advantageous to run the query in SQL Server and return the results. I say in general because there are other factors; is it a very complex search, size the dataset involved, etc.
only two letters away from being an asset
|
|
|
|
|
Hi Mark:
I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements?
By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though.
Thanks for your input.
Mark
|
|
|
|
|
|
|
If you're using SQL2005Express and you're completely new to SQL and SQL Server then I would recommend this book[^]for your learning pleasure.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row.
If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.
|
|
|
|
|
Thanks for the responses everybody.
Mark
|
|
|
|
|
I have 2 tables : Client(ClientID and other informations) and ProjectClient(ProjectID,ClientID).
I must select clients and all project id's they are working at, the project id's being in one field, separated by comma.Could you help me, pls?
Thank you.
|
|
|
|
|
So you are basically just asking how to join the two tables together?
It would look like this:
SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID
If you want all the info for just one client then you just do this (if for example the client you wanted had a clientID of 5):
SELECT * FROM Client, ProjectClient WHERE Client.ClientID = ProjectClient.ClientID AND Client.ClientID = 5
|
|
|
|
|
I don't know why you would want something like this, but it sounds like you need a function:
CREATE FUNCTION GetProjectIDs (@nClientID int)
RETURNS varchar(1000) AS
BEGIN
DECLARE @nProjectID int
DECLARE @cProjectIDs varchar(50)
SELECT @cProjectIDs = ''
DECLARE ProjectIDs CURSOR FOR
SELECT ProjectID
FROM ProjectClient
WHERE ClientID = @nClientID
ORDER BY ProjectID
OPEN ProjectIDs
FETCH NEXT FROM ProjectIDs INTO @nProjectID
WHILE @@FETCH_STATUS = 0
BEGIN
select @cProjectIDs = @cProjectIDs + case when len(@cProjectIDs) = 0 then '' else ',' end + cast( @nProjectID as varchar)
FETCH NEXT FROM ProjectIDs INTO @nProjectID
END
CLOSE ProjectIDs
DEALLOCATE ProjectIDs
RETURN(@cProjectIDs)
END
Then call it like this:
select *, dbo.GetProjectIDs(client.clientid) as ProjectIDs from client
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Use SQL Server to get your data. Do the formatting in the client side program. Keep both sides doing what they're best at: SQL Server is not really that good at string manipulation.
For this task I would use a DataSet and use the version of DataAdapter.Fill which fills a DataSet. I'd write a query that returned two resultsets:
CREATE PROC uspGetClientsProjects
AS
SELECT ClientID, Field1, Field2
FROM Client
SELECT ClientID, ProjectID
FROM ProjectClient If you Fill a DataSet from that, you get two DataTables in your DataSet. You can then add a DataRelation to the DataSet's Relations property to link Client and ProjectClient together. Then, to get your output, iterate over the Client table, and use the GetChildRows method of the DataRow object to get the matching ProjectClient rows.
|
|
|
|
|
Thanks for your answers.I resolved the problem using 2 cursors.
|
|
|
|
|
How can i write a select SQL statement for the DB that uses the string from CEdit1 ?
lets say database name : "Names_City" class : CNames_City
col 1 : "name"
col 2 : "city"
col 3 : "country"
CString sName , sCity , sCountry;
CEdit1 : "m_name" sName;
CEdit2 : "m_city" sCity;
CEdit3 : "m_country" sCountry;
1) i have onChange for "m_name .When i write some txt in m_name i need to search in "Names_City > name = and if it matches i need the city in m_city and the country in m_country "
2)when i write in m_city and then in m_name i need to search in "name","city" and if m_name == "name" and m_city == "city" i need "country" in m_country.
* Remember i have ~260.000 Records ;
thank you in advance !
*please show me an example !
Bravoone
|
|
|
|
|
I am looking into starting a transactional replication process. The company I work for has aquired a property outside this country. I have to figure out how to make both sites function on their independent databases and have real time access to each other.
I figured transactional would be the best option. what are some of the factors to consider? Our Corporate system functions with alot of auto generated id numbers and the database is dynamic.
If i create a record here and i get id no 5, lets assume someone in the different country tries to do the same thing would he also get id no 5? if the databases have not syncronized since.
Please excuse my ignorance for I am trying to learn how this functions.
Thank you
|
|
|
|
|
|
How can i export data from sql/access/oracle to excel file and vice versa
in vc++
|
|
|
|
|
Hello,
I have a windows application that uses a sql server remote database.
Which is the best method to deploy an application that has to connect to a remote server on an another computer, that is part of the LAN Network.
I have been told that during installation the connection will be setup.
At the moment I am using the sqlclient connection. I have my connection string in the app.config. But when the customer installs that want to be able to browse to the server that they will have to connect to.
Well as I have never deployed, I will never know what is best.
Give you could give a short example, i would be most grateful.
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
