Thread: insert and update in vb.net

insert and update in vb.net

From

Freeking Wu

Date:

19 December 2005, 11:45:29

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

Re: insert and update in vb.net

From

Ludek Finstrle

Date:

19 December 2005, 11:56:50

> 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

Re: insert and update in vb.net

From

Freeking Wu

Date:

19 December 2005, 12:30:39

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

Re: insert and update in vb.net

From

Ludek Finstrle

Date:

19 December 2005, 12:50:24

> 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

Re: insert and update in vb.net

From

"Campbell, Greg"

Date:

19 December 2005, 14:28:39

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