[ACCESS 2003] create an SQL query - Database Discussion Boards

Re: kindly help excel query statement in vb.

Bob Zagars17-Jan-06 20:36

17-Jan-06 20:36

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

ADO 'Background Fetch Size' property VC++

tuxyboy16-Jan-06 1:33

tuxyboy

16-Jan-06 1:33

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?

Export Data

Tiger45615-Jan-06 21:44

Tiger456

15-Jan-06 21:44

Hai

How can i export data in SQLServer 2005 Tables as SQL insert Script. Is there any free utility for this purspose.

Regards,

Re: Export Data

Colin Angus Mackay15-Jan-06 22:59

Colin Angus Mackay

15-Jan-06 22:59

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

Re: Export Data

Tiger45616-Jan-06 17:39

Tiger456

16-Jan-06 17:39

Thanks for your reply.

dsn confusion

TofuBug2415-Jan-06 10:03

TofuBug24

15-Jan-06 10:03

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

Re: dsn confusion

tuxyboy16-Jan-06 1:39

tuxyboy

16-Jan-06 1:39

http://support.microsoft.com/kb/q165866/

[ACCESS 2003] create an SQL query

Sasuko15-Jan-06 0:50

Sasuko

15-Jan-06 0:50

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?

Re: [ACCESS 2003] create an SQL query

Dave Kreskowiak15-Jan-06 16:42

Dave Kreskowiak

15-Jan-06 16:42

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

File xsd

AlbePes14-Jan-06 23:06

AlbePes

14-Jan-06 23:06

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

Check on table before adding

gharry14-Jan-06 9:35

gharry

14-Jan-06 9:35

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

Re: Check on table before adding

Colin Angus Mackay15-Jan-06 1:02

Colin Angus Mackay

15-Jan-06 1:02

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

List of NT Users In Server Domain SQL Server

Bob Zagars14-Jan-06 8:28

Bob Zagars

14-Jan-06 8:28

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

procedure and datetime

dexom13-Jan-06 19:37

dexom

13-Jan-06 19:37

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

Re: procedure and datetime

PolarbearDK14-Jan-06 4:56

PolarbearDK

14-Jan-06 4:56

Don't use: ... NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111)
Just use: ... NgayNhap >= @FromNgayNhap

Re: procedure and datetime

Muhammad Javed Khan19-Jan-06 1:29

Muhammad Javed Khan

19-Jan-06 1:29

You have to concatenate " ' " to dates....

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
Suspicious | :suss:

Thoughts on how to load data...

PolarbearDK13-Jan-06 13:41

PolarbearDK

13-Jan-06 13:41

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?

Sorting using BindingSource with master-detail relationships

kooks2k13-Jan-06 12:48

kooks2k

13-Jan-06 12:48

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

ADO Item Collection Mystery

mjackson1113-Jan-06 12:22

mjackson11

13-Jan-06 12:22

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

Re: ADO Item Collection Mystery

mjackson1114-Jan-06 10:11

mjackson11

14-Jan-06 10:11

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

jszpila13-Jan-06 5:56

jszpila

13-Jan-06 5:56

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

Re: removing "similar duplicates" from select results

Michael Potter13-Jan-06 10:49

Michael Potter

13-Jan-06 10:49

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

Re: removing "similar duplicates" from select results

jszpila13-Jan-06 10:56

jszpila

13-Jan-06 10:56

You know, I can't tell you how many times I did something just a *little* different than that without the results Mad | :mad:

. All it took was just a that MIN. Thanks for your help!

-------------------
abort, retry, fail?

Using Grant

Tiger45612-Jan-06 23:46

Tiger456

12-Jan-06 23:46

Hai

I am using SQL server 2005 BETA . After logging into SQL Server using WindowsAuthentication.
When I try to run comand GRANT CONNECT TO [MyUser] on query Analyser.
An error message is returned
Cannot find the user 'MyUser', because it does not exist or you do not have permission.
I had created a usernamed 'MyUser'. How can i solve this problem

Regards

SQL 2005 ConnectionString

victor_ba8512-Jan-06 20:25

victor_ba85

12-Jan-06 20:25

hi, i'm trying to connect to a sql connection, in my computer, but it keeps sending me this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

i tried to use the : "server=(local)" in the connectionstring, but it doesn´t matter, what can i do?, is there a way that a i can modify the default settings?...where?

Thanks.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.