"Text instance" "original" Operation SQLite database with vb.net

Reprint please indicate source: Hatemath return (http://www.cnblogs.com/hatemath/)

Environment:

Visual Studio 2017 Community Edition

64-bit Windows 7 system

The new solution is based on .Net 4.5.0.0 

Steps:

1. Download and install System.Data.SQLite

Home: http://system.data.sqlite.org/index.html

Because I am a 64-bit Win7, the new solution is based on. Net 4.5.0.0, so the download is for the 64-bit,. net4.5 version:

Http://system.data.sqlite.org/blobs/1.0.105.2/sqlite-netFx45-setup-x64-2012-1.0.105.2.exe

The default installation path is: C:\Program files\system.data.sqlite

2. Create a new solution with vb.net, based on. NET 4.5.0.0

Then place a DataGridView, a button, and the properties are all default.

3. Adding a reference to a DLL in vb.net

In the solution Manager, locate the project name (not the solution name), right-click the popup menu, select: Add-"reference

Then proceed as shown:

4. Enter the test code

Code reference from Wind Cloud Studio, http://blog.csdn.net/angxiao/article/details/7732018

1 Imports System.Data.SQLite
 2 Public Class Form1
 3 Dim conn As SQLiteConnection
 4 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 5 If System.IO.File.Exists("test.db3") = True Then
 6 System.IO.File.Delete("test.db3")
 7 End If
 8 SQLiteConnection.CreateFile("test.db3")
 9 conn = New SQLiteConnection("Data Source=test.db3;Pooling=true;FailIfMissing=false")
10 If conn.State <> ConnectionState.Open Then
11 conn.Open()
12 MsgBox ("Open successfully!")
13 End If
14
15 Dim cmd As New SQLiteCommand
16 cmd.Connection = conn
17 cmd.CommandText = "CREATE TABLE Test (ID INTEGER PRIMARY KEY, TestName VARCHAR(500), TestTime DateTime, Operator VARCHAR(100))"
18 Dim result As Integer = cmd.ExecuteNonQuery()
19 If result = 0 Then
20 MsgBox ("success")
21 Else
22 MsgBox ("Failed")
23 End If
twenty four 
25 cmd = conn.CreateCommand()
26 cmd.CommandText = "insert into Test(TestName,TestTime,Operator)values(@Name,@TestTime,@Operator)"
27 cmd.Parameters.Add("@Name", Data.DbType.String).Value = "Motion"
28 cmd.Parameters.Add("@TestTime", Data.DbType.DateTime).Value = Now()
29 cmd.Parameters.Add("@Operator", Data.DbType.String).Value = "peer"
30 result = cmd.ExecuteNonQuery()
31 If result <> 0 Then
32 MsgBox ("Insert success")
33 End If
34 SelectShowInfo()
35
36 ‘
37 cmd = conn.CreateCommand()
38 cmd.CommandText = "update Test set TestName=‘moving 1'"
39 result = cmd.ExecuteNonQuery()
40 If result <> 0 Then
41 MsgBox ("Update Successful")
42 End If
43 SelectShowInfo()
44
45 ‘
46
47 cmd = conn.CreateCommand()
48 cmd.CommandText = "delete from Test"
49 result = cmd.ExecuteNonQuery()
50 If result <> 0 Then
51 MsgBox ("delete successfully")
52 End If
53 SelectShowInfo()
54
55 cmd.Dispose()
56
57 If conn.State = ConnectionState.Open Then
58 conn.Close()
59 End If
60 End Sub
61
62 Public Sub SelectShowInfo()
63 Dim sa As New SQLiteDataAdapter("select * from Test", conn)
64 Dim ds As New System.Data.DataSet
65 sa.Fill(ds, "Test")
66 Dim mytable As New System.Data.DataTable
67 mytable = ds.Tables("Test")
68 Me.DataGridView1.DataSource = mytable
69 Me.DataGridView1.Refresh()
70 End Sub
71 End Class 

5. First run, click Button1 and the exception appears:

System.dllnotfoundexception: "Unable to load DLL" SQLite.Interop.dll ": The specified module could not be found.

Workaround:

Copy the SQLite.Interop.dll and SQLite.Interop.pdb from the C:\Program files\system.data.sqlite\2012\bin to the debug directory of the project.

6. Second run of the program, error again:

It's not scientific. Obviously is a normal 64-bit platform DLL file, why is the format is not correct?

It turns out that the problem is on the solution platform of Visual Studio, the default is "any CPU"

We need to create a new 64-bit platform:

And remember to copy the SQLite.Interop.dll and SQLite.Interop.pdb to the Bin\x64\debug directory of the project, or the DLL can not be found.

7. The third run of the program, you can open the database normally, and successfully insert, modify, delete records.

Finish

Reprint please indicate source: Hatemath return (http://www.cnblogs.com/hatemath/)

"Text instance" "original" Operation SQLite database with vb.net