|
Try the following:
CREATE PROCEDURE usp_SearchItems
@pName NVARCHAR(75),
@pNSN NVARCHAR(16),
@pTPart NVARCHAR(20),
@pVPart NVARCHAR(50)
AS BEGIN
DECLARE @pQuery NVARCHAR(1024)
SET @pQuery = 'SELECT * FROM tblItems WHERE (fldInactive = 0)'
IF (@pName<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldItem LIKE ''%' + REPLACE(@pName, '''', '''''') + '%'')'
END
IF (@pNSN<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldNSN LIKE ''%' + REPLACE(@pNSN, '''', '''''') + '%'')'
END
SET @pQuery = @pQuery + ' ORDER BY fldItem'
--PRINT @pQuery
EXEC(@pQuery)
END
If you have any problems then uncomment the "PRINT" statement to find out what the final contructed query was.
The "REPLACE" function is being used to protect the stored procedure against quote characters. Do a search for "SQL Injection Attacks" to find out more about this problem.
Regards
Andy
|
|
|
|
|
You could use something like this. Default the parameters to null then coalesce or inull the parameter. The example proc below returns one result set but if you wanted to you could just as easily return one result set per parameter passed in.
Create Proc usp_SearchItems
@pName nvarchar(75) = null,
@pNSN nvarchar(16)= null,
@pTPart nvarchar(20)=null,
@pVPart nvarchar(50) =null
AS
Select *
From tblItems
Where (fldInactive=0)
and fldItem Like '%' + coalesce( @pName , fldItem) + '%'
and fldNSN Like '%' + coalesce( @pNSN , fldNSN)+ '%'
....
|
|
|
|
|
Can anyone help me why "N" is used before any property (eg. "IsUserTable")in OBJECTPROPERTY in Sql Server ?
|
|
|
|
|
It converts the string into a Unicode string.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Strictly speaking, th N' doesn't itself "convert" the string, but rather specifies that it should be converted (if needed) and stored/retrieved as a Unicode string by the server...
|
|
|
|
|
True. I should be more careful with my grammar.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
|
Hello,
I was hoping someone could give me a bit of information on the way I should be creating / handling my table adapters.
I have a persistant business object that is managing a number of database calls using a lot of different table adapters. The question I have is what the best practice is for creation of these table adapters. Is it better to persist a reference to each of my different table adapters over the life of my business object (i.e. create them all once, store them in class variables, then dispose of them all at the end) or is it better to create them as they are required (i.e a new table adapter created for each function call).
If someone could give a bit of an insight into what a table adapter is doing in terms of the connections created for it during instantiation that would be most helpful in helping me decide the most efficient approach.
Thanks for your help,
Adam
|
|
|
|
|
Since each creation of a new table adapter will require one or more network round trips to populate the date, it is better to persist a reference as long as the data contained is not likely to become stale rapidly. The trade off is that keeping populated table adapters around can consume a lot of memory if the tables are large.
|
|
|
|
|
You seem to be inferring that each instance of a table adapter stores the data from the table in the application. Are you certain this is correct as I assumed that data was only retrieved once the Select function of a table adapter was called in order to fill a data table object? (It is possible that my orginal message was not clear enough and you thought I was referring to data table objects rather than table adapters)
I always assumed that table adapters stored connection details for the appropriate database object - these are the resources I was hoping to find out about so that I can ensure my application is not leaving connections open inappropriately or unnecessarily hammering the database.
|
|
|
|
|
I'm not going to comment on Table Adapters as I don't use (like) them very much. However, you may want to look at using Query Analyzer to see what is happening with your connections (and queries). This tool is invaluable.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
NamelessParanoia wrote: ou thought I was referring to data table objects
Yes, I was confused...
Each call to Myadapter.Fill or Myadapter.Update will cause a network round trip. If you open the connection before calling Fill, then the connection will remain open, otherwise it is opened and closed for you. The data (and schema information) gets stored in the datatable/dataset.
|
|
|
|
|
 Hi Guy's
i am searching for the details about 'Fine Tuning of SQL-Query' can any one give me any good site or other details about this. I mean what steps are carried out to increase the performance of SQL Query? so the data will flow fluently please help me...
Thanks in advance
Sasmi
|
|
|
|
|
In SQL Server, you can use the Query Profiler. Also, you will want to look at the execution plan for your query.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi.can someone help me in writing code for retrieving records from database table.i should get the list of records when the page of my web aplication is loaded.
thanks in advance
|
|
|
|
|
Too vague. Have you attempted to search for a tutorial on ADO.NET?
|
|
|
|
|
How to Integrate SQL server 2005 Reporting Services into asp.net Application
|
|
|
|
|
hi,
what is the differance between connected and unconnected layer?
I read a book that tried to explain that but
I saw in the examples that in both layers
the commands are the same and you can retrieve data insert data
and so on.
so what is the differance?
|
|
|
|
|
A connected system will keep the connection to the database open. Disconnected means that the data is read in to memory, and the connection is closed - if the data changes, a connection to the database is obtained to persist the change and the connection is closed again.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have written a data access layer running under com+
Using the IBM Informix ODBC driver, I am able to select from a table with no problems, when I attempt to updtate the table, the error I receive is "Invalid string or buffer length" (all my googling seems to indicate that this message may have something to do with parameters)
I have set up a test application that performs identical operations to the com+ app. These are:
. Open Connection
. Create a select command
. Fill dataset adapter using select command and adapter
. Alter 1 single field in a single row
. Create an update command
. Call adapter.update
Now here's the weird thing.
The test app works fine, the com+ app fails. When I compare the SQLTrace between the 2 applications. The test application which works, generates many, many more ODBC instructions than the failing com+ app.
Each application is performing the identical workload through the same driver using the same the system.data.odbc objects.
Does anybody have any ideas? I've been banging my head against this for a while now and have no idea what to try next.
Much thanks.
-- modified at 3:35 Wednesday 10th January, 2007
|
|
|
|
|
The bug was caused by me having only one pair of eyes.
Adding another 2 eyes to the code revealed...
That If you use the ODBCParameter object, and the sourceColumn property contains a leading space...
Things don't work.
|
|
|
|
|
Hi,
I want to prevent the admin from being able to store 2 user records which both have the same username.
So far, I have pupulated a Dataset variable with the details, but I am unable to work it into an IF statement. Any suggestions??
Thank you
|
|
|
|
|
Have you tried ?:
string sql = @'
IF NOT EXISTS(SELECT * FROM foo WHERE user = @username) BEGIN
INSERT INTO foo (user) VALUES (@username)
SET @Success = 1
END ELSE BEGIN
SET @Success = 0
END';
bool bSuccess = false;
using(SqlConnection conn = new SqlConnection("connection string")){
SqlCommand cmd = new SqlCommand(conn, sql);
cmd.Parameters.Add("@username", SqlDbType.Varchar, 50);
cmd.Parameters.Add("@Success", SqlDbType.Bit);
cmd.Parameters["@username"].Value = DataSet.Tables[0].Rows[0]["Username"];
cmd.Parameters["@Success"].Direction = ParameterDirection.InputOutput;
conn.Open();
cmd.ExecuteNonQuery();
bSuccess = (bool) cmd.Parameters["@Success"].Value;
}
if(!bSucess)
{
//tell the user
}
|
|
|
|
|
Dear All,
I am bit new to this programmming world. Currently i am making web application using C# ,asp.net and sql. in a web page there i have listbox with multiple selection option. now with single option i am able to save the form data. but problem occur when there is multiple option. as i am confuge with how can i based on selection (no of selected data). i can save the record. please guide me.
thanks
regards
imran khan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)