Problem in insertion into sql server from vb.net

Question

0.00/5 (No votes)

See more:

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 ??

Posted 25-Oct-11 23:44pm

Ahmed Abu Aziz

Updated 31-Oct-11 0:02am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VB Overlord · Answer 1 · 2011-10-25T23:52:00

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

VB

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

VB Overlord · Answer 2 · 2011-10-26T01:26:00

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")

VB

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...

Ahmed Abu Aziz · Answer 3 · 2011-11-12T20:54:00

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"

Problem in insertion into sql server from vb.net

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0