|
If you use executenonquery the returncode is the result of that call. So you don't need to worry about result sets.
Ben
|
|
|
|
|
I made a second error with the parameters. The only one I will want to pass is the @NewAccountID. The @AccountID must be set by
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1
since there is no posibility of there everybeing more than one of the PlaceInReferralLine being equal to 1.
I also need to make sure
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID
is empty or null before I continue to process. Otherwise the ReferredBy column could be changed.
I supposed I could to this at the program level rather than at the SQL Server level.
|
|
|
|
|
I think I would do this as well in the sql stored procedure. It would be the most efficent there.
Ben
|
|
|
|
|
Ok I've looked, I probably don't know exactly what I need so can you point me in teh right direction for information on how to use the results from the select statements directly within the stored procedure.
|
|
|
|
|
There are several things you can do. I think you said you didn't want the record to exist. I think the best way to do that would be:
if (Select count(*) from table where column = @param) = 0
begin
end
If the record might exist, but the value for the field is null you can do:
if (select isnull(column2,'') from table where column = @param) = ''
Begin
End
Ben
|
|
|
|
|
Ok so how would I do something like
@AccountID = Select AccountID FROM Table where Col2 = 1;
|
|
|
|
|
Declare @accountID int
select @accountid = accountid form table where col2 = 1
There are no ; in sql.
Ben
|
|
|
|
|
Hi there .
During transaction is began , I inserted new data in table , until I don't call commit() or rollback() functions , another processes can not access to this table.
I mean, if i try to add a new data in a table with transaction and before closing transaction(commit or rollback) another process can't read that table; then a exception will occurred.
what is the problem?
DMASTER
|
|
|
|
|
Developer611 wrote: what is the problem?
There is no problem. A transaction shouldn't last long enough for there to be a problem. I'm guessing the other process times out waiting for the table to become available. And while it is in use in a transaction you have made it unavailable.
The solution is to make your transactions shorter.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Thanks dear Colin . But how can I do this in short time . Did you have any solutions ?
DMASTER
|
|
|
|
|
Developer611 wrote: But how can I do this in short time . Did you have any solutions ?
What the heck are you actually doing? A transaction should start, the query(ies) run and the transaction committed or rolledback. What else are you doing in there that make it take so long?!
If you start a transaction perform some queries, wait for user input, do some more queries then finally end the transaction then you have some serious redesigning to do.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
I have 5 or more queries that run in same time and one of queries take along time to done.
I have to do all of this queries in a transaction.
I have no way to do this in separate transactions.
I think transactions work with temporary tables that mean changes will apply to original tables after commit. This means that we can use original tables while a transaction is running!?!
|
|
|
|
|
SalarSoft wrote: I think transactions work with temporary tables that mean changes will apply to original tables after commit. This means that we can use original tables while a transaction is running!?!
No. If you in a transaction and you alter a table you are altering that actual table. No temporary tables are involved unless you explicitly involve them. Changes are made as you make them. If you roll back the changes are reversed.
SalarSoft wrote: I have 5 or more queries that run in same time and one of queries take along time to done.
If you are using SQL Server 2005 you may like to look into Snapshot transactions. That might help you.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hi to all brod and sis,
i would like to ask some help for this problem coz i was confuse in computing year and month in one column only like for example i have column date_hired then i want to get the year and then the month versus Now just to get the exact year and month, this is should be the output in sqlsever analyzer..
date_hired Year Month
2005-09-11 2 22 ---- i know that there is no 22 months
how i am going to compute that months coz in our calendar we only have 12 and more that can give some computation for that problem which will be my pattern in making my stored procedure..
thanks in advance
|
|
|
|
|
your query is not clear.
however the result of year and mth from date_hired can be obtained (as per your example) ....
select date_hired, datediff(yy, date_hired, getdate()) AS 'Year', datediff(m, date_hired, getdate()) AS 'Month'
now it will return months as 23
Regards
KP
|
|
|
|
|
Use the DatePart function.
SG
|
|
|
|
|
Try to understand your question... hope this is what you want:
SELECT [date_hired], [Year] = DATEDIFF(m, [date_hired], GETDATE())/ 12, [Month] = DATEDIFF(m, [date_hired], GETDATE()) % 12
reference --> http://www.oin1.com/Technical/SQL/DateVsCurrent.htm[^]
Eliz.K
|
|
|
|
|
Hello everybody
I have a table "Employee" which contains 20 records. i want to select records from 10 to 15. how can I select?
Regards, Qaiser Nadeem
|
|
|
|
|
You need to make a ID field in Employee table from 1 to 20
Then do this
Select * from tblEmployee where ID in [10,15]
It seem to be a solution or an answer.
|
|
|
|
|
select * from Employee<br />
where [id] >= 10 and id <=15
I Love SQL
|
|
|
|
|
If you only want 10 of the 20 records you can do
select top 10 * from yourtable
Ben
|
|
|
|
|
in sql server there is a new feature which will help you to send e-mails via the database .do you know exactly how to do this .
eg:
if(time period expires)
send an e-mail;
|
|
|
|
|
|
Hello,
VS 2005
I have a datagridview that is bound to a bindingsource.
I am trying to merge this into another data table that has the same schema.
I have filled the dgv with the following code:
The reason i have used a dataview (dv) is so that when the customer selects from the combo box in the dgv, it doesn't automatically filter the rows.
'Fill the pending orders that belong to the order ID<br />
Private Sub FillPendingOrders()<br />
Try<br />
Me.TA_OrderDetails_dsCodeRed1.Fill(Me.DsAddComponetAndEquipment.OrderDetails)<br />
<br />
dv = New DataView(Me.DsAddComponetAndEquipment.OrderDetails)<br />
dv.RowFilter = String.Format("Status = '{0}' AND OrderID = '{1}'", "Ordered", CInt(Me.cboPendingOrders.SelectedValue))<br />
Me.dgvPendingOrders.AutoGenerateColumns = False<br />
Me.bsOrderDetailsPending.DataSource = dv.ToTable()<br />
Me.dgvPendingOrders.DataSource = Me.bsOrderDetailsPending<br />
Catch ex As Exception<br />
MsgBox(ex.Message)<br />
End Try<br />
End Sub
However, when i update the datagridview by entering some number into the cells, and click the save button. I have checked the database to see if the value has not been updated, and it hasn't.
This is how i am updating:
Me.bsOrderDetailsPending.EndEdit()<br />
Me.DsAddComponetAndEquipment.OrderDetails.Merge(DirectCast(Me.bsOrderDetailsPending.DataSource, DataTable), false)<br />
'Just to check to see if the value is displayed in the source column. This displayed what I value i put into the source cell ok.<br />
source = DirectCast(Me.bsOrderDetailsPending.Current, DataRowView)("Source").ToString()<br />
<br />
Me.TA_OrderDetails_dsCodeRed1.Update(Me.DsAddComponetAndEquipment.OrderDetails)
Many thanks for suggestions,
Steve
|
|
|
|
|
Hi, I got the following from a tutorial on using store procedure for custom paging and I'd like to have few questions answered.
CREATE PROCEDURE uspPaging
@nStartValue INT,
@nEndValue INT
AS
SET NOCOUNT ON
DECLARE @tblTempData TABLE
(
nID INT IDENTITY,
EmployeeID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
SupervisorID NCHAR(5)
)
INSERT INTO @tblTempData
(
EmployeeID,
LastName,
FirstName,
SupervisorID
)
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo
FROM Employees
ORDER BY
EmployeeID,
FirstName
SELECT EmployeeID,
LastName,
FirstName,
SupervisorID
FROM @tblTempData
WHERE nID BETWEEN @nStartValue AND @nEndValue
ORDER BY
nID ASC
What I don't understand is how does the computer know what the values of @StartValue and @EndValue are if they are not initialized. In this tuturial and another one I was reading, the @ symbols are used in naming the variables. Is it a must to use the @ symbol as the prefix of the variable name? Also does Between mean that if StartValue = 1 and EndValue = 5, then only EmplpoyeeID = 2 through 4 will be returned? Thank you in advance for your help.
-- modified at 23:57 Wednesday 1st August, 2007
|
|
|
|