|
Hi there. It depends on the database you're using. Here's one way in SQL Server:
select partId, sum(quantityA) as a, sum(quantityB) as b
from
(
select partId
,case (when location='a' then quantity else 0) as quantityA
,case (when location='b' then quantity else 0) as quantityB
from myTable
) x
group by partId
|
|
|
|
|
Thanks a lot
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Hi,I have a problem when I try to get resultset from a procedure.
if there is only select statement in my procedure,it works fine.
but if I add a insert statement in procedure,pRst->GetState() will return adStateClosed
develop environment:
vc6+MDAC2.7
sql server 2000
here is the procedure code:
create table tem(t_id int,t_date datetime)
go
insert tem select 1,dateadd(minute,1,getdate())
go
drop proc pTest
go
create proc pTest
as begin
--insert tem select 2,dateadd(minute,2,getdate()) --if I add this line,it won't work
select * from tem
end
go
here is the ado code:
_ConnectionPtr pConn;
_RecordsetPtr pRst;
_CommandPtr pCmd;
pConn.CreateInstance(__uuidof(Connection));
pCmd.CreateInstance(__uuidof(Command));
pRst.CreateInstance(__uuidof(Recordset));
....
try
{
pCmd->CommandText = "pTest";
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
//pRst = pConn->Execute("pTest",NULL,adCmdText);
//both of above return adStateClosed
}
catch (_com_error &e)
{
....
}
thanks
|
|
|
|
|
Can someone please explain what the SET NOBROWSETABLE off / on value does?
This setting doesn't appear in the Microsoft books online.
Thanks,
K
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
See KB 275483[^]. At the end of the article, it describes SET NO_BROWSETABLE ON.
There's some information for the SQL Server 2005 Beta in KB 885146[^].
Basically, the option is there to support client-side cursors. After a certain amount of experience my recommendation would be to avoid client-side cursors and indeed updateable recordsets in any form. Use static, forward-only recordsets and explicit INSERT, UPDATE and DELETE commands instead.
Be aware that SQL Server only supports a single forward-only resultset at a time on a given connection. ADO fakes multiple active resultsets by creating another connection under the covers - if you're not aware of this you can end up deadlocking with yourself. Also note that the second connection is not part of the same transaction.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I am trying to copy a record from an existing record in the database and then save it as a new record. The dataset I am using has a couple of references to child tables which have primary key info as well. Does anyone know an easy way to do a copy, get a new primary key information and set the primary keys on the copy to the new one?
Thanks.
|
|
|
|
|
I have a simple C# app that uses an OleDBDataAdapter on an Access database. One of the fields is a date/time. I insert a new row into the table and the date portion of the date/time is there but the time is always "00:00:00" (the time fields are set properly in the DateTime object when I look at it in the debugger)
I tried setting the schema to us DBTimestamp and get an exception thrown during the database update 'type mismatch on criteria query'
any ideas on what could be causing or what I need to do to get both the date and time portions of a DateTime to trasfer to a database?
thanks!
|
|
|
|
|
I had to set all my Access date formats (in the table designer) to 'General Date'. The format seems to dictate the output format. Other formats are not fully understood by the ODBC driver (guessing).
|
|
|
|
|
Is there a way to do a query on 2 differents DB ?
Please help !
|
|
|
|
|
You can do this if you use linked servers.
Affordable and reliable hosting? Click here!
|
|
|
|
|
I am re-writing a control I was working on to use dynamically created template columns instead of using the designer for it. For one of my columns, I need a DropDown list, this worked in my old version fine. However, when writing the Databind event for the new control I am running into a problem where when I call the DataBind() on the listcontrol, it's causing an infinite loop.
It simply cuts out on the bind, and the method re-starts, I cant figure out why this is happening.
However, if I simply loop though all the items in the Dataset, and add them to the dropdown list that way, this works fine - however, much rather use databinding to do this.
private void TemplateControl_DataBindingDL(object sender,System.EventArgs e)
{
Debug.WriteLine("Starting DataBind:" + System.DateTime.Now);
DropDownList lc;
lc = (DropDownList) sender;
string strSqlCommand;
DataGridItem container = (DataGridItem) lc.NamingContainer;
SqlConnection sqlConnection1 = new SqlConnection();
DataSet dsTemp = new DataSet();
strSqlCommand = "SELECT " + fieldName + " FROM V_T_TASKS GROUP BY " + fieldName;
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(strSqlCommand,sqlConnection1);
sqlConnection1.ConnectionString = "...//connection string";
sqlConnection1.Open();
sqlDataAdapter1.Fill(dsTemp);
Debug.WriteLine("assigning datasource...");
lc.DataSource = dsTemp;
lc.DataTextField = fieldName;
lc.DataValueField = fieldName;
Debug.WriteLine("assignedg datasource... binding");
Debug.WriteLine("Rows count=" + dsTemp.Tables[0].Rows.Count);
lc.DataBind();
Debug.WriteLine("..bound");
dsTemp.Clear();
Debug.WriteLine("Cleared...");
lc.Items.Add( DataBinder.Eval(container.DataItem,fieldName,"{0}"));
Debug.WriteLine("Ending DataBind:" + System.DateTime.Now);
lc.SelectedIndex = 0;
sqlConnection1.Close();
}
When I run this, it'll just get to the last two debug statements, then jump back upto the start and keep looping - not throwing any errors or exceptions either.
If I take out the databind and replace it with a normal loop to add each item in the dataset, it works fine.
any ideas?
|
|
|
|
|
Alright everybody, hope you're all well. :->
Does anybody know how to remove the autonumber functionality from the id column in a table through a SQL Statement in SQL Server, it's obviously easy through Enterprise Manager  .
I'm assuming it would be an ALTER TABLE statement but was wondering if anyone knows the syntax for this or if there is an easier method, like a stored procedure.
Help will be greatly appreciated.
Thanks
|
|
|
|
|
The following page has a some sample TSQL that does this.
Basically, it creates a new column of the same type as the identity, copies the data into the new column, drops the identity column and renames the new column to the name of the old identity column.
Affordable and reliable hosting? Click here!
|
|
|
|
|
Hi,
Thanks for the reply, however i think you may have forgotten to include the link.
Would be greatly appreciated if you could post it as i have found nothing in the way of doing this so far.
Thanks again,
|
|
|
|
|
|
Hello,
I have a problem with this code here.
cmd.CommandText = "UPDATE DBPROF SET (ID,NAME,PROF) = " +<br />
"(?,?,?) WHERE (rowguid = '"+strGUID+"')";<br />
<br />
cmd.Parameters.Add(@"ID",dID);<br />
cmd.Parameters.Add(@"NAME",strName);<br />
cmd.Parameters.Add(@"PROF",strPROF);<br />
Why this code isn't working for updating db ?
also values instead "?" don't work:
(@ID,@NAME,@PROF)
|
|
|
|
|
You don't say what database you are using - This is important information as there are subtle differences between each type of database (even from the same Vendor - MS Access has some differences with SQL Server)
Also, you have ommitted to describe what the cmd object is. Is it an OleDbCommand, a SqlCommand, an OracleCommand or what? There may be some slight differences in the way each of these work as a result of interacting with different databases. This information is also good to know as it helps diagnose the problem.
Why are you using parameters for the update values, but you are injecting the GUID into the string (this leaves you open to a SQL Injection attack)
Finally, a stab at the answer:
The syntax I would use is:
UPDATE DBPROF SET ID=@id, NAME=@name, PROF=@prof WHERE rowguid='GuidValueHere' This is valid for SQL Server.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
cmd.CommandText =
"UPDATE DBPROF SET ID = ?, " +
"NAME = ?, PROF = ? " +
"WHERE rowguid = ?";
cmd.Parameters.Add( "ID", dID );
cmd.Parameters.Add( "NAME", strName );
cmd.Parameters.Add( "PROF", strProf );
cmd.Parameters.Add( "rowguid", strGUID ); If you're using the SQL Server Managed provider, change the ? in CommandText for @param names, then supply the same @param names for the corresponding parameters:
cmd.CommandText =
"UPDATE DBPROF SET ID = @id, " +
"NAME = @name, PROF = @name " +
"WHERE rowguid = @rowguid";
cmd.Parameters.Add( "@id", dID );
cmd.Parameters.Add( "@name", strName );
cmd.Parameters.Add( "@prof", strProf );
cmd.Parameters.Add( "@rowguid", strGUID );
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thx, works
|
|
|
|
|
hi,
I have installed MSDE (sql2kdesksp3.exe) in my PC by following the guidelines given in Lars article
http://www.codeproject.com/database/ConfigureMSDE.asp[^]
but when i tried to connect using osql (C:\> osql –E –S localhost\myinstance),
it is giving the following error: "Server does not exist or access denied".
i dont have any idea what's wrong? i could see that the service is running.
i am running on a dual boot with Linux and Windows 2000 professional.
I installed it in Administrator login only without any password.
Can you please help?
|
|
|
|
|
Hello,
What is the best place to keep my SqlConnection object? Should I have only one application-wide? or one new every time I need it?
Where should I open it? every time I'm going to use it? or at the beginning of my application?
The application is what I consider more or less database intensive. It uses a lot of the database for some time, and then stands idle for some other time.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
What is the best place to keep my SqlConnection object?
I usually create some sort of ApplicationConfiguration class which pulls out the information from the app.config or web.config file and creates a SqlConnection for me.
Luis Alonso Ramos wrote:
Should I have only one application-wide? or one new every time I need it?
I'd say a new one each time you need it. .NET has connection pooling so you don't need to worry about storing one somewhere for when you need it. Also, if you create a new connection each time you need it you can potentially have two connections open at the same time.
Luis Alonso Ramos wrote:
Where should I open it? every time I'm going to use it? or at the beginning of my application?
I'd say open it, use it, close it. Like this:
SqlConnection conn = MyAppConfig.GetDatabaseConnection();
try
{
conn.Open();
}
catch(SqlException ex)
{
}
finally
{
conn.Close()
}
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
Colin's advice is good (as always). Just remember that for connection pooling to work, your connection string must be identical for all the connections (otherwise a new pool is created, which has significant overhead). Use a single string reference for the connection string,and preferably use integrated security so you don't have to worry about exposing usernames or passwords.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hello,
I'm designing a new database, and I was wondering what is better: to use default values for some fields, or to allow NULL values?
I know NULLs are more of a hassle because you have to explicitly check for NULL everytime, but they allow the user to indicate if the value was provided or not. With default values you don't have to check every time for NULL, but you don't know if the user entered the default value or didn't enter anything.
I was thinking about using default values for most fields, and only using NULL values where I really need them (eg. date when an employee was fired).
Any thoughts on this?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
what is better: to use default values for some fields, or to allow NULL values?
It depends on your situation. If you need to beable to store a value that is essentially unknown, or doesn't exist then you need a null. If your column is mandatory but there is a frequent value being repeated you might consider making a default value.
I'd say the easiest rule to use is: If I need to know that the user didn't enter anything I use a null, otherwise I use a default (or force the user to input something)
So things like AddressLine2 might default to an empty string as it doesn't matter that the user didn't enter anything. But something like a TaskCompletionDate may be null because the task may be in progress and you don't know when it will complete.
By the looks of it, you have pretty much come to this conclusion anyway.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
