Re: Need help postgre with visual basic - Mailing list pgsql-odbc

Hi dodol garut.
Your applicaton looks nearly workable.

Recommendations:
1. Use care when sending code that contains a connection string with password.
2. Use pgAdmin to build your database creation scripts.
3. Use VB help, particulary MSDN to nail down syntax of ADO commands.
4. Name your global supermarkt ADO Connection object something like gcnnSupermarket intead of
dbSupermarket. Almost all connection objects are named "cnn"-something or "conn"-something. The "g" is
just an indication or a variable that is global to your applicaiton.
5. Test your connection string in isolation, to see if that works.
6. Rather than applying adUseClient to the connection object apply it to each recordset
(e.g. rs.CursorLocation = adUseClient)
7. Do NOT use adDynamic for a cursor type parameter, (unless you REALLY REALLY mean to).
8. Eliminate statements that do nothing (e.g. ExecuteSQL = AffectedRecords).
ExecuteSQL was supposed to be an function, but that function is not in your code, and it would be on the
other side of the equals sign.
9. Once you get the select and insert to work, explore PostgreSQL functions. They act like stored
procedures. Syntax is like "SELECT insert_produk(kodeproduk,namaproduk,hargasatuan)". See PostgreSQL docs
on how to build functions (return type void). pgAdmin again makes this pretty easy. pgAdmin is your friend.

expected code:
Sub cmdSave()

      on error goto err_sub

     'change to a single-ton select to guarantee the return on only one record, as long as the table exists
     StrSQL1 = "select count(*) as kode_count from produk where kodeproduk='" & Text1.Text & "'"

     Set rsValid = New ADODB.Recordset
     rsCursorLocation = adUseClient 'put the client cursor location here, just to be sure

     'for a read only 1 record query use adForwardOnly and adLockReadOnly
     rsValid.Open StrSQL1, utama.gcnnSupermarket, adOpenForwardOnly, adlockReadOnly, adCmdText

     If (rsValid("kode_count") = 0) Then
         StrSql2 = "insert into produk(kodeproduk,namaproduk,hargasatuan) values('" & _
                    Text1.Text & "','" & _Text2.Text & "'," & Text3.Text & ")"

    'better yet use a Stored Procedure for faster execution and to  move db syntax/logic to database
         'StrSql2 = "SELECT insert_produk('" & Text1.Text & "','" & _Text2.Text & "'," & Text3.Text & ")"

         utama.gnnSupermarket.Execute StrSql2 [, AffectedCount, adCmdText] 'don't really need stuff in
brackets
    .
    .
    .

     Else
        ...
     End If

     set rsValid = Nothing
     exit Sub

err_sub:
     'err handling, msg boxes, etc.

End sub



dodol garut wrote:
> I try to build a simple  visual basic project with PostgreSQL. Here i attach that project.
>   That project still have problem and i need someone please to solve that problem.
>   For query is in supermarket.sql
>   Thanx before
>
>   best regards,
>   ERIS RISO
>   Indonesia
>
> Send instant messages to your online friends http://uk.messenger.yahoo.com
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match