|
when you have more than one filter criteria It's always a good practice to do it in a single dynamic query(T-Sql String). sp_executesql command can be used to execute a string and also it support's substituting any paramerter values that is specified in T-Sql String. I have gone through your Query and written a stored procedure. Hope it will help you to modify your's...
Create Procedure sp_undertakingsSelect
@Filter NVarchar(50),
@DateFrom DateTime,
@MatterRef NVarchar(200),
@sortBy NVarchar(50)
AS
Set NoCount ON
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
Set @SQLQuery = 'Select * From undertakings where (1=1) '
If @Filter = 'Active'
Set @SQLQuery = @SQLQuery + ' And Discharged = 0'
If @Filter = 'Discharged'
Set @SQLQuery = @SQLQuery + ' And Discharged = 1'
If @DateFrom Is Not Null
Set @SQLQuery = @SQLQuery + ' And DateTaken >= @DateFrom'
If @MatterRef <> ''
Set @SQLQuery = @SQLQuery + ' And MatterRef LIKE '''+ '%' + @MatterRef + '%' + ''''
If @sortBy = 'matterRef'
Set @SQLQuery = @SQLQuery + ' Order By MatterRef '
If @sortBy = 'Date'
Set @SQLQuery = @SQLQuery + ' Order By DateTaken'
Set @ParamDefinition = '@Filter NVarchar(50),
@DateFrom DateTime,
@MatterRef NVarchar(200),
@sortBy NVarchar(50)'
Exec sp_Executesql @SQLQuery, @ParamDefinition, @Filter, @DateFrom, @MatterRef, @sortBy
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)
ErrorHandler:
Return(@@ERROR)
GO
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Worked a treat, many thanks for taking the time to respond to my question.
|
|
|
|
|
Ed Hill _5_ wrote: many thanks
You're most welcome
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
can i ask for the codes on "password problem" like what you have in your system. codes on how the user can retrieve his password if ever he forgot it. in php or sql codes please. with detailed instructions... thank you! More Power Guys!
|
|
|
|
|
If you are using .NET 2, use the Membership provider and let it take care of it all for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
LyndonJohn wrote: the codes on "password problem" like what you have in your system
All my code that deals with passwords stores them as salted hash values. You cannot retrieve passwords stored as a salted hash.
|
|
|
|
|
I hash my passwords with salted hashes, so there isn't any way to get it.
LyndonJohn wrote: how the user can retrieve his password if ever he forgot it.
The user shouldn't be using my software if he/she is retarded enough to lose their password. I do have admin tools that allow resets specific to my apps.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hi
Is it possible to write a query that will return the datatype of each field as opposed to actual data?
tks
Richard
|
|
|
|
|
Look at the INFORMATION_SCHEMA.COLUMNS to find the data type of columns in the database. (Assums SQL Server - You didn't say, but it is the most common database used on this forum)
|
|
|
|
|
Hi Colin
Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
|
|
|
|
|
RichardBerry wrote: Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
It is a view, not a table.
It is on the Master database and will be picked up regardless of which database you are in.
I'm also curious, did it not occur to you just to type SELECT * FROM INFORMATION_SCHEMA.COLUMNS or search for references to it on the internet?
|
|
|
|
|
Hi Colin
Thanks that was exactly what I was looking for.
Colin Angus Mackay wrote: I'm also curious, did it not occur to you just to type SELECT * FROM INFORMATION_SCHEMA.COLUMNS or search for references to it on the internet?
No it did not occur to me, since I looked through all the databases on the server, and could not find a table with that name, so I thought perhaps this was a table that was not in my database. I had never heard of a 'view' before, so I am currently GOOGLING 'SQL View' to learn more about that. It is sometimes difficult for a NOVICE (myself) to ask the right questions or look in the right places for information.
|
|
|
|
|
I use ExecuteReader and then ask the DataReader for the DataTypes.
DataReader.GetSchemaTable()
or
DataReader.GetFieldType()
|
|
|
|
|
RichardBerry wrote: Is it possible to write a query that will return the datatype of each field
In sql Server, there is a system stored procedure (sp_columns) which returns column information for the specified table or view. You can execute that to get the DataType of the columns.
Exec sp_columns [@TableName]
Regards
J O H N ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Hi John
Thanks for your reply. I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE..... Etc
It would actually be great if I could get all table data for the database in the above format.
When executing 'Exec sp_columns [@TableName]' I tried replacing 'TableName' with various strings but cant seem to get any data back. I tried *, vektron.scheme.units, scheme.units, units. But none seemed to work. Typically to execute a normal Select query, I would use:
SELECT * FROM vektron.scheme.units
Any idea what I could be doing wrong?
|
|
|
|
|
RichardBerry wrote: I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
return's no data? How come? It works fine in my system...
I have a table with the name tblProducts and I executed the stored proc as below...It return's the complete information...
Exec sp_columns [tblProducts]
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi John
Got it right - my syntax was wrong - I was putting in the '@' before the table name.
BTW, is there a way to get more the info on all tables at once? I tried
Exec sp_columns [*] but that gave no data back?
Thanks for your help!!
|
|
|
|
|
RichardBerry wrote: BTW, is there a way to get more the info on all tables at once?
EXEC sp_tables will list all the tables in the current Database
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi,
I have a table with more than one row for a particular employee. Want to take one by one and put it to different columns in another table.
This example below will make it clear, please check.
e.g.,
First table
Emp_id dependents
100 aaa
100 bbb
200 ccc
200 ddd
second table
emp_id dependent1 dependent2
100 aaa bbb
200 ccc ddd
How can I make the first table to the format of second table.
Thanks in advance for the help.
Thanks,
Trav
trav1
|
|
|
|
|
|
i am trying to connect to MS Access database using DSN and i am getting following error :
Microsoft OLE DB Provider for ODBC Drivers Error '80004005'
and
SQL Set Connection Attr failed
|
|
|
|
|
Ok, what language are you using?
Whats the code where your connecting?
Whats the connection string?
|
|
|
|
|
i am using VBScript
i am getting error at line where i have written
rs.open "DSNname"
|
|
|
|
|
Post all your code. No one can assist you unless you show us what you have done.
Paul Marfleet
|
|
|
|
|
Hi,
I am need of information regarding Database status during Database Refresh operation in sql server 2000.
The application uses 2 DB’s say db1 and db2. The db1 (read-write) and db2 (read only) resides in the server1. The db2 resides in server2 also.
Requirement: When the db2 in server1 is down i.e., when the DB refreshes, it throws an error and the application will not be able to access the DB tables. In that case, we should connect to the server2 and access db2.
Problem: In order to switch from server1 to server2, first we should check the status of db2 during refresh in server1.
Error during DB refresh:
Msg 927, Level 14, State 2, Line 1
Database 'Database_name' cannot be opened. It is in the middle of a restore.
If we can find the status of DB during refresh operation, this scenario can be handled and can be switched to server2.
Thanks,
Sarayu
Sarayu
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
