Hi
habe mir mal ein Sqlite example runtergeladen um mich mal mit Sqlite zu beschäftigen.
Die Tabellen habe ich mit DB Browser for Sqlite erstellt.
Das Problem was ich habe, ist das löschen eines Datensatzes komme da einfach nicht klar.
Hier mal der Code
Bekomme diese Meldung.
Wahrscheinlich sitze ich schon zu lange an diesem Problem und habe die Übersicht verloren...
habe mir mal ein Sqlite example runtergeladen um mich mal mit Sqlite zu beschäftigen.
Die Tabellen habe ich mit DB Browser for Sqlite erstellt.
Das Problem was ich habe, ist das löschen eines Datensatzes komme da einfach nicht klar.
Hier mal der Code
VB.NET-Quellcode
- Imports System.IO
- Imports System.Data.SQLite
- Imports System.ComponentModel
- Public Class admin
- Private connectionString = String.Empty
- Private password_changed As Boolean = False
- Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
- ' declare the SQLite database connection string
- connectionString = String.Format("Data Source={0};Version=3;",
- Directory.GetCurrentDirectory() & "\" & "myadress.db")
- ' Load Users List
- LoadUserFromDb()
- End Sub
- ' Populate the ListBox with user data from database table "users"
- Private Sub LoadUserFromDb()
- Dim userInfo As DataRow = Nothing
- Dim sql As String = "SELECT * FROM users;"
- Try
- Dim current_index As Int16 = lb_users.SelectedIndex
- Using conn As New SQLiteConnection(connectionString.ToString)
- Using cmd As New SQLiteCommand(conn)
- cmd.CommandText = sql
- conn.Open()
- Using da As New SQLiteDataAdapter(cmd)
- Dim dt As New DataTable
- da.Fill(dt)
- If dt.Rows.Count > 0 Then
- lb_users.DisplayMember = "user_fullname"
- lb_users.ValueMember = "user_id"
- lb_users.DataSource = dt
- End If
- End Using
- End Using
- End Using
- If current_index > 0 Then
- lb_users.SelectedIndex = current_index
- End If
- Catch ex As Exception
- MsgBox(ex.Message)
- End Try
- End Sub
- Private Sub LoadUserInfo(user_id As String)
- Dim userInfo As DataRow = Nothing
- Dim sql As String = "SELECT * FROM users WHERE user_id = @user_id;"
- Try
- Using conn As New SQLiteConnection(connectionString.ToString)
- Using cmd As New SQLiteCommand(conn)
- cmd.Parameters.AddWithValue("@user_id", user_id)
- cmd.CommandText = sql
- conn.Open()
- Using da As New SQLiteDataAdapter(cmd)
- Dim dt As New DataTable
- da.Fill(dt)
- If dt.Rows.Count > 0 Then
- userInfo = dt.Rows(0)
- txt_user_id.Text = userInfo("user_id")
- txt_user_name.Text = userInfo("user_name")
- txt_user_fullname.Text = userInfo("user_fullname")
- txt_user_password.Text = userInfo("user_password")
- 'CheckBox1.Text = userInfo("admin")
- txt_user_salt.Text = IIf(IsDBNull(userInfo("user_salt")), "", userInfo("user_salt"))
- password_changed = False
- End If
- End Using
- End Using
- End Using
- Catch ex As Exception
- MsgBox(ex.Message)
- End Try
- End Sub
- ' This function will create new user.
- Private Sub insert_new_user()
- Dim SQL As String = String.Empty
- Dim iReturnValue As Integer = 0
- Dim oCmd As New SQLiteCommand
- Dim passSalt As String = String.Empty
- Dim passHash As String = String.Empty
- passSalt = Utils.CreateSalt()
- passHash = Utils.GetSaltedHash(txt_user_password.Text.Trim, passSalt)
- ' Add new user
- SQL &= "INSERT INTO users (user_name, user_fullname, user_password, user_salt,admin) "
- SQL &= "VALUES (@user_name, @user_fullname, @user_password, @user_salt,@admin)"
- Try
- With oCmd
- .Parameters.AddWithValue("@user_name", txt_user_name.Text.Trim)
- .Parameters.AddWithValue("@user_fullname", txt_user_fullname.Text.Trim)
- .Parameters.AddWithValue("@user_password", passHash)
- .Parameters.AddWithValue("@user_salt", passSalt)
- .Parameters.AddWithValue("@admin", CheckBox1.Checked.ToString.Trim)
- End With
- Using conn As New SQLiteConnection(connectionString.ToString)
- Using oCmd
- oCmd.Connection = conn
- oCmd.CommandText = SQL
- conn.Open()
- iReturnValue = oCmd.ExecuteNonQuery()
- End Using
- End Using
- LoadUserFromDb()
- Catch ex As Exception
- If ex.Message.Contains("Einschränkung fehlgeschlagen") Then
- MsgBox("Der Anmeldename muss eindeutig sein")
- Else
- MsgBox(ex.Message)
- End If
- End Try
- End Sub
- ' This function will update the information and password of the current user
- Private Sub update_user_data()
- Dim SQL As String = String.Empty
- Dim iReturnValue As Integer = 0
- Dim oCmd As New SQLiteCommand
- Dim passSalt As String = String.Empty
- Dim passHash As String = String.Empty
- If password_changed = True Then
- passSalt = Utils.CreateSalt()
- passHash = Utils.GetSaltedHash(txt_user_password.Text.Trim, passSalt)
- ' Update the current user information
- ' the current password has been changed, create new salt and new hashed passord
- SQL &= "UPDATE users SET user_name = @user_name, user_fullname = @user_fullname,admin "
- SQL &= "user_password = @user_password, user_salt = @user_salt,@admin "
- SQL &= "WHERE user_id = @user_id"
- With oCmd
- .Parameters.AddWithValue("@user_id", txt_user_id.Text.Trim)
- .Parameters.AddWithValue("@user_name", txt_user_name.Text.Trim)
- .Parameters.AddWithValue("@user_fullname", txt_user_fullname.Text.Trim)
- .Parameters.AddWithValue("@user_password", passHash)
- .Parameters.AddWithValue("@user_salt", passSalt)
- .Parameters.AddWithValue("@admin", CheckBox1.Checked.ToString.Trim)
- End With
- Else
- ' Update the current user information
- ' the current passord didn't changed, no need to create salt and hashed password
- SQL &= "UPDATE users SET user_name = @user_name, user_fullname = @user_fullname,admin=@admin "
- SQL &= "WHERE user_id = @user_id"
- With oCmd
- .Parameters.AddWithValue("@user_id", txt_user_id.Text.Trim)
- .Parameters.AddWithValue("@user_name", txt_user_name.Text.Trim)
- .Parameters.AddWithValue("@user_fullname", txt_user_fullname.Text.Trim)
- .Parameters.AddWithValue("@admin", CheckBox1.Checked.ToString.Trim)
- End With
- End If
- Try
- Using conn As New SQLiteConnection(connectionString.ToString)
- Using oCmd
- oCmd.Connection = conn
- oCmd.CommandText = SQL
- conn.Open()
- iReturnValue = oCmd.ExecuteNonQuery()
- End Using
- End Using
- LoadUserFromDb()
- Catch ex As Exception
- MsgBox(ex.Message)
- End Try
- End Sub
- Private Sub btn_exit_Click(sender As Object, e As EventArgs) Handles btn_exit.Click
- Me.Close()
- End Sub
- Private Sub MainForm_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
- Application.Exit()
- End Sub
- Private Sub lb_users_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lb_users.SelectedIndexChanged
- Try
- Dim user_id As String = lb_users.SelectedValue.ToString
- LoadUserInfo(user_id)
- Catch ex As Exception
- End Try
- End Sub
- Private Sub btn_add_user_Click(sender As Object, e As EventArgs) Handles btn_add_user.Click
- txt_user_name.Select()
- txt_user_id.Clear()
- txt_user_name.Clear()
- txt_user_fullname.Clear()
- txt_user_password.Clear()
- txt_user_salt.Clear()
- End Sub
- Private Sub btn_save_user_Click(sender As Object, e As EventArgs) Handles btn_save_user.Click
- If String.IsNullOrEmpty(txt_user_id.Text.Trim) Then
- ' Add new user
- insert_new_user()
- Else
- ' Update current user information
- update_user_data()
- End If
- End Sub
- Private Sub txt_user_password_TextChanged(sender As Object, e As EventArgs) Handles _
- txt_user_password.TextChanged
- password_changed = True
- End Sub
- Private Sub Btn_Delete_Click(sender As Object, e As EventArgs) Handles Btn_Delete.Click
- Dim connect As New SQLite.SQLiteConnection()
- Dim command As SQLiteCommand
- connectionString = String.Format("Data Source={0};Version=3;",
- Directory.GetCurrentDirectory() & "\" & "myadress.db")
- 'connect.Open()
- 'connect.Close()
- connect.Open()
- command = connect.CreateCommand
- command.CommandText = "DELETE FROM users WHERE user_id =@user_id" 'Die Query
- command.ExecuteNonQuery() ' Die Query wird ausgeführt
- command.Dispose()
- connect.Close() 'Schließen
- End Sub
- End Class
Bekomme diese Meldung.
Wahrscheinlich sitze ich schon zu lange an diesem Problem und habe die Übersicht verloren...