Solved

Why wont my SQL server table save inserted records using an adapter with VB.net

Posted on 2011-09-30
30
365 Views
Last Modified: 2012-05-12
I am new VB.net and SQL Server, I am using VB Express 2010 and SQL Server 2008 R2 express. I have tried many examples of inserting records in to SQL Server and they all have the same problem. The database table will not store the inserted records, I am thinking it could be a SQL server configuration problem.
I simply have a gridview and a button to refresh the gridview,  I have a textbox and a button to save the text to the database table. I enter some text in the text box, click save, then click the Display button and the gridview displays the text entered. I can repeat this many times and the gridview always shows all entries. However when I view the database table there are no records added.
Please help.
Imports System.Data.SqlClient

Public Class frmDatabase

    Private Const ConnectionString As String = "Data Source=.\SQLEXPRESS;" & _
            "AttachDbFilename=|DataDirectory|\Practice.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(ConnectionString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property
    Public Function GetData() As DataView
        Dim SelectQry = "SELECT * FROM tblParts "
        Dim SampleSource As New DataSet
        Dim TableView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = SelectQry
            SampleCommand.Connection = Connection
            SampleDataAdapter.SelectCommand = SampleCommand
            SampleDataAdapter.Fill(SampleSource)
            TableView = SampleSource.Tables(0).DefaultView

        Catch ex As Exception
            Throw ex
        End Try

        Return TableView
    End Function


    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
        DataGridView1.DataSource = GetData()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        
        Dim InsertQry = "insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')"
        Dim con As SqlConnection
        con = Connection
        Dim adapter As New SqlDataAdapter
        Try
            adapter.InsertCommand = New SqlCommand(InsertQry, con)
            adapter.InsertCommand.ExecuteNonQuery()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        End
    End Sub

End Class

Open in new window

0
Comment
Question by:dearness
  • 16
  • 14
30 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 36894473
        Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')",con)
        Try
            Cmd.ExecuteNonQuery()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window


Use SqlCommand
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894495
 Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')",con)
        Try
            con.open()
            Cmd.ExecuteNonQuery()
            con.close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window


ups, you must open the connection first
0
 

Author Comment

by:dearness
ID: 36894498
Crashman,
Thanks for the reply, The gridview displays the additional records as beforebut the database table is still empty. If I close the VB express program and then re start it, the gridview is empty.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894501
you must open and close de connections

 Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')",con)
        Try
            con.open()
            Cmd.ExecuteNonQuery()
            con.close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window

0
 

Author Comment

by:dearness
ID: 36894507
Crashman,
Your second example gives an invalidOperationException, The connection was not closed, the connections current state is open.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894514
ok, that's because you have a exception, and the code dont pass for the con.close
restar the applications and put this, try and tell if you recieve errors
 Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')",con)
        Try
            con.open()
            Cmd.ExecuteNonQuery()
            con.close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
            If con.State = ConnectionState.Open Then
                then con.close   
            End If
        End Try

Open in new window

0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894520
Dammit, error in the code

If con.State = ConnectionState.Open Then
                con.close   
End If

Open in new window


remplace with this
0
 

Author Comment

by:dearness
ID: 36894552
Crashman,
Still same exception error,
 Exception error Exception error
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894563
OK, the problem is because the connections is open in some place before the con.open
try this again

if this works is because you are open the connection in some place before...
 Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')")
        Try
            If con.State = ConnectionState.Close Then
            con.open()
            end if

            Cmd.ExecuteNonQuery()
            con.close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
            If con.State = ConnectionState.Open Then
               con.close   
            End If
        End Try

Open in new window

0
 

Author Comment

by:dearness
ID: 36894579
Crashman,
I know what you are saying, just cant find the problem.
Syntax error in you last example,
If con.State = ConnectionState.Close Then
'Close' is not a memeber of system.Data.ConnectionState

I think I need to forget the gridview load and concentrate on the save to database
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894592
Sorry,

If con.State = ConnectionState.Closed then
0
 

Author Comment

