Insert text from text box to access db

[christopherwms]

I am having trouble getting the program to actually insert a string from a textbox to an access db. Below is my code. I have a total of 4 tables, and am wanting to insert into the table titled ticket. I am wanting to insert into a field titled ID. I am trying to learn this for school, but the books the school uses doesn't cover this at all. All help is appreciated.

Public Sub insert()
Dim da As DataSet
Dim command As OleDbCommand
Dim sql As String = "select ticket from ticket"
Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Chris\Documents\Visual Studio 2008\Projects\WindowsApplication3\WindowsApplication3\bin\Debug\ETUO1.accdb;")
con.Open()
Dim SQLstr As String
Dim dp As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Insert into Ticket", con)
SQLstr = String.Format("INSERT INTO TableTicket VALUES('{0}')", txtNumber.Text)
command = New OleDbCommand(SQLstr, con)
MessageBox.Show("works")
con.Close()

End Sub

[jmcilhinney]

You're fairly close but you're not actually executing your command. Follow the Data Access link in my signature. There are also a couple of other little things you should fix up, like specifying what columns to inset the data into and using parameters.

[christopherwms]

Here is my current code, I am having trouble with the Using connnection, and Using command lines.

Public Sub insert()
Dim connection As OleDb.OleDbConnection
Dim command As OleDb.OleDbCommand




Using connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Chris\Documents\Visual Studio 2008\Projects\WindowsApplication3\WindowsApplication3\bin\Debug\ETUO1.accdb;")
Using command As New OleDbCommand("SELECT TICKET(ID) FROM ETUO1", connection)
command.Parameters.AddWithValue("@ID", txtNumber.Text)
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())

'Use totalQuantity here.
End Using
End Using


connection.Close()

End Sub

[jmcilhinney]

First up, this is just a bit vague:

I am having trouble with the Using connnection

What does that mean? It's not actually fun to guess.

Secondly, that is not valid SQL code. If your intent is to get the Ticket value from the ETOU1 table for a specific ID then you need to write valid SQL for the purpose:

SELECT Ticket FROM ETUO1 WHERE ID = @ID

Finally, why would you assign that Ticket value to a variable of type Double named 'totalQuantity'? Is it the total quantity of anything? Is it a Double? It's a ticket number, isn't it, so shouldn't the variable be named 'ticketNumber' or the like? The value is an integer isn't it, so shouldn't the variable be type Integer?

[christopherwms]

I am now gett a syntax error on the line that has the following:
adapter.InsertCommand = cmd()
row("tktNum") = tktNumber.Text

The table is ticket, and the field is tktNum
Here is the code:

Try
            Dim tktNumber As Double
            Using conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Chris\Documents\Visual Studio 2008\Projects\WindowsApplication3\WindowsApplication3\bin\Debug\ETUO1.accdb;")

                Using adapter As New OleDb.OleDbDataAdapter("SELECT tktNum FROM Ticket", conn)
                    Using cmd As New OleDb.OleDbCommand("INSERT INTO Ticket (tktNum) VALUES (@tktNum)", conn)
                        cmd.Parameters.AddWithValue("@tktNum", "tktNumber")
                        adapter.InsertCommand = cmd()
                        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                        Dim table As New DataTable
                        adapter.FillSchema(table, SchemaType.Source)
                        Dim row As DataRow = table.NewRow()
                        row("tktNum") = tktNumber.Text
                        table.Rows.Add(row)
                        adapter.Update(table)
                        conn.Open()
                        cmd.ExecuteNonQuery()
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

[jmcilhinney]

adapter.InsertCommand = cmd()

What are the parentheses for?

[christopherwms]

Sorry that was a typo; now the only error left is this:
error BC30456: 'Text' is not a member of 'Double'.
This is from: row("tktNum") = tktNumber.Text

[jmcilhinney]

That code has some real problems. Follow the Data Access link in my signature and work out which example matches what you want to do, then implement the same pattern in your code. At no point do I mix AddWithValue and ExecuteNonQuery with a DataTable and DataAdapter.

[christopherwms]

Here is the new problem; the db is now only showing tktNumber.Text inside each cell for an entry. Here is the code

Try
                        Using conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|Data Directory|\ETUO1.accdb;")

                                Using cmd As New OleDb.OleDbCommand("INSERT INTO Ticket tktNum) VALUES (@tktNum)", conn)
                    cmd.Parameters.AddWithValue("@tktNum", "tktNumber.Text")
                                                           conn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            Catch ex As Exception
            MsgBox(ex.Message)
        End Try

[jmcilhinney]

That code's much better. As for the issue, the database is displaying that value because that's the value you're assigning to the parameter:

cmd.Parameters.AddWithValue("@tktNum", "tktNumber.Text")

That has no relationship to any variable. It's a String literal containing those characters. The double quotes are saying use the characters within as literal text.

Also, you don't want the Text property of anything. Text is generally a property associated with controls. For instance, the Text property of a Label or TextBox is a String containing the text displayed in the control. You're using a Double, which is its own value. If you want the value of the tktNumber variable then you use the tktNumber variable:

cmd.Parameters.AddWithValue("@tktNum", tktNumber)

That said, I can't see why you'd be using a Double. The Double type is for floating-point values. Surely a ticket number doesn't have any fractional part, so it should be an integral type, i.e. Short, Integer or Long.

[christopherwms]

I have changed to a different text box which happens to be a multi line text box. When I perform the insert it doesn't appear to be storing all the lines. Is this an issue with the db or the insert statement.

Thanks in advance