Adding a text file to a SQL database 1

[clydejones]

Hi All,

Just found out I need to add a text file to a SQL database with my ASP program. Any ideas where I can get some sample code? I've been trying to do a search on bulkcopy but can find any ASP examples. Any help would be appreciated.

Thanks,
Clyde

 

[onpnt]

can you simply add teh path to the text file in the database or does the DB need the complete content with in the file?

_____________________________________________________________________

_{Hakuna matata!!}

 

[clydejones]

Man,
That was quick. onpnt, I need to put the content of the file in the database.

Thanks,
Clyde

 

[onpnt]

first task is to get the content
FileSystemObject or FSO is a quick and easy way
to do that

here's a example
<%
dim fs,f,t,x
set fs=Server.CreateObject(&quot;Scripting.FileSystemObject&quot

set t=fs.OpenTextFile(&quot;c:\test.txt&quot;,1,false)
x=t.ReadAll
t.close
%>

straight from the mouth of w3schools.com

http://www.w3schools.com/asp/met_readall.asp

obviously taking out the write portions and such

thats the most basic form of getting all the content out of a text file
from the way it sounds you don't need much more then that. although
formatting may be something to keep in mind.

the database portion isn't to bad either. the only thing
you need be concerned with is the size of teh field you intend
to insert this data into.

here's some info on that side of things

http://www.databasejournal.com/features/mssql/article.phpr/2212141

http://www.basenow.com/help/Data_types_in_Microsoft_SQL_Server.asp

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7719

doubt you'll need more the 8000 cahr's which varchar will support
so create the table column etc.. at varchar(8000)
note: keep the field as small as you can while being safe not
to allow errors from large insert's. if you insert 5 char's in that
8000 cahr field you still take the resources of 8000.

the steps to do the insert are basic to any other ASP process
just do teh FSO function and get the data
then create a connection object
Dim ConnectionString : ConnectionString = &quot;SQL Server connection string&quot;
ConnectionString = ConnectionString & &quot;database name and location&quot;

Dim ConnectionObj
set ConnectionObj = Server.CreateObject(&quot;ADODB.Connection&quot
ConnectionObj.Open ConnectionString,3,3

you may notice I'm copying from a different post I made earlier today. lazy

anyhow, then build the SQL insert

Dim SQLInsert
SQLInsert = &quot;INSERT INTO table VALUES ('&quot; & x & &quot;')&quot;

the best part is there isn't much mroe to that. just execute it

ConnectionObj.Execute(SQLInsert)

the SQL statement is the only thing that is waht you need to worry about
if you're inserting into a large table just name the columns etc.

for example if you want to insert into a table with three columns such as
ID textFile Employee

and ID and Employee are set alreay just do a
SQLInsert = &quot;INSERT INTO table (textFile) VALUES ('&quot; & x & &quot;')&quot;

or if you have a user entered employee
SQLInsert = &quot;INSERT INTO table (textFile, Employee) VALUES ('&quot; & x & &quot;','&quot; & EmployeeVariable & &quot;')&quot;
and so on

more info on that

http://www.w3schools.com/sql/sql_insert.asp

don't forget to clean things up also
ConnectionObj.Close

_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

just incase you also need the connection strings here's most common one.

&quot;Provider=SQLOLEDB;Data Source=myserver;User ID=user name;Password=password;Initial Catalog=your db name&quot;




_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

and aside the OLEDB the ODBC may be a better choice.

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer

_____________________________________________________________________

_{Hakuna matata!!}

 

[clydejones]

onpnt,
Thanks for the replys. This info will get me started. I still may have a question or two but then again maybe not. Anyways, thanks a bunch and HAPPY NEW YEAR. It's Party Time.

Til Next Year,
Clyde

 

[clydejones]

onpnt,

Everything seems to work til I get to the Execute statement. I get a &quot;syntax or access violation&quot; error. Doubt that it is a syntax error because if I just try to enter a string everything works fine. Also, I may need to be looking at the bulkcopy method. Any ideas?


Clyde

 

[clydejones]

onpnt,

I figured out the error. I got it to work with a text file that did not have odd characters in it. The path I'm going down now is trying to use the bulkcopy method. Can seem to find any examples of ASP code to do this. Thanks for your help. Any suggestions would be appreciated.

Clyde