Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Unanswered: Pervasive PSQL V11 with Pervasive ADO.NET V3.5

    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.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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)
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Feb 2011
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •