Solved

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

Posted on 2011-09-30
30
363 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

18 Experts available now in Live!

Get 1:1 Help Now