|
 I'm using this code to execute an stored procedure:
VARIANT vRecords;
_RecordsetPtr m_pRecordsetPtr;
irecordsaffected=0;
try{
vRecords.vt=VT_I4;
m_pRecordsetPtr=pQDef->Execute(&vRecords, NULL, adCmdText);
irecordsaffected= vRecords.iVal;
}
when I do it for "Insert" statements, the vRecords variable gets the correct value, but I get always 0 if I try an "Update" statement, any idea?.
thanks in advance.
|
|
|
|
|
The problem was that I was using allways the same _CommandPtr (pQDef), and never delete the Params used, this is why I got always 0 records when Update
and 1 record on the first Insert.
now works fine.
David.
|
|
|
|
|
Hi, Currently i need to display information from 1 table. This is the information of the table which contain 3 fields
Logdate ProjCode Hrs
2003-09-11 SBS 3
2003-09-11 asd 9
2003-09-11 asd 9
2003-09-12 dsa 6
2003-09-13 zxc 5
2003-09-13 cxz 2
When i add a new field using
Quey: Select *, (select Sum(Hrs) from Table a where a.Logdate = b.Logdate Group by Logdate)from Table b
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 22
2003-09-11 asd 9 22
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 7
how do i edit my query statement where i can get
Result:
Logdate ProjCode Hrs Sum
2003-09-11 SBS 3 22
2003-09-11 asd 9 null
2003-09-11 asd 9 null
2003-09-12 dsa 6 6
2003-09-13 zxc 5 7
2003-09-13 cxz 2 null
as only display 1 sum in 1 logdate where the rest is null.
|
|
|
|
|
create procedure RepProjHours<br />
as begin<br />
set nocount on<br />
<br />
--Create temporary table with line-number.<br />
create table #temp1 (<br />
LineNo integer identity not null,<br />
LogDate datetime null,<br />
ProjCode varchar(3) null,<br />
Hrs decimal(6,2) null<br />
)<br />
insert into #temp1<br />
select LogDate, ProjCode, Hrs<br />
from MyTimesheetTable<br />
order by LogDate, ProjCode, Hrs desc --Nice ordering?<br />
<br />
--Find the first timesheet entry for each day.<br />
create table #temp2 (<br />
LogDate datetime null,<br />
MinLineNo integer not null,<br />
TotalHrs decimal(6,2) not null<br />
)<br />
insert into #temp2<br />
select LogDate, Min(LineNo), Sum(Hrs)<br />
from #temp1<br />
group by LogDate<br />
<br />
--Join everything together (with subtotal on first line for each date).<br />
select T1.LogDate, T1.ProjCode, T1.Hrs, T2.TotalHrs<br />
from #temp1 T1<br />
left outer join #temp2 T2<br />
on T2.LogDate = T1.LogDate<br />
and T2.MinLineNo = T1.LineNo<br />
order by T1.LineNo<br />
end
Hope this helps. There are several other ways of achieving the same effect).
Andy
|
|
|
|
|
ALTER TABLE tt ALTER COLUMN [cc] [int] identity [(2, 1)] not null
and it failed with below error:
Incorrect syntax near the keyword 'IDENTITY'.
How can I make it work ?
Thanks
|
|
|
|
|
Remove the square brackets around the parentheses after 'IDENTITY'. Then it should work.
|
|
|
|
|
I Removed the square brackets around the parentheses after 'IDENTITY'.
ALTER TABLE tt ALTER COLUMN [cc] [int] identity (2, 1) not null
but it still failed with below error :
Incorrect syntax near the keyword 'IDENTITY'.
why?~
|
|
|
|
|
I have a query that fills a datagrid with a order number, order qty, and shipped qty. After I click a button to fill the grid I want the code to be able to highlight any rows where the order qty isn't equal to the shipped qty.
|
|
|
|
|
Hi,
Environment - VB.NET, ASP.NET, SQL Server 2000.
In a SQL User-Defined Function, I am selecting a column which returns multiple rows. I need to construct one single string out of those returned values. To do that, I am using CUROSR.
Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.
Is there a way, I can construct this String without using CURSORs??
Please advice. Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I don't believe there is.
Generally, rolling up data is an operation best performed on the client in procedural code.
You can reduce the cost of your cursors by ensuring that you use a LOCAL FAST_FORWARD STATIC cursor.
|
|
|
|
|
If you're satisfied with adding them to a single column, I recently discovered that this actually worked (atleast on MS SQL 2000):
DECLARE @t varchar(8000)
SET @t = ''
SELECT @t = columnname + @t FROM thetable
SELECT @t
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
Thanks for the responses.
Arjan: I read on some other site article that this way of adding the values to single column is a bug in SQL. Probably, will be taken care of in future updates.. ..thought of passing this information to you and readers of this question .
Though, I am still looking for a neat solution for this problem.
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Hi,
I am writing a small program to test the scalability of SQL Server. My code is:
class MyClass<br />
{<br />
string sql = "select * from table1";<br />
string conn_s = "...";<br />
<br />
private bool threadFail = false;<br />
private bool ThreadFail <br />
{<br />
get <br />
{<br />
lock ( this ) <br />
{<br />
return threadFail;<br />
}<br />
}<br />
set<br />
{<br />
lock ( this ) <br />
{<br />
threadFail = value;<br />
}<br />
}<br />
}<br />
<br />
private void DoQuery()<br />
{<br />
DataSet ds = new DataSet();<br />
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn_s);<br />
<br />
while ( !ThreadFail ) <br />
{<br />
try <br />
{<br />
da.Fill(ds);<br />
Thread.Sleep(100);<br />
} <br />
catch ( Exception e ) <br />
{<br />
ThreadFail = true;<br />
}<br />
}<br />
}<br />
<br />
public void Test()<br />
{<br />
int i = 0;<br />
while ( !ThreadFail ) <br />
{<br />
i++;<br />
Thread t = new Thread(new ThreadStart(DoQuery));<br />
t.Start();<br />
Thread.Sleep(100);<br />
}<br />
ShowMessage( "Total threads = " + i );<br />
}<br />
}
However, there's a problem. It threw an exception of without enough memory. I think it's because of "da.Fill(ds)". It occupied a lot of memory every time. What I want to know is: can I use OleDbCommand.ExecuteReader() instead of OleDbDataAdapter.Fill()? And I don't get the data from sqlserver. But I am not sure if the OleDbCommand.ExecuteReader() already run the sql command in SQL Server? Because I need the query to be executed but I don't want the return data.
Anyone has any idea? Thanks in advance!
|
|
|
|
|
Why don't you try using OleDbCommand.ExecuteNonQuery. It will execute the query, but won't return nothing.
Free your mind...
|
|
|
|
|
|
Is it possible to do the following query?
declare @Year varchar(5)
set @Year='02/03'
SELECT
case
when @Year='02/03' then AccountNumber between '50' and '59'
when @Year='01/02' then AccountNumber between '40' and '49'
else AccountNumber end
FROM Master
WHERE (Number = '71')
|
|
|
|
|
Hi everyone,
Is somebody able to answer my question? - I'm trying to have access to the MSysObjects table (in MSAccess database file) to get a list of all user-defined tables it holds. As the subject suggests I'm using ADO.NET. My select command (SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0) query gives me familiar to many of us "...no read permission in MSysObjects" exception, that (in my case) means that I didn't configue MSAccess to allow anyone to see any system/hidden tables. And it's Ok, but the funny thing is that I can create and open connection to the same *.mdb file from the VS.NET IDE (using Data Connections in the Server Explorer) without any changes of the database properties from the MSAccess application. So, VS.NET knows some other way to get (unlimited?) access to any *.mdb file (by either programmaticaly changing database properties, or using some other tricks).
I've found a workarround for this issue at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/html/_mfcnotes_tn054.asp[^]
but they're usind direct calls to DAO to change internall database setting from the user's code. So:
- am I able to use direct DAO calls from inside my C# code?
- is there any good method to do it by means of just C# itself?
- is there probably another way to get the list of user tables without pulling MSysObjects table?
Any ideas will be highly appreciated.
Thanks,
Armen.
|
|
|
|
|
 I have this little problem:
