|
Hi all~
E.g "Select * from table_A where firstName Like '%" + string + "%'";
How to change the ad-hoc sql to prepared statement using operator 'like' and '%'?
Thanks
|
|
|
|
|
Not sure what you mean, but have a look at the sp_executesql stored procedure. That may point you in the general direction
Gregg
|
|
|
|
|
If using prepared statement,
The sql will like this:
cmd.CommandText = "Select * from table_A where firstName Like ?"
cmd.Parameter.Add("FirstName", "John");
cmd.Prepared();
cmd.ExecuteNonQuery();
Then if I would like to get the records which the firstName contains string "John". Using ad-hoc statement, "%" operator can be used. If using prepared statement, but to put "%" operator or using other way to achieve the result.
|
|
|
|
|
I'm using the following select statement to identify records that contain a non alpha and non numeric character (ex &,/). I would like to modify this script to identify what these characters are and provide a count for each of them. How can this be done?
<br />
Select LastName, count(*) as 'Counts'<br />
From DataTable<br />
Where (LastName like ('%[^ a-z0-9]%'))<br />
Group by LastName<br />
Thank you,
|
|
|
|
|
Hi, how could i create a temp table without using create table #aux?
i need to have a dinamic name for each user who is working with my app, its that possible?
thankx
|
|
|
|
|
Why do you need dynamic names? You can create a #table in every (different) connection with the same name. The #temp tables are 'connection bound'.
Wout Louwers
|
|
|
|
|
Hello people,
I want to run a query for customers whose last order was placed before the beginning of this month. Since there is no LastOrder field, and I can only use OrderDate, how do you write a statement that would take only the latest OrderDate that is less than last month. I really only know the basic stuff, I'm guessing I might need to write an if statement or a loop.
Thanks in advance.
|
|
|
|
|
JON10884 wrote:
I'm guessing I might need to write an if statement or a loop.
Writing loops in SQL is very inefficient, you should only consider this as a last resort. Database systems are fine tuned to work on sets of data so by extracting the set into individual rows and operating on the individual rows is very inefficient.
It would be helpful if you could post the relevant tables and columns
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
doesn't need a loop. You could use the horrible distinct clause
select * from
(
select distinct(customerid)
from orders
where orderdate < '2004-12-01 00:00'
) j
left join
(
select distinct(customerid)
from orders
where orderdate >= '2004-12-01 00:00'
) k
on k.customerid=j.customerid
where k.customerid is null
|
|
|
|
|
SELECT Customers.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID
HAVING MAX(Orders.OrderDate) < '20041201' Assuming you keep your orders in an Orders table, and your customers in a Customers table, of course!
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
You guys are the best! thanks a lot!
But one more question, I'm using Mike's algorithm, how do you select more fields to display?
|
|
|
|
|
As long as you want to select more fields from the customers table, you should include the fields in both the select and group by clause. Like:
<br />
SELECT Customers.CustomerID, Customers.Name <br />
FROM Customers <br />
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID<br />
GROUP BY Customers.CustomerID, Customers.Name <br />
HAVING MAX(Orders.OrderDate) < '20041201'<br />
Wout Louwers
|
|
|
|
|
I did that but I want the date to display and if you group by date it shows the duplicates.
|
|
|
|
|
I 'me not sure if this will work, but you could try:
<br />
SELECT Customers.CustomerID, MAX(Orders.OrderDate)<br />
FROM Customers <br />
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID<br />
GROUP BY Customers.CustomerID<br />
HAVING MAX(Orders.OrderDate) < '20041201'<br />
Wout Louwers
|
|
|
|
|
You the man! Thanks a ton!
|
|
|
|
|
I am new to SQL and I am currently working on a creating Calendar application. I store all data in the Calendar table in my database. Essentially I am trying to get the database to email a user when a trigger date is reached.
For example, if event is on some date I want the database to email a reminder to the member of staff who manages that event is coming in x number of days in advance of the expiration date.
Please help me to do so.
Thanks Kissy
|
|
|
|
|
Hello, I am creating a database with access and I have a question involving a macro I have created. In the Begging line of the macro immediately following the Warnings on field in design view, I have a RunApp Command that executes a batch file which takes about three minutes to complete. The problem is, the rest of the queries being used in this macro depend on the new data brought in by the batch file. Can anyone suggest a way to get around this so taht the batch file can execute before I continue executing the rest of the macro? Thanks in advance for any help!
Frank Lepkowski
|
|
|
|
|
hi there,
I have a collection of record in a data table which is populated from a dataset.
I would like to replace some field values in that data table. This replacement is
temporary (I am not going to update it back to database or dataset).
Eg:
EmpID EmpName
1101 My Name
1102 Your Name
What I need to do is, I need to replace all "Emp ID" values to it's corresponding encrypted value.
Answer will be
EmpID<t> EmpName
xabc<t> My Name
ssbc<t> Your Name
Now I am iterating through individual records of my data table and updating with my required value. The iteration took little more time, when I deal with lot of records.
Is there any better way to achieve my result.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Are you using SQL server or Access or ??
can your Encryption code be ported to Transact-SQL? If so I would simply create a function that encrypts your values and run it at the server and represents it in a view.
'Cause I'm living on things that excite me, be it pastries or lobsters or love...
I'm just trying to get by, being quiet and shy, in a world full of push and shove...
Jimmy Buffett - The Wino and I know
|
|
|
|
|
Not at all possible. I can't ship the encription algoritham with T-SQL. Sybase is the database.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
If you're not going to update the database with the new values, why bother putting the values in the dataset?
You could use some kind of intrinsic data object to the language you're using. Such as an array or a collection.
using System.Beer;
|
|
|
|
|
I want to use all methods which normally offered by dataset and data table. This is the main reason that, i am keeping all values in DataTable. Now do you think arraylist is good enough ?
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Well you've answered your own question. If updating the dataset is too slow, then you need to do it a different way.
using System.Beer;
|
|
|
|
|
HOw we can take sql server db backup in c#.net?
I have an idea if we create a .bat file and than run it in c#.net but the problem is that i don't know what in real i have to do.
Muhammad Waqas Butt
waqasb4all@yahoo.com
|
|
|
|
|
WaqasButt wrote:
HOw we can take sql server db backup in c#.net?
The same way as any other database access using C#, by using the SqlConnection and SqlCommand objects.
You can write some C# that makes a connection to the database (so long as the user making the connection has the sysadmin, db_owner or db_backupoperator role) then you can issue the BACKUP DATABASE command with the SqlCommand.ExecuteNonQuery()
For details on the BACKUP command you may with to read the SQL Server Documentation[^]
So, your C# code will look something like this:
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("BACKUP DATABASE...", conn);
cmd.ExecuteNonQuery();
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
