|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
July 31st, 2006, 04:37 AM
|
|||
|
|||
|
Storing the data into database
Code:
Public cn As Connection
Public rsStationInfo As Recordset
Public rsWQSampleData As Recordset
Public rsDepthCode As Recordset
Public rsTideLevel As Recordset
Public rsTideCycle As Recordset
Public rsSeason As Recordset
Private Sub CmdConvert_Click()
On Err GoTo Errhandler
nInFile = 1
'If Len(Pathtxt.Text) = 0 Then
' MsgBox "Please select the summary file"
' Exit Sub
'ElseIf Len(DBPathtxt.Text) = 0 Then
' MsgBox "Please select the database file"
' Exit Sub
'End If
Close #NumFile
ReDim Preserve InFile(1) As String
InFile(0) = Pathtxt.Text
For fNo = 0 To nInFile - 1 ' for each input summary file (.sum)
Open InFile(fNo) For Input As #NumFile
'If Not EOF(NumFile) Then ' read data from .sum file till EOF
Line Input #NumFile, ContractHead ' read a line of data
Line Input #NumFile, JobHead
Line Input #NumFile, s1
s2 = "" & ParseString(data(), s1, ",") ' extract data fields divided by comma
VesselHead = data(0)
Vessel = data(1)
WeatherHead = data(2)
Weather1 = data(3)
SeaConHead = data(4)
SeaCon = data(5)
TideHead = data(6)
Tide = data(7)
WindDirHead = data(8)
WindDir = data(9)
Line Input #NumFile, s3
Line Input #NumFile, s3
Do While Not EOF(NumFile) ' read data from .sum file till EOF
Line Input #NumFile, s4
If Left$(s4$, 11) = "Head_Vessel" Then
Line Input #NumFile, s4
Line Input #NumFile, s4
Line Input #NumFile, s4
End If
Text1.Text = "" & s4
s4 = "" & ParseString(WQInfo(), s4, ",") ' extract data fields divided by comma
StationName = Trim(WQInfo(0))
Time = WQInfo(1)
Easting = Trim(WQInfo(2))
Northing = Trim(WQInfo(3))
Depth = Trim(WQInfo(4))
Temperature = Trim(WQInfo(5))
Salinity = Trim(WQInfo(6))
DOPercent = Trim(WQInfo(8))
DOMgl = Trim(WQInfo(9))
Turbidity = Trim(WQInfo(10))
pH = Trim(WQInfo(11))
Depth1 = Trim(WQInfo(14))
Temperature1 = Trim(WQInfo(15))
Salinity1 = Trim(WQInfo(16))
DOPercent1 = Trim(WQInfo(18))
DOMgl1 = Trim(WQInfo(19))
Turbidity1 = Trim(WQInfo(20))
pH1 = Trim(WQInfo(11))
Date = WQInfo(21)
SeasonID = 1
TideLevelID = 1
TideCycleID = 1
WaterDepth = 30
Weather = "Sunny"
Observation = "test"
remarks = "test"
Call FileCopy(App.Path & "\DataSource\WQData.mdb", DBFname)
Call OpenDB(DBFname)
Call AddRecord(StationName, Date, SeasonID, TideCycleID, TideLevelID, Weather, WaterDepth, Observation, remarks, Time, Easting, Northing, _
Depth, Temperature, Salinity, DOPercent, DOMgl, Turbidity, pH, Depth1, Temperature1, Salinity1, DOPercent1, DOMgl1, Turbidity1, pH1)
Loop
'End If
Close #NumFile
Call CloseDB
Next
Errhandler:
End Sub
Function AddRecord(StationName, SurveyDate, SeasonID, TideCycleID, TideLevelID, Weather, WaterDepth, Observation, remarks, Time, _
Easting, Northing, DepthCoDepth, Temperature, Salinity, DOPercent, DOMgl, Turbidity, pH, Depth1, Temperature1, Salinity1, DOPercent1, DOMgl1, Turbidity1, pH1)
If cn.State = adStateOpen Then
cn.Execute "Insert into WQSampleData(SampleID, StationID, Time, Easting,Northing,Depth, Temperature, Salinity, DOPercent, DOMgl, Turbidity, pH, Depth1, Temperature1, Salinity1, DOPercent1, DOMgl1, Turbidity1, pH1 ) " & _
"VALUES (' ',' ','Time', 'Easting','Northing','Depth', 'Temperature', 'Salinity', 'DOPercent', 'DOMgl', 'Turbidity', 'pH', 'Depth1', 'Temperature1', 'Salinity1', 'DOPercent1', 'DOMgl1', 'Turbidity1', 'pH1' );"
End If
End Function
The Sample ID and Station ID are the auto number However, i can't add the data into the DB And it said "Syntax error on insert statement" What wrong with that?
|
|
#2
August 1st, 2006, 08:20 AM
|
||||
|
||||
|
Enzo,
The best thing to do is to write the SQL string out to a Label on your form and cut and paste it into your DB package and try and run it. This will give you an idea of the problem. Without having time to trawl through all your code I think your problems lie in the Function AddRecord: Firstly, be careful what you name the parameters because Time is a reserved word. Secondly, check what you have declared the fields as in the database because I dont think you should have single quotes around numeric data. Finally, I believe the main cause of your problem is that you are inserting the names of the variables into the DB and not the values. I may be wrong but I think you need to break up the string using '" & variablename & "', try something like this: Code:
Function AddRecord(StationName, SurveyDate, SeasonID, TideCycleID, TideLevelID, Weather, WaterDepth, Observation, remarks, varTime, _
Easting, Northing, DepthCoDepth, Temperature, Salinity, DOPercent, DOMgl, Turbidity, pH, Depth1, Temperature1, Salinity1, DOPercent1, DOMgl1, Turbidity1, pH1)
If cn.State = adStateOpen Then
cn.Execute "Insert into WQSampleData(SampleID, StationID, Time, Easting,Northing,Depth, Temperature, Salinity, DOPercent, DOMgl, Turbidity, pH, Depth1, Temperature1, Salinity1, DOPercent1, DOMgl1, Turbidity1, pH1 ) " & _
"VALUES (' ',' ','" & varTime & "', '" & Easting & "','" & Northing & "','" & Depth & "', '" & Temperature & "', '" & Salinity & "', '" & DOPercent & "', '" & DOMgl & "', '" & Turbidity & "', '" & pH & "', '" & Depth1 & "', '" & Temperature1 & "', '" & Salinity1 & "', '" & DOPercent1 & "', '" & DOMgl1 & "', '" & Turbidity1 & "', '" & pH1 &"' );"
End If
End Function
Like I say, I dont know what data types your fields are defined as, if they are numeric you will have to remove the single quotes: eg. , " & pH & ",
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Storing the data into database |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
