Updating database from textbox.

[johmolan]

I am using the following code:


objXtra.Clear()
objConnection = New OleDbConnection(strConnectionString)
objCommand = New OleDbCommand

With objCommand
.CommandText = "INSERT INTO [Xtra] (Tabell) VALUES ('" & TextBox6.Text & "');"
.CommandType = CommandType.TableDirect
.Connection = objConnection
End With

objDataAdapter = New OleDbDataAdapter(objCommand)
objDataAdapter.Update(objXtra)
DataGridView2.DataSource = objXtra

objConnection.Dispose()
objConnection = Nothing
objCommand.Dispose()
objCommand = Nothing
objDataAdapter.Dispose()
objDataAdapter = Nothing


But it does not update the table, Caan anyone please help me out here,

 

[jmcilhinney]

You seem to have used the TableDirect enumeration member somewhat arbitrarily. This is what the documentation says:

When the CommandType property is set to TableDirect, the CommandText property should be set to the name of the table to be accessed. The user may be required to use escape character syntax or include qualifying characters if any of the tables named contain any special characters. All rows and columns of the named table will be returned when you call one of the Execute methods.

Does that apply to your situation? Have you set the CommandText to the name of a table? Are you trying to retrieve all rows and all columns of a table?

 

[johmolan]

I am trying to add info to only 1 of the 4 columns in the table, the tables name is "Xtra" and the column in named "tabell"

 

[jmcilhinney]

In my previous post I explained that TableDirect is wrong and why. You must obviously use a different value. What other values are there? What does each of them mean? Which one applies to your situation? I can tell you that there are only two other values, so you should be able to work out which is correct fairly easily. Even if you can't understand the documentation you should still be able to get it by trial and error.

 

[johmolan]

You asked the question:

Are you trying to retrieve all rows and all columns of a table?

And I just answered that question, I did not say if I had tried out your answer yet or not.

 

[jmcilhinney]

You asked the question:

Are you trying to retrieve all rows and all columns of a table?

And I just answered that question, I did not say if I had tried out your answer yet or not.

I didn't want an answer to that question. I already know that the answer is "no". You were supposed to ask yourself that question and use the answer to determine whether what you were doing made sense. Sorry if I didn't make that clear.

The idea is that if TableDirect is ONLY for retrieving all columns and all rows from the specified table and you do NOT want to retrieve all columns and all rows from the specified table then obviously TableDirect is not the value you should be using.

 

[johmolan]

I changed the code to:

objXtra.Clear()
objConnection = New OleDbConnection(strConnectionString2)
objCommand = New OleDbCommand

With objCommand
.CommandText = "INSERT INTO [Xtra] (Tabell) VALUES ('" & TextBox6.Text & "');"
.CommandType = CommandType.Text
.Connection = objConnection
End With

objDataAdapter = New OleDbDataAdapter(objCommand)
objDataAdapter.Update(objXtra)
DataGridView2.DataSource = objXtra

objConnection.Dispose()
objConnection = Nothing
objCommand.Dispose()
objCommand = Nothing
objDataAdapter.Dispose()
objDataAdapter = Nothing

But with this command the adapter is set to update and not add new rows to the table?? or am I wrong?

 

[jmcilhinney]

The only SQL code you have there is an INSERT statement so the only thing the adapter can do is insert new records.

 

[johmolan]

An that is exactly what I want it to do. add the text form the textbox into a new row in the table, but there still must be something wrong that I do since it still doesn't work. I I just can't seem to see what that is

 

[JohnH]

The only SQL code you have there is an INSERT statement so the only thing the adapter can do is insert new records.

Would it really be called for Update when the INSERT statement was assigned the SelectCommand? johmolan coded New OleDbDataAdapter(objCommand), and documentation is clear about what this constructor does.

With objXtra.Clear() there is also no rows to update. johmolan, if you're not really using DataSet/DataTable, then ditch the OleDbDataAdapter (bridges datatable/set with commands/connection to database) and just run the OleDBCommand with ExecuteNonQuery. Be sure to use Parameters and not hardcode query parameter values into the command text.

 

[jmcilhinney]

Would it really be called for Update when the INSERT statement was assigned the SelectCommand? johmolan coded New OleDbDataAdapter(objCommand), and documentation is clear about what this constructor does.

Good point.

 

[cjard]

But it does not update the table, Caan anyone please help me out here,

You'd make your life a lot easier if you would just read the tutorials I've been pointing you to for a while.

In this case, your table is called Xtra. You'd make a TableAdapter (the tutorial shows you how) based on the SQL :

SELECT * FROM Xtra

The tableadapter wizard would then also go and make for you, an Insert, an Update and a Delete query.
You'd ensure that ONE CHECKBOX (called Generate DBDirect Methods) is ticked, and then you'll also get available on the tableadapter Some methods you can directly use to insert/update and delete single records.

Thus you'd have some code that is 100% correct and working (Because the IDE wrote it for you) in less time than it took to write your post, and you'd use it like:


1: To Fill a Datatable (show to user and allow user to edit)
xtraTableAdapter.Fill(myDS.Xtra)

2: To upload the users changes back to the database
xtraTableAdapter.Update(myDS.Xtra) 'note that the word Update is not necessarily an SQL UPDATE. Update() will perform I/U/D as necessary depending on the datatable row states

3: To insert a single record (what youre tring to do now) from a text box
xtraTableAdapter.Insert(myTextBox.Text)


Or in the case of 3:, just have the textbox bound to a datatable, add a new row to the table, let the user type the value, then follow the instructions for 2:

In each scenario, I've typed 1 line of code that works. You have 17 lines of code that doesnt work.. I'll leave you to choose your route..