Update/Save SQL table

[Goofy]

I founded this kind of a code for a saving table to sql database.
http://www.dbazine.com/cook5.html

 yhteys is my connection string...

Dim sqlConn As SqlConnection(yhteys)

Dim sqlRegion As SqlDataAdapter = _
              New SqlDataAdapter("select * from region", sqlConn)

Dim sqlTerritories As SqlDataAdapter = _
              New SqlDataAdapter("select * from territories", sqlConn)

Dim regionCommandBuilder As SqlCommandBuilder = _  
                         New SqlCommandBuilder(sqlRegion)
Dim territoriesCommandBuilder As SqlCommandBuilder = _
                              New SqlCommandBuilder(sqlTerritories)

sqlRegion.Update(dsNorthwind, "Region") 
sqlTerritories.Update(dsNorthwind, "Territories")
sqlConn.Close()
sqlConn.Dispose()

what are "region" and "territories"? am i supposed to fill thoes with my table names or something? Or is there an easier way to update database?

 

[Robby]

"region" and "territories" are supposed to be tables in the database. But Northwind doesnt contain those tables.

Im sure if you follow that tutorial step-by-step it will work.

 

[Goofy]

hmm ok

i tried this and i did read the whole tutorial

Public Class Form1
.....
Private dsTietokantaOhjelma As New DataSet("TietokantaOhjelma")
.....
TietoKantaOhjelma is like northwind where my table is located
Number is primary key
name is the name of the Table
Sub Save()
Dim con As New SqlConnection(MyConnString)
Dim sqlNumber As SqlDataAdapter = New SqlDataAdapter("Select from number", MyConnString)
Dim SqlTable As SqlDataAdapter = New SqlDataAdapter("Select from " & nimi, MyConnString

Dim NumberCommandBuilder As SqlCommandBuilder = NewSqlCommandBuilder(sqlNumber)
Dim TableCommandBuilder As SqlCommandBuilder = NewSqlCommandBuilder(sqlTable)

sqlNumber.Update(dsTietokantaOhjelma,  "Number")
sqlTable.Update(dsTietokantaOhjelma,  name
con.close()
con.dispose()

End sub

And the only error message i get while runing is:

sqlNumber.Update(dsTietokantaOhjelma, "Number")
Error message:
Unhandled exception of type System.InvalidOperationException occurred ion system.data.dll
Additional information: Update unable to find TabMapping[DVD] or DataTab

 

[Robby]

In the declare you put nimi

in the Update you put Name and did not close the bracket at the end.

Rename your variable to something else, Name is a reserved word.

I suggest that you put Option Explicit On at the top of each code page.

 

[Goofy]

hmm ok but. im not doing this program with this pc iam writing here so i couldnt copy paste my code. actually im not using name variablae. its nimi but nimi = name in english so making it more clear to you i desided to use "name" instead of "nimi"

bracket is closed in my program. just forgot to write it there

Im sure about that it cant find that row called "Number" (i use "numero in my program but its "number" in english. That number(numero) row is my primary key.

Error message now:
Error message:
Unhandled exception of type System.InvalidOperationException occurred ion system.data.dll
Additional information: Update unable to find TabMapping[Number] or DataTable Numero.

I think i must somehow define that primary key row and table name. or something like that

 

[Goofy]

ok now it works.

Public Class Form1
PRivate dsTietokantaOhjelma As New Dataset("TietokantaOhjelma")

----------------------------
Sub SaveTable()

Dim sqlTaulu As DataAdapter = New SqlDataAdapter("Select * from " & TableName, MyConnString)

Dim TauluCommandBuilder As SqlCommandBuilder = NEw SqlCommandBuilder(sqlTaulu)

sqlTaulu.Update(dsTietokantaOhjelma, TableName)
End sub

thanks anyway