|
The problem for me is not calculating the pg total. The main problem is that if the table is spanning multiple pages then how do i get a report item to appear on each page below the table so that i can display the total without using the page footer. i cant use the table footer and cause it to repeat on every page because the table footer is processed only once at the end of the table
|
|
|
|
|
Hi, I have develop database application in vb 2005 and using sql express as database back end. I have declare a connection as below
Dim cnnDICAdmin As New SqlConnection("Data Source=.\ SQLEXPRESS;Database=DICDbase;Integrated Security=True;User=Admin;Pwd=") but when my application but when i run amy application its display an error as below:
System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open database "DICDbase" requested by the login. The login failed.
Login failed for user 'CAMBODIAGFATM\roathkanel'."
Number=4060
Procedure=""
Server=".\ SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at DIC_Administration.frmProvince.btnAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\roathkanel\My Documents\Database\DIC Administration\DIC Administration\Province\frmProvince.vb:line 38
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.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(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at DIC_Administration.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
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()
Please let me know how to solve this problem. Thank in advance.
|
|
|
|
|
Check out this:
^
It shows conenction strings for most databases.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
This is the common problem when you have installed the sql server express edition.
First you have to make sure that your Connection String is Correct.
most likely this problem is caused because in default condition sql server express edition cannot allow remote connection.
so please do following
Open "SQL Server Management Studio Express"
Connect to your Database
Right Click on the Database in Object Explorer
go to Properties
select "Security"
Now in Server Authentication Mode
select "SQL Server And Windows Authentication Mode"
Restart the SQL Server Service (if you dont know how then restart the computer  )
if still have the problem then
go to
Start ->
SQL Server 2005 ->
Configuration Tools ->
SQL Server Surface Area Configuration
Then select "SQL Server Surface Area Configuration for Service & Connection"
now select "Remote Connection" ->
tick on "Remote and Local Connection" ->
again tick on "Use Both TCP/IP and Named Pipe"
Now go to Service "Stop" it and then "Start" it.
if you still have the problem then double check your connection string 
|
|
|
|
|
Hey guys, I have got two tables, in PurchaseT I store the data for a purchase (Amount and PurchaseCode), in PurchaseCodeT I store the names for each code, (1 = Grocery, 2 = restaurant and ...) now I have a select command which get some records from PurchaseT, but instead of the codes I want to have the name for each code ... how can I pull that off !!
Thanx heaps !
Mr.K
|
|
|
|
|
SELECT P.Amount, P.PurchaseCode, C.Name AS PurchaseCodeName
FROM Purchase P
INNER JOIN PurchaseCode C ON C.PurchaseCode = P.PurchaseCode
|
|
|
|
|
For MS SQL Server 2000...
Sometimes I want to define and use variables within a View, but it appears that is not possible.
That includes using table variables in Views. I'm new to SQL. Is there a way to do this? If not can someone explain the reason for it being not allowed? Google hasn't been much help on this one. Thanks
|
|
|
|
|
Variables are procedureal constructs. A view is for creating alternative views of your data only. Not sure what it is you want to do, but once your view is defined to you can "pass a variable" to it from your stored procedure or batch statement like this:
SELECT col1 + @myVariable AS Col1Alias
FROM myView
WHERE col2 = @myVariable2
Just think of a view as a derived table instead of a script or code file and that should help. The kind of stuff you're describing can be done w/in a stored procedure, that's what you want to use in most cases.
|
|
|
|
|
Views just execute select statements, and act very similarly to tables, you can do formatting and things in a view, and limit the columsn in one or more tables that someone has access to. that and formatting and long complicated joins are their main purpose i believe, its a lot better to put a long complex query in a view and have a simple select in your code, if you're using it in an application, makes it easier to change if something is wrong or table layouts change. If you want to declare varibles or run other queries, you can use a stored procedure. you can run cursors, use varibles, even run other stored procedures in them, pretty much anything youcan do in query analyzer. in sql 2000 if you need a result set back just select it back..select <whateveryouwant> in teh sp...be careful tho because you can do multiple selects like that in one stored procedure, and when it executes it will return multiple result sets, which can cause problems if you're using an app (win forms or web) to read the data.
|
|
|
|
|
As someguy said a view is basically a select. However you can do some real cutes with a view as long as you are not passing IN information.
IE you can get a view to select from a UDF where data is filtered and formatted in the udf using functionality that is not available to the view. We have one that checks the top 1 date from various tables - used to verify that all the data has been loaded correctly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have filled a table variable with some invoice data and it looks like this:
Invoice# Batch#
733F7F75 946
733F7F75 924
733F7F75 929
733F7F75 940
733F7F75 948
733F7F75 939
733F7F75 947
733F7F75 923
733F7F75 938
733F7F75 922
733F7F75 927
733F7F75 944
733F7F75 945
I have a basic select query on that temp table, something like this:
select InvoiceNumber, BatchNumber from @t order by InvoiceNumber
what can I do to get the highest batchNumber (Underlined result up there ^) from my table variable?
|
|
|
|
|
How about using max function?
|
|
|
|
|
Hi,
Use Max function to get the largest number from the table. The syntax is:
SELECT MAX(net_amount) FROM Sales;
SELECT MAX(company) FROM Sales WHERE net_amount > 100;
I hope this would be helpful.
Vinay
ComponentOne LLC.
www.componentone.com
|
|
|
|
|
i have insert trigger its function is insert one column in a table
and stored procedure its function to insert data to remained columns in the same table .
can i call the procedure within the trigger to perform row task completely
or change my scenario
regards
|
|
|
|
|
Why not try it and see? Its quicker than posting a message and waiting for a reply 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
trigger text:
Create trigger dbo.invoiceNum
on invoice_summary
fro insert
as
begin
insert into invoice_details(invoice_num)
select invoice_num from inserted
exec insertInvoiceDetails /*stored procedure to insert to remained columns in invoice_details table */
end
GO
when trigger fired it insert the Invoice_num
but when stored procedure fired it tries to insert new row and didn`t find invoice_num (Not Null column) then raised error thar Invoice_num don`t allow nulls
regards
|
|
|
|
|
So does your stored proc work outside the trigger, even with no parameters?
You really need to try and cure these problems yourself, you get no benefit from other people telling you what is wrong - how do you think we find out these things?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes you can,..
trigger is a plsql block and u can call procs / funcs in it.
|
|
|
|
|
Hello all,I'm facing problem with applying the MySql.Data.dll file in Reference of my VS 2003. When I try to add in,I saw a prompup and show this Error Message:
A reference to' C:\Documents and Setting\Visual Studio PRojects\Test\MySql.Data.dll' could not be added. This is not a valid assembly or COM component. Only assemblies with extention 'dll' and COM components can be referenced. Please make sure that the file is accessible, and that it is a valid assembly or COM component.
Anyone could guide me how to solve this problem?
Thanks for reading~!
Regards
Jack
|
|
|
|
|
|
is there anyone who have an experience in sql editor project, please send me an info on how to build it using oracle as the main server, i'm doing a project on my own companies sql scratch pad,from where it connects directly to oracle server where the editor also attach to the main companies application.
any info or sample codes is a big help.
xybr0xym0r0n
|
|
|
|
|
I need to run a standard select but I am not getting any results. The problem is, the user input goes something like '200' and the value in the field I am comparing to is '000200' (contains leading zeros, for some reason that is beyond me). Also, the data type for the column, in my table, is char and I also need to know if my parameter needs to be of the same type. Does anyone have any suggestions/ideas to best approach this situation?
Thanks,
T
|
|
|
|
|
The one who designed the database probably thought it would be a good idea to use char. Could be right, could be wrong... but without speculating on that, here is one solution:
create procedure FindMatch(@param varchar(100)) as
select * from TheTable where TheKey = right('000000' + @param, 6)
I'm assuming the actual column type is char(6). Otherwise, adjust the right invocation. Also, I've assumed you've already trimmed the parameter - otherwise, replace @param with ltrim(rtrim(@param)).
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Although the data type is char, but the data in the column is numeric then you can convert the field and then compare.
select * from table_name where cast(field_name as int) = 100;
Om Prakash Pant
|
|
|
|
|
Good Afternoon All
I have the Following Cursor
DECLARE VAL_CURSOR CURSOR FOR
SELECT PROPERTY_ID, NEW_MARKET_VALUE, NEW_ATTRIB_CODE,
ACTUAL_EXTENT,11 AS VAL_REASON_ID, 6 AS VAL_METHOD_ID
FROM PROPERTY_MASS
WHERE NEW =1
OPEN VAL_CURSOR
DECLARE @PROPERTY_ID INT
DECLARE @MARKET_VALUE INT
DECLARE @NEW_ATTRIB_CODE VARCHAR(12)
DECLARE @STATUS_DATE VARCHAR(100)
DECLARE @ACTUAL_EXTENT FLOAT
DECLARE @VAL_REASON_ID INT
DECLARE @PAR_METHOD_ID INT
SET @STATUS_DATE = GETDATE()
FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@VAL_REASON_ID,@PAR_METHOD_ID
WHILE @@FETCH_STATUS = 0
BEGIN
IF ISDATE(@STATUS_DATE)=0 PRINT CAST(@PROPERTY_ID AS CHAR(20))+'"'+@STATUS_DATE+'"'
FETCH NEXT FROM VAL_CURSOR INTO
@PROPERTY_ID, @MARKET_VALUE,@NEW_ATTRIB_CODE,@STATUS_DATE,@VAL_REASON_ID,@PAR_METHOD_ID
END
CLOSE VAL_CURSOR
DEALLOCATE VAL_CURSORbtw - why are you using a CURSOR instead of
when i run it gives me the Following Error
Msg 16922, Level 16, State 1, Line 26
Cursor Fetch: Implicit conversion from data type datetime to float is not allowed.
What is Wrong
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
