Pervasive PSQL V11 with Pervasive ADO.NET V3.5

PeterMilner · #1 (**permalink**) 02-13-11, 08:21

Hi All,

I am struggling with what should be a very simple sql insert statement. I am trying to use parameters and the parameters.addwithvalue method to specify the data elements which are to be added to the table.

I have had several attempts at the code in various different formats, but I come up against a syntax error every time and I dont understand why.

This is the code I am trying to use;

Code:

Dim SQLInsert As String = "INSERT INTO Products " _
        & "(OSCId, ProductCode, ProductName, BundledProduct, AvailabilityID, " _
        & "RestrictDeliveryID, MakeAnOffer, ProductURL, Category1, Category2, " _
        & "Category3, Category4, Category5, ProductGroupCode, Manufacturer, " _
        & "Image, DateAdded, DateAvailable, DateLastModified, DateTasLastUpdated, " _
        & "Quantity, PriceEXVAT, VATRate, VATAmount, PriceIncVAT, SpecialPrice, " _
        & "Weight, Unit, UnitPlural, SpecialsDateAdded, SpecialsLastModified, " _
        & "SpecialsExpiresDate, SpecialsDateStatChg, SpecialsStatus, FeaturedDateAdded, " _
        & "FeaturedLastModified, FeaturedExpiresDate, FeaturedDateStatChg, Get1FreeQualQty, " _
        & "Get1FreePrdMult, Get1FreePrdFreeID, Get1FreePrdFreeCode, Get1FreePrdFreeQty, " _
        & "Get1FreeDateAdded, Get1FreeDateLastMod, Get1FreeDateExpires, Get1FreeDateStatChg, " _
        & "Get1FreeStatus, EOREOR)" _
        & _
        " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " _
        & "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? " _
        & "?, ?, ?, ?, ?, ?)"

Dim SQLCmd As New PsqlCommand(SQLInsert, SQLConnect)
        With SQLCmd.Parameters
            .AddWithValue("?OSCId", OSCProduct.ID)
            .AddWithValue("?ProductCode", OSCProduct.Model)
            .AddWithValue("?ProductName", OSCProduct.Name)
            .AddWithValue("?BundledProduct", OSCProduct.BundledProduct)
            .AddWithValue("?AvailabilityID", OSCProduct.AvailabilityID)
            .AddWithValue("?RestrictDeliveryID", OSCProduct.RestrictDeliveryID)
            .AddWithValue("?MakeAnOffer", OSCProduct.MakeAnOffer)
            .AddWithValue("?ProductURL", OSCProduct.URL)
            .AddWithValue("?Category1", OSCProduct.Category1)
            .AddWithValue("?Category2", OSCProduct.Category2)
            .AddWithValue("?Category3", OSCProduct.Category3)
            .AddWithValue("?Category4", OSCProduct.Category4)
            .AddWithValue("?Category5", OSCProduct.Category5)
            .AddWithValue("?ProductGroupCode", TASProduct.ProductGroupCode)
            .AddWithValue("?Manufacturer", OSCProduct.Manufacturer)
            .AddWithValue("?Image", OSCProduct.Image)
            .AddWithValue("?DateAdded", OSCProduct.DateAdded)
            .AddWithValue("?DateAvailable", OSCProduct.DateAvailable)
            .AddWithValue("?DateLastModified", OSCProduct.DateLastModified)
            .AddWithValue("?DateTasLastUpdated", OSCProduct.DateTasLastUpdated)
            .AddWithValue("?Quantity", OSCProduct.Quantity)
            .AddWithValue("?PriceEXVAT", OSCProduct.PriceExVAT)
            .AddWithValue("?VATRate", OSCProduct.VATRate)
            .AddWithValue("?VATAmount", OSCProduct.VATAmount)
            .AddWithValue("?PriceIncVAT", OSCProduct.PriceIncVAT)
            .AddWithValue("?SpecialPrice", OSCProduct.SpecialPrice)
            .AddWithValue("?Weight", OSCProduct.Weight)
            .AddWithValue("?Unit", OSCProduct.Unit)
            .AddWithValue("?UnitPlural", OSCProduct.UnitPlural)
            .AddWithValue("?SpecialsDateAdded", OSCProduct.SpecialsDateAdded)
            .AddWithValue("?SpecialsLastModified", OSCProduct.SpecialsLastModified)
            .AddWithValue("?SpecialsExpiresDate", OSCProduct.SpecialsExpiresDate)
            .AddWithValue("?SpecialsDateStatChg", OSCProduct.SpecialsDateStatusChange)
            .AddWithValue("?SpecialsStatus", OSCProduct.SpecialsStatus)
            .AddWithValue("?FeaturedDateAdded", OSCProduct.FeaturedDateAdded)
            .AddWithValue("?FeaturedLastModified", OSCProduct.FeaturedLastModified)
            .AddWithValue("?FeaturedExpiresDate", OSCProduct.FeaturedExpiresDate)
            .AddWithValue("?FeaturedDateStatChg", OSCProduct.FeaturedDateStatusChange)
            .AddWithValue("?Get1FreeQualQty", OSCProduct.Get1FreeQualifyQty)
            .AddWithValue("?Get1FreePrdMult", OSCProduct.Get1FreeProductMultiple)
            .AddWithValue("?Get1FreePrdFreeID", OSCProduct.Get1FreeProductFreeID)
            .AddWithValue("?Get1FreePrdFreeCode", OSCProduct.Get1FreeProductFreeModel)
            .AddWithValue("?Get1FreePrdFreeQty", OSCProduct.Get1FreeProductFreeQty)
            .AddWithValue("?Get1FreeDateAdded", OSCProduct.Get1FreeDateAdded)
            .AddWithValue("?Get1FreeDateLastMod", OSCProduct.Get1FreeDateLastModified)
            .AddWithValue("?Get1FreeDateExpires", OSCProduct.Get1FreeDateExpires)
            .AddWithValue("?Get1FreeStatus", OSCProduct.Get1FreeStatus)
            .AddWithValue("?EOREOR", OSCProduct.EOREOR)
        End With

SQLCmd.ExecuteNonQuery()

I have tried all the different permitations of specifying the parameters I can find on the web including, @parameter with @parameter in the addwithvalue, parameter with paramter in the addwithvalue, ? with both @parameter and ?parameter in the addwithvalue but nothing seems to work.

I would be really grateful if someone could advise me on what I am doing wrong.

mirtheil · #2 (**permalink**) 02-13-11, 09:26

Is the error in compiling or running? I created a simple table using:

Code:

create table paramtest (f1 char(10), f2 char(10))

and used the code below and it worked:

Code:

Imports Pervasive.Data.SqlClient

Module Module1

    Sub Main()
        Dim SQLConnect As New PsqlConnection("ServerDSN=demodata")
        SQLConnect.Open()
        Dim SQLInsert = "insert into paramtest (f1, f2) values (?,?)"
        Dim SQLCmd As New PsqlCommand(SQLInsert, SQLConnect)
        With SQLCmd.Parameters
            .AddWithValue("f1", "test1")
            .AddWithValue("f2", "test2")
        End With
        Dim res As Integer
        res = SQLCmd.ExecuteNonQuery()
        Console.WriteLine("Res = " & res.ToString())

    End Sub

End Module

I don't see anything wrong with your statement. Post the full error if it's when running. I'm also using the latest PSQL v11 (v11.01)

PeterMilner · #3 (**permalink**) 02-13-11, 16:55

Thanks very much for that, it gave me the cofidence to know I was on the right track.

In the end I found a missing , in the values statement and had one or two issues with the bit data type and null dates, both of which are sorted now.

The code is running as I write this

Thanks again

Internet Services

Web Development

Digital Marketing

Consumer Tech