|
First, create a Ranks table with the possible ranks (1 to 9), just one column called Rank.
Now try this query:
SELECT Ranks.Rank, CASE WHEN [count] IS NULL THEN 0 ELSE [count] END AS [count]
FROM Ranks
RIGHT OUTER JOIN (
SELECT Rank, COUNT(*) AS [count]
FROM T
WHERE ID = @ID
GROUP BY Rank) AS sub ON sub.Rank = Rank.Rank
* DISCLAIMER: I get my left and right outer joins mixed up. So, you may need to change the RIGHT OUTER JOIN to a LEFT OUTER JOIN.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Using the above Ranks table, couldn't you just do a left join?
SELECT Ranks.Rank, Count(T.Rank)
FROM Ranks
LEFT JOIN T
ON (Ranks.Rank = T.Rank AND T.ID = 3)
GROUP BY Ranks.Rank
The left join forces all of Ranks to be displayed and Count() can than properly process the null entries as Zero.
|
|
|
|
|
|
Hi,
I'm new to ADO.net,but have couple of years of experience in classic ADO !
Could you please explain me, what is the best method (way) to insert a record in a multiuser environement.
Stored Procedure option seems to be good, but since i'm using MySql,i can't.
Advance Thanks,
aB
|
|
|
|
|
If you're stuck with MySQL, you need to just pass SQL. If you don't have transactions, you're in trouble.
MySQL sucks.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi Everyone,
1.I am working on DataGrid. Actually I have relationship between two table(Customers and orders)
displaying on DataGrid. I want to update a record of orders table when I click or select on datagrid,
all the records of order table goes to Textbox control then I or user can update the table easily.
2. How to store the primary key of order table. ( I am using ArrayList )
Thanks
|
|
|
|
|
i want to restore database using restore command but i do not now what parameter should use?i search the google and.... but did not get advantage?
|
|
|
|
|
Hi all,
I'm tryng to create trigger at run time.
I'm using Oracle database.
This is my code:
--------------------------------------------------------------------------------
Dim CmdTmp As OleDbCommand = New OleDbCommand
With CmdTmp
.Connection = oConn
'change_status Trigger
.CommandText = "CREATE TRIGGER change_status AFTER INSERT ON invoices " & _
"REFERENCING NEW AS newRow FOR EACH ROW " & _
"BEGIN update orders set orders.order_status='Close' where orders.orderID = :newRow.orderID; " & _
"END change_status;"
.CommandType = CommandType.Text
Try
.ExecuteNonQuery()
Catch ex As Exception
HasErr = True
Err += ex.Message + Chr(13)
End Try
...
--------------------------------------------------------------------------------
I dont get exceptions but the triggger wasn't created...
does anyone know what is wrong with my code..?
Thank you.
RoyRose
|
|
|
|
|
Hey anyone, everyone
We had our principle server crash this weekend (server2003) and the database backup file was inprogress of backup when it crashed, so the backup file shows "incomplete". I have the mdf and ldf files, but since they were active when it crashed they are not accessable right now.
Are there any tools available to recover this db from the active/ not detached .mdf file?
Currently when I try to reattach the files I get a Error 823, it seems my only option right now is to run dbcc checkdb, but since i've never done it and i'm also working on an entirely new build of sql server i'm not working against a db that i can query or even send a contorl/command to.
ANY help would be awesome!
thanks
|
|
|
|
|
Hi All
Got problems intrying to update foxpro 2.6 table when
setting Null value to a DateTime field by dataset.
There are two fields in tblTest:
ID int 4,
cDT Date/Time
part of my code is as follows:
DataRow row=new ds_tblTest.Tables[0].NewRow();
row[0]=2;
row[1]=DBNull.Value;
ds_tblTest.Tables[0].Rows.Add(row);
da_tblTest.Update(ds_tblTest);
ds_tblTest.AcceptChanges();
...
An error message will pop up when executing "da_tblTest.Update(ds_tblTest)" like this:
Field cDT does not accept null values..
I tried to update tblTest with OleDbCommand and succeed, but
have trouble when updating with dataSet...Very frustrated indeed.
Any help will be highly appreciated...
Many thanks!
Mei
|
|
|
|
|
Look at the UpdateCommand that is being (automatically if you wrapped an OledbCommandBuilder around the ds) built for the datatable. Modify it to look like the oledbCommand (text and parameters) that worked for you...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
:(Many thanks for your kind reply.
The oleDbInsertCommand1 looks like this:
this.oleDbInsertCommand1.CommandText = "INSERT INTO test(ID, cDT) VALUES (?,?)";
this.oleDbInsertCommand1.Connection = this.conn_Ole;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.Integer, 2, "ID"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("cDT", System.Data.OleDb.OleDbType.DBDate, 0, "cDT"));
....
I think it should be ok..
The oleDbCommand I used to update foxpro table works fine:
string s_Ins="UPDATE tblTest SET cDT=CTOD('') WHERE ID=1";
OleDbCommand cmd2=new OleDbCommand(s_Ins,conn2);
int i2=cmd2.ExecuteNonQuery();
Very strange indeed. Is that possible to set Null to DataTime field in foxpro table?
Mei
|
|
|
|
|
Suppose you're making a site with a registration module, I'm doing this with DeKlarIT
step 1 - customer enters e-mail addy
step 2 - customer is e-mail verfied
step 3 - customer enters financial details
step 4 - customer's financial details are verified
step 5 - customer enters in advanced details
step 6 - adavanced details verified
...
you've got to keep track of what stage is the customer in, what do you do - create a status integer field which stores values 0-n or create lots of boolean fields.. this question applies to db and your classes
|
|
|
|
|
I don't see what this has to do with a database other than the data being entered is going in and out of a database. Surely the tracking infomation is a part of the web application. I suppose you could put it in the database if you want to give the user the option of returning to where they left off if this is a particularly long operation.
It depends on the functionality that you want which you should used. If you use an integer then it assumes that everything before it is completed and correct. If you use a set of booleans then you could give the the user the possibility of jumping around the pages in the order they choose, but the implementation will be more complex.
Also, this smells like a homework question. You're going to have to make your own decision on this one.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
it already occured to me that lots of booleans would mean the user could hop through the pages in any order but let me rephrase the question, although both methods are acceptable, which method would be more elegant to code
Take the first example -
you have a field in the db called status which is a integer and you have a enum in your code, take the other situations, after running a code generation tool on the db you have flags like User.Registration.IsEmailActivated, User.Registration.IsDataActivated, User.Registration.IsRegistered
|
|
|
|
|
|
Hi, can anyone bring more insight as to make db access thread-safe programmatically? i.e. if there are multithreaded programme or even several programmes trying to access a same db, what do you guys usually do to prevent them conflicting each other?
|
|
|
|
|
Let the database sort it out. So long as you follow the following rules you shouldn't run into many problems:
* Ensure that things are transacted properly.
* Access the tables in the same order for each function. e.g. If some function access table A, B, and D and another function access tables B, C and D ensure that they are accessed in the order A, B, C, and D because if the first function accesses tables in the order D, B and A and the second function access the tables in the order B, C and D then you could run into deadlock. The first function locks table D, meanwhile the second function locks table B, the first function then wants to access table B but can't so it waits for the lock to be released. The second function then gets table C and then requests table D but it cannot because it is locked. The second function then waits for the lock on table D to be released. Now both functions cannot complete until the other has - they are deadlocked. The database will detect this and rollback one of the queries (the deadlock victim). Your application can then attempt again to run function that was the deadlock victim.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
thx! I was using mutex and 'd been doing locking in the code. Looks like it is unneccessary.
|
|
|
|
|
Use transactional error handling with sql server. Do this especially if you are upating tables that have a related tables.
Declare @intErrorCode int
select @intErrorCode = @@Error
begin transaction
If @intErrorCode = 0
begin
-- insert SQL Statement
set @intErrorCode = @@Error
end
If @intErrorCode = 0
begin
-- insert another SQL Statement
set @intErrorCode = @@Error
end
IF @Error = 0
commit transaction
else
rollback transaction
return @Error
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Can NHibernate call stored procedure? I read NHibernate reference doc, doesn't look like it's there, am I right about this?
Norman Fung (c)
|
|
|
|
|
hi,
I am using
VisualStudio2003,
Access2003
Vb.Net and ADO.Net for db access.
I get this message when i try to use ExecuteReader method :
IErrorInfo: GetDescription failed with E_FAIL( some hexvalue )
Now i tried MSDN it says GetDescription supposed to return description of error that is generated by database.
This is what I am doing:
I have a table called section
g_AdoCon = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + db)
//checked the db variable, it is valid path and everything and this
// conenction works in another class fine
If g_AdoCon.State = ConnectionState.Closed Then
g_AdoCon.Open()
End If
m_Command = New OleDbCommand
m_Command.Connection = g_AdoCon
query = "Select * from section where cls_id = 3"
m_Command.CommandText = Query
m_Reader = m_Command.ExecuteReader(CommandBehavior.CloseConnection)
the exception is raised when m_Command.ExecuteReader is executed.
Am really stuck on this, sionce MSDN didn't really help and i have no idea what that exception means.
Pleas help!
|
|
|
|
|
So what was the hex value -- have you tried to look it up in the KB?
How and where is m_reader declared?
Have you tried putting a try -catch around the ExecuteReader and printing the error description in the catch block?
dumping the stack trace?
It would appear that you have only provided selected parts of your code...
maybe a cut/paste of the whole thing would shed some light.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
"So what was the hex value -- have you tried to look it up in the KB"
0x80004005 - E_FAIL. Microsoft says underlying component did not had a specific error number for encountered error. It happens for a lot of things but nothing specific for ADO. jsut means a general failure. How ever i found a similar problem on net on this query.
"Select * from mytable where language = 'EN'"
The problem got solved when authormodified the query to
"select * from mytable where [LANGUAGE] = 'EN'"
apparently ado treated language as keyword.( thatw as the cause given ). But this dosn't work in my case and am totally lost.
Rob Graham wrote:
Have you tried putting a try -catch around the ExecuteReader and printing the error description in the catch block?
dumping the stack trace?
AI got the error message by using the try catch, so answer is yes. dumping the stack trace????dunno what u mean here.
Rob Graham wrote:
How and where is m_reader declared?
m_reader is decalred a private member of the class in which all this is happening as
Private m_Reader As OleDbDataReader
Rob Graham wrote:
It would appear that you have only provided selected parts of your code...
maybe a cut/paste of the whole thing would shed some light.
cut paste whole class??? well it never gets past this point so i dunno if pasting the rest of code is nay use at all, cuz it has nothing to do with this.
Randhawa
|
|
|
|
|
since the reader is a private member, rather than a local variable, I presume you are reusing it in other functions. Did you remember to close the reader each time you were through with it (closing the connection might not be enough)? As a general rule, I create datareaders as local variables, rather than members, and close them after each use.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