I have created a Installation disk to install a suite of applications along with installing MSDE (Single workstation so using MSDE for databsae). My problem is that I don't know how to import a stored procedure into my SQL database??
Does anyone know how to import a stored procedure from a txt file through Install shield, or through visual c++??
I am really struggling on this one.
Please could someone help me with this matter.
Will be eternally grateful.
|
|
|
|
|
I believe InstallShield has the capability of running an external program. If so, use the osql tool.
|
|
|
|
|
I have a table1 and I wanted to add a foreign key from table2 for 2 fields:id,grpid. How do I do this?
|
|
|
|
|
Is there any way to kill sleeping database connections of SQL programmatically to avoid memory leaks.Help on this topic will be encouraged.
Thanks
Altaf-Ur-Rehman
|
|
|
|
|
First, find active connection by using the procedures:
sp_who or sp_who2
this will return a list of all active connections,
then to stop a non-needed session, get the SPID of the session you want to end and use the KILL command like so..
KILL {SPID of connection you want terminated)
and there you go... to test this, use Query analyzer.
- Sage
|
|
|
|
|
Thanks for your reply, but i want to ask that how could we close sleeping database connection in Visual C++ (i.e programatically) by spid.Please tell if you have some knowledge about it.
|
|
|
|
|
I have added a component that access database using ADO into an existiong application which used Pro-C to connect to an oracle database. I have tested with both MS Access and MS SQL Server are working fine, however when I tried to connect to oracle with the ADO component, the Pro-C which also connect to oracle database will give an exception error while disconnecting, the error is something like access voilation of NTDLL.dll. I noticed that both the connection with Pro-C and ADO seems not to be allow to exist at the same time. Any idea ?
|
|
|
|
|
I have done some research on some different newsgroups
and website and think I have figured out how to upload a
document file into my SQL Server. below is the code I am
using, but it never store the data into the database. I
do not get any error messages so I assume it works, but when
I check the table, there is no data. Is there something I am missing?
Thanks for your help.
<br />
Public Function StoreFile(ByVal FileData As Byte(), ByVal ServerName As String, ByVal SqlPassword As String, ByVal SqlUser As String)<br />
'Make the Connection to the SQL server<br />
Dim ConnectionString As String = ""<br />
ConnectionString = ConnectionString & "data source=" & ServerName & ";"<br />
ConnectionString = ConnectionString & "password=" & SqlPassword & ";"<br />
ConnectionString = ConnectionString & "persist security info=False;"<br />
ConnectionString = ConnectionString & "user id=" & SqlUser & ";"<br />
'ConnectionString = ConnectionString & "packet(Size = 4096)"<br />
<br />
Try<br />
'Declare the Connection Object.<br />
Dim Conn As SqlConnection = New SqlConnection(ConnectionString)<br />
'Open the Connection.<br />
Conn.Open()<br />
<br />
<br />
'Declare a DataAdapter.<br />
Dim DA As SqlDataAdapter = New SqlDataAdapter()<br />
<br />
'Set the Select,Delete,Insert, and Update Command for this DataAdapter<br />
DA.SelectCommand = New SqlCommand("SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All", Conn)<br />
DA.InsertCommand = New SqlCommand("INSERT INTO EDIAPPS.dbo.Documents_All(DOC_Type, DOC_Data) VALUES (@DOC_Type, @DOC_Data); SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @@IDENTITY)", Conn)<br />
DA.UpdateCommand = New SqlCommand("UPDATE EDIAPPS.dbo.Documents_All SET DOC_Type = @DOC_Type, DOC_Data = @DOC_Data WHERE (DOC_ID = @Original_DOC_ID) AND (DOC_Type = @Original_DOC_Type); SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @DOC_ID)", Conn)<br />
DA.DeleteCommand = New SqlCommand("DELETE FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @Original_DOC_ID) AND (DOC_Type = @Original_DOC_Type)", Conn)<br />
<br />
'Delclare a dataSet Object.<br />
Dim DS As New DataSet()<br />
<br />
'Fill the Dataset with the Table data from the Database.<br />
DA.Fill(DS)<br />
<br />
'Delclare a New DataRow Object.<br />
Dim DR As DataRow = DS.Tables(0).NewRow<br />
<br />
'Populate the new row with our information.<br />
DR("DOC_Type") = 1<br />
DR("DOC_Data") = FileData<br />
<br />
'Add New Row to out DataSet.<br />
DS.Tables(0).Rows.Add(dr)<br />
DS.AcceptChanges()<br />
<br />
'Update the Database with our changes.<br />
DA.Update(DS)<br />
Conn.Close()<br />
Catch Ex As Exception<br />
MessageBox.Show(Ex.Message, "Application Error", MessageBoxButtons.OK)<br />
Return False<br />
Catch DBE As SqlClient.SqlException<br />
MessageBox.Show(DBE.Message, "SQL Error", MessageBoxButtons.OK)<br />
Return False<br />
Finally<br />
End Try<br />
<br />
End Function<br />
=================================
When I was in school, all I wanted was to get out into the real world.
Now that I'm in the real world, all I want is to go back to school.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
