|
Dear Friends,
Consider the table:
id country
=== =======
1 Australia
2 Burma
3 Canada
4 USA
I've an input string:
set @input = 'I live in Australia'
Now i need to find out that is there any substring of my input string present in the 'country' column of above table.
example, in my sample string: "I live in Australia", the substring 'Australia' is present in the table above. Thats what i like to know. So how can i make such SQL query ??
|
|
|
|
|
|
I would assume you are using a stored procedure? The key elements in a query like this are the LIKE keyword and a % sign on both sides of your search variable. Something like the following should do it for you:
SELECT id, country FROM [YourTable] WHERE country LIKE '%' + input + '%'
HTH
-Nick Parker
|
|
|
|
|
... Is SQL Server ideal for client-server application using a peer-to-peer connection? What's the difference if there's a hub that would connect 2 computers rather than having a peer-to-peer connection?
|
|
|
|
|
fume wrote:
... Is SQL Server ideal for client-server application using a peer-to-peer connection? What's the difference if there's a hub that would connect 2 computers rather than having a peer-to-peer connection?
The network makes no difference. SQL just uses the stack that the OS supplies. It does not care if you are connected via Satellite, 10mbit or cross-over.
Cheers
Mike
Johannesburg, South Africa
|
|
|
|
|
Not entirely true...
SQL uses a choice of protocols, TCP-IP being one, Named Pipes another. If the OS does not provide one of the supported protocols, SQL would not be accessible outside the local node.
I doubt that it would work over a direct serial connect unless it were carring one of the suppoerted connection protocols.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
I have a DataSet that is filled by a SqlDataAdapter. If a row is deleted within the app, this is properly reflected in both the DataSet and central database when Update() is called on the SqlDataAdapter.
However, if records are deleted from the database by some other process (outside the application), and I subsequently call the Fill(dataset.table) method of the SqlDataAdapter, the rows that were deleted from the database still seem to exist in the DataSet.
Any ideas how I can ensure that if records have been deleted from the database, that these deletions are fully reflected in the DataSet?
-- James --
|
|
|
|
|
The deleted rows are likely only visible in the deleted rows view (Datatable retains all your original rows unless you clear it first, or call datatable.acceptchanges after you call Fill to "refresh" the data. Same applies to modifications made by others. Strange behavior perhaps, but that how it's documented.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Calling AcceptChanges on the datatable after the Fill did not seem to remove the extra records. However, calling Clear on the table just before calling Fill does the trick. I was a little wary of using Clear for fear of deleting my database, but I see it only clears the table and doesn't mark records for deletion.
Thanks for the help!
-- James --
|
|
|
|
|
In Access, there's a field in my table: Count number;
I try to use it in my project like this:
DataRow row;<br />
<br />
public string Count<br />
{<br />
get<br />
{<br />
if ( row != null && row["Count"] != null ) <br />
{<br />
return row["Count"].ToString();<br />
}<br />
return "";<br />
}<br />
set<br />
{<br />
if ( row != null ) <br />
{<br />
row["Count"] = ( value == "" ) ? null : value; }<br />
}<br />
}
but when I set the do this: this.Count = ""; I got an exception. Acctually what I want to do here is same as "update count = null" in sql.
How can I fix that?
Thanks in advance.
|
|
|
|
|
There is VSS in Visual Studio 6.0. But I can't find it in Visual Studio .Net
Anyone know why?
Thanks in advance.
|
|
|
|
|
I believe VSS is only in the Enterprise Edition of VS.NET (along with Visio) and needs to be explicitly installed
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
But when I installed visual studio .Net, I chose fully installed. Is it isolated from VS.Net now?
|
|
|
|
|
In my (RTM) VS.NET Enterprise Edition CDs, VSS is on a CD by itself and, if I recall correctly, had to be installed separately.
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
VS.NET integration with source-safe can be found on the File menu, under the Source-Control option.
I'm not sure if the installation is isolated from VS.NET as I did an upgrade so my Visual Source Safe is still under my Microsoft Visual Studio 6 start menu item.
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
For Reports / Statistics I have a Table with the Log Data. The Problem is that for some days i have several entries and for others i have none. I can get the number of entries for one day with the count() and grouping the datepart()s, but then I don't have entries for the days that havn't any Log Data.
From these Data I have to build a table report (one table per month, with the days listed horizontaly and the type of entry vertically (also grouped). This looks not very nice, if you only have 4 days in a month ...
Even worse I have to build a Chart, where this looks very strange when you have only some days in a month.
The Reports and Charts I build with Cristal Reports for .NET.
Is there a Solution in the SQL Server or maybe in the Cristal Reports?
Thanks
Roland
|
|
|
|
|
Is it possible to isolate more than 1 DataAdapers Update into single ADO.NET transaction? I want to insert sam rows in aplications log table, but only if previous Update was ok. And if log insert fail, I want to roll back whole transaction. When trying to implement it, I got feeling that DataAdapter Update method itself call Commit. If it is not possible, any idea aboat roundtrip? Thanks,
Risbom
|
|
|
|
|
The following code will allow you to have SQL Server script objects with out using Enterprise Manager.
I found this code at
http://www.sqlmag.com/Forums/messag...&threadid=18602
Enjoy
Here is what ya do.
in this code I am scripting all the Procedures.
-----------------------------------------------------------------------
declare @cmd2 varchar(255)
declare GetProcedures cursor fast_forward
for
select 'sp_helptext [' + name + ']' from sysobjects where type = 'P'
open GetProcedures
fetch next from GetProcedures into @cmd2
while @@fetch_status = 0
begin
exec (@cmd2)
print 'GO'
fetch next from GetProcedures into @cmd2
end
close GetProcedures
deallocate GetProcedures
-------------------------------------------------------------------
This works Just change the type to match what ever you want to script.
If you are not sure of the Type then run this first
SELECT * FROM SYSOBJECTS
William O'Malley
|
|
|
|
|
All my previous projects have involved me being on site to install my software. That meant I could create a SQL script of my database structure and use query Analyzer to run the script and create the database tables.
However, I know find my self in the position of doing a "shrinkwrap" piece of software where the user will be installing the software.
What is the recommended way of creating an SQL server database? I've thought about writing an executable that runs an SQL script but wonder if there are better ways.
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
If the data structures never vary from install to instal, you could build an empty template database, detatch it from youre server, distribute it as part of your install, and run a very simple script to attach it. Only downside is the size of the template db (likely 1MB)...
Another altenative is to script the schema, including a Create Database, and read it all with osql (command line tool, always present even with MSDE).
I have used both of these sucessfully. The first is the most foolproof, and fastest from the users perspective.
The hardest arithmetic to master is that which enables us to count our
blessings. - Eric Hoffer
|
|
|
|
|
Thanks for the response.
I'm currently experimenting with running the SQL schema script from Installshield. This seems to be easy enough to do. The bastard bit is working with Installshield to do the user-interface and script flow to ask the user for the server name, passwords etc.
I hate Installshield.
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
So do I.
In my case I wrote a generic installer engine that can do COM+ component installs, database creation, as well as version/date checked file copying, file compression et cetera, all driven by XML, with a simple WTL-based wizard UI. Robust enough for the kind of stuff my own company does, and easy to modify & test.
Steve S
|
|
|
|
|
Hi,
Three tables... Authors, Books and (Books/Authors), a book can have several authors, and author can have several books ( quite classic  ).
My question is... I would like to get in a select a list of books and his authors ( in the same row), e.g.
One Book - Mark Hammon, John kirk
Antoher Book - Another Author, Another thing
I know with number there are quite powerful methods ( count, avg, ...), but with strings ? could it be possible to concat this strings ??
I think with SQL Server maybe it's possible but with Ms Access... , thanks in advance
Braulio
|
|
|
|
|
You can easily concatenate columns in the same row with the + operator (might be & in Access, because the expression engine is based on VBA). However, I don't know any way of aggregating rows into a string.
(Note to MS: it'd be really useful if there was a way to define your own aggregate functions...)
I think you'll have to do this client side.
|
|
|
|
|
Hi,
I found a solution with SQL Server, a bit ugly T-SQL thing...
<br />
CREATE TABLE foo<br />
(c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL CHECK (c2 NOT LIKE<br />
'%,%'), PRIMARY KEY (c1,c2) )<br />
<br />
INSERT INTO foo VALUES ('Tom','Car')<br />
INSERT INTO foo VALUES ('Tom','House')<br />
INSERT INTO foo VALUES ('Tom','Plane')<br />
INSERT INTO foo VALUES ('Tom','Boat')<br />
INSERT INTO foo VALUES ('Jim','House')<br />
INSERT INTO foo VALUES ('Jim','Boat')<br />
<br />
CREATE TABLE Results<br />
(c1 VARCHAR(10) PRIMARY KEY, c2list VARCHAR(100) NOT NULL)<br />
<br />
INSERT INTO Results<br />
SELECT c1,MAX(c2) FROM foo GROUP BY c1<br />
<br />
WHILE @@ROWCOUNT>0<br />
UPDATE Results<br />
SET c2list =<br />
(SELECT MAX(c2)<br />
FROM foo<br />
WHERE foo.c1 = Results.c1 AND<br />
foo.c2<LEFT(c2list,CHARINDEX(',',c2list+',')-1))<br />
+ ', ' + c2list<br />
WHERE EXISTS<br />
(SELECT *<br />
FROM foo<br />
WHERE foo.c1 = Results.c1 AND<br />
foo.c2<LEFT(c2list,CHARINDEX(',',c2list+',')-1))<br />
<br />
SELECT * FROM Results<br />
<br />
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
