Re: insert and update in vb.net - Mailing list pgsql-odbc

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
>