[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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

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
dearness
Asked:
dearness
  • 16
  • 14
1 Solution
 
CrashmanCommented:
        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
 
CrashmanCommented:
 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
 
dearnessAuthor Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
CrashmanCommented:
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
 
dearnessAuthor Commented:
Crashman,
Your second example gives an invalidOperationException, The connection was not closed, the connections current state is open.
0
 
CrashmanCommented:
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
 
CrashmanCommented:
Dammit, error in the code

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

Open in new window


remplace with this
0
 
dearnessAuthor Commented:
Crashman,
Still same exception error,
 Exception error Exception error
0
 
CrashmanCommented:
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
 
dearnessAuthor Commented:
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
 
CrashmanCommented:
Sorry,

If con.State = ConnectionState.Closed then
0
 
dearnessAuthor Commented:
Different exception error

Exception -2
0
 
CrashmanCommented:
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
 
dearnessAuthor Commented:
Crashman,
No erros but exactly same problem, database table still remains empty even though the gridview shows data entries.
0
 
CrashmanCommented:
Ok, here exist another problem, can you post the entire code a one example of you database?
0
 
dearnessAuthor Commented:
Shut down application and re-start, datagrid empty reflection empty database table, This is CRAZY.
0
 
CrashmanCommented:
Post your code, and will see
0
 
dearnessAuthor Commented:
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
 
dearnessAuthor Commented:
A couple of DB screen shots, dont know if it helps.
DB Table - 1 DB Table - 2
0
 
CrashmanCommented:
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
 
dearnessAuthor Commented:
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
 
CrashmanCommented:
ok, for i can see is a example or a test proyect, can you upload?
0
 
dearnessAuthor Commented:
Having trouble with uploading, some files not allowed to be uploaded.
0
 
CrashmanCommented:
with winrar?...exist a lot of server, rapidshare, megaupload, etc...
0
 
dearnessAuthor Commented:
Tried 7-Zip didn't work. Let me winrar
0
 
dearnessAuthor Commented:
Winrar also wont do it.
0
 
CrashmanCommented:
0
 
dearnessAuthor Commented:
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
 
dearnessAuthor Commented:
0
 
dearnessAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now