Skip site navigation (1) Skip section navigation (2)

Re: insert and update in vb.net

From: "Campbell, Greg" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Freeking Wu <whtech(at)gmail(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: insert and update in vb.net
Date: 2005-12-19 15:28:35
Message-ID: 43A6D1A3.7070705@us.michelin.com (view raw or flat)
Thread:
Lists: 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
> 

Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)

In response to

pgsql-odbc by date

Next:From: noreplyDate: 2005-12-19 17:07:24
Subject: [ psqlodbc-Bugs-1000481 ] VFP and SQLCancel
Previous:From: Ludek FinstrleDate: 2005-12-19 13:50:20
Subject: Re: insert and update in vb.net

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group