Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 29th, 2003, 05:47 PM
Registered User
 
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Microsoft Text Driver Use

Anyone used the Microsoft Text Driver in code to connect to a text file for input? You should be able to create a DSN-less connection and then use that as an input file with SQL etc. It looks like it can be used under Provider=Microsoft.Jet.OLEDB.4.0; with extended properties as text and by Driver=(Microsoft Text Driver (*.txt *.csv);
 
Old September 30th, 2003, 02:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I've not used it from ASP, only from VB, but as long as the anon user has permissions to the file it shouldn't be a problem. Are you looking for the syntax for a connection string? Try this:
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\Temp\;" & _
       "Extended Properties=""text;HDR=No;FMT=Delimited"""

Note that the directory goes in the DataSource part of the conn string (I've never found a reference to what Extended Properties can be used). The filename goes in the actual SQL, like this:
oRs.Open "SELECT * FROM fileName", oConn, _
         adOpenForwardOnly, adLockReadOnly, adCmdText

Is that what you were looking for?
Phil
The Following User Says Thank You to pgtips For This Useful Post:
 
Old September 30th, 2003, 04:02 PM
Registered User
 
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That's mostly what I have found. Any yes I have not found any docs on the methods at all even in MSDN. I see this form as you state with the Extended properties and then I see a form as below:

    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=c:\somepath\;" & _
    "Extensions=asc,csv,tab,txt"

Both seem to have methods and properties that don't seem to be documented anywhere that I can find. They also discuss a Schema.ini file that defines the text file as a table type that can then be used by the SQL SELECT statement.

How did you define the text file to the program?
 
Old October 1st, 2003, 02:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

That other form is just a different driver, the ODBC text driver.

What do you mean by "define the text file to the program"? Are you talking about accessing the "fields" in the file? If so, I just use ordinal position, e.g. oRs.Fields(0).Value. You can use a schema.ini to define field names if you like, but you don't have to.

rgds
Phil
 
Old October 1st, 2003, 05:00 PM
Registered User
 
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think it finally clicked for concept on the textfile driver. The link setup is to the directory not the file. The files in that directory are the tables. Conceptually it is the same as the SQL where you link to the SQL database which is like a directory and then call out the tables which are files within the directory.
 
Old October 2nd, 2003, 02:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yep, you've got it ronhawker, that's exactly the concept. Sorry I didn't make this clearer originally.

rgds
Phil
 
Old October 3rd, 2003, 12:42 PM
Registered User
 
Join Date: Sep 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I can see the text file input fine now.

I don't have any problem with the connection as I can see both the SQL in a recordset and the text file values with Dreamweaver recordsets. I don't have a problem if the join is two tables in a single connection. The problem seems to be how to connect and define two different connections. It seems to be the way I am setting up the connections either through a command or conneciton object or my approach.

<%@LANGUAGE="VBSCRIPT"%>


<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
'Dim MM_arrowhead_STRING
'MM_arrowhead_STRING = "dsn=arrowhead;uid=xxx;pwd=xxx;"
%>
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
'Dim MM_lsprint_STRING
'MM_lsprint_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\url/public;Extended Properties=Text;"
%>
<HTML>
<HEAD>
<TITLE>Parts OBS Seach by Batch</TITLE>
</HEAD>
<BODY>
<%
  Dim objCommandA, objRSA
  Set objCommandA = Server.CreateObject("ADODB.Command")

 objCommandA.ActiveConnection = MM_arrowhead_STRING

  Dim objCommandL, objRSL
  Set objCommandL = Server.CreateObject("ADODB.Command")

  objCommandL.ActiveConnection = MM_lsprint_STRING


  objCommandA.CommandText = "SELECT hawker.Summarytest.PartNumber,LSPRINT#TXT.PARTNUMB ER " & _
       "FROM LSPRINT#TXT INNER JOIN hawker.SummaryTest " & _
       "ON hawker.Summarytest.PartNumber = LSPRINT#TXT.PARTNUMBER"
' objCommandA.CommandType = adCmdText

  Set objRSA = objCommandA.Execute
  Set objCommandA = Nothing


  While Not objRSA.EOF
    Response.Write objRSA("PARTNUMBER") & "<BR>"
    objRSA.MoveNext
  Wend

  objRSA.Close
  Set objRSA = Nothing
  objRSL.Close
  Set objRSL = Nothing
%>
</BODY>
</HTML>
 
Old October 6th, 2003, 03:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

SQL runs against a single connection. I don't know of any way to bring a 2nd connection into play, and I doubt there is one (though I'm wary of saying you definitely cannot do it )

The only way I can think to get both the db and the text file within a single connection is to set up the text file directory as a linked server within SQL Server. Alternatively you could use the OPENDATASOURCE command, which is like a temporary linked server set-up. It depends how frequently you want to do this, and how much access you have to your SQL Server config.

Here's an example of the OPENDATASOURCE syntax:
Code:
SELECT hst.PartNumber, lt.F1 
FROM hawker.Summarytest hst
INNER JOIN 
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Temp;Extended Properties="Text;HDR=No;FMT=Delimited"')...LSPRINT#TXT lt
ON hst.PartNumber = lt.F1
Note that the field names within the text file are F1, F2 etc.

Let me know if you want some help on linked server set-up.

rgds
Phil
 
Old October 6th, 2003, 04:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thought I may as well post how to do the linked server set-up...

From Enterprise Manager:
 Security > Linked Servers > New Linked Server
 General tab inputs:
 Linked Server : type anything, e.g. TEXTTEST
 Server type - click 'other data source'
 Provider : choose Microsoft Jet 4.0 OLEDB Provider
 Product Name : leave blank
 Data Source : type in the directory, e.g. C:\Temp
 Provider String : type in Text;HDR=No;FMT=Delimited

 leave other tabs with default values

 then SQL becomes:
Code:
SELECT hst.PartNumber, lt.F1 
FROM hawker.Summarytest hst
INNER JOIN 
TEXTTEST...LSPRINT#TXT lt
ON hst.PartNumber = lt.F1
 
Old December 19th, 2004, 06:19 PM
Ziv Ziv is offline
Registered User
 
Join Date: Dec 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Ziv
Default

When I use this connection method, I am only receiving numerical data and none of the string text from the CSV file.
Any ideas on why this would be happening?

I am using the code from:
http://www.danielklann.com/excel/imp...files_into.htm

Thank you for your help,
Ziv





Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC Microsoft Access Driver Issue mat41 Classic ASP Professional 5 June 30th, 2008 10:37 AM
Microsoft Excel Driver Error cancer10 Classic ASP Databases 0 January 23rd, 2007 06:42 AM
Microsoft Text Driver and ADODB ? x23 MySQL 3 February 15th, 2005 05:27 PM
[Microsoft][ODBC Microsoft Access Driver] Syntax e chinedu Classic ASP Databases 3 November 18th, 2004 03:48 PM
Microsoft Text Driver ??? x23 SQL Server ASP 0 November 5th, 2004 05:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.