|
| |
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
 |
Uploading data or appending data question - 8/26/2005 6:51:23
If I want to upload data on a client side db to a server side database, is there an upload process or an append process?
Or do I have to somehow loop an Insert query?
Thanks
Dave
|
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/27/2005 6:28:03
Depends on the database etc - are you using asp, .net or php???
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 15:03:29
ASP
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 15:53:39
and the databases? Access or SQL Server 7/2000/MSDE or mySQL??
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 16:04:06
Sorry - Access!
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 16:10:26
Are you moving some of the data in the client db to the server db, or replacing the server db with the client db? Is this from several clients or one? Is it just data or data and structure too?
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 16:19:32
The project is to collect data on a instructors laptop using localhost IIS. A survey is taken using asp, data collected and when possible the instructors will "append" their data into the main server db. The tables "Results" will be the same in both locations but the db will be NTS.mdb for the instructor and LMS.db for the server.
The file structure will be identical.
After an INSERT in finished on the instructor side, I will delete the records in the instructors "Results" table and start new.
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 16:20:54
quote:
After an INSERT in finished on the instructor side, I will delete the records in the instructors "Results" table and start new.
After an INSERT in finished from the instructor side to the server side, I will delete the records in the instructors "Results" table and start new.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 16:59:15
Will the instructors laptops be on the same network as the server, or are they remote? I'm assuming remote, but if not then it might be easier to split the database into front-end with the forms and reports, and back-end holding the data tables, and then linking the tables between the 2. Then they automatically update.
Otherwise, you will have to use an insert query on a loop. The only other way I can think of is to import data, but I think it's difficult to append data to an existing table this way (though I could be wrong).
Or perhaps you write the data to xml files and import them into Access, if you have Access 2003. That might work, and then it's like a bulk insert and you don't need a loop. Much more efficient.
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 17:04:09
The laptops will be remote when they collect data. After the data is collected, then the instructor will upload.
I am not sure how to loop an insert.
I can insert single recods, but I have never looped inserts.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 17:46:06
You'd need a page with the local database connection and the server database connection in it. Then I'd open a connection and move the data you want "uploaded" into either a recordset, or even better, use GetRows to put the data into an array. Then you can use a For loop to build an insert statement and the last line would execute the statement
This is some code I used to move data from an Access database to a SQL database. It might give you an idea of what you want. It won't work exactly, as I had to chop out a lot of If statement etc, but it's ok as an example.
[code]
'connstrings here
myConnLocal = ....
myConnServer = ...
'sql statement to get the local data
mySQL = "SELECT * FROM NewTable"
'SETTING RECORDSET OBJECT AND OPENING RECORDSET USING SQL STATEMENT
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, myConnLocal
If myRS.BOF = True and myRS.EOF = True Then
Response.Write "No Records returned"
Else
'put the data in an array
alldata=myRS.Getrows
'get the limits of the array
numcols = Ubound(alldata,1)
numrows = Ubound(alldata,2)
End If
'loop through each row of the array
For i = 0 to numrows
myStatement = "INSERT INTO ServerTable (FirstCol, SecondCol) VALUES ( "
'loop through each field
For j = 0 to numcols
thisfield = alldata(j,i)
'some sort of If or Select statement here to help build the string
If NOT IsNull(thisfield) then
thisfield = Replace(thisfield, "'", "''")
myStatement = myStatement & myArray(j) & "='" & thisfield & "', "
Else
myStatement = myStatement & myArray(j) & "=" & thisfield & ", "
End If
Next
myStatement = myStatement & ");"
'write out the INSERT statement for error checking Response.write "<p>" & myStatement & "</p>"
'execute the INSERT
myConnServer.Execute(myStatement)
' move on to the next line of the array
Next
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/30/2005 18:32:11
Thanks it gives me a starting point.
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/31/2005 12:34:24
First thanks for all of the help!!
I am new at arrays and Get.Rows so pls be patient.
Here is where I am now.
All is well until type mismatch identified below.
<%
'ON ERROR RESUME NEXT
set ConnStrS = Server.CreateObject("ADODB.Connection")
RelativePath = "../fpdb/NTS.mdb"
AbsolutePathS = Server.Mappath(RelativePath)
ConnStrS.Provider = "Microsoft.Jet.OLEDB.4.0"
connStrS.Open = "Data Source=" & AbsolutePathS & ""
set ConnStrL = Server.CreateObject("ADODB.Connection")
RelativePath = "../fpdb/LMS.mdb"
AbsolutePathL = Server.Mappath(RelativePath)
ConnStrL.Provider = "Microsoft.Jet.OLEDB.4.0"
connStrL.Open = "Data Source=" & AbsolutePathL & ""
set rs = Server.CreateObject("ADODB.Recordset")
mysql = "SELECT * From ResultsCrit"
rs.open mysql, ConnStrL
If rs.BOF = True and rs.EOF = True Then
Response.Write "No Records returned"
Else
'put the data in an array
alldata = rs.Getrows
'get the limits of the array
numcols = Ubound(alldata,1)
numrows = Ubound(alldata,2)
End If
Response.Write "<br>Cols " & numcols
Response.Write "<br>Rows " & numrows
'Response.End
'loop through each row of the array
For i = 0 to numrows
myStatement = "INSERT INTO New (FirstCol, SecondCol) VALUES ( "
'loop through each field
For j = 0 to numcols
thisfield = alldata(j,i)
'some sort of If or Select statement here to help build the string
If NOT IsNull(thisfield) then
thisfield = Replace(thisfield, "'", "''")
myStatement = myStatement & myArray(j) & "='" & thisfield & "', "
Else
myStatement = myStatement & myArray(j) & "=" & thisfield & ", "
End If
Next
myStatement = myStatement & ");"
'write out the INSERT statement for error checking Response.write "<p>" & myStatement & "</p>"
'execute the INSERT
connStrS.Execute(myStatement)
' move on to the next line of the array
Next
%>
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 8/31/2005 12:59:44
try putting a ' in front of connStrS.Execute(myStatement) and then running the page, it should write out the insert statements and you might be able to see what is wrong with them. It's probably that you've got a comma after the last column.
Where it says 'some sort of If or Select statement here to help build the string
put a Select statement
Select case j
case numcols
myStatement = myStatement & myArray(j) & "='" & thisfield & "'"
case Else
myStatement = myStatement & myArray(j) & "='" & thisfield & "',"
End Select
you may need to add more to the select statement, to deal with columns that return numbers (so no ' ' around thisfield) etc.
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 8/31/2005 13:30:19
Thanks
| |
|
|
|
DaveKstl
Posts: 551
Joined: 4/21/2004
Status: offline
|
RE: Uploading data or appending data question - 9/2/2005 16:57:33
What does myarray(j) represent?
I did not pick up a value for this.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: Uploading data or appending data question - 9/3/2005 4:12:21
Sorry, that's a mistake, it should be alldata(j,i) or thisfield. Must've been having a brainstorm!
You've basically got all your data in a table, and each row (i) is numbered from 0 to numrows/Ubound(alldata,2), and each column/field (j) is numbered from 0 to numcols/Ubound(alldata,1).
Ubound is a function that finds the upper limit of an array.
You've got 2 For loops on the go. The first loop moves from 0 to numrows along the rows. Nested inside that is the second For loop that goes from 0 to numcols. The 2nd loop continues right to the end before the first loop gets to move again. It's like moving along a battleships grid.
01234
1
2
3
4
So you do one row loop, and then loop along the columns to the end, then a new row loop and along the columns...
If you want a different string concat to happen for column 3 than the others, then you can use a Select statement to check the value of j (the column 'name') and when it's that column then it uses your different string concat.
You probably knew all that and it was just my lax variable-naming that stumped you, but I thought I'd be thorough (this time)! 
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
