从vb.net插入sql服务器时出现问题 [英] Problem in insertion into sql server from vb.net

问题描述

我有一个从vb.net到表中插入数据的问题，当我执行该程序并执行插入操作时，似乎没有将数据插入数据库中.但是如果我将数据集与填充功能一起使用，则插入的数据将出现在我为其创建的dataGridView中.

现在我有这段代码用于测试数据库插入.卜也指责同样的问题

I had a problim in inserting data to table from vb.net where when im excute the program and perform an insertion it does not appear that that the data was inserted in database. but if i used the data set with fill function the data was inserted appear in the dataGridView that i was created for.

now i have this code for testing database insertion. Bu also the same problem accured

Public conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True")
   Public comm As New SqlCommand
   Public adapt As New SqlDataAdapter
   Public Database1DataSet As New DataSet
   Public sqlquery As String
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       Try
           conn.Open()
           Dim sql As String
           sql = "INSERT INTO Employee (Name) VALUES (''" & TextBox1.Text & " '')"
           adapt.InsertCommand = New SqlCommand(sql, conn)
           adapt.InsertCommand.ExecuteNonQuery()
           TextBox1.Clear()
       Catch ex As Exception
           MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
       Finally
           conn.Close()
       End Try



   End Sub

请问连接字符串是否正确?这个问题的原因是什么?

Does The connection string is correct ?? What is the reson of this problem ?? What is The solution for this ??

推荐答案

这对我有用:
在这种情况下，我使用的是服务器精简版，但是字符串应该相同.

This works for me:
I am using in this case Server compact version, however the string should be the same.

Friend Shared Sub CheckInStaff(ByVal id As Integer, ByVal checkInTime As Date, ByVal checkInDate As Date)

         Using ceconn As New SqlCeConnection(My.Settings.TimeDataConn)
            Dim mycmd As SqlCeCommand
            If ceconn.State <> ConnectionState.Open Then
               ceconn.Open()
            End If
            Const sqlstr As String = "insert into Hours(StaffID, StartTime, StartDate,IsCheckOut) values (@Staffid, @StartTime, @StartDate,0)"
            Try
               mycmd = New SqlCeCommand(sqlstr, ceconn)
               mycmd.Parameters.AddWithValue("@Staffid", id)
               mycmd.Parameters.AddWithValue("@StartTime", checkInTime)
               mycmd.Parameters.AddWithValue("@StartDate", checkInDate)
               mycmd.ExecuteNonQuery()
            Catch ex As SqlCeException
               MessageBox.Show(ex.Message.ToString)
            Finally
               ceconn.Close()
            End Try
         End Using

是的，sql server或sql紧凑，sql字符串相同
当然，您必须对其进行更改以适合您，但是它给了您一个主意
否则，您可以在SQL中创建存储过程并从VB.net调用它们.
像这样:(它来自我正在开发的一个程序，它位于数据类"中)

Yeah,sql server or sql compact, the sql string is the same
of course you have to change it to fit you, but it give you a idea
otherwise you can make stored procedures in SQL and Call them from VB.net

Like this:(It from one program i am working on and it is place in a "Data Class")

Dim conn As New SqlConnection(My.Settings.ConnString)
If conn.State = ConnectionState.Closed Then conn.Open()
Dim trans = conn.BeginTransaction
Using cmd As New SqlCommand()
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "uspSaveCustomer"
cmd.Connection = conn
cmd.Transaction = trans
cmd.Parameters.Add("@custID", SqlDbType.VarChar).Value = ID
cmd.Parameters.Add("@custTitle", SqlDbType.VarChar).Value = Title
cmd.Parameters.Add("@custFirstName", SqlDbType.VarChar).Value = FirstName
cmd.Parameters.Add("@custLastName", SqlDbType.VarChar).Value = LastName
cmd.Parameters.Add("@custAddress", SqlDbType.VarChar).Value = Address
cmd.Parameters.Add("@custLevel", SqlDbType.VarChar).Value = Level
cmd.Parameters.Add("@custPostalCode", SqlDbType.Int).Value = PostalCode
cmd.Parameters.Add("@custPostalCity", SqlDbType.VarChar).Value = PostalCity
cmd.Parameters.Add("@custPhone", SqlDbType.VarChar).Value = Phone
cmd.Parameters.Add("@custExt", SqlDbType.VarChar).Value = Ext
cmd.Parameters.Add("@custMobile", SqlDbType.VarChar).Value = Mobile
cmd.Parameters.Add("@custFax", SqlDbType.VarChar).Value = Fax
cmd.Parameters.Add("@custEmail", SqlDbType.VarChar).Value = Email
cmd.Parameters.Add("@custInternet", SqlDbType.VarChar).Value = Internet
cmd.Parameters.Add("@custCompanyID", SqlDbType.Int).Value = CompanyID
cmd.Parameters.Add("@custStatus", SqlDbType.SmallInt).Value = Status
cmd.Parameters.Add("@custType", SqlDbType.SmallInt).Value = Type
cmd.Parameters.Add("@custOtherInfo", SqlDbType.VarChar).Value = OtherInfo
cmd.Parameters.Add("@custDeleted", SqlDbType.Bit).Value = CInt(Deleted)
cmd.Parameters.Add("@custAllowCompOrders", SqlDbType.Bit).Value = CInt(AllowCompOrders)
cmd.Parameters.Add("@custIsCompany", SqlDbType.Bit).Value = CInt(IsCompany)
cmd.Parameters.Add("@custTakenBy", SqlDbType.VarChar).Value = TakenBy
cmd.Parameters.Add("@custRegDate", SqlDbType.SmallDateTime).Value = RegDate
cmd.Parameters.Add("@custBirthDate", SqlDbType.SmallDateTime).Value = BirthDate
cmd.ExecuteScalar
trans.Commit()
If conn.State = ConnectionState.Open Then conn.Close()

.... .......(这里有一些错误处理代码)
希望它能给您一些有用的东西...

...........(Some error handling code after here)
Hope it give you something to work with...

问题是:
将复制到输出目录"设置为始终复制"
因此将数据库在运行状态下复制到Debug文件夹中，并将更改保存到Debug Folder内的数据库中，但是该数据库出现在Visual Studio中并没有改变，而在项目的主文件夹中.

解决方案是将属性设置为请勿复制"

The problem was:
The "copy to output directory " set to "Copy always"
So the database copied into folder Debug at run state ,the chnges saved into database inside Debug Folder, But the database appeares in visual studio does not changed witch is in the main folder of the project.

The solution is to set the property "do not copy"

这篇关于从vb.net插入sql服务器时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！