|
i want help with this issue,i want to make sql expression as a math operation (add two columns and store the result in a new column)...i tried it as i used to do it in vs 2003 but it didn't work,it gave me an exception,i'd like to know how to make sql expression in ado.net using vs 2005 ,and do i have to make changes in the database or what?!!!
|
|
|
|
|
To add a new column to a table, run a DDL query like the following:
ALTER TABLE mytable
ADD mycolumn datatype [NULL|NOT NULL]
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
this isn't what i mean...i mean i want to get a calculated column from other two column the application is ado.net and im using vs 2005 it gives me exception that myCalculatedColumn is a calculated one that's it!!! that's what the exceptiona says only...when i write the expression in vs.net like this :
myDataset.table["MyTable"].columns["MyCalculatedColumn"].Expression = "Acolumn - Bcolumn";
this throw exception which i don't know why!!! anyone knows what should i do!!
|
|
|
|
|
here is the exception btw : System.InvalidOperationException was unhandled
Message="The column mapping from SourceColumn 'Overall' failed because the DataColumn 'Overall' is a computed column."
Source="System.Data"
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at testingthefuckingexepression.GymDataSetTableAdapters.NewaccountingTableAdapter.Update(NewaccountingDataTable dataTable) in C:\Documents and Settings\Mr.mohamed\My Documents\Visual Studio 2005\Projects\testingthefuckingexepression\testingthefuckingexepression\GymDataSet.Designer.cs:line 2236
at testingthefuckingexepression.Form1.newaccountingBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\Mr.mohamed\My Documents\Visual Studio 2005\Projects\testingthefuckingexepression\testingthefuckingexepression\Form1.cs:line 30
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at testingthefuckingexepression.Program.Main() in C:\Documents and Settings\Mr.mohamed\My Documents\Visual Studio 2005\Projects\testingthefuckingexepression\testingthefuckingexepression\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
|
|
|
|
|
Hi All
I am new to Sql Server , I want to Create a row level trigger on table
My Reqiurement is as below :
i create one data transfer utility which Transfer data of sourece table to destination table of mapped column (Mapping is done manually i.e. name of source & destination columns may be same or different )
I want to write trigger on source table for insert , it take new inerted value of mapped column of source and insert it into mapped column of destination table
Can any one help me
Thanks for Your Time
Anil jagdale
|
|
|
|
|
hi
you can write the trigger on Source table for insert..
and you will get the inserted value from table "insertiing"
eg; select emp_staffid from inserting
will return the emp_staffid ie: inserted
and you can insert that value in to detination table
regards
Joe
-- modified at 6:24 Friday 2nd November, 2007
|
|
|
|
|
hi All
In my Company we are using SQL 2000, and we want to upgrade to SQL2005. we have an inhouse GIS web Application that uses ARc GIS servers. Am not experienced in GIS. i just Gooogled what was Arc GIS server. Now ArcGIS server needs to be upgraded to version 9.2. So my question is, can we still keep the version of SQL 2000, while the upgrade of 2005 is taking place on the same server. because we want to keep some other applications under SQL 2000 And still upgrade to ArcGIS 9.2.
i hope i explained it, i tried
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Create a backup of your database. Restore it under a different name and run the upgrade wizard against this new database.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi Paul
Thanks for the reply. So if i have Written my stored procedures in SQl2000, will they get Affected in 2005?
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Shouldn't be any problem. SQL 2005 is backwards-compatible, so all your stored procedure code should work OK.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks
i Appreciate your help, last Question
If i have 2 servers one Running SQl2005 And the Other Running SQL 2000, will it be Possible to Access the a certain database in SQL2005 server from SQl 2000 server?
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I'm not sure what you mean? How do you want to access it?
The best way to find out would be to try it for yourself. I don't have access to an instance of SQL 2000 at the moment.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Is it possible to iterate through a table or a temporary, because i have to add a temporary column and insert different values for each row according to condition to return a comma separated value. I show a rough sketch what i want to do.
Select SAP.LoginName,var="--Some Very Long Query according to a criteria within the rows of a table like where Test.Folder_ID=CM.Folder_ID--"
From dbo.sysCommunicatorMessages CM
Inner Join dbo.sysAppLogins SAP On CM.MESSAGEFROM = SAP.LOGIN_ID
where "--Some Criteria--" and CM.Folder_ID<>-2
I have used functions to solve the problem but they are currently not acceptable.
Bye
|
|
|
|
|
You could use a CURSOR. Have a look at this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi
My aim is to create ms access database through c# dynamically
if any one knows or have any idea abt it please let me know
regards
sindhu tiwari
Always Innovative
|
|
|
|
|
Did you try searching Google? I tried and found this[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
any idea abt creating a table and its fields dynamically...........................................
regards
sindhu tiwari
its me sid
|
|
|
|
|
Use the CREATE TABLE statement.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
thanks for ur valuable,meaningful and expertise reply................................................
regards
sindhu tiwari
its me sid
|
|
|
|
|
need help Guys.,
how can i convert money to varchar, to add some prefix. having problem with the below statement.
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],
The Complete Sproc is
USE FastLoan_Commissions
GO
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[upExceptions_GetPending]<br />
@UserID varchar(10)<br />
AS<br />
BEGIN<br />
<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NULL<br />
<br />
UNION ALL<br />
<br />
--UPFRONT LOAN INCREASES<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NOT NULL<br />
<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOANS<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOAN INCREASES<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NOT NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
ORDER BY TradingName, SettlementDate<br />
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate<br />
END
Thanks in Advance
|
|
|
|
|
it seems like to me that you would do something like this instead
Case When Isnull(LP.RefinanceAmount, '')='' Then cast(L.LoanAmount as varchar(20))
Else 'N.F' + cast(LP.RefinanceAmount as varchar(20)) END [Amount],
|
|
|
|
|
still got the same error, but anyway thanks for the reply
|
|
|
|
|
Hi friends
I want to write a code for restor my database backup. I want to write it in SQL procedure. Whaat I must do?
thanks
|
|
|
|
|
|
If I recall, there should be a stored proc already there to to backups and restores. I could be wrong, though.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|