by:dearness
ID: 36894603
Different exception error

Exception -2
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894622
Dammit

Dim con As SqlConnection
        con = Connection 
        Dim Cmd as new SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')", con)
        Try
            If con.State = ConnectionState.Closed Then
            con.open()
            end if

            Cmd.ExecuteNonQuery()
            con.close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
            If con.State = ConnectionState.Open Then
               con.close   
            End If
        End Try

Open in new window

0
 

Author Comment

by:dearness
ID: 36894637
Crashman,
No erros but exactly same problem, database table still remains empty even though the gridview shows data entries.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894651
Ok, here exist another problem, can you post the entire code a one example of you database?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:dearness
ID: 36894655
Shut down application and re-start, datagrid empty reflection empty database table, This is CRAZY.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894677
Post your code, and will see
0
 

Author Comment

by:dearness
ID: 36894697
Complete code attached.
Imports System.Data.SqlClient

Public Class frmDatabase

    Private Const ConnectionString As String = "Data Source=.\SQLEXPRESS;" & _
            "AttachDbFilename=|DataDirectory|\Practice.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(ConnectionString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property
    Public Function GetData() As DataView
        Dim SelectQry = "SELECT * FROM tblParts "
        Dim SampleSource As New DataSet
        Dim TableView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = SelectQry
            SampleCommand.Connection = Connection
            SampleDataAdapter.SelectCommand = SampleCommand
            SampleDataAdapter.Fill(SampleSource)
            TableView = SampleSource.Tables(0).DefaultView

        Catch ex As Exception
            Throw ex
        End Try

        Return TableView
    End Function


    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
        DataGridView1.DataSource = GetData()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        
        Dim InsertQry = "insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')"

        Dim con As SqlConnection
        con = Connection
        Dim Cmd As New SqlCommand("insert into tblParts (PART_ID) values('" & CStr(txtNewEntry.Text) & "')", con)
        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

            Cmd.ExecuteNonQuery()
            con.Close()
            MsgBox("Row inserted !! ")
        Catch ex As Exception
            MsgBox(ex.ToString)
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try


    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        End
    End Sub

End Class

Open in new window

0
 

Author Comment

by:dearness
ID: 36894729
A couple of DB screen shots, dont know if it helps.
DB Table - 1 DB Table - 2
0
 
LVL 8

Accepted Solution

by:
Crashman earned 500 total points
ID: 36894759
AHHHHHHH, i think, you must change the property of the database in the proyect, i reallly dont remember, but the database has 3 properties, 1 copy the database everytime, or 2 dont copy, and the problem is because everytime you run your application the database is copy from the proyect to the bin folder and overwrite the database with the information...
0
 

Author Comment

by:dearness
ID: 36894802
Crashman,
I see a Copy to Output Directory property for the database., I tried all three options.
Do not copy - throws a whole new exception
Copy always - default
Copy if newer - no change
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894808
ok, for i can see is a example or a test proyect, can you upload?
0
 

Author Comment

by:dearness
ID: 36894839
Having trouble with uploading, some files not allowed to be uploaded.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894843
with winrar?...exist a lot of server, rapidshare, megaupload, etc...
0
 

Author Comment

by:dearness
ID: 36894870
Tried 7-Zip didn't work. Let me winrar
0
 

Author Comment

by:dearness
ID: 36894898
Winrar also wont do it.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 36894907
0
 

Author Comment

by:dearness
ID: 36894969
http://www.mediafire.com//ee.db

Hope this works, I really appreciate you help and persistence. I really want to start getting some database applications going.
0
 

Author Comment

by:dearness
ID: 36894971
0
 

Author Closing Comment

by:dearness
ID: 36898183
Trap for young players alright, I learnt alot thanks.
The debug database was the one I saw updating, not the one I thought.

http://www.go4answers.com/Example/vbnet-seems-update-debug-database-33638.aspx
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
Help with simplifying SQL 6 49
email about the whoisactive result 7 27
SQL Server Question 5 26
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now