Postgres With VB.NET...

[milkmood]

Anybody use Postgres (8.0) with VB.NET? I am a total n00b at all this code stuff, I'm getting there. Anybody can build forms, but getting the stuff to talk to the DB is a little beyond my pervue at the moment.

I could use some help, just little questions here and there. I have the form pretty much ready to start using the DB, and I have the DB set up and running with the tables I need (I think). I also have the connection established on my local machine in VS.

Using Visual Studio .NET 2003 Professional

MSN Messenger : [email protected]
Yahoo Messenger: milkmood

Thanks a bunch.

[PilgrimPete]

I haven't a clue about PostGres, but given that it is a database, and I know a little about them... post those queries (no pun intended), see how fast I can prove my total ignorance

What sort of thing are you looing for? SQL advice, connection advice..?

[milkmood]

Mostly connection advice and vb syntax for inserting, updating, selecting, etc.

I have successfully connected to the DB with the built in ODBC Adapter, and it seems to work fine. Just need to figure out how to make the code talk to it.

CP

[PilgrimPete]

OK. So is thsi the sort of thing you want, or is this too basic:

private void GetRecords()
        {
            OdbcConnection conn = new OdbcConnection();
            OdbcCommand cmd = new OdbcCommand("SELECT field1 FROM table", conn);
            OdbcDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while( dr.Read() )
            {
                //do stuff here
            }
        }
        private void DoUpdates()
        {
            OdbcConnection conn = new OdbcConnection();
            OdbcCommand cmd = new OdbcCommand("UPDATE table SET field1 = 99", conn);
            cmd.ExecuteNonQuery();
        }

(Obviously that's C# , because I don't have VB.Net on this machine, but I can translate if necessary...)

[milkmood]

That helps a some...VB code would be more helpful, but that's a starting point. The comment 'do stuff here' I assume is where my sql statements go, I also assume the sql statements won't be in comment. Is that going to be strictly in sql format, or is there other syntax that needs to be there?

So that code should be in a button click event like this?

Private Sub CustOverwriteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustOverwriteButton.Click
        MessageBox.Show("Are you sure you want to overwrite this record?" & Environment.NewLine & "Overwriting this record will permantently delete the old customer record.", "Overwrite Record", _
            MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
 'Then some 'update' derivation of your code above. 
    End Sub

Then I'll use another similar piece of code in the textbox subs with a 'select' statement to get data, eh?

CP

[PilgrimPete]

In VB, it would be something like this:

private sub GetRecords()
    dim conn as new OdbcConnection(<your connection string>)
    dim cmd as new OdbcCommand("SELECT field1 FROM table", conn)
    dim dr as OdbcDataReader
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    'this is looping through your records
    do while( dr.Read() )
      'do stuff here, for example fill a combo box, or an unbound grid etc.
    loop
end sub
private sub DoUpdates(keyValue as string, value as string)
    dim conn as new OdbcConnection(<your connection string>)
    dim cmd as new OdbcCommand(String.Format("UPDATE table SET field1 = '{0}' WHERE keyfield = '{1}'", value, keyValue), conn)
    cmd.ExecuteNonQuery()
end sub

(I'm assuming that VB.Net has the String.Format(...) method.)

How you get the data depends really on how you want to use it - you can either return a DataReader or a DataSet. they both have different uses/limitations.

[milkmood]

Ok, let's run through this. Like I said, I'm new to all of this (programming AND DB), and it pretty much got dropped in my lap (though I agreed to try). Please forgive my noobiness...

The database shows up in my Server Explorer, along with 7 tables, and their columns that I created in pgAdminIII. No problem there.

This is VS Pro, so I know I can use the wizards to set up the data adapter, connections, etc.

First, what are the differences between the OdbcDataAdapter, OdbcConnection, and the OdbcCommand controls?

Is there a particular order these have to be configured in? Do I need all three? Do I need multiple instances of any/all of them?

Second, what's the difference between a DataSet and DataView? Do I need one or both or multiples of each of these?

Thanks!