无法使用vb.net将数据保存到sql [英] Unable to save data to sql using vb.net

问题描述

我正在尝试将数据保存到mdf文件中，该文件已从SQL服务器分离并添加到解决方案资源管理器中的我的APP_DATA文件夹中，但我无法执行此操作。我们将不胜感激。谢谢！这是我的代码：

I am trying to save data to an mdf file which has been detached from SQL server and added to my APP_DATA folder in Solution Explorer, but I am unable do that. Your help will be appreciated. Thank you! Here is my code:

Dim con As New SqlConnection
Dim cmd As New SqlCommand

Dim EmployeeNo As Integer
Dim EmployeeName As String
Dim SupervisorName As String
Dim DateCreated As Date
Dim WeekRange As String
Dim MonthRange As String
Dim ScheduleIn As String
Dim ScheduleOut As String
Dim WorkStatus As String

EmployeeNo = EmpNoText.Text
EmployeeName = EmpNameText.Text
SupervisorName = TeamLeadDropDown.Text
DateCreated = DateCreatedTextBox.Text
WeekRange = WeekRangeTextBox.Text
MonthRange = MonthDropDown.Text
ScheduleIn = ScheduleInBox.Text
ScheduleOut = ScheduleOutBox.Text
WorkStatus = StatusDropDown.Text


Try
    con.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf;Initial Catalog=JIBKPI;Integrated Security=True"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "INSERT INTO AgentAttendance ([EmployeeNo], [EmployeeName], [SupervisorName], [DateCreated], [WeekRange], [MonthRange], [ScheduleIn], [ScheduleOut], [WorkStatus]) VALUES (@EmployeeNo, @EmployeeName, @SupervisorName, @DateCreated, @WeekRange, @MonthRange, @ScheduleIn, @ScheduleOut, @WorkStatus,)"
    cmd.ExecuteNonQuery()
    MsgBox("Successfuly saved!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly)
Catch ex As Exception
    MsgBox("Error: Unable to save data.")
Finally
    con.Close()
End Try

这是我在web.config中的连接字符串：

Here is my connection string in web.config:

<connectionStrings>
<add name="JIBKPIConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=&quot;C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\JIBKPI.mdf&quot;;Initial Catalog=JIBKPI;Integrated Security=True" providerName="System.Data.SqlClient"/>

推荐答案

快速浏览一下代码，为什么你的代码没有保存数据有两个原因。


首先，你没有为SqlCommand提供任何参数值对象，因此没有给语句写入数据库的任何内容。



其次，在尝试执行SQL语句时，此代码应该爆炸。你有一个额外的逗号，不应该在你提供的CommandText的最后。仅这一点应该导致SQL Server抛出错误。但是，当你看到Catch black吞噬错误时，你永远不会知道出了什么问题。您可能想要更改 MsgBox 代码以包含递交给您的错误消息，以便您可以更轻松地诊断问题。

After a quick glance at the code, there are two reasons why you're code isn't saving the data.

First, you're not providing any of the parameter values to the SqlCommand object and therefore not giving the statement anything to write to the database.

Second, this code should explode when trying to exectue the SQL statement. You have an extra comma that should not be there at the very end of the CommandText you provided. This alone should cause the SQL Server to throw an error. But, seeing as youre Catch black is swallowing the error, you'd never know what went wrong. You may want to change your MsgBox code to include the error message that's handed to you so you can diagnose your problems easier.

To除了戴夫所说的，MsgBox在网站环境中是一个非常糟糕的主意 - 它在开发中工作，因为客户端和服务器是同一台机器 - 但是在生产中立即失败，因为消息框显示在服务器上，客户端无法看到或响应，以及它在哪里，直到托管管理员对你生气...



尝试使用MDB文件时也存在类似的问题直接在网站上，除非你确切地知道你在做什么和代码来解决问题：MDB不是多用户访问的好格式，这是网站的全部内容。再一次，在开发中运行良好的东西往往在生产中相当惊人。对于多用户访问，您需要将数据库放回SQL或MySql下，否则以后会遇到大问题。

To add to what Dave says, MsgBox is a very bad idea in a website environment - it works in development because the Client and the server are the same machine - but is fails immediately in production, because the message box is displayed at the Server, where the client cannot see it or respond, and where it sits until the hosting admin gets annoyed with you...

A similar problem also exists with trying to use MDB files directly in websites, unless you know exactly what you are doing and code to allow for the problems: MDB is not a good format for multi-user access which is what websites are all about. Again, what works fine in development tends to fall over fairly spectacularly in production. For multi user access, you need to put your DB back under SQL or MySql, or you will get huge problems later.

这篇关于无法使用vb.net将数据保存到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！