使用 VB .NET 将数据插入 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14127777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert data into SQL Server using VB .NET
提问by Bellu
i'm trying to insert some sample data into a sql server.
我正在尝试将一些示例数据插入到 sql server 中。
I'm using Visual Basic 2010 Express.
我正在使用 Visual Basic 2010 Express。
Here's the code:
这是代码：
Public Sub insert()
Dim myconnect As New SqlClient.SqlConnection
myconnect.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DATABASE_NUOVO.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
mycommand.Connection = myconnect
mycommand.CommandText = "INSERT INTO utenti (nome) VALUES ('mario')"
myconnect.Open()
Try
mycommand.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.Message)
End Try
myconnect.Close()
MsgBox("Success")
End Sub
The code seems to run correctly, but when i look into the database after running the debug i don't see the sample data.
代码似乎运行正确，但是当我在运行调试后查看数据库时，我没有看到示例数据。
Where is the problem?
问题出在哪儿？
Thanks
谢谢
回答by marc_s
As I've said before on this site - the whole User Instance and AttachDbFileName=approach is flawed - at best! Visual Studio will be copying around the .mdffile and most likely, your INSERTworks just fine - but you're just looking at the wrong .mdf filein the end!
正如我之前在本网站上所说的那样 - 整个User Instance 和 AttachDbFileName=方法是有缺陷的 - 充其量！Visual Studio 将在.mdf文件周围复制，很可能，您的INSERT工作正常 - 但您最终只是在查看错误的 .mdf 文件！
If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close()call - and then inspect the .mdffile with SQL Server Mgmt Studio Express - I'm almost certain your data is there.
如果您想坚持使用这种方法，请尝试在myConnection.Close()调用上设置断点- 然后.mdf使用 SQL Server Mgmt Studio Express检查文件 - 我几乎可以肯定您的数据在那里。
The real solutionin my opinion would be to
在真正的解决办法在我看来，将
install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g.
Database_Nuovo)connect to it using its logical database name(given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
Data Source=.\SQLEXPRESS;Database=Database_Nuovo;Integrated Security=Trueand everything else is exactlythe same as before...
安装 SQL Server Express（无论如何你已经完成了）
安装 SQL Server Management Studio Express
在SSMS Express 中创建您的数据库，给它一个逻辑名称（例如
Database_Nuovo）使用它的逻辑数据库名称（在服务器上创建它时给出）连接到它- 不要乱用物理数据库文件和用户实例。在这种情况下，您的连接字符串将类似于：
Data Source=.\SQLEXPRESS;Database=Database_Nuovo;Integrated Security=True其他一切都和以前完全一样......
Also: check what the value of the Copy to Output Directoryproperty is on your DATABASE_NUOVO.mdffile in the App_Datadirectory (find it inside your Visual Studio Solution Explorer).
另外：检查目录中文件Copy to Output Directory上的属性值是什么（在 Visual Studio 解决方案资源管理器中找到它）。DATABASE_NUOVO.mdfApp_Data
What might happen (and does, more often than not):
可能会发生什么（并且经常发生）：
- when Visual Studio starts your app for debugging, it copies
Database_Nuovo.mdfto the output directory where the app is running (your.\debug\bindirectory) - your
INSERTthen runs against this copyof the.mdffile and works just fine - you stop debugging and go check the database file again - but this time, you're looking at the
Database_Nuovo.mdfin theApp_Datadirectory --> and of courseyour inserted data isn't theresince it was inserted into a different file!
- 当 Visual Studio 启动你的应用程序进行调试时，它会复制
Database_Nuovo.mdf到应用程序运行的输出目录（你的.\debug\bin目录） - 您
INSERT然后运行针对该副本中的.mdf文件和工作得很好 - 你停止调试，并再次去检查数据库文件-但是这一次，你看
Database_Nuovo.mdf在App_Data目录- >和当然你插入的数据是不存在的，因为它被插入到不同的文件！
回答by RBarryYoung
Try changing your Try..Catchcode to handle more error types than just SqlExceptions. Like this:
尝试更改您的Try..Catch代码以处理更多的错误类型，而不仅仅是 SqlExceptions。像这样：
Try
mycommand.ExecuteNonQuery()
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.Message, , "Sql Exception")
Catch ex As System.Exception
MsgBox(ex.Message, , "General Exception")
End Try
SqlExceptions are not the only exceptions that can be thrown here.
SqlExceptions 不是唯一可以在这里抛出的异常。
回答by ridoy
Try with it..
试试看。。
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DATABASE_NUOVO.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" //check your connection string carefully whether it points right directory
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])" //make sure here your table and column name is exactly like as your database
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table...");
Finally
con.Close()
End Try
