dont know why....

OK, as i seem to come here a lot about things, I'm going to make this as simple as possible.

I'm having problems with connecting to a file called "userlist.mdb"
it is a database.

i have two subs, and nether work....really being annoying...
Blubb, Jason, or anybody else that can see the problem, can you explain to me why its not working?...

...also, might be noted that I'm using VB.net 2005...

passwords encrypted with a basic AES encryption...
keys are the keys to the passwords, key for all the keys is "Default"....im slack for the moment...

first user is johnsmith, and password for that is Password1

sub 1
[highlight=vb.net]Public Sub dbcheck(ByVal user As String, ByVal password As String)
Try
Dim con As New OleDb.OleDbConnection
Dim dbprovider As String = "PROVIDER=Microsoft.Jet.OlEDB.4.0;"
Dim filelocation As String = ""
Console.WriteLine(filelocation)
Dim dbsource As String = "Data Source =" & filelocation & "userlist.mdb;"
Dim sql As String
Dim Datset As New DataSet
Dim Data_adapt As OleDb.OleDbDataAdapter

con.ConnectionString = dbprovider & dbsource

Try
con.Open()
sql = "SELECT * FROM TBlist"
Data_adapt = New OleDb.OleDbDataAdapter(sql, con)
Data_adapt.Fill(Datset, "usernames")

sql = "SELECT * FROM TBpasswords"
Data_adapt = New OleDb.OleDbDataAdapter(sql, con)
Data_adapt.Fill(Datset, "passwords")

Dim int As Integer = 0
For int = 0 To intusers
userlist(int) = Datset.Tables("usernames")****ws(int).Item(4)
passlist(int) = Datset.Tables("passwords")****ws(int).Item(1)
keylist(int) = Datset.Tables("passwords")****ws(int).Item(2)
Next

int = 0
For int = 0 To intusers
Dim keycheck As String = DEScrypt.decrypt(keylist(int), "Default")
Dim usercheck As String = userlist(int)
Dim passcheck As String = DEScrypt.decrypt(passlist(int), keylist(int))
If user = usercheck Then
If password = passcheck Then
My.Settings.loggedin = True
End If
End If
Next

Catch ex As Exception
Console.WriteLine("Error, could not open file")
End Try
con.Close()
Catch ex As Exception
Console.WriteLine("File not able to be opened")
End Try

End Sub[/highlight]

sub 2
[highlight=vb.net] Public Sub dbcheck2(ByVal user As String, ByVal password As String)
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim datread As OleDbDataReader
Dim filedir As String = "Data Source=userlist.mdb;"

Try
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & filedir)
'provider to be used when working with access database
con.Open()
cmd = New OleDbCommand("select username from TBlist", con)
datread = cmd.ExecuteReader
While datread.Read = True
userlist(0) = datread(0)
userlist(1) = datread(1)
userlist(2) = datread(2)
userlist(3) = datread(3)
End While
cmd = New OleDbCommand("select passkey from TBpasswords", con)
datread = cmd.ExecuteReader
While datread.Read = True
keylist(0) = DEScrypt.decrypt(datread(0), "Default")
keylist(1) = DEScrypt.decrypt(datread(1), "Default")
keylist(2) = DEScrypt.decrypt(datread(2), "Default")
keylist(3) = DEScrypt.decrypt(datread(3), "Default")

End While
cmd = New OleDbCommand("select password from TBpasswords", con)
datread = cmd.ExecuteReader
While datread.Read = True
passlist(0) = DEScrypt.decrypt(datread(0), keylist(0))
passlist(1) = DEScrypt.decrypt(datread(1), keylist(1))
passlist(2) = DEScrypt.decrypt(datread(2), keylist(2))
passlist(3) = DEScrypt.decrypt(datread(3), keylist(3))
End While
datread.Close()
con.Close()

