|
Yes.
And, by the way, do you know your caps lock key is broken?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
i have a problem on adding a where clause on my excel sql statement this is my excel detail
abc company
ppa_codes amount
ab 2500
cd 2300
i only want to select the rows starting with ppa codes upto cd.
what are the possible statement that i can use?
i am using "select * from sheet1$" but it includes the abc company and the null value next to it. what are the possible columns that i'm going to use on my where clause, for example "select * from sheet1$ where ___"
thanks in advance tahnk you again
september
|
|
|
|
|
To write something you have to know more about your values being a string. If the alpha characters range is "aa" thru "zz" also if the numbers part of the string "0000" thru "9999" and if between them the value does not matter. Then I could write you something.
Bob Zagars
|
|
|
|
|
There is one thing I can't figure out with VC 6.0 & ADO. How can I change the 'Background Fetch Size' (DBPROP_ASYNCFETCHSIZE) property of the recordset running asynchron? It's the number of records retrieved between two fetchprogress events. It's default is 15.
It's easy to do it in VB, but in VC++ before opening the recordset, I don't have any dynamic properties available, after executing the SQL the property is read only. I suppose if it's possible in VB it must be possible in VC++ 6.00 as well. I've hard times finding any docs about it even in SDK. As far as I know this OLEDB property is supported by the Cursor Service, which is initilaized when changing cursor to adUseClient.
Any idea?
|
|
|
|
|
Hai
How can i export data in SQLServer 2005 Tables as SQL insert Script. Is there any free utility for this purspose.
Regards,
|
|
|
|
|
anig1234 wrote: How can i export data in SQLServer 2005 Tables as SQL insert Script.
What I do is have my SELECT statement output the appropriate script
SELECT 'INSERT INTO MyTable(Column1, Column2, Column3) '+
'VALUES('+Column1+','+Column2+','+Column3+');'
FROM MyTable
Then I can copy the script out of the query analyzer into a text file (actually you can have the query analyser write it directly to a file, but you have edit it afterwards to remove the other bits and pieces it writes out)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
|
I'm not sure if i should have posted this in the asp.net forum or not but it's mostly a database sort of a question.
I've been tasked at work to write a library that will parse a tab delimited text file and filter the data and stuff it into a normalized sql server database.
basically the text file is a straight textdump from the clients main offices product database and the sql server database the info's being entered into is for their website to pull from for their online catalog.
now to make my life easier i set up a dsn using microfts text/csv ODBC driver the actualy library is done and works beautifuly. my problem (and it might not even be a problem) is that i'm confused on how to distribute the dsn that loads the textfiles.
basically i need to set the dsn to point to the "dump" folder on their website's server so that when they upload a new copy of the file the library knows where to find it to pull the information from right now the dsn is just a user one pointing to a logical disk file on my home computers desktop (i do alot of my testing at home)
sorry if this is a little simple of a question i deal more with sql server and straight connection strings and for some reason the dsn thing just confusses the hell out of me
thanks for the help
guys and gals
Ryan
|
|
|
|
|
http://support.microsoft.com/kb/q165866/
|
|
|
|
|
i need to create a query but i don't want to make it using View mode or wizard, but just as in SQL server inserting a SQL text query and run.
Is it possible?
|
|
|
|
|
All you have to do is create a new query then click the View menu, then SQL.
Type away...
Keep in mind that Access doesn't support batch queries like SQL Server does.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi all,
I am a beginner, and my questions are: what is the function of a schema xsd in a solution with access to a database?
What can I do with a data set created starting from xsd file?
Thanks a lot
Bye
AlbePes
|
|
|
|
|
The following question:
A user enters the name of a horse
The program then checks for this name in a FEI table file
If found the rest of the data must be entered and the total are written to a table called Horses and the resulting ID to a third file to connect a rider afterwards.
Is it possible to check in the SQL command if the the is on the first table and if the result is true check the other database and is that result is tru return the ID in that second Table, something like
IF(SELECT Horse_name, Horse_FEI From FEI WHERE Horse_Name LIKE inputName%)=True Then
IF(SELECT IDHorse From Boekelo_Horses WHERE Horse_ID = FEI.Horse_FEI)= True Then
Get all data and show edit screen
END IF
END IF
|
|
|
|
|
gharry wrote: IF(SELECT Horse_name, Horse_FEI From FEI WHERE Horse_Name LIKE inputName%)=True Then
IF(SELECT IDHorse From Boekelo_Horses WHERE Horse_ID = FEI.Horse_FEI)= True Then
Get all data and show edit screen
END IF
END IF
The structure you are looking for is something like this:
IF EXISTS(SELECT * FROM FEI
INNER JOIN Boekelo_Horses AS bh ON bh.Horse_ID = FEI.Horse_FEI
WHERE Horse_Name LIKE @inputName)
BEGIN
SELECT 1;
END
ELSE
BEGIN
SELECT 0;
END
You can then call this SqlCommand with ExecuteScalar() and it will return a 1 for true, and 0 for false. Your calling application can then decide to take appropriate action depending on the outcome.
I've also taken your two SQL statements and rolled them in to one as they were disjointed, the second IF statement seemed to need input from the first that it was never going to get.
In a IF EXISTS(SELECT ....) structure, the list of columns in SELECT is ignored so you don't need to specify them, and specifying * is about the only time it is not inefficient to do that as the Query Optimiser already knows it needs no fields, it is just looking for one row with any data before it will stop processing the statement.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Can someone help me? I can get a list of NT groups (xp_enumgroups) but how do I get a list of NT users not autorized yet for the database?
Bob Zagars
Senior Software Engineer
|
|
|
|
|
I have proc as below: It don't understand the datetime type that I input I don't know why please help me. Thanks.
ALTER PROCEDURE SP_PHIEUNHAP_Search
(
@FromNgayNhap datetime,
@ToNgayNhap datetime,
@CheckNgayNhap bit,
@MaNCC int,
@FromNgayLap datetime,
@ToNgayLap datetime,
@CheckNgayLap bit,
@NguoiLap int
)
AS
DECLARE @sql nvarchar(500)
DECLARE @flag bit
declare @tungay datetime
declare @denngay datetime
SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien '
SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG '
if (@CheckNgayNhap = 1 )
begin
SELECT @sql = @sql + ' where NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ' and NgayNhap <= ' + CONVERT(NVARCHAR, @ToNgayNhap,111)
set @flag = 1
end
if(@CheckNgayLap = 1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111)
else
SELECT @sql = @sql + ' where NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111)
set @flag = 1
end
if(@NguoiLap != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
else
SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
set @flag = 1
end
if(@MaNCC != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
else
SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
set @flag = 1
end
SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC '
EXEC sp_executesql @sql
-- modified at 4:31 Saturday 14th January, 2006
|
|
|
|
|
Don't use: ... NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111)
Just use: ... NgayNhap >= @FromNgayNhap
|
|
|
|
|
alter PROCEDURE SP_PHIEUNHAP_Search
(
@FromNgayNhap datetime,
@ToNgayNhap datetime,
@CheckNgayNhap bit,
@MaNCC int,
@FromNgayLap datetime,
@ToNgayLap datetime,
@CheckNgayLap bit,
@NguoiLap int
)
AS
DECLARE @sql nvarchar(500)
DECLARE @flag bit
declare @tungay datetime
declare @denngay datetime
SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien '
SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG '
if (@CheckNgayNhap = 1 )
begin
SELECT @sql = @sql + ' where NgayNhap >= ''' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ''' and NgayNhap <= ''' + CONVERT(NVARCHAR, @ToNgayNhap,111) + ''''
set @flag = 1
end
if(@CheckNgayLap = 1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + ''''
else
SELECT @sql = @sql + ' where NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + ''''
set @flag = 1
end
if(@NguoiLap != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
else
SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
set @flag = 1
end
if(@MaNCC != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
else
SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
set @flag = 1
end
SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC '
EXEC sp_executesql @sql
|
|
|
|
|
I’ve been working with test and deployment of a standard database based application. It really bugs me how some of the data loading tasks are performed right now.
Examples:
During unit testing, the database is reset to a known state by truncating/deleting all data (this is done by a script), insert some basic system data using SQL insert statements, and load remaining data from XML using Microsoft SQL XML Bulk-loading.
During first deployment, a new database is setup during installation, but without data. Afterwards a database backup is restored and data is transferred from backup database to new database. (Ugly I know!).
During following deployments (updates), SQL script inserts, updates and deletes data during installation.
There must be a better way to complete ALL above tasks! Do you have any ideas?
I’ve been thinking about defining an XML data format to describe the necessary steps to perform the load, and make a .NET component to process the data and update the database.
The format should be:
• Simple.
• Easy to read for humans (with XML knowledge).
• Able to insert,delete/truncate,update data.
• Readable using a forward only XML reader.
• Able to skip loading some sections (example: load data unless data is already present).
• Able to handle nested data where child records receives auto generated keys from parent (ID propagation I think it’s called).
This is a very early and simple draft of how a data file might look:
<?xml version="1.0" encoding="UTF-8"?>
<data>
<table name="Language">
<truncate/>
<append>
<row LanguageID="1" LanguageText="Danish"/>
<row LanguageID="2" LanguageText="English"/>
<row LanguageID="3" LanguageText="Ger"/>
<row LanguageID="4" LanguageText="Goof"/>
</append>
<update>
<row LanguageID="3" LanguageText="German"/>
</update>
<delete>
<row LanguageID="4"/>
</delete>
</table>
</data>
Has someone else already made something that can do this?
Any thoughts?
|
|
|
|
|
Hi
i have two tables lets say t_order and t_customer
the t_order(FK: CustomerID) is the child table and t_customer (PK: ID)is the parent table.
i have bindind the two tables to a datagrid using BindingSource so the customer Name appears instead of CustomerID,also i have managed to filter them using Parent(RelationShip) function ,but i does not work for Sorting.
so is there any way for sorting a table using a detail in other related table?
thanks
|
|
|
|
|
I am fetching records from a SQL database. I create an ADODB::_RecordsetPtr ptr. In my code this works:
newID = ptr->Fields->Item["ID"]->Value;
These don't:
newID = ptr->Fields->Item[0]->Value;
newID = ptr->Fields->Item[1]->Value;
newID = ptr->Fields->Item[0]->GetValue();
newID = ptr->Fields->GetItem(0)->Value;
newID = ptr->Fields->GetItem(0)->GetValue();
The SQL string creatint the recordset is "SELECT ID FROM COMPONENTS WHERE ACTIVE<>0"
Any idea why the ordinal approach doesn't work? I would like to save the overhead of comparing strings inside ADO as this may be called up to 2 million times in valuing the database.
Thanks,
Mark Jackson
|
|
|
|
|
Duh, the parameter to GetItem is a variant not an int, 0 = NULL that's why it was bombing. What I get for coding early in the morning....
Mark Jackson
|
|
|
|
|
Hello everyone,
I'm doing a query for user names and email address from a database. Unfortunately, due to circumstances out of my control, data is poorly/incorrectly formatted and sometimes leads to *mostly* duplicate entries for the same thing.
Example:
SELECT DISTINCT(email), firstname + ' ' + lastname <br />
FROM users <br />
WHERE compdate BETWEEN '1/1/2006' AND '1/31/2006' <br />
AND email IS NOT NULL <br />
AND email <> ' ' <br />
ORDER BY TLH_1_1240
will return the following:
1 test01@test01.com John Doe
2 test01@test01.com John T. Doe
As you can see, the email is the same and the name is similar enough, at least for my purposes.
The DISTINCT is obviously working in a way that I don't understand and carrying over to distinct names. I just want distinct email addresses and the name that comes up associated with them first. Your thoughts?
Thanks in advance for your help!
-------------------
abort, retry, fail?
-- modified at 11:57 Friday 13th January, 2006
|
|
|
|
|
Try something like:
SELECT
email,
MIN(firstname + ' ' lastname) AS fullname
FROM
users
WHERE
compdate BETWEEN '1/1/2006' AND '1/31/2006' AND
email IS NOT NULL AND
email <> ' '
GROUP BY
email
|
|
|
|
|
You know, I can't tell you how many times I did something just a *little* different than that without the results . All it took was just a that MIN. Thanks for your help!
-------------------
abort, retry, fail?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
