|
If iam making a table of location in that table i have columns city,county,condition. And i had some values added in these columns..
after addeding the values in these three columns now i want to add the new column temperature by
alter table location add(tempareture number(2));
but i want to add the values from start in this new column how that is possible is that possible for 1st column value as follow..
insert into location(tempareture) values(34) where city='Athens';
like that in other columns i can put the value tempareture????
please tell me solution..
Raj Khatri
|
|
|
|
|
seems you need to update not to insert
update location<br />
set tempareture=34<br />
where city='Athens';
|
|
|
|
|
Hi all the experts in Code Project,
I've a problem with returning the value returned by ExecuteScalar function under OleDbCommand. Here is a code sample.
...
cmd.CommandText = searchSqlstr;
long result = Convert.ToInt64(cmd.ExecuteScalar());
connection.Close();
return result;
Is there a way to rewrite this code so that I DO NOT need the 'result' variable?
since we always have to close the connection, if I write:
return Convert.ToInt64(cmd.ExecuteScalar());
it would be a bad idea right?
So is there a trick to do this?
Thanks a Lot
|
|
|
|
|
cmd.CommandText = searchSqlstr;
try
{
return Convert.ToInt64(cmd.ExecuteScalar());
}
finally
{
connection.Close();
}
Better yet would be a using(){} statement for the connection object, so that it will automatically be disposed, but since you didn't list all the code from instantiation to disposal, I can't reacreate it accurately.
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
try
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = searchSqlstr;
return Convert.ToInt64(cmd.ExecuteScalar());
}
finally
{
connection.Close();
}
}
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Ah! Great tip! totally forgot about the using statements 
Thanks a Lot!
|
|
|
|
|
In the example given, the connection.Close(); is redundant, since the using block will take care of that. Which makes the try-finally is also redundant, (unless you want to implement a catch). So a simple version is:
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = searchSqlstr;
return Convert.ToInt64(cmd.ExecuteScalar());
}
my blog
|
|
|
|
|
Hi, I tried the c# forum, but this might be better. How do I programatically get table names from a .mdb file? There doesn't seem to be a way. I'd like to be able to open any .mdb file, read in the list of tables, etc.
Thanks for any help or tips.
Brian.
|
|
|
|
|
There are a couple of ways to do it....
One way is to use ADODB.Connection.OpenSchema method - returns a recordset with table names and characteristics if specifying the adSchemaTables flag
Other way is to use ADOX - Create a Catalog object, give it an ActiveConnection and it exposes a Tables collection - useful for seeing what's there and also modifying schema info...
As for doing it in .NET - no idea! SUspect that ADO.NET will support the above, but worth checking lol
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
The problem description goes like this...
person A is accessing a record to update, i want this record to be locked till the person A finishes his updation.
i want this to be implemented at BLL(Not IN database)
Regards,
|
|
|
|
|
Use distributed transactions.
However, you'll have to create locks on the records somehow on the database. By default, SQL Server only creates a shared lock on a row/page when it's involved in a SELECT statement.
If it's a simple, one-query update statement, SQL Server automatically locks and isolates the record until the query is committed.
*Edit: I read below where you responded and indicated that you use Oracle. Not knowing much about Oracle or PL/SQL, I'm going to quit bothering you with my platform-specific opinions.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
SELECT * FROM user WHERE clue IS NOT NULL<br />
GO
(0 row(s) affected)
I hope clue is indexed - it could take quite a while with over 6 billion rows.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
I'm using VB.Net. My users will fill out an Excel spreadsheet I made for them, then send it back. Upon sending it back, is there a way to automate it into a tSQL database?
Thank you for any help!
|
|
|
|
|
|
Ok, DTS looks good once it's there. What's the best way to send it to the server from the web form? Code would help, I'm new at this stuff.
Thanks again!
|
|
|
|
|
Dear friends,
This question is related to SQL queries.
I have two tables in SQL Server Database. Both of these tables have a column "eMail" which is of type varchar(). The emails may be repeated in a table, and an email present in one table can be present in the other table too.
Now i want to create another table with a column "eMail" in it. I want to copy all the eMail addresses of the above two tables in third table. But in such a manner that all emails entered in third table are distinct.
How can i do this with SQL queries ?
Imtiaz
|
|
|
|
|
insert table3(email)
select distinct email from table1
union
select distinct email from table2
...should do it. The key is union, not union all, which would not return distinct values.
my blog
|
|
|
|
|
Hello,
I am having some trouble with the SQLPlus tool over the command line.
Here is what I do:
on the command line I give the command:
sqlplus /nolog @c:\mycommands.sql
The mycommands.sql file contains:
CONNECT sys/syspass@mydatabase as sysdba
spool c:\output.txt
Select * from some_table
spool off
exit
However, the command line only shows the message connected and does not exit. The spool file also contains no information. I want it to execute the command and quit SQLPlus.
I hope someone can help me.
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi everyone,
I am having a weird Oracle problem. I used the command line tool dbca (database configuration Assistant) to create an Oracle database like so:
dbca dbca -silent -createDatabase -templateName Transaction_Processing.dbc
-gdbname TESTDB -sid TESTDB
The database was successfully created. It said that the initial sys password was change_on_install.
The problem is that I could log onto as sys using any password and even when I did [i]ALTER USER sys IDENTIFIED by syspass[/i], it did not change the password. It says that the user has been altered but I can still logon as sys with any password.
Am I missing something? Please help!
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
I take it that you installed the Oracle Server on the same PC that you program on? If you connect from another computer (you'll have to install the Oracle Client), it should prompt you for a password.
Remove the "NTS" from the SQLNET.AUTHENTICATION_SERVICES list in your sqlnet.ora file. If that's the only entry, you can put a pound sign (#) in front of the line. That will disable the integrated NT authentication, and require you to use a password.
Don't forget to also change your SYSTEM user's password; the default password for SYSTEM is "manager".
Incidentally, it'd make a world of difference for speed if you kept the Oracle server on a different computer then the one you develop with. You could even push Oracle onto an older, slower computer- as long as you meet the minimum system requirements for the Oracle Server, you'll be OK.
-Thomas
|
|
|
|
|
I think I'm either getting over my head, have complicated the issue beyond the scope of SQL, or maybe I have some gaps of knowledge missing for SQL queries. Irregardless, I will try to present the idea, my attempted SQL query, and the issue.
I have 7 tables I want to join (seriously).
- Table 1: ERAS - EraCode, Description
- Table 2: AGES - EraCode, AgeCode, Description
- Table 3: GROUPS - GroupCode, Description
- Table 4: CATEGORIES - GroupCode, CategoryCode, Description
- Table 5: SKILLS - GroupCode, CategoryCode, SkillCode, Description
- Table 6: SUBSKILLS - GroupCode, CategoryCode, SkillCode, SubSkillCode, Description
- Table 7: SKILLSINAGES - EraCode, AgeCode, GroupCode, CategoryCode, SkillCode, SubSkillCode
Assume these table have a one to many relationship on the fields that are common.
I created a successful query in Access that manages to get Table 3, 4, 5, and 6 together
<br />
SELECT s.Synopsis, ss.Synopsis, ss.Class, ss.Progression, s.Class, s.Progression, c.Progression, c.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode, cg.Description, c.Description, s.Description, ss.Description, ss.Table FROM ((CATEGORYGROUPS AS cg LEFT JOIN CATEGORY AS c ON cg.GroupCode = c.GroupCode) LEFT JOIN SKILLS AS s ON (c.CategoryCode = s.CategoryCode) AND (c.GroupCode = s.GroupCode)) LEFT JOIN SUBSKILLS AS ss ON (s.GroupCode = ss.GroupCode) AND (s.CategoryCode = ss.CategoryCode) AND (s.SkillCode = ss.SkillCode) ORDER BY cg.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode;";<br />
But now I need to add in more restrictions which include Table 1 and 2, therefore I created table 7 as the "relational table" if that is the correct term but I am having difficulty in applying it to my previous query. I though I could do this
<br />
SELECT s.Synopsis, ss.Synopsis, ss.Class, ss.Progression, s.Class, s.Progression, c.Progression, c.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode, cg.Description, c.Description, s.Description, ss.Description, ss.Table FROM (((CATEGORYGROUPS AS cg LEFT JOIN CATEGORY AS c ON cg.GroupCode = c.GroupCode) LEFT JOIN SKILLS AS s ON (c.CategoryCode = s.CategoryCode) AND (c.GroupCode = s.GroupCode)) LEFT JOIN SUBSKILLS AS ss ON (s.GroupCode = ss.GroupCode) AND (s.CategoryCode = ss.CategoryCode) AND (s.SkillCode = ss.SkillCode)) LEFT JOIN SKILLSINAGES sia ON (cg.GroupCode = sia.GroupCode) AND (c.CategoryCode = sia.CategoryCode) AND (s.SkillCode = sia.SkillCode) AND (ss.SubSkillCode = sia.SubSkillCode) WHERE sia.EraCode LIKE 'ERANAME' AND sia.AgeCode LIKE 'AGENAME' ORDER BY cg.GroupCode, c.CategoryCode, s.SkillCode, ss.SubSkillCode;<br />
But I get an error in access that says the Join operation is not supported? Does anyone know what that means? (a completely different question) Does this design make sense?
Thanks!
|
|
|
|
|
Your database design reminds me of old main frame DB designs. They did not have random access to the data (reel to reel tapes) so they duplicated the data across a lot of tables.
I think you have to make better use of Primary Keys and stop the data duplication. First define your descrete entities. Find the attributes that unique to those entities - ignore the attributes that connect entities.
It is hard to give you a sample structure without knowing what the data describes but, things do not look correct.
As an Example: SKILLSINAGES contains an EraCode and an AgeCode. AGES also contains an EraCode. How or why would you keep them in sync? Maybe you need both because they can be different.
I think a better DB design would go a long way in eliminating your query problems.
|
|
|
|
|
the data simply describes skills a person may have where these skills are categorized into Groups, Categories, Skills and SubSkills. Also these skills could be different across time spans, so I have Era and Ages which are categories within an Era. So at the top we have an Era, within that an Age, within that Groups, and so on to Categories, Skills and Subskills.
I think I know what you mean however let me make sure I understand what you are proposing.
I could perhaps make ERAS just a table of Eras, same with AGES, GROUPS, CATEGORIES, SKILLS, and SUBSKILLS. Then define a table that combines the keys for those tables into one table called RELATIONALSKILLS or something. This table would be large, that is it's not improbable for there to be 1000's of records in it.
I think that makes sense, so then, let me ask another question. Say this implementation is correct. Now I make my Query to RELATIONALSKILLS, and I want to gain access to the ERA, AGES, etc tables. Would I still have to perform joins or is there a way to setup the database where I gain access to those fields automagically?
Thanks for the feedback. It may humiliating, but it shows I have a lot more to learn. I would rather make a strong relational table than have a application suffer through a clunky and weak designed Table. 
|
|
|
|
|
You have identified the need for a time-span. We can attempt to solve this task first. Ignore the rest of the problem right now.
Questions:
1) How many ERAS are there?
2) How many AGES per ERAS are there?
3) Do AGES overlap ERAS in time?
3) Do AGES descriptions repeat between ERAS?
4) Is this basically a fixed list once it is in production?
You may be over doing the relationship. You may only need one table to represent both ideas if the numbers are small enough and there is no overlap.
CalendarSpan
============
CalendarSpanId INTEGER --AutoNumber(Identity) primary Key
TimeOrder INTEGER --The order of when this Era/Age occurs (0=Earliest)
Era TEXT(30) --The Era
Age TEXT(30) --The Age within the Era
|
|
|
|
|
Well.. to answer
1) unknown, but currently this has only one record. A user has the ability to add more.
2) unknown, but currently there are only 11 ages per the one era.
3) No. Ages are defined to be a unique time span
4) It's possible to have an age that could exist in multiple eras but there wouldn't be a connection there other than the name of the age being the same.
5) No. I'm working on a interface to add, edit and delete.
Also, I defined a field to hold a "Code" version of the Description, that is I stripped all characters but alpha/numeric characters (such as periods, dashes, colons, etc). I did this for searching purposes. In your own personal opinion, is this worth it? or no.
|
|
|
|
|
I am assuming the ERACODE and AGECODE are Primary Keys that are never displayed to the user. If this is the case then your time-spans are defined correctly. You are not storing any sequence information but, that might not be important for you application.
Currently, you have 11 AGES records. If the count is not going to be enourmous (100+), I would just load them all into memory and use RegEx to do any fancy searches. I always try to never store calculated data - it can make your edit code more difficult. You can also create a query in Access that handles the calculation on the fly and query against that.
Currently, I would only remove ERACODE from the SKILLSINAGES since AGES already contains a link to it. It is unnecessary duplication. Of course, you will have to back it up with a unique constraint on the EARCODE + AGES.DESCRIPTION Columns. You don't want your users defining two AGES within the same ERAS that have the same DESCRIPTION.
Your next chain looks like this:
GROUPS
|----CATEGORIES
|----SKILLS
|----SUBSKILLS
Knowing the SUBSKILLS you should be able to get the SKILLS, CATEGORIES and GROUPS without a problem. There is no need to duplicate this information within each table.
Your SKILLSINAGES table becomes.
SKILLSINAGES
=================
SkillInAgesId - Primary Key
AgeCode
SubSkillCode
Some people would use AgeCode + SubSkillCode as the primary key. I have always liked PK's that have no meaning other than to uniquely identify the row. There are good arguements on both sides of the issue.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
