Pervasive PSQL V11 with Pervasive ADO.NET V3.5

PeterM

Member
Joined
Jan 5, 2011
Messages
8
Location
Wiltshire, UK
Programming Experience
1-3
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 at the moment.

This is the code I am trying to use;
VB.NET:
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.

Many thanks.
 
I loaded this into a programmers text editor:
"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)" _
And counted the number of commas. There were 48

Then I loaded this:
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " _
& "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? " _
& "?, ?, ?, ?, ?, ?)"
And counted 47 commas. A closer look shows a missing comma on the end of the second line of question marks

I'd recommend you write a software tool to generate your queries, if none exists already.. a simple app that you paste a list of column names into and it will write a query to a pattern..
If youre using an ODBC driver you really should consider using the dataset designer in visual studio to generate a proper data layer but if it doesnt work out, even a simple code like this will save much fustration:

VB.NET:
Dim sb as New Stringbuilder("INSERT INTO xxx (")
For Each s as String in array_of_column_names
  sb.append(s).Append(",")
Next
sb.Length -= 2 'chop off trailing comma/space
s.Append(")VALUES(")
For Each s as String in array_of_column_names
  sb.Append("?,")
Next
sb.Length -= 1
sb.Append(")")

You get the idea :)
 
Last edited:
Thanks for your reply,

I found the missing comma after spending hours going through it, I cant believe I didnt spot it before!

I have now managed to get the query to work as I would like, I did also have one or two issues with dbnull, but those too have now been sorted.

This is by far the biggest of the queries, as it builds the SQL database from the online webstore data of the largest table, that being the products table.

Thanks again for your assistance.
 
Back
Top