TextBox to Database [HELP!]

[Andrees92]

Hi, this is my first post, and I'm in need of some help making an application that saves the data from a textbox to an access database.

This is my code, it gives no errors, but when I use it, ie, I input the text in the textbox and click update, it won't work, the value won't be added to the database, any help is appreciated.

Imports System.Data.OleDb

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.TablaTableAdapter.Fill(Me.BaseDataSet.Tabla)

        Dim objcmd As New Data.OleDb.OleDbCommand

        Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Resumen Guia\Base.mdb")

        Button1.BackColor = System.Drawing.Color.CornflowerBlue

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

        Dim con As New OleDb.OleDbConnection

        Dim cmd As OleDb.OleDbCommand

        Dim icount As Integer

        Dim str As String

        Try

            con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Resumen Guia\Base.mdb")

            'con.Open()

            str = "insert into Tabla values('" & TextBox1.Text & "')"

            cmd = New OleDb.OleDbCommand(str, con)

            icount = cmd.ExecuteNonQuery

            MessageBox.Show(icount)


        Catch

        End Try

        'con.Close()

    End Sub

    Private Sub TablaBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TablaBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TablaBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.BaseDataSet)

    End Sub


End Class

I know the con.Open and .Close are commented, but don't worry, because even uncommented they won't work.

[Reverend Jim]

It would help if you actually put some code in your Catch block otherwise you will never see that you are getting an error. Your first problem is that you are not opening the connection to the database. You commented out that line. Try cnanging your code to

Try
    con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Resumen Guia\Base.mdb")
    con.Open()
    str = "insert into Tabla values('" & TextBox1.Text & "')"
    cmd = New OleDb.OleDbCommand(str, con)
    icount = cmd.ExecuteNonQuery
    MessageBox.Show(icount)
Catch ex As Exception
    MsgBox(ex.Message)
End Try

I see you also declared a connection object in the Form1_Load but you don't do anything with it. I suggest you create the object at the class level (global) and set the connection string once in Form1_Load. That way if you change your data source you only have to modify the string in one place.

[Andrees92]

Done. It works perfectly, but I have another question. Is it possible to modify this code to make it work with a Service-Based Database? I believe I should change the OleDb.OleDbConnection for something that refers to SQL, but don't know exactly what, any pointers will help.
Thanks in advance!
-- Andres

[Reverend Jim]

What do you mean by a "service-based database"? If you are going to convert from Access to SQL server then you can either change your connection string accordingly, or you can use SqlClient which is optimized for SQL Server (and also allows you to use named parameters). Examples of how to connect can be found in this code snippet

[Andrees92]

Don't worry, I'll stick to OLEDB, I bother you one last time :P.
The Insert command works great, BUT, I'm having trouble with the UPDATE command, I tried with two or three, but they don't seem to work, and besides I cannot understand them fully. What I want to do is to edit this columns: Address, CUIT, Cuenta Corriente. The table also has the Name column, but the user would select the Name he wants to edit from a ComboBox and edit only Address, CUIT and cuenta corriente.
Best regards.

[Andrees92]

Solved, I managed to fix the update and now it updates the Table, but it updates EVERY record on it (I have 4 names: Albert, John, Kelly, Matt, those 4 names will have their CUIT, Cuenta Corriente, and Address records, I only want one of them, which was already specified to have its records modified).

My code:

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Dim con As New OleDb.OleDbConnection

        Dim cmd As OleDb.OleDbCommand

        Dim icount As Integer

        Dim str As String

        Try
            con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Resumen Guia\Base.mdb")
            con.Open()
            str = "UPDATE Tabla SET Ciudad = mCiudad, CUIT = mCUIT, [Cuenta Corriente] = mCC WHERE Nombre = Nombre"
            cmd = New OleDb.OleDbCommand(str, con)
            Dim param As New OleDb.OleDbParameter
            Dim param2 As New OleDb.OleDbParameter
            Dim param3 As New OleDb.OleDbParameter
            Dim param4 As New OleDb.OleDbParameter
            'add content txtProdName as parameter
            param.ParameterName = "mCiudad"
            param.Value = TextBox19.Text()
            cmd.Parameters.Add(param)
            param2.ParameterName = "mCUIT"
            param2.Value = TextBox20.Text()
            cmd.Parameters.Add(param2)
            param3.ParameterName = "mCC"
            param3.Value = TextBox21.Text
            cmd.Parameters.Add(param3)
            param4.ParameterName = "Nombre"
            param4.Value = ComboBox4.Text
            cmd.Parameters.Add(param4)

            icount = cmd.ExecuteNonQuery
            MessageBox.Show(icount)
        Catch ex As Exception
            MsgBox("Cliente editado con éxito!")
        End Try

    End Sub

[Reverend Jim]

You just have to specify enough fields in the WHERE clause to uniquely identify the record you want to UPDATE. If that's not clear enough then post some sample data and I'll try to show you.

[Andrees92]

My database.

Name│Address│CUIT│Cuenta Corriente
A1│A1's address│A1's CUIT│A1's CuentaCorriente
A2│A2's address│A2's CUIT│A2's CuentaCorriente
A3│A3's address│A3's CUIT│A3's CuentaCorriente

Thats basically what it looks like

[Andrees92]

I was able to fix it, as I was using a ComboBox to select the record to edit, I changed the WHERE to:

WHERE Nombre = ComboBox1.SelectedValue

Being Nombre(name) the column in the database.
Thanks for the help Jim!
-- Andres