Re: insert and update in vb.net

1. Why not use Npgsql? Are you developing one source to connect to a variety of DB backends?
2. Do you actually need commands with parameters?
3. The order of your construction seems strange. I would expect
a. Create the dataset
b. Update rows in the dataSET
c. Use the dataAdapter w/CommandBuilder to update back to the source
This way the changes to the rows, trigger the rowUpdating event when Command Builder is attached.
4. If I were not going to use the dataset,..like in your example
I would use the much simpler command.ExecuteNonQuery.
This would avoid transferring from server to client all the records (SELECT * FROM test) into a
detatched dataset.

Now if you are going to databind to a control like a grid, the issue is just the order the code units get
called in. In that case you are likely to to construct and bind a dataset in a form_load() and update from
the datset in a grid_afterupdate() event.

Freeking Wu wrote:
> hi, I want to operate the postgres database via vb.net. I download the
> postgres server 8.1 and the latest pgsql-odbc drviers. But question comes.
> I can select data from db correctly . But when I insert or update the data,
> no error or exception occurs. but the db takes no effect. I test the odbc
> driver with access 2002 and I can update or insert data. I changed several
> parameters with the connection string .But it doesn't work .How can I do?
>
> following is my main code.
>
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim strConn As String
> strConn = "DRIVER=PostgreSQL
> ANSI;SERVER=localhost;PORT=5432;UID=postgres;Password=postgres;DATABASE=test;pwd=postgres;ReadOnly=0"
> 'strConn = "DRIVER={PostgreSQL
> ANSI};UID=postgres;pwd=postgres;LowerCaseIdentifier=0;UseServerSidePrepare=0;ByteaAsLongVarBinary=0;"
> _
> & "Protocol=6.4;" _
> & "ReadOnly=1;" _
> & "SSLMODE=disable;" _
> & "PORT=5432;" _
> & "SERVER=localhost;" _
> & "DATABASE=test;"
> 'strConn = "dsn=test;uid=postgres;pwd=postgres"
>
> Dim cnDb As OdbcConnection
> Dim dsDB As New DataSet
> Dim adDb As OdbcDataAdapter
> Dim cbDb As OdbcCommandBuilder
> Dim cmd As OdbcCommand
> Dim cmdIns As OdbcCommand
>
> Try
> cnDb = New OdbcConnection(strConn)
> cnDb.Open()
> dsDB = New DataSet
> adDb = New OdbcDataAdapter
> cbDb = New OdbcCommandBuilder(adDb)
>
> ' Create the SelectCommand.
>
> cmd = New OdbcCommand("SELECT * FROM test ", cnDb) ' & _
> '"WHERE id = ? ", cnDb)
>
> 'cmd.Parameters.Add("@id", OdbcType.NVarChar, "1")
>
> adDb.SelectCommand = cmd
>
> 'adDb.UpdateCommand = New OdbcCommand(strConn, cnDb)
> cmd = New OdbcCommand("UPDATE test SET name = ? " & _
> "WHERE id = ?", cnDb)
>
> cmd.Parameters.Add("@name", OdbcType.NChar, 50, "test444")
> cmd.Parameters.Add("@id", OdbcType.Int, 8, 4)
>
> adDb.UpdateCommand = cmd
>
> cmdIns = New OdbcCommand("INSERT INTO test values(100,'100')",
> cnDb)
> adDb.InsertCommand = cmdIns
>
> adDb.Fill(dsDB)
> adDb.Update(dsDB)
> cnDb.Close()
>
> Finally
> If Not cnDb Is Nothing Then cnDb.Dispose()
> End Try
> End Sub
>