Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
EXCEL, SQL, and ASP
- Thread starter TonyRosen
- Start date
SkipVought
Programmer
Hi,
I assume that your Excel Spreadsheets are tables.
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
I assume that your Excel Spreadsheets are tables.
Code:
<%@ Language=VBScript %>
<% Response.Buffer = True %>
<% Server.ScriptTimeout = 1000 %>
<html>
<%
Dim DB, rsEFF
cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & server.mappath("Block Status TZT.mdb")
Set DB = Server.CreateObject("ADODB.Connection")
DB.Open cst
Set rsEFF = Server.CreateObject("ADODB.Recordset")
Sql = "SELECT * FROM BOM_1 BOM "
rsEFF.Open SQL,DB,1,2
do while not rsEFF.EOF
'stuff
rsEFF.MoveNext
loop
......Skip,
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.Small Medium @ Large!
- Thread starter
- #4
- Thread starter
- #6
Or you could write some VBA code underneath the spreadsheet that will write to the database. I have done that before. And, you just place an event button on the spreadsheet surface or create a macro that will just run everything you want to the database.
Of course, you will have to design the VBA code to handle connections and SQL syntax. No problem.
So, the user just has to hit the button and all of the data is sent to the coded destination.
"I think there is a world market for maybe five computers."
--Thomas Watson, chairman of IBM, 1943
Of course, you will have to design the VBA code to handle connections and SQL syntax. No problem.
So, the user just has to hit the button and all of the data is sent to the coded destination.
"I think there is a world market for maybe five computers."
--Thomas Watson, chairman of IBM, 1943
along the lines of ignoreme's post, you can interact a com component to make use of SQL's DTS abilities, and in turn could take the file upload/insert DB in a single phase with less issues.
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
DreXaKa - Robert
if all else fails, light it on fire and do the happy dance!
- Thread starter
- #9
- Thread starter
- #11
i will make a forewarning.. building a component can be a ![[curse]](/data/assets/smilies/curse.gif "[curse] [curse]")
to deal with, especially from scratch, i'd really digging into google for a few hours of different searches on ASP / ADO / Excel / Import / DTS / Com Component
someone might have one built 90% to your specs already.
as for the vba code in the spreadsheet, this could be compromiseable, and causes security issues. you can at least build server side filters/handling to where the only interaction from user to DB is sending a file for processing that may or may not pass specs.
DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
to deal with, especially from scratch, i'd really digging into google for a few hours of different searches on ASP / ADO / Excel / Import / DTS / Com Componentsomeone might have one built 90% to your specs already.
as for the vba code in the spreadsheet, this could be compromiseable, and causes security issues. you can at least build server side filters/handling to where the only interaction from user to DB is sending a file for processing that may or may not pass specs.
DreXaKa - Robert
if all else fails, light it on fire and do the happy dance!
- Thread starter
- #13
I figured that I would:
1) upload the file
2) redirect the user to an ASP page which will copy said data into the SQL database (since I will know the format of each)
3) delete the file
4) redirect user to a success page
Can that be any more simple?
1) upload the file
2) redirect the user to an ASP page which will copy said data into the SQL database (since I will know the format of each)
3) delete the file
4) redirect user to a success page
Can that be any more simple?
- Thread starter
- #15
What I ended up doing:
1) upload the file
2) Rename the file since every other sales person's file is named the same thing
3) redirect the user to an ASP page which will copy said data into the SQL database (since I will know the format of each) from the Excel file
3) delete the file
4) redirect user to a success page
And, it worked like a champ
1) upload the file
2) Rename the file since every other sales person's file is named the same thing
3) redirect the user to an ASP page which will copy said data into the SQL database (since I will know the format of each) from the Excel file
3) delete the file
4) redirect user to a success page
And, it worked like a champ
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question