|
Hi,
i m getting problem in a query ,
==> Order_Recieved_Detail
ordID | ordQuantity | ordCost | productID | orID
=================================================
1_____|___10________|_2500____|____1______|___1
2_____|___10________|_3000____|____2______|___1
3_____|__ 10 _______|_3000____|____3______|___1
4_____|___10 _______|_2500____|____1______|___2
5_____|___10 _______|_3000____|____2______|___2
now i m getting problem in calculation of getting sum of ordQuantity of productID 1 of orID 1 and 2 to get 20
i want to get the following result
ordQuantity | productID
==========================
___20_______|____1______
___20_______|____2______
__ 10 ______|____3______
the query which i am using just sum all the itmes in the orderquantity column and not giving the sum of productID 1 and 2
waiting for any one to help me out....
"Winner's don't do different things , they do things differently "
|
|
|
|
|
Try this:
SELECT SUM(ordQuantity) AS ordQuantity, productID
FROM Order_Received_Detail --(is your table name Order_Recieved_Detail or Order_Received_Detail?)
GROUP BY productID
if you need them to be only for orID 1 and 2 then put the following line before the GROUP BY
WHERE orID IN (1,2)
Hope it helps! 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi Ebbert:
it works .... thanks..
its really nice to have people like you how always help out people like me which are new comers in this computer field ....
thanks again Edbert P.
|
|
|
|
|
right now i am getting data thru SQLDataReader,and binding it to a data grid.Here i am hve 2 problems
1> how do i change the name of the headers of columns
2> how do i add a check box column. And then get the id from the checked column(s)
|
|
|
|
|
1. Right click on the grid and choose Property Builder from the menu then choose columns.
2. Again from the Property Builder dialog choose columns. Then create a template column for the checkbox column. Close out the dialog and right click on the grid select edit template.
Also, stay away from the dataReader if possible. Try DataAdapter.Fill(DataSet) instead.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
thank you for the reply but the problem is:
well right now i am retriving data from several tables of the data base and with several rows of results so have to use sqlDataReader
thi have made a template column but from where to set it as a check box and how to access the particular row to get the info of the whole row (like registration ID's of the checked columns)
Zainab
|
|
|
|
|
The DataAdapter.Fill(DataSet) will fill multiple resultset back to the client without holding a connection. When using a datareader, the connection cannot be pooled or used by any other user until the datareader.close() method is executed.
You can set the value in grid's ItemDataBound event or you the DataBinder.Eval in the html.
e.g.
value='<%# DataBinder.Eval(Container, "DataItem.CheckBoxColumn") %>'
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hello,
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
I appreciate your helps.
|
|
|
|
|
Vu Truong wrote:
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure (or, if all you're doing is say, logging changes to a table back into another table for audit purposes, just use the trigger itself to update the log table)
An extended stored procedure is an exported function from a DLL (C-style), so you'll probably need to expose your C# code in a manner that SQL Server can understand, or write a C++ wrapper that can interop with your code.
Ian Darling
"If we've learned anything from history, it's that those who feed trolls are condemned to repetitive conversations. Or something like that." - Eric Lippert
|
|
|
|
|
Ian Darling wrote:
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure
I recently did just that.
I wrote several XP's to send messages to a specified server and port.
Of course there is an issue of protocol for the message format.
In general to write an XP you need the following :
1. Make sure you use the right srv header :
i.e. place
C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE
before
C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE
in the list of include directories.
#include <srv.h>
2. Make sure you link with the OpenDS library :
#pragma comment(lib, "opends60") // MSSQL srv_*() functions
3. Make sure all your XP's are compiled as C not C++.
i.e. wrap with
#ifdef __cplusplus
extern "C" {
#endif
...
#ifdef __cplusplus
}
#endif if functions are in a cpp file.
4. Provide a __GetXpVersion() function in your library :
__declspec(dllexport) ULONG __GetXpVersion( void )
{
return(ODS_VERSION);
}
I ended up writing two sets of functions :
CMKMSSQL_API SRVRETCODE xp_cmkInitSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastSvcMsg ( SRV_PROC *SP );
and
CMKMSSQL_API SRVRETCODE xp_cmkInitGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastGisMsg ( SRV_PROC *SP );
The *SvcMsg ones just send raw data to the specified server:port.
The *GisMsg ones send protocol formated messages to one of my GIS servers.
xp_cmkInit*Msg is called to create a connection to a specified server:port
Usage : EXEC xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]
The output handle is a long and is used to by the Term/Send functions.
xp_cmkTerm*Msg is called to close a connection.
Usage : EXEC xp_cmkTermSvcMsg <@handle>
xp_cmkSend*Msg sends a blob of data using the connection associated with the specified handle.
Usage : EXEC xp_cmkSendSvcMsg <@handle> <@msg>
Because the message is sent using
sock->Send( (byte*)srv_paramdata(SP, 2), srv_paramlen(SP, 2) );
The message can be any data type.
xp_cmkBroadcast*Msg sends a message to all connections.
Usage : EXEC xp_cmkBroadcastSvcMsg <@msg>
This is the one i call in the triggers.
...cmk
Save the whales - collect the whole set
|
|
|
|
|
what is wrong with this code.
string commandString = "SELECT * FROM Company WHERE (Country = 'Brazil')";
System.Data.OleDb.OleDbDataAdapter DataAdpter9 = new System.Data.OleDb.OleDbDataAdapter(commandString, oleDbConnection1);
DataAdpter9.Fill(dataSet4);
dataGrid1.DataSource = dataSet4.Tables["Company"].DefaultView;
It gives me this error using try/catch:
"Object reference not set to an instance of an object"
DataSet and mispelled DataAdapter are all declared in the form_load.
|
|
|
|
|
You probably forgot to declare the dataset4 as new dataset before trying to fill it.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
There could be several possible reasons for this kind of error.. It would have been easier to tell if oyu would have debugged the application and told exactly which line is throwing this exception. Anyway... the possibilities are...
a) You haven't instantiated a connection object.
b) You haven't instantiate a dataset object
c) Form Load event is not attached...(check in InitializeComponent() for the event)
Hope this info solves your prob.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
Dear all,
I get a stupid error while executing this update procedure and can't find the mistake.
The SQL statement works fine in the Enterprise Manager:
"UPDATE tb_cont_SmartSell_orders SET OR_state = N'printing' WHERE (OR_state = N'E-Mail')"
But when I'm using the procedure I get the error message:
..ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed....
If I remove "rs1.close" from the procedure it works but I don't understand why.
I call the procedure with "UpdateOrderState "E-Mail", "printing"
======
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)
Dim strUpdate, rs1, rsconnect1
Set rsConnect1 = Server.CreateObject("ADODB.Connection")
Set rs1 = Server.CreateObject("ADODB.Recordset")
'rs1.CursorLocation = adUseServer
'rs1.CursorType = adOpenKeyset
'rs1.LockType = adLockOptimistic
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"
rs1.open strUpdate, gDBCONSTRING
rs1.close
set rs1 = nothing
set rsconnect1 = nothing
end Sub
======
Many Thanks in advance!
Pete
|
|
|
|
|
The 'problem' is you do not open a recordset, but you are executing a command. Therefore you cannot close the recordset...
What you can (should) do is this:
<br />
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)<br />
<div style="margin-left:3em"><br />
Dim strUpdate, cn<br />
Set cn = Server.CreateObject("ADODB.Connection")<br />
cn.Open gDBCONSTRING<br />
<br />
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"<br />
<br />
cn.Execute strUpdate<br />
cn.Close <br />
set Cn = nothing<br />
</div><br />
end Sub<br />
Wout Louwers
|
|
|
|
|
Many thanks Wout!
It's working fine now.
br Pete
|
|
|
|
|
Hi,
Can i use activexscript in transform data task to determine whether the transformation of current table field failed or succeed? This is because i need to insert the failed field to another table. I don't want to validate the dtssource to do this because the transformation still can be failed.
This is the program logic :
Function Main
DTSDestination("Destination") = DTSSource("Source")
Main = DTSTransformStat_OK
(Validate current transformation here)
IF (TRANSFORMATION FAILED) THEN
(INSERT FAILED FIELD VALUE TO A TABLE)
END IF
END FUNCTION
Can i do this in the script? Or i need to use other task like data driven task, activex task, etc.
Thanks for the help.
|
|
|
|
|
i was some how able to get a resultset wanted by my boss. But can this query be optimized further.
<br />
DECLARE @name nvarchar(50)<br />
<br />
DECLARE cur_name CURSOR FOR<br />
SELECT url <br />
FROM urltable<br />
<br />
OPEN cur_url<br />
FETCH NEXT FROM cur_name INTO @name<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
select name from mytable where name like '%' + @name+ '%'<br />
<br />
FETCH NEXT FROM cur_name INTO @name<br />
END<br />
CLOSE cur_name <br />
DEALLOCATE cur_name
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
You could try
<br />
Select name<br />
From MyTable<br />
Inner Join urlTable on MyTable.name like ''% + urlTable.url + '%'<br />
but I'm not sure if it will work...
Wout Louwers
|
|
|
|
|
thx, but query is verry resource and time consuming. any suggestion to optimize the resource usage also..
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
The resource and time consuming part is like '%' + urlTable.url + '%'.
As far as I known you cannot solve this because of the first '%'.
But you could try:
<br />
Select name<br />
From MyTable, urlTable<br />
Where MyTable.name like ''% + urlTable.url + '%'<br />
Or
<br />
Select name<br />
From MyTable, urlTable<br />
Where CharIndex(MyTable.name, urlTable.url) > 0<br />
or create a full-text catalog on urlTable and use Contains... You can read about full-text catalogs in the SQL Server Books Online
Wout Louwers
|
|
|
|
|
Hi
I'm new to C# and have a little problem inserting Data into a table!
I want to crate a little CD Database:
The table has the following rows: CDID, Artist and Album!
The Code I wrote looks like this:
private void button1_Click(object sender, System.EventArgs e)
{
System.Data.SqlClient.SqlConnection myConnection =
new System.Data.SqlClient.SqlConnection("Data Source=AUTUMN/NetSDK; Initial Catalog=mediadb; User Id=sa;Password=mypwd;");
string myInsertQuery = "INSERT INTO CD (CDID, Artist, Album) Values(" + txtCDID.Text.ToString() + ",'" + txtArtist.Text + "','" + txtAlbum.Text +"')";
System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
This code doesen't work and I'm wondering why? Everytime i hit the button, i gen an Error message. Something like SQl Exception.... and Systemerror....
Can someone help me out?
Thank
Autumn
|
|
|
|
|
Can you please specify what error message you got when you run the code?
Thanks.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi
I got a Systemerror.... SQL Exception when I tried t insert the Data! But I found out, that I made some basic mistakes while coding...
However... it works now!
Thanks
Autumn
|
|
|
|
|
I want to retrieve the Autoinc-ID of the last inserted record.
With sqlserver the command for that is: "select scope_identity()".
But that only seems to work if I use an insert string that includes the values, like:
"insert into Testtable (Name) values ('Charlie')";
In that case "select scope_identity()" returns the ID of the last inserted record. But I prefer using parameters (because of double quotes, date-formatting,...):
"insert into Testtable (Name) values (@Name)";
cmd.Parameters.Add("@Name",SqlDbType.Char, 25).Value = "Charlie";
If I use parameters like in the sample above, I get a null returned on "select scope_identity()".
Any ideas?
Thanks, Bruno.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