Dim int As Integer
For int = 0 To 3
If userlist(int) = user Then
If passlist(int) = password Then
My.Settings.loggedin = True
End If
End If
Next
Catch ex As Exception
Console.WriteLine("Error, file problem")
Console.WriteLine(ex)
Console.ReadLine()
End Try

End Sub[/highlight]

if you see the problem, can you please say it in this form.

function name =

problem
<line that does not work>

- why it does not work

- if possible, how to fix it

thanks if anybody can help, being annoying...

vscans of database=
vscan 1
vscan 2

(not ranked in any order.....)
@Jason
@Blubb1337
@-Rez
@freedompeace
@gamer4evere

/post 500
also, if known, what can i do at 500 posts...

Okay, I'm just going to pretty much rewrite the whole thing.

Also, I'm using references to ADODB and ADOX, rather than Ole.

ADOX: "Microsoft ADO Ext. 6.0 for DDL and Security" -- COM tab
ADODB: "adodb" -- NET tab.

[highlight=vb.net]
Imports ADOX
[/highlight]

Here's some generic database functions that can be applied to any access project really:
[highlight=vb.net]
Public Function Connect(ByVal databaseLocation As String, ByRef connection As ADODB.Connection) As Boolean
If IO.File.Exists(databaseLocation) Then
Try
connection.Close() 'close it if it's open
Catch ex As Exception
End Try
Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" + databaseLocation + """"
connection = CreateConnection(conString)
If connection IsNot Nothing Then
Return True
Else
DisposeCOM(connection)
Return False
End If
Else
Return False
End If
End Function

Public Sub DisposeCOM(ByRef o As Object)
Try
Runtime.InteropServices.Marshal.ReleaseComObject(o )
o = Nothing
Catch ex As Exception
o = Nothing
Finally
GC.Collect()
End Try
End Sub

Public Function PrepareDatabase(ByVal db As String, ByRef connection As ADODB.Connection) As Boolean
If connection Is Nothing AndAlso Connect(db, connection) Then
Return True
ElseIf connection IsNot Nothing AndAlso connection.State <> 1 AndAlso Connect(db, connection) Then
Return True
ElseIf connection IsNot Nothing AndAlso connection.State = 1 Then
Return True
Else
Return False
End If
End Function

Public Function CreateConnection(ByVal conStr As String) As ADODB.Connection
Try
Dim tempCon As New ADODB.Connection()
tempCon.ConnectionTimeout = 0
tempCon.ConnectionString = conStr
tempCon.Open()
Return tempCon
Catch ex As Exception
Return Nothing
End Try
End Function

Public Function QueryDatabase(ByVal query As String, ByVal connection As ADODB.Connection) As ADODB.Recordset
If (connection IsNot Nothing AndAlso connection.State = 1) Then
Try
Dim cmd As New ADODB.Command
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.ActiveConnection = connection
cmd.CommandText = query
Return cmd.Execute()
Catch ex As Exception
Return Nothing
End Try
Else
Return Nothing
End If
End Function

Private Function SHA256Hash(ByVal hash As String) As String
Dim hashedReturn As String = String.Empty
Using shaCrypt As New SHA256CryptoServiceProvider()
Dim hashBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(hash)
Dim cryptedBytes As Byte() = shaCryp*****mputeHash(hashBytes)
hashedReturn = String.Join("", cryptedBytes.Select(Function(b As Byte) b.ToString("x2")).ToArray())
End Using
Return hashedReturn
End Function
[/highlight]

Now, here's how I would write the Database function (NOTE: I don't like AES encryption, or your multi-table style, so I omitted that.) In my case there is a single table containing the username and a SHA256-encrypted password (which is fairly hard to crack, to say the least). There's no need for a reversible encryption algorithm here, as you can just compare the hash of the given password to the stored hash.

Here's the code:
[highlight=vb.net]
Private Function CheckLogin(ByVal username As String, ByVal password As String, ByVal connection As ADODB.Connection) As Boolean
Dim success As Boolean = False
If Not (String.IsNullOrEmpty(username) OrElse String.IsNullOrEmpty(password)) Then
If (connection IsNot Nothing AndAlso connection.State = 1) Then
Dim record As ADODB.Recordset = QueryDatabase("SELECT COUNT(*) AS count FROM your_database WHERE username=" + username + " AND password=" + SHA256Hash(password), connection)
If record IsNot Nothing AndAlso Not record.EOF Then
Dim matches As Integer = 0
Integer.TryParse(record.Fields("count").Value, matches)
If matches = 1 Then
success = True
End If
End If
End If
End If
Return success
End Function
[/highlight]

Okay, that's all the functions done, now you just need to put it together:

[highlight=vb.net]
Dim con As New ADODB.Connection() 'initialize a new connection
Dim dbLocation As String = "C:\Examples\Test.mdb"
If PrepareDatabase(dbLocation, con) Then
'database connection was successful, continue
If CheckLogin("foo", "bar", con) Then
MessageBox.Show("Successfully Logged In", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End If
[/highlight]

thanks Jason,
but need to use my method of encryption, and a multi table style...
there the 2 things that cant change....(restricted to those 2 things)

multi table thing is for a competition my teacher is having with me, and i need some practice and revision before i try and well, verse him at it.
and the encryption thing is a thing i put up againced him.
(his was the multi table style...)

if your able to keep those 2 things, would be great, otherwise, i'll just make a hybrid of this if i have to.

again, thanks.

just modify my check login then.

If you want, show me an example of how the tables are laid out and I'll write it as an ADO example.

^added the database as an attachment

easier than me explaining it....i think...or..

TBlist -
username - 3rd item,
and there are 4 users. ranging from row 0 to 3.

TBpasswords -
password is item 1, same layout for rows.
passkey is item 2, same layout or rows

Ah that's fairly simple, except for the fact that your keys don't align. You need to add a field to your password table that holds an ID corresponding to a specific user or this won't work well (it may work, but without proper indexing this is a bad practice. There should always be a logical link between two tables. I'm going to write this with an extra "ID" field added to the TBPasswords table.

[highlight=vb.net]
Private Function CheckLogin(ByVal username As String, ByVal password As String, ByVal connection As ADODB.Connection)
Dim success As Boolean = False
If Not (String.IsNullOrEmpty(username) OrElse String.IsNullOrEmpty(password)) Then
If (connection IsNot Nothing AndAlso connection.State = 1) Then
Dim record As ADODB.Recordset = QueryDatabase("SELECT p.*, u.* FROM TBpasswords p, TBlist u WHERE u.username = '" + username + "' AND p.ID = u.ID", connection)
If (record IsNot Nothing AndAlso Not record.EOF) Then
'... validate the login, do this yourself. Put your crap here, before "record.MoveNext()". That's the final check.
record.MoveNext()
If (success) Then success = record.EOF 'make sure only one result was returned.
End If
End If
End If
Return success
End Function
[/highlight]

Now all you need to do is complete the function. The 'record' variable holds one row after these checks.
To access the various fields is a simple matter of:
[highlight=vb.net]
record.Fields("fieldname").Value
[/highlight]

All fields from both tables were imported so you can access their fields without discrepancy. There is an ambiguity of the ID field between the two tables so they are called "p.ID" and "u.ID" for TBpasswords and TBlist respectively.

Enjoy.

jason, can you strip that down to its bare essentials?...
there is always going to be a username and password, so is the :
If Not (String.IsNullOrEmpty)
needed?...

Originally Posted by *****179

jason, can you strip that down to its bare essentials?...
there is always going to be a username and password, so is the :
If Not (String.IsNullOrEmpty)
needed?...

You should always have concise functions that aim to handle their input internally. Especially where databases are concerned. But yeah you could probably get rid of it.

dont know why....

Post a Reply

Similar Threads

Tags for this Thread