|
None of these questions can be answered without knowing the schema of your database, you should be able to answer them all from the books online, and this smells like homework 
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
1. For security you should always store the passwords in an encrypted form. When the user registers for the first type encrypt the password. When the user logs in on subsequent sessions encrypt the password again and search for the encrypted version of the password.
SELECT COUNT(*) FROM myRegistrationTable WHERE UserName=@userName AND EncryptedPassword=@encryptedPassword The above code will return 0 if the user name and password cannot be found, or 1 otherwise. If it returns any other value the database contains duplicate information.
2. You can apply a unique constraint to the table to ensure that no duplicates are entered. Also the primary key is by default a unique value - so sufficient uniqueness may already be covered. If you do this SQL Server will raise an error when you perform your insert and you can pick this up in your .NET application by catching the SqlException that is generated.
You could also write a stored procedure that checks for the existance of a record first then conditionally inserts the new record if there isn't one already.
3. Use the DELETE command.
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I've seen lots of reference to RDA and Merge using IIS and SqlServer.
What if we don't use SqlServer, we use Oracle?
Is there another way to sync our PPCs?
Canada has 6 zones, none of which are erogenous.
|
|
|
|
|
Hi!
I'm new to oracle and i'm trying to connect to a remote oracle db server. I use the following connection string:
string strConn = "Provider=OraOLEDB;DataSource=<dbname>;User ID=<usrid>;Password=<pwd>";
I get the error: The provider OraOLEDB is not registered on the local machine.
What does this means? The provider is installed on the server. Do I have to install it on the local machine too?
Can someone please help me...
|
|
|
|
|
DuckFace wrote:
Do I have to install it on the local machine too?
Yes, if this is a desktop application. No, if you are talking to a web page.
Michael
CP Blog [^]
|
|
|
|
|
Thx for response.
Now i have installed the oracle provider for oledb on my local machine. I have the following method for connecting to the db:
public void createOLEDBConn()
{
string strConn = "Provider=OraOLEDB.Oracle;Data Source=<dbname>;User Id=dummy;Password=dummy";
OleDbConnection myOleDbConn = new OleDbConnection(strConn);
MessageBox.Show("Just before open!");
myOleDbConn.Open();
MessageBox.Show( myOleDbConn.ServerVersion.ToString(), "Test Conn");
myOleDbConn.Close();
}
The first messagebox is being showned, but when I call the open method, the application just waits and I have to stop it myself. I don't know why. Do you?? The dummy user is one I created on the server
|
|
|
|
|
Hi
I use ADO (MDAC 2.8) in a multithreaded application
I call the Connection's Execute method from more than 1 thread.
Is this safe ? or must I use a critical section to protect this operation ?
|
|
|
|
|
ADO's objects are marked ThreadingModel=Apartment in the registry. COM will marshal any calls back to the thread which created the object. This means that concurrent calls will get queued up until the creating thread is ready to handle them.
If this is a problem, create a separate Connection object on each thread.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thank you for the reply
this behavior will not cause problems..It looks safe and that's what I need..
being queued will not cause a big performance problem at all.
|
|
|
|
|
I have the following query
<br />
SELECT *<br />
FROM (((((SK_SKILLSINAGES AS sia LEFT JOIN SK_SKILLS AS s ON (sia.SkillCode = s.SkillCode) OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode)) INNER JOIN SK_CATEGORY AS c ON sia.CategoryCode = c.CategoryCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON (sia.GroupCode = cg.GroupCode) AND (cg.GroupCode = c.GroupCode)) INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode) INNER JOIN PR_DEVELOPMENTPOINTS AS pd ON (sia.CategoryCode = pd.CategoryCode) AND (sia.GroupCode = pd.GroupCode)<br />
WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null) OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null) AND sia.CategoryCode = c.CategoryCode AND sia.GroupCode = cg.GroupCode AND sia.AgeCode = ae.AgeCode) AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND pd.ProfessionCode = 'ANIMIST'<br />
ORDER BY sia.AgeCode, sia.GroupCode, sia.CategoryCode, sia.SkillCode, sia.SubSkillCode;<br />
I have created an index for each of the fields that are being joined and have created an multiple field index for the sort criteria in SK_SKILLSINAGES however this query still takes a long time to load; around ten seconds. If I remove the ORDER BY sort criteria the query is much much faster. Might I be forgetting something crucial that could help speed up this query. It all seems dependant on the sort.
Thanks!
|
|
|
|
|
I would suggest you run this through the Index Tuning Wizard. Blindly creating indexes isn't really the way to go.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I see that the Index Tuning Wizard is a relic of Microsoft SQL Server database, something I don't have. I am using Microsoft Access and access the jet database through ADO. This could be the reason in itself but with that I don't know. Without having to install an SQL server database package might there be another solution.
I actually managed to get a few indexes in place to get a sorted result so I can remove the ORDER BY stuff, however the query still takes a long time to load. When the statement is calling a simple table to return roughly the same number of records it goes fast, but when I introduce JOIN statements, it gets slow.
|
|
|
|
|
brchris wrote:
I see that the Index Tuning Wizard is a relic of Microsoft SQL Server database
I wouldn't describe it as a "relic"
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
True. I meant it in a good way. 
|
|
|
|
|
Try the following:
SELECT *
FROM SK_SKILLSINAGES AS sia
INNER JOIN AG_AGESINERAS AS ae
ON sia.AgeCode = ae.AgeCode
AND ae.Description = 'Middle Ages'
INNER JOIN SK_CATEGORYGROUPS AS cg
ON sia.GroupCode = cg.GroupCode
INNER JOIN SK_CATEGORY AS c
ON sia.CategoryCode = c.CategoryCode
INNER JOIN AG_ERA AS e
ON ae.EraCode = e.EraCode
AND e.Description = 'Rolemaster'
INNER JOIN PR_DEVELOPMENTPOINTS AS pd
ON sia.CategoryCode = pd.CategoryCode
AND sia.GroupCode = pd.GroupCode
AND pd.ProfessionCode = 'ANIMIST'
LEFT JOIN SK_SKILLS AS s
ON (sia.SkillCode = s.SkillCode)
OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode))
WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null)
OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null)
ORDER BY sia.AgeCode, sia.GroupCode, sia.CategoryCode, sia.SkillCode, sia.SubSkillCode;
I have tried to simplify and reorder the SQL. You have a number of joins that restrict the number of records returned (e.g. your "pd.ProfessionCode = 'ANIMIST'" clause). You might want to try moving the more restrictive tables to the top of the "from" clause.
My guess is that the "or" statements involved with the link to SK_SKILLS are the big performance drain (because many databases won't bother to use the indexes) You can test this by removing that part of the statement). A possible way of getting around the "OR" problem is to use a union-join:
SELECT * FROM tables
WHERE sia.SkillCode = s.SkillCode
UNION ALL
SELECT * FROM tables
WHERE sia.SkillCode = s.ParentSkillCode
AND sia.SubSkillCode = s.SkillCode
ORDER BY <columns>
This approach allows the database engine to use separate indexes for the two different links to the skills table.
Hope this helps.
Andy Harman
|
|
|
|
|
You're right about the OR in the SQL. It seems if I remove it, the query goes through faster. Using the UNION query does solve the problem too. Thanks for the reply!
|
|
|
|
|
I have a slight problem inserting the proper result into an MS-SQL table. The result should be <14> however all I get is PRE. BTW, the SQL command not broken appart like you see it on this limited width posting..........
I thought using single quotes desiganted a 'string'? Help........
string pattern1 = @"(<\d{2,3}>)";
Regex r1 = new Regex(pattern1, RegexOptions.Compiled);
Match PRE = r1.Match(stringOfData);
{
try
{
connection1.Open();
SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES ('PRE')";
SqlDataAdapter1.SelectCommand.ExecuteNonQuery();
connection1.Close();
}
catch(SqlException error)
{
MessageBox.Show(error.Message.ToString());
}
listBox1.Items.Add(PRE.ToString());
}
}
|
|
|
|
|
Ummmm... Are you trying to say that the VALUES('PRE') in your CommandText is supposed to be the result of Match PRE = r1.Match(stringOfData) ???
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Yes, this particular Regex matches numbers between < >. For instance <14>, however only PRE is written to the database........
|
|
|
|
|
You are inserting hardCoded "PRE" value rather than variable PRE
Change your sql to
SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES (" + PRE.Value + ")";
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
Hello All,
I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers)
test_user looks like this:
user_id,time_answered,+some other statistics
1 0
2 0
3 0
test_answer looks like this:
test_id,user_id,answered
0 1 1
0 2 1
0 3 0
1 1 1
1 2 1
1 3 0
3 1 1
3 2 0
3 3 0
Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id.
What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with.
select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id
(The output will be:
user_id,sum
1 3
2 2 )
Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.)
As I told you this is probably an super simple question but I cannot get it right?
hope you guys can help me!
|
|
|
|
|
What do you mean it is destroying all the other data in the table? What is your existing UPDATE query?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
What I mean is that if I do the select with an select into statement it will create a new table for me with the result. I dont have an update statement, because all the ones I tried generates errors 
|
|
|
|
|
UPDATE test_user
SET no_answered = SUM(answered)
FROM test_answer
WHERE user_id = test_answer.user_id I haven't tested this, but it's along these lines.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I'm not particularly sure that this is what you want.
You want to update or insert into the test_user table depending on the records in test_answer, right?
If that is the case, this might be the answer:
INSERT INTO test_user (user_id, time_answered)
(
SELECT user_id, SUM(answered)
FROM test_answer
WHERE answered = 1 AND user_id NOT IN (SELECT user_id FROM test_user)
GROUP BY user_id
);
UPDATE test_user
SET time_answered = SUM(answered)
FROM test_answer
WHERE test_answer.user_id = test_user.user_id;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
