Can't get data to commit?

[Francesco]

Getting my feet wet with .NET, and I bump in to this stupid problem that I trust you can help me with.

I have a (local) database, with some stored procedures for inserting, deleting, etc.

Stored procedures work just fine when I execute them by hand.

Created a tableadapter with these stored procedures in it. Now when I use that to insert a new record, something seeeeeems to happen. I get (good)results back from the insert and update functions, It is skipping existing records in another sub (meaning that it does not insert records twice). But if I check my database at the end of the program, no records have been added.

It is just as if no commit has taken place

Now I added commits in my SP. Based on what I read at MSDN, I added an update after my insert, but still....nada.

This CANNOT be so hard! ......Can it?

[SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] LoadName([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] name [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] res [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] tableadapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] CatalogDataSetTableAdapters.ArtistTableAdapter[/SIZE]
[SIZE=2]res = tableadapter.Insert(name, "")[/SIZE]
[SIZE=2]res = tableadapter.Update(name)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

[SIZE=2][COLOR=#0000ff]ALTER PROCEDURE [/COLOR][/SIZE][SIZE=2]dbo.spInsertName(@Name [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2](50),@Notation  [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2](50))[/SIZE]
[SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]INSERT INTO [/COLOR][/SIZE][SIZE=2][Names] [/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2][Name], [/SIZE]
[SIZE=2][Notation][/SIZE]
[SIZE=2]) [/SIZE]
[SIZE=2][COLOR=#0000ff]VALUES [/COLOR][/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]@Name, [/SIZE]
[SIZE=2]@Notation[/SIZE]
[SIZE=2])[/SIZE]

I tried the blasted thing also with a sqlCommand, but with the same results (or lack of).

 

[gmacek]

Here is the code that will do what you want. You do not need a table adapter in this case, a simple command.executescalar will do.

I tried to put enough comments into the code to explain each step.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'setup and populate the variables we want to write to the DB
Dim myName As String = "Joe Doe"
Dim myNotation As String = "myNotes"
'Set up your SQL Query. This is the name of your stored procedure
Dim Query As String = "spInsertName"
'set the connectionstring here
Dim connectionString As String = "your connection string here"
Dim connection As New SqlConnection(connectionString)
'set the command here
Dim command As New SqlCommand(Query, connection)
'make sure the command type is set to be a stored procedure
command.CommandType = CommandType.StoredProcedure
'Prepare the command parameters
command.Parameters.Add(New SqlParameter("@Name", myName))
command.Parameters.Add(New SqlParameter("@Notation", myNotation))
'let's use as try/catch block to get the error message, just in case
Try
'open the connection to the DB
connection.Open()
'execute the command
command.ExecuteScalar()
Catch ex As Exception
'in case of an error bring up a message box
MessageBox.Show(ex.Message)
Finally
'make sure we close the connection and dispose of the command object
connection.Close()
command.Dispose()
End Try
End Sub

I hope this helps.

Have fun.
G.

 

[Francesco]

Thanks G.

But the problem does not appear to be with the adapter or the vb code. As I mentioned, I tried inserting it through a SQLConnection as well.

Besides, prior to this sub sits one in the line of 'if not exists, then insert'. It does not process double records.

So during execution, the data is available in an instance(?) of my database. But it's like it is rollbacked at the end of the show.

Now I am under the assumptions that a. SQLServer (Express) is set to autocommit by default and b. Stored Procedures in SQLServer do not require (or support) commit statement.

I might be wrong, but as you can see above I did try it with specific commit commands as well without success.

 

[gmacek]

Hi Francesco,

I guess I do not understand what you are saying. You only need to commit if you use transactions in a database. What I understand is that you simply want to insert a row in your DB table. The code I gave you would do this. There is no need to use a data adapter, that is don't use your
Sub LoadName(ByVal name AsString)
....
Just copy my code into you project and try it.

However, If you do db transactions, that is you do an Insert in table A and afterwards an Update in table B, and only if the Update in table B is successful you commit the Insert to table A, that is a different story.

G.

 

[cjard]

If youre using a file-based database technology, you should ensure that your database file is not being repeatedly copied to the output directory each time the project is built; this has caught oput a few of my friends with their access databases; updates appeared to not be working but it was because the original database was being copied over the live one each time they ran the project

 

[Francesco]

I am trying to say that the data is there during execution time, but not afterwards. It makes no difference if I use SQLcommand or data adapters.

It could be that I am working in a database instance that is not being moved to the actual db, but that is what a commit is all about, isn't it?

I am starting to suspect that it is something on the database side of things, and at this point I should mention that I am using VB express with a SQL Express database. Are there any limitations that I should be aware of? Any parameters that require some out-of-the-box tweeking?

Frustration kicks in.

 

[Francesco]

Got it

This article explains my predicament

http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

Now I would expect a simpler solution for working (and testing) with a local database, but that's a different story

Thanks for the help, onwards we march.

 

[cjard]

So.. you were working with a file-based SSX database? Was it indeed being overwritten every time you started the project?