Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    london uk
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2k, ADO, SQL Strings (Access 2000)

    I have developed a class for reading from and writing to an MS SQL Server (v7) database, using forms as intermediaries for updating data.

    The class reads the data from the server, inserts into and array together with other data from the table/View. This is then used to populate forms in Access.

    The problem I am having is not with the class per se, but with the SQL string used to update and insert data.

    The class has Addnew and Update methods which take the new or updated data from the array and use it to generate a SQL string which is then used to update the SQL database using the ADO connection object as in MyConn.execute SQLString

    This works in most cases. However, where it falls down is when I have a long bit of text in a memo field. For instance:

    the string:

    "Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(1 as smallInt),'Renew bath complete with white pressed steel bath, including taps, bath panels and frame, to match existing or as specified, all pipework and fittings, earth bonding, make good to tiled surround and seal joint to wall','PD4010',cast(1 as smallInt),cast(304.33 as float),cast(25 as float),cast(25 as float),'Bath, basin, sink','Job') SELECT @@IDENTITY AS 'Identity'"

    Works with no problem. The memotext (beginning 'Renew bath...') is 217 chars long.

    The SQLString:

    "Insert into vOrderLines ([lngFID],[intDefNo],[strDefectDesc],[strDefectCode],[intUnits],[baseSORCost],[itemCost],[costXVat],[strTrade],[strJob]) Values (cast(81 as Int),cast(2 as smallInt),'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f ','ES7051',cast(1 as smallInt),cast(562.11 as float),cast(456 as float),cast(456 as float),'Chimneys','Job') SELECT @@IDENTITY AS 'Identity'"

    fails with a -2147217900 error, - "Unclosed quotation mark before the character string 'Rebuild chimney in new brick up to 3 courses below lowest roof level, point and refit pots, tiles, slates, cowls, lead flashing, cables and wiring, renew pots, cowls, flue liners, lead flashing as necessary and clear all debris from roof and gutters. 1 f'."

    The memotext here is 247 chars long.

    At first, I thought it might be a poblem with the string itself, but after using debug.print to extract it and pasting it into SQL Query Analyser, it worked on its own. Also, pasting it into a pass-through query with the Return Records property set to No, also worked. Using DAO to alter the pass-through queries SQL parameter in case of error failed because only part of the string was fed through. This is where I think the error is - Access/DAO/ADO is truncating my string.

    What I would like to know is if there is a work-around that will allow my, otherwise successful class, to work in these cases?

    thanks

    Bill

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    I think there are easier ways to accomplish your goal, but I'll pass on that for the moment to answer your question.

    What data type and length is your field in question in the SQL table? Is it varchar or text? If varchar, how long?

    The error you received souns like there are extra apostrophes (') in your string somewhere. Although, there obviously were not any in the strings you posted. If you do find an apostrophe in your string (which will cause an error like the one you're already seeing), you will want to use a Replace() function in VBA to replace the apostrophe with the appropriate SQL syntax (note, I added an apostrophe on purpose):<pre>strDesc = "Rebuild chimney in new brick up to 3 courses below lowest " _
    & "roof level, point and refit pots, tiles, slates, cowls, lead flashing, " _
    & "cables and wiring, renew pots, cowls, flue liners, lead flashing " _
    & "as necessary and clear all debris from roof and gutters of owner's home. 1 f"

    strDesc = Replace(strDesc,"'","'+CHAR(39)+'")

    '(abbreviated SQL string to include strDesc at the appropriate time)
    strSQL = "INSERT INTO........." & strDesc & "......"</pre>

    This will find any apostrophes and replace them with the CHAR(39) function, which will generate an apostrophe in T-SQL. (Note also that the replacement string closes the string then concatenates the CHAR(39) function, then re-opens the string.)

    Hope this helps!

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    london uk
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Thanks Mark, I'll have to have alook at this later. The offending field in the SQL server was nvarchar (255), but I subsequently changed it to ntext.

    The problem is not with the actual string I am generating. Cutting it out and pasting it into query analyser works. Also, pasting it into a pass-through also works. the problem is that using the string as in MyConnection.Execute SQLstring or as an adCMDText parameter gives an error. I think that Access is truncating my strings and hence SQL Server is getting a badly formed string as a result. What I need is a way of passing the the string directly to the server so that it executes without truncation. I found the following link on the web which seems to be near to what I want, provided I can overcome the truncation:

    http://www.algonet.se/~sommar/dynamic_sql.html

    Have a nice weekend

    Bill

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Earlier I mentioned a better method for your situation.... now might be a good time to tell you about it.

    This method is probably not as critical for Access development as it is for Web development (my current fort

  5. #5
    New Lounger
    Join Date
    Jul 2001
    Location
    london uk
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    I think I know what you mean - create a Stored Procedure - sp_tblOrderInsert say. Declare variables @Contractor, @Cost say for al the fields, with defuault values. then, when I pass the values from Access, if they are different from the default, the SP uses them or something like that.

    I would like to se a bit of code if you can spare it and its not too much trouble.

    I think provided I have standard names for the stored procedures, then I can still use my class. In the short term, I thin I can get round the problem using spExecSQL as in the article I mentioned in my reply and break my longer SQL strings into pieces and concatenate them in the SP. The article says its not wise to do it this way as all the users need Insert and Update permissions on the tables. Its not a probelm for us at the moment, bu I can see that in the future, it would be better to control access to the tables using SP's instead. More work, but safer.
    Thanks

    Bill

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Mark,

    I encourage you to post it if it isn't proprietary. As this becomes a topic of more interest to mainstream Access developers, it would be nice to have an example in the archives for reference. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Charlotte

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    I'm posting some sample code for passing data to a Stored procedure for inserting or updating. A few notes about this code: 1) I've tested this only in an ASP environment. You may need to tweak it some for use in Access, 2) I'm sure there are other ways to accomplish the same task - however, this is a VERY efficient method and works quite well in the ASP environment.


    Without further adeau (or is it ado - no pun intended)
    Here's the code to use in the Access/VBA side:<pre> Dim cmd as ADODB.Command
    Dim lanID as String, PSID as Long, strRectext as String

    set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "proc_ins_upd_Recommendation"
    cmd.CommandType = adCmdStoredProc

    'We'll return a value from the Stored Procedure - to test for success or failure
    cmd.Parameters.append cmd.CreateParameter("return",adInteger,adParamRetu rnValue)
    'Pass "I" for Insert or "U" for update - Insert in this case
    cmd.Parameters.append cmd.CreateParameter("@procAction",adChar,adParamIn put,1,"I")

    'Create a Parameter object for EACH parameter (along with its data type and size)
    'Note that the parameters must be in the SAME ORDER as they are listed in the Stored Procedure.
    'Below are examples of 3 parameters (lanID, PSID, strRectext)
    cmd.Parameters.append cmd.CreateParameter("@lanID",adVarChar,adParamInpu t,20,lanID)
    cmd.Parameters.append cmd.CreateParameter("@PSID",adInteger,adParamInput ,,PSID)
    cmd.Parameters.append cmd.CreateParameter("@rectext",adVarChar,adParamIn put,200,strRectext)

    cmd.Execute

    'Return the result from the Stored Procedure
    intResult = cmd.Parameters("return")

    set cmd = nothing

    'I use 30 as an error code
    If intResult <> 30 Then
    'SP was successful
    MsgBox "Record Successfully Inserted/Updated"
    Else
    'Error Handler Code
    End If</pre>

    <img src=/w3timages/blackline.gif width=33% height=2>

    Here's the code in the Stored Procedure. Again, note that the parameters must be in the SAME ORDER as they were sent from the Command object. They must also be the same data types and lengths as their respective fields in the database. Note also that this procedure can be used for either inserting or updating records (depending on the "procAction" variable - I or U). This procedure returns 1 for a successful Insert and 2 for successful Update or 30 for any error.

    SQL code is as follows:<pre>CREATE PROCEDURE proc_ins_upd_Recommendation
    @procAction char(1),
    @lanID varchar(20),
    @PSID int,
    @rectext varchar(200)
    AS

    IF @procAction = 'I'
    GOTO doInsert
    ELSE
    GOTO doUpdate

    doInsert:
    BEGIN
    BEGIN TRAN doInsert
    INSERT INTO tbl_Recommendation (lanID, PSID, rectext)
    VALUES (@lanID, @PSID, @rectext)

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRAN doInsert
    RETURN 30
    END
    ELSE
    COMMIT TRAN doInsert
    RETURN 1
    END

    doUpdate:
    BEGIN
    BEGIN TRAN doUpdate
    UPDATE tbl_Recommendation SET lanID = @lanID,
    rectext = @rectext
    WHERE PSID = @PSID

    IF @@ERROR <> 0
    BEGIN
    ROLLBACK TRAN doUpdate
    RETURN 30
    END
    ELSE
    COMMIT TRAN doUpdate
    RETURN 2
    END</pre>

    This technique was provided by Doug L. Clark, currently Head Web Developer at Butler University

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Hey Mark ~ nice looking NEW site! re the coming hosting: .Net?

  9. #9
    New Lounger
    Join Date
    Jul 2001
    Location
    london uk
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Many thanks Mark, I'll have a play with this on Monday at work.

    Bill

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    (I realize this may be a little off-topic... <img src=/S/pirate.gif border=0 alt=pirate width=22 height=18>)

    Thanks Peter! Of course, the new hosting will support the .NET platform...

    I just need to get some details worked out and I'll be up and running as a hosting reseller. I've gotten several headaches from dealing with 3rd party web hosts, so I decided to jump in the game myself - for the benefit of my present and future clients. It will be much easier to set up hosting myself than to play mediator between my clients and other web hosts.

    Stay tuned for more details...

    If you have any more questions, we can start a post in the Web or Scuttlebut forum. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    You're quite welcome, Bill.

    I'll be waiting to hear how it works for you (and anyone else who finds it helpful).

    Best,

  12. #12
    New Lounger
    Join Date
    Jul 2001
    Location
    london uk
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2k, ADO, SQL Strings (Access 2000)

    Further to Marks postings, I have taken his code and modified it to fit my purpose. Sadly, none of this answered my original question regarding the reason for the string truncation, but it did provide me with a useful work around which I have included in an attached text file.

    The code is not pretty but I've tried it out and it seems to work. There are SP_Add and SP_Update for all the tables I am using.
    You need to watch out for rs.field(0) as this does not map to cmd.parameter(0) - which seems to be the return value, but if the first field is the primary key, then this does not matter.

    Bill
    Attached Files Attached Files

Posting Permissions

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