|
Hi,
I'm trying to design a login script using VB.net 2008 and the System.Data.SQLite wrapper. So far I have my SQLite tables setup with a default user (user: admin pass: test dob: 1990/01/01 name: John Smith) and I connect using the following code:
Imports System.Data.SQLite
Dim userName As String
Dim passWord As String
userName = usrBox.Text
passWord = passBox.Text
Dim sqlConnection As New SQLite.SQLiteConnection()
Dim sqlCommand As New SQLiteCommand
sqlConnection.ConnectionString = "Data Source=users.db3
sqlConnection.Open()
sqlCommand = sqlConnection.CreateCommand()
sqlCommand.CommandText = "SELECT * FROM users WHERE username = " & userName
However, I don't know how to check if the statement has rows for me to check if the user exists, and then I would have to do the same for passwords.
Anyone know how to check if the statement has rows using a similar function?
Cheers,
Ben.
|
|
|
|
|
You could use a "SELECT COUNT(*) ..." as SQL and then add something like this to your code:
Integer.Parse(sqlCommand.ExecuteScalar().ToString)
Another method is to create a DataTable first (using your SQL statement above):
Dim aDataTable As DataTable<br />
Dim aDbDataAdapter As DbDataAdapter<br />
<br />
sqlCommand.CommandText = sql<br />
aDbDataAdapter.SelectCommand = sqlCommand<br />
aDbDataAdapter.Fill(aDataTable)<br />
aDbDataAdapter.FillSchema(aDataTable, SchemaType.Source)<br />
aDataTable.TableName = tableName
and then evaluate the Row-Count:
aDataTable.Rows.Count
|
|
|
|
|
Maybe I have been looking at this too long.
I pass a DataTable,etc to the function. Summary values are calculated and new rows inserted at the appropriate places. The recursive part works good and the new rows are inserted into the correct place in the DataTable.
The problem occurs when the end of the DataTable is found and I call the Exit For statement, the Function should jump to the return statement, it however jumps to the Next statemnet and continues executing the loop.
Private Function SetNewDataRow(ByVal dt As DataTable, ByVal index As Integer, Optional ByVal fno As Integer = 0) As DataTable
Dim nra(13) As Object
nra = New Object() {"", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
nra(0) = "F" & (fno + 1).ToString & " Totals"
For i As Integer = index To dt.Rows.Count - 1
For j As Integer = 1 To dt.Columns.Count - 1
If TypeOf dt.Rows(i).Item(j) Is DBNull Then
nra(j) += 0
Else
nra(j) += CType(dt.Rows(i).Item(j), Double)
End If
Next
'--------------------------------------
' Debug Info -Display array contents
'--------------------------------------
Dim sb As New StringBuilder()
For n As Integer = 0 To 13
sb.Append(nra(n))
If n < 13 Then
sb.Append(", ")
End If
Next
Trace.WriteLine(sb.ToString)
'--------------------------------------
If i < dt.Rows.Count - 1 Then
If (Left(dt.Rows(i).Item(0).ToString, 4) <> Left(dt.Rows(i + 1).Item(0).ToString, 4)) Then
Dim nr As DataRow = dt.NewRow
nr.ItemArray = nra
dt.Rows.InsertAt(nr, i + 1)
'Exit For ' This works
SetNewDataRow(dt, i + 2, fno + 1)
End If
ElseIf i = dt.Rows.Count - 1 Then
Dim nr As DataRow = dt.NewRow
nr.ItemArray = nra
dt.Rows.Add(nr)
Exit For ' Problem here - Routine does not exit.
' Instead, jumps to the 'Next' statement
End If
Next
Return dt
End Function
I have tried numerous things, what am I missing?
(edited code)
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Monday, April 5, 2010 8:11 AM
|
|
|
|
|
The ElseIf at the bottom of loop will never be taken - the test is the same as previous If. So the Exit For will never execute.
The commented out Exit For is unnecessary - it is only taken when the end of loop condition is met so the loop would exit anyway.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Sorry David, there was a typo in the code sample.
I have found another way to do what I want, however, the behaviour of the recursive function is still perplexing. Note, I have tried this with a delegate Sub and still got the same weird behaviour.
Private Delegate Sub SetNewDataRow(ByRef dt as DataTable, ByVal index As Integer, _
ByVal fno As Integer)
Private Sub SetNewRow(ByRef dt as DataTable, ByVal index As Integer, _
Optional ByVal fno As Integer = 0)
...
If i < dt.Rows.Count - 1 Then
If (Left(dt.Rows(i).Item(0).ToString, 4) <> Left(dt.Rows(i + 1).Item(0).ToString, 4)) Then
nr = dt.NewRow
nr.ItemArray = nra
dt.Rows.InsertAt(nr, i + 1)
Dim del As New delRowSumm(AddressOf SetNewDataRow)
del(dt, i + 2, fno + 1)
End If
End If
Next
dt.Rows.Add(nra)
End Sub
It does evaluate i < dt.Rows.Count - 1 = False but jumps back into the For Loop 
I got around it like this:
...
Dim fno As Integer = 0
Dim index as Integer = 0
While index <> -1
index = SummRow(dt, index, fn)
fno += 1
Loop
...
Private Function SummRow(ByRef dt As DataTable, ByVal index As Integer, _
ByVal fno As Integer) As Integer
Dim res As Integer = 0
Dim arr() As Object = {"F" & (fno + 1).ToString & " Totals", _
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
For i As Integer = index To dt.Rows.Count - 1
For j As Integer = 1 To dt.Columns.Count - 1
If Not TypeOf dt.Rows(i).Item(j) Is DBNull Then
arr(j) += CType(dt.Rows(i).ItemArray(j), Double)
End If
Next
If i < dt.Rows.Count - 1 Then
If (Left(dt.Rows(i).Item(0).ToString, 4) <> Left(dt.Rows(i + 1).Item(0).ToString, 4)) Then
Dim nr As DataRow = dt.NewRow
nr.ItemArray = arr
dt.Rows.InsertAt(nr, i + 1)
res = i + 2
Exit For
End If
Else
dt.Rows.Add(arr)
res = -1
Exit For
End If
Next
Return res
End Function
I will keep digging at the original problem though. 
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Here's some code that does what I think you are trying to do.
It's a function that returns a DataTable with the additional rows.
I'm not keen on adding/inserting rows in loops where the loop is controlled by the count of items in the table. I've seen situations where the count is evaluated only when the loop is entered so when rows are added/inserted in the loop fails to deal with some records. Don't know if VB.Net does this in it's For loop.
Private Function SummRow(ByRef dt As DataTable) As DataTable
'EDIT - Oops - has a bug that causes runtime error
End Function
OO buffs will not like the arr() of objects because it's not type safe. It also assumes the DataTable rows have a string followed by up to 13 numeric values.
This fixes bug.
Private Function SummRow(ByRef dt As DataTable) As DataTable
Dim tmp as DataTable = dt.Clone()
Dim rowNum As Integer = 0
Dim arr() As Object = {"", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
Dim fNo As Integer = 0
Dim column0 as String
'Loop through the DataTable
While rowNum < dt.Rows.Count
'Set controls for next group of rows based on column0
fNo += 1
column0 = Left(dt.Rows(rowNum).Item(0).ToString, 4)
arr(0) = "F"& fno.ToString & " Totals"
'Loop through rows with same entry in column 0
While (rowNum < dt.Rows.Count) AndAlso (column0 = Left(dt.Rows(rowNum).Item(0).ToString, 4))
For colNum As Integer = 1 To dt.Columns.Count - 1
If Not TypeOf dt.Rows(rowNum).Item(colNum) Is DBNull Then
arr(colNum) += CType(dt.Rows(rowNum).ItemArray(colNum), Double)
End If
Next colNum
tmp.Rows.Add(dt.Rows(rowNum)
rowNum +=1
End While
'Add the running totals row on change of column0
Dim newRow as DataRow = tmp.NewRow
newRow.ItemArray = arr
tmp.Add(newRow)
End While
Return tmp
End Function
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
modified on Tuesday, April 6, 2010 3:32 AM
|
|
|
|
|
Are you aware that you could just replace the Exit For with a Return dt?
If the only purpose of exiting the for loop is to finish the function, you can simply return the value. If it still doesn't execute the return, then you haven't gotten into that If block.
So, instead of:
ElseIf i = dt.Rows.Count - 1 Then
Dim nr As DataRow = dt.NewRow
nr.ItemArray = nra
dt.Rows.Add(nr)
Exit For
End If
ElseIf i = dt.Rows.Count - 1 Then
Dim nr As DataRow = dt.NewRow
nr.ItemArray = nra
dt.Rows.Add(nr)
return dt
End If
And are you stepping through the whole process? Is that where you're seeing that it's not getting to the return? Because if you are using recursion, after you call the SetNewDataRow recursively, after it returns, it's going to just to Next and it might appear like it didn't work.
|
|
|
|
|
Hi!
FOr the fun, I try to understand the recursivity in VB.net and I try to recreate a custom TreeView/TreeNode.
I progressively recreated the properties and methods of originals TreeView and TreeNode.
So, I have a customTreeView Class inheriting from Panel(for drawing with GDi), a customTreeNode class inheriting from Control and a customTreeNodeCollection inheriting from List(Of customTreeNode).
All customTreeNode instances and my instance of customTreeView have an instance of customTreeNodeCollection for storing childNodes.
I can add node the same way I do with originals TreeView ... with Add. But with the original, just after adding the childNodes to a parent node, if i check the "Level" property, it is already filled.
I don't understand, how, in my instance of customTreeNodeCollection, I can inform the Level of my New TreeNode.
I have to think well of recovering the level of the parent node and of adding +1 to it, but I does not manage either to recover parent TreeNode of the instance of this TreeNodeCollection.
Is there a trick know with recursivity ? or is there an other way to achieve it ?
Sorry for bad english 
|
|
|
|
|
You do not tell it what level it is, all you do is set what the parent node is.
Look at the properties available, and the methods available on a tree node.
|
|
|
|
|
The level / depth of the current node compared to the node root ... or, the number of parents.
"Look at the properties available, and the methods available on a tree node."
Yes, it would be easy using the true TreeNode, but it is not my case, I start from scratch for my TreeNode while inheriting Control.
So my question is:
I have nested list of nested list of nested lists ......
I'm on a node, how-to get his parent node and therefore, his level
And I think I must attribute this level in the "Add" method of my customTreeNodeCollection class. But I don't know, from this class, how-to access the instance of customTreeNode who have instanciated this collection
|
|
|
|
|
Look at Directory Recursion functions used for file systems, and apply the same principles to the nodes. i.e. have a function that can be called, pass in the current node, and keep a static count somewhere and keep walking up the parent until it is empty, then you will have the top (root node). and the depth to the node under test.
|
|
|
|
|
Hi,
Maybe there is no need to store the level, just calculate the value when it's needed by walking up the list of nodes via the Parent property. The root or top node has no parent and the Parent property will return null or Nothing when you get there.
The first example using a loop is probably easier to understand than the second recursive version.
public Int32 Level() {
Int32 levelValue = 0;
Node node = this;
while (node.Parent != null) {
levelValue = levelValue + 1;
node = node.Parent;
}
return levelValue;
}
public Int32 Level() {
if (this.Parent == null) {
return 0;
} else {
return Level() + 1;
}
}
Sorry about using c# in the Basic forum. If you can't decipher it, post back and I'll attempt a translation.
Alan.
|
|
|
|
|
I have searched in "Directory" for coll functions, but I don't know if it can serve me for my need.
Hi Alan N. As I sayed at start of this post, My first idea is to not using the real TreeNode and it's cool feature "Parent".
Since I want to write my own class inheriting "Control", I need to write my own property "Parent" because for controls, "Parent" is the parent control, and not the parent node.
I understand recursives functions for itering the child nodes (or child customTreeNodes in my List for my case), but I don't know how to make the same for going up in the parents.
I don't find simple examples on the web who make the same.
In any case, thank you for your suggestions which help me to consider the things in various ways.
|
|
|
|
|
Hi,
Yes, I understood that you would be implementing your own version of the parent property which is different from the Control.Parent property and I think I see what the problem is. You are able to travel down through the tree via the node collections but from your description I would guess that there are no upward links in place. That is, if you pick any node in your tree you could go down to find it's children but not up to it's parent.
To be able to go both up and down each node must contain two links
CLASS Node
NodeCollection children
NodeCollection parent
and importantly the NodeCollection must contain a link to it's parent node
CLASS NodeCollection
Node parent
The overall data structure is an example of a Doubly Linked List, complicated by the fact that the elements of the list are alternating Nodes and NodeCollections.
Alan.
|
|
|
|
|
Yes That's it. You have explained my problem better than me.
I succeeded in doing about what I wanted.
In the "Add" method of my customTreeNodeCollection, I call a recursive function which looks at all the children (and nested children) and informs their properties “Level” and “ParentNode”.
So, when I add a root node to the custom collection of my custom TreeView, this function can inform all the nodes 
My custom TreeNodeCollection
Public Class cTreeNodeCollection
Inherits List(Of cNode)
Public Overloads Sub Add(ByVal node As cNode)
MyBase.Add(node)
SetAllLevelsAndParents(node.ChildNodes, 1, node)
End Sub
Private Sub SetAllLevelsAndParents(ByVal nodeList As List(Of cNode), ByVal level As Int32, ByVal parentNode As cNode)
For Each n As cNode In nodeList
Console.WriteLine(level & " | " & n.Text)
n.Level = level
n.ParentNode = parentNode
SetAllLevelsAndParents(n.ChildNodes, level + 1, n)
Next
End Sub
End Class
But your help will be very helpfull !
The answer for me, is to pass in parameters the current node and it's level for the recursive function.
With the level+1, i can inform the level of the childs, and with the node, I can inform the parent of the childs.
I don't know if it's a robust solution, but it seems working for the moment.
|
|
|
|
|
I am making an MCQ based quiz in VB 2008. I would like to have such an environment that picks up questions / answer options from a database (MS ACCESS) file and displays it to the user one after another. Say the quiz contains 20 questions.
How can this be done?
|
|
|
|
|
Write some code to do it?
Try searching Google, MSDN or this site - you'll find plenty of info.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Razanust wrote: displays it to the user one after another
Normally MCQ based quiz are not in sequence they are Random.
Razanust wrote: How can this be done?
at what stage are you. what is the difficulty / Problem you are having
SOFTDEV
If you have knowledge, let others light their candles at it. [ Let's work to help developers, not make them feel stupid ]
|
|
|
|
|
I am a beginner to it.
Well the problem i am facing is that i can't find a proper procedure of how to link the questions with the help of databases to the VB Form.
That is if i want the label's text to be same as the variable say 'Question1' i would write text.label1= Question1
Now i want this thing to do with Access Database Files.
Any reference Article or sample code would be quite helpful.
|
|
|
|
|
|
There are lots of good articles here on CodeProject. Maybe this[^] will interest you.
|
|
|
|
|
I suggest you also have a look here[^].
|
|
|
|
|
Thanks to all for your help.
I found another good video at http://vimeo.com/8618838.
|
|
|
|
|
Hi all,
I'm using the unrar.dll and the decompressor-class.
I use a rar-file for extraction with:
Dim d As New Decompressor("filename")
d.UnPackAll("path")
After extraction I wan't to delete the rar-file by code, but I get
the message that the file is allready in use.
I can't find a process in task manager which I can delete.
And there is not event like "d.Dispose()" or "d.Close", "d = Nothing" didn't help too.
How can I set the file free for deleting?
Thanks all for help
br Maik
|
|
|
|
|
The process that's holding the file open is most likely your own. Apparently, that library holds the file open for the life of the Decompressor object. Your best source of information would be the people who wrote that library.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
