add or remove things?

[donavan]

I have been trying to find a good tutorial/howto on this for literally weeks now and I can find lots of examples of code nothing that really explains what the heck is going on line by line. Here is my problem. I have a SQL CE file I need to be able to write and delete from. currently I have the SQL CE file linked to a couple of combo boxes and I can get them to display things in them but at some point I will need to add or remove things from the lists the combo boxes display. Can someone out there please either tell me how to do this or give me a good website that explains everything that is going on. I haven't done much coding since the days of VB5&6 so I need something that doesn't assume i know everything already. This is what i have so far but it errors out. Thanks for the help.

Option Explicit On
Option Strict On

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Data.SqlClient
Public Class CustomerEntry
    Dim newCust As String = txtCustAdd.Text
    Public Const ConnStr As String = "Data Source = .\Prep.sdf; File Mode=Shared Read; Persist Security Info=False"
    Dim InsertQuery As String = "INSERT INTO CustomerInfo(CompanyName) Values('newCust')"
    Dim myConnection As New SqlConnection
    Dim myCommand As SqlCommand
    Private Sub CustomerEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.CustomerInfoTableAdapter.Fill(Me.PrepDataSet.CustomerInfo)
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        myConnection.Open(ConnStr)
        myCommand = New SqlCommand(InsertQuery)
        myConnection.Close()
        MsgBox("Record Added")
    End Sub
End Class

 

[demausdauth]

One thing that would help is what the error is that you are getting. Also what the steps are that you do that cause the error.

By the code that you show I am assuming that you receive the error when you click on the button.

Option Explicit On
Option Strict On

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Data.SqlClient
Public Class CustomerEntry

    Public Const ConnStr As String = "Data Source = .\Prep.sdf; File Mode=Shared Read; Persist Security Info=False"

    Dim myConnection As New SqlConnection
    Dim myCommand As SqlCommand
    Private Sub CustomerEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.CustomerInfoTableAdapter.Fill(Me.PrepDataSet.CustomerInfo)
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim newCust As String = txtCustAdd.Text
        Dim InsertQuery As String = "INSERT INTO CustomerInfo(CompanyName) Values('newCust')"

        Dim ExecuteMessage As String = "New Recorded Added: "
        Dim myResults As Integer = -1

        Try

        
            'Make your connection -- since the variable is already created we need to 
            ' instantiate a new instance of the connection
            myConnection = New SqlConnection(ConnStr)

            'Check to see what state it is in
            If Not myConnection.State = ConnectionState.Open Then myConnection.Open()

            'Now we need a command object -- the command object needs to know at minimum 
            ' what connection to use and the command it is going to run. Since we know 
            ' these ahead of time we can put them in the constructor
            myCommand = New SqlCommand(InsertQuery, myConnection)

            'Now you have to do something with your command

            ' ''Performs the sql statement and returns an integer of the number of rows affected
            ' '' Good for Inserts, Updates, and Deletes -- returns -1 for Selects no matter
            ' '' how many rows are affected by the select.
            ''Dim myResults As Integer = myCommand.ExecuteNonQuery()

            ' ''Returns a sqlData reader object -- which allows you to read the data returned in 
            ' '' a forward only manner.
            ''Dim sqlReader As SqlDataReader = myCommand.ExecuteReader()

            ' ''Performs the sql statement and returns only the results in the first column, first row
            ' ''  I most often use when I want a Select Count(*) From Table
            ''Dim obj As Object = myCommand.ExecuteScalar()

            'I choose to use the ExecuteNonQuery since we are inserting a new row into the database
            myResults = myCommand.ExecuteNonQuery()

            ExecuteMessage &= myResults.ToString()

        Catch ex As SqlException
            ExecuteMessage = ex.Message

        Catch ex As Exception
            ExecuteMessage = ex.Message
        Finally
            'Once the command has been executed then we need to close the command and connection objects
            myCommand.Dispose()
            myConnection.Close()
            myConnection.Dispose()
        End Try

        MessageBox.Show(ExecuteMessage)
        
    End Sub
End Class

Honestly, I would use this approach.

Private Sub ButtonClickEvent(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'With the information you have provided I would set my click event up more like this
        ' The Using keywords will automatically dispose of managed objects
        ' Even though the Using keywords handle the disposal of the managed objects automatically
        ' we still want to handle the errors if present so that is why we still use a Try...Catch
        Dim ExecuteMessage As String
        If Not String.IsNullOrEmpty(txtCustAdd.Text) Then

            ExecuteMessage = "New Recorded Added: "
            Dim myResults As Integer = -1

            Try
                'Make the new connection object
                Using sqlCon As New SqlConnection(ConnStr)

                    'check to see if it is open
                    If Not sqlCon.State = ConnectionState.Open Then sqlCon.Open()

                    'Make the new command object
                    ' The command text here is using a sql parameter @NewCustomer 
                    Using sqlCmd As New SqlCommand("INSERT INTO CustomerInfo(CompanyName) Values(@NewCustomer)", sqlCon)

                        'The reason that I use a parameter here is then the value passed into the 
                        ' parameter object is made query safe. Best practice use parameters when 
                        ' you can.

                        'Add the parameter to the command object, as a varchar(50) change size accordingly
                        sqlCmd.Parameters.Add("@NewCustomer", SqlDbType.VarChar, 50)

                        'Set the value of the parameter
                        sqlCmd.Parameters("@NewCustomer").Value = txtCustAdd.Text

                        'Execute my command object and get the number of rows affected
                        myResults = sqlCmd.ExecuteNonQuery()

                        ExecuteMessage &= myResults.ToString()

                    End Using 'end sqlCmd
                End Using 'end sqlCon

            Catch ex As SqlException
                ExecuteMessage = ex.Message

            Catch ex As Exception
                ExecuteMessage = ex.Message
            End Try
        Else
            ExecuteMessage = "Please enter a Customer Name"
        End If 'end check for new customer

        MessageBox.Show(ExecuteMessage)
    End Sub

Using Statement (Visual Basic)

VB.NET Sample code: Using SqlParameter Class