insert and update in vb.net

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

> 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.

What version do you mean with "latest pgsql-odbc drviers"?

> 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?

Could you post mylog output? I want check if problem isn't in ODBC driver.

Thanks,

Luf

pgsql odbc driver is 8.01.02 . following is the log file. thank you very
much

[2032]globals.extra_systable_prefixes = 'dd_;'
[2032][[SQLAllocHandle]][2032]**** in PGAPI_AllocEnv **
[2032]** exit PGAPI_AllocEnv: phenv = 94773136 **
[2032][[SQLSetEnvAttr]] att=200,3
[2032][[SQLAllocHandle]][2032]PGAPI_AllocConnect: entering...
[2032]**** PGAPI_AllocConnect: henv = 94773136, conn = 94779272
[2032]EN_add_connection: self = 94773136, conn = 94779272
[2032] added at i =0, conn->henv = 94773136, conns[i]->henv = 94773136
[2032][SQLGetInfo(30)][2032]PGAPI_GetInfo: entering...fInfoType=77
[2032]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
[2032][[SQLSetConnectAttr]] 103
[2032]PGAPI_SetConnectAttr 103
[2032]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[2032][SQLDriverConnect][2032]PGAPI_DriverConnect: entering...
[2032]**** PGAPI_DriverConnect: fDriverCompletion=0,
connStrIn='DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]our_connect_string = 'DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]attribute = 'DSN', value = 'test'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]attribute = 'UID', value = 'postgres'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='postgres',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]attribute = 'PWD', value = 'xxxxx'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='postgres',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]globals.extra_systable_prefixes = 'dd_;'
[2032]globals.extra_systable_prefixes = 'dd_;'
[2032]our_connect_string = 'DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]attribute = 'DSN', value = 'test'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]attribute
= 'UID', value = 'postgres'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]attribute
= 'PWD', value = 'xxxxx'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]PGAPI_Disconnect:
about to CC_cleanup
[2032]in CC_Cleanup, self=94779272
[2032]after CC_abort
[2032]entering PGCONN_Destructor
[2032]exiting PGCONN_Destructor
[2032]after LIBPQ destructor
[2032]exit CC_Cleanup
[2032]PGAPI_Disconnect: done CC_cleanup
[2032]PGAPI_Disconnect: returning...
[2032][[SQLFreeHandle]][2032]PGAPI_FreeConnect: entering...
[2032]**** in PGAPI_FreeConnect: hdbc=94779272
[2032]enter CC_Destructor, self=94779272
[2032]in CC_Cleanup, self=94779272
[2032]after LIBPQ destructor
[2032]exit CC_Cleanup
[2032]after CC_Cleanup
[2032]after free statement holders
[2032]exit CC_Destructor
[2032]PGAPI_FreeConnect: returning...
[2032][[SQLFreeHandle]][2032]**** in PGAPI_FreeEnv: env = 94773136 **
[2032]in EN_Destructor, self=94773136
[2032]exit EN_Destructor: rv = 1
[2032] ok
[2032][[SQLAllocHandle]][2032]**** in PGAPI_AllocEnv **
[2032]** exit PGAPI_AllocEnv: phenv = 94773136 **
[2032][[SQLSetEnvAttr]] att=200,3
[2032][[SQLAllocHandle]][2032]PGAPI_AllocConnect: entering...
[2032]**** PGAPI_AllocConnect: henv = 94773136, conn = 94779272
[2032]EN_add_connection: self = 94773136, conn = 94779272
[2032] added at i =0, conn->henv = 94773136, conns[i]->henv = 94773136
[2032][SQLGetInfo(30)][2032]PGAPI_GetInfo: entering...fInfoType=77
[2032]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
[2032][[SQLSetConnectAttr]] 103
[2032]PGAPI_SetConnectAttr 103
[2032]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[2032][SQLDriverConnect][2032]PGAPI_DriverConnect: entering...
[2032]**** PGAPI_DriverConnect: fDriverCompletion=0,
connStrIn='DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]our_connect_string = 'DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]attribute = 'DSN', value = 'test'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]attribute = 'UID', value = 'postgres'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='postgres',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]attribute = 'PWD', value = 'xxxxx'
[2032]copyAttributes:
DSN='test',server='',dbase='',user='postgres',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[2032]globals.extra_systable_prefixes = 'dd_;'
[2032]globals.extra_systable_prefixes = 'dd_;'
[2032]our_connect_string = 'DSN=test;UID=postgres;PWD=xxxxxxxx;'
[2032]attribute = 'DSN', value = 'test'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]attribute
= 'UID', value = 'postgres'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]attribute
= 'PWD', value = 'xxxxx'
[2032]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[2032]PGAPI_Disconnect:
about to CC_cleanup
[2032]in CC_Cleanup, self=94779272
[2032]after CC_abort
[2032]entering PGCONN_Destructor
[2032]exiting PGCONN_Destructor
[2032]after LIBPQ destructor
[2032]exit CC_Cleanup
[2032]PGAPI_Disconnect: done CC_cleanup
[2032]PGAPI_Disconnect: returning...
[2032][[SQLFreeHandle]][2032]PGAPI_FreeConnect: entering...
[2032]**** in PGAPI_FreeConnect: hdbc=94779272
[2032]enter CC_Destructor, self=94779272
[2032]in CC_Cleanup, self=94779272
[2032]after LIBPQ destructor
[2032]exit CC_Cleanup
[2032]after CC_Cleanup
[2032]after free statement holders
[2032]exit CC_Destructor
[2032]PGAPI_FreeConnect: returning...
[2032][[SQLFreeHandle]][2032]**** in PGAPI_FreeEnv: env = 94773136 **
[2032]in EN_Destructor, self=94773136
[2032]exit EN_Destructor: rv = 1
[2032] ok

On 12/19/05, Ludek Finstrle <luf(at)pzkagis(dot)cz> 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.
>
> What version do you mean with "latest pgsql-odbc drviers"?
>
> > 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?
>
> Could you post mylog output? I want check if problem isn't in ODBC driver.
>
> Thanks,
>
> Luf
>

> pgsql odbc driver is 8.01.02 . following is the log file. thank you very
> much

There is only info about connect and disconnect in mylog file.
Either you don't turn on mylog output in DataSource (you tern on it only
in Global) or you attach bad mylog. mylog output is created for setup
dialog too.

Please set mylog output (under Global and DataSource button)
erase C:\mylog_* and then run your application.
Please send me mylog which appear in C:\ after you close you application.

Regards,

Luf

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
>