I have a sqlite table with following fields:
Langauge level hours
German 2 50
French 3 40
English 1 60
German 1 10
English 2 50
English 3 60
German 1 20
French 2 40
I want to loop through the records based on language and other conditions and then pass the current selected record to a different function. So I have the following mixture of actual code and psudo code. I need help with converting the psudo code to actual code, please. I am finding it difficult to do so.
Here is what I have:
Private sub mainp()
Dim oslcConnection As New SQLite.SQLiteConnection
Dim oslcCommand As SQLite.SQLiteCommand
Dim langs() As String = {"German", "French", "English"}
Dim i as Integer = 0
oslcConnection.ConnectionString = "Data Source=" & My.Settings.dbFullPath & ";"
oslcConnection.Open()
oslcCommand = oslcConnection.CreateCommand
Do While i <= langs.count
If langs(i) = "German" Then
oslcCommand.CommandText = "SELECT * FROM table WHERE language = '" & langs(i) & "';"
For each record selected 'psudo code
If level = 1 Then 'psudo code
update level to 2 'psudo code
minorp(currentRecord) 'psudo code: calling minorp function and passing the whole record as a parameter
End If 'psudo code
If level = 2 Then 'psudo code
update level to 3 'psudo code
minorp(currentRecord) 'psudo code: calling minorp function and passing the whole record as a parameter
End If 'psudo code
开发者_C百科 Next 'psudo code
End If
If langs(i) = "French" Then
oslcCommand.CommandText = "SELECT * FROM table WHERE language = '" & langs(i) & "';"
For each record selected 'psudo code
If level = 1 Then 'psudo code
update level to 2 'psudo code
minorp(currentRecord) 'psudo code: calling minorp function and passing the whole record as a parameter
End If 'psudo code
If level = 2 Then 'psudo code
update level to 3 'psudo code
minorp(currentRecord) 'psudo code: calling minorp function and passing the whole record as a parameter
End If 'psudo code
Next 'psudo code
End If
Loop
End Sub
Many thanks for your help.
The datatable has a DataRow object which you can pass to your functions.
I would suggest you create a class, e.g.
public class LanguageCourse
'Prob better to make these properties
public Language as string
public Level as integer
public Hours as integer
public sub new(language as string, level as integer, hours as integer)
Language = language
Level = level
Hours = hours
end sub
end class
Your code above could then become the following:
Private sub mainp()
Dim oslcConnection As New SQLite.SQLiteConnection
Dim oslcCommand As SQLite.SQLiteCommand
Dim langs() As String = {"German", "French", "English"}
Dim i as Integer = 0
oslcConnection.ConnectionString = "Data Source=" & My.Settings.dbFullPath & ";"
oslcConnection.Open()
oslcCommand = oslcConnection.CreateCommand
'Not sure why you were looping round these like this. It's also not a great idea to
'build up your sql queries by concactenating strings, better to parameteris them, but
'seeing as how this seems to be hard coded anyway, better even like this:
dim course as LanguageCourse
oslcCommand.CommandText = "SELECT * FROM table WHERE language IN ("German", "French", "English");"
For each record selected 'psudo code
course = new LanguageCourse(record.language, record.level, record.hours)
'This function should handle your update as you just seem to be adding one to
'something, for certain criteria.
minorp(course)
Next 'psudo code
End Sub
Note that this is still pseudo code, as this will not compile :)
I would suggest you move this logic to the database. SQL can perform these checks and update the data via an update statement with a where clause.
精彩评论