Pervasive .Net Data Provider

[billy_bob169]

I am trying to create an application using a Pervasive database, but I cannot get the Pervasive SQL Data adapter or Connection components to work. Anyone used these yet? I can make the application run somewhat using an OLE DB connection, but would rather use managed code. Any help would be much appreciated!

 

[Alistair]

I have found that, in general, it is the same as working with System.Data.SqlClient.

Wheres the problem. What are you using for a connect string, as this hung me up for a bit.

 

[billy_bob169]

Ive gotten the application to connect to the database now, but I am having a hard time getting it to update and insert records. As lame as it sounds, I am used to the wizards that microsoft created for their data providers. Creating update and insert commands from scratch is not something I have had to do before.

 

[Alistair]

This should help. Notice the question marks in the SQL statment instead of something like #LockerID. Its odd, but PSQL likes it that way.

daHMLRENTS = New PsqlDataAdapter("SELECT * FROM HMLRENTS", cn)

Dim SQLInsert As String
SQLInsert = "INSERT INTO HMLRENTS (LockerID, IDCUST, StartDate, StopDate, Keys, Notes) VALUES " & _
                    "(?, ?, ?, ?, ?, ?);"

Dim InsertCMD As New PsqlCommand(SQLInsert, cn)
With InsertCMD.Parameters
    .Add("LockerID", PsqlDbType.Integer, 4, "LockerID")
    .Add("IDCUST", PsqlDbType.VarChar, 12, "IDCUST")
    .Add("StartDate", PsqlDbType.Date, 4, "StartDate")
    .Add("StopDate", PsqlDbType.Date, 4, "StopDate")
    .Add("Keys", PsqlDbType.VarChar, 20, "Keys")
    .Add("Notes", PsqlDbType.VarChar, 254, "Notes")
End With

daHMLRENTS.InsertCommand = InsertCMD

daHMLRENTS.Fill(dsHMLRENTS, "HMLRENTS")

 

[billy_bob169]

I assume the update command would be very similar? How did you setup you dataset? Like I said, this is new to me. For some reason my company decided to go with Pervasive on our latest project.

 

[Alistair]

I assume the update command would be very similar?

Yes, update follows the same format.

INSERT INTO tablename SET col1 = ?, col2 = ?, col3 = ?

How did you setup you dataset? Like I said, this is new to me. For some reason my company decided to go with Pervasive on our latest project.

Like as in what program I used? "Control Center", unless youre using linux, then "dbmaint" to create the DB and "Control Center" to add tables.

Frankly, Id like to see an expert PSQL developer at work so I could find at least one justification for using this stupid software. I think that Accpac is the only reason and I think its just that theyve been using it forever.

 

[billy_bob169]

I think I have finally got a handle on it now. Thanks for you help!