SQL Express not saving anything. Help!

[ghall426]

OK. I've asked a few questions here and have had a few problems along the way, but I think my problems might be due to visual basic, not my programming! I am using VB Express 2005 with a SQL 2005 database.

At first, I tried programming the database as code. I can get the new rows to add to memory. When I use the update command, it returns a number equal to the number of rows I'm trying to add. It looks like everything is working fine, but the data is never added to the database.

I can use the "database explorer" and add data manually and it works OK there.

Next, I tried not writing any code and just creating a new database, attaching it as a datasource, and dragging it over to my form as a datagrid. Same thing. I can add files to the datagrid but nothing is ever saved back to the .mdf file.

I've tried reinstalling VB and all SQL components but it hasn't helped. I don't know if there's some sharing violation that it can't write to the file? I've tried creating new apps and new databases. Are there other files I need to delete in addition to uninstalling the app? I don't know what else to try. Any help at all would be appreciated!

[tommygrayson]

Ok. First post the code where the part of not saving your data is mentioned. I read you were updating something.

[ghall426]

Dim myCon As SqlConnection = New SqlConnection(My.Settings.ConnString)
Dim myDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Rescue", myCon)
Dim myDS As DataSet = New DataSet
myDA.Fill(myDS, "Rescue")

Dim myCB As SqlCommandBuilder = New SqlCommandBuilder(myDA)

Dim myRow As DataRow = myDS.Tables("Rescue").NewRow
myRow.Item("CallNum") = "33"
myDS.Tables("Rescue").Rows.Add(myRow)
myDA.Update(myDS, "Rescue")

The connection string was auto-generated. It is:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CallLog.mdf;Integrated Security=True;User Instance=True

The last line of code with the update command returns a 1 meaning 1 row added successfully. I can put this code in a button and run it multiple times adding 1 row each time.

Looking at myDS.Tables("Rescue").Rows.Count will show me the total rows I've added. If I try to view the data in "Database Explorer" it doesn't exist. Once I stop the execution and restart it, the data no longer exists.

It would be helpful if somebody could paste the code in a button and create a database with 1 table "Rescue" and 1 field "CallNum" and tell me what happens.

Greg

[sapator]

Try a
"myDS.AcceptChanges"
before updating.

[ghall426]

Try a "myDS.AcceptChanges" before updating.

It still doesn't work. The Update command now returns 0.

Greg

[sapator]

Another 1.
Try updating without the table
myDA.Update(myDS)
also there is a parameter myda.endinit, try to use it before adding any row to the dataset.
Sorry i got to go (it's 4 in the morning )

[ghall426]

Another 1.
Try updating without the table
myDA.Update(myDS)

That generates an error:
Update unable to find TableMapping['Table'] or DataTable 'Table'.

So I need the table name.

Can somebody please try executing my block of code to see if it's my computer or VB at fault or indeed a problem with the coding. Please?

Greg

[ghall426]

Ok. I solved my own problem. Now that I know what the problem is, I searched for it and found it to be a very common problem. Here's one thread:
http://www.vbforums.com/showthread.p...=copy+if+newer

Basically the database in the app directory gets copied to the bin/debug folder every time you run or debug the application. You need to change the "copy always" to "copy if newer".

Also, if viewing the data in "database explorer", be sure to link up to the bin/debug temporary database. The one in your main application directory will never change.

Greg

[szlamany]

So this is SQL SERVER EXPRESS - not MS SQL SERVER 2005 - right?

You should download the free management tool for SQL SERVER EXPRESS.

It's the third download at this site:

http://msdn.microsoft.com/vstudio/express/sql/

That way you will be able to "view" the server operations - actually see yourself connected to a database - see what the properties are to that database.

I'm guessing that you are attaching the database at runtime - as opposed to having the server be "always" attached to the database. Is there a reason you are doing it this way?