Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2013
    Posts
    23

    Unanswered: Run-time error '-2147217900 (80040e14)'

    I am trying to execute this simple code and it keeps giving me a Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC SQL Server Driver][SQL Server] Unclosed quotation mark after the character string 'tlarse2'.
    Code:
      '/Declaring the SQL expression to be executed by the server
        strSQL = "Update dbo_tTbl_LoginPermissions " _
        & "SET LoginName = '" & StrUserName & "' " _
        & "SET PWD = '" & StrPWD & "' " _
        & "SET fldPWDDate = '" & Now() & "' " _
        & "WHERE intLoginPermUserID = 3;"
    Even though it gives me the following output screen: Update dbo_tTbl_LoginPermissions SET LoginName = 'tlarse2 ' SET PWD = '577 582 578 581 584 590 601 ' SET fldPWDDate = '11/6/2013 4:18:34 PM' WHERE intLoginPermUserID = 3;

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    ...
    SET fldPWDDate = '" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "' " _
    ...
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you need a set before each assignment. I would expect a comma in place of subsequent set.

    Eg:-
    Update mytable set column1 = 1, column2 = 'blah'
    Where.....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2013
    Posts
    23

    Still receiving error

    I added that comma, but to no avail I am still receiving the error.

  5. #5
    Join Date
    Oct 2013
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    Try:
    Code:
    ...
    SET fldPWDDate = '" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "' " _
    ...

    I did change the date to be exactly the format that I need and that seems to get passed through. Still erroring out. Thanks though.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you say 'added that comma' do you mean you added a comma or you replaced other sets with a comma?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2013
    Posts
    1

    If this may help

    1. I am not familiar with dbo_tTbl_LoginPermission where you have used the underscore. I would rather use
    dbo.tTbl_LoginPermission ( with a dot ) or even ignore the database owner prefix.

    2. The use of the key word SET. I normally use it once and the rest will be 'Comas' e.g SET USERID='xxx', PWD='xx'

    3. If you are updating SQL server, it would be advisable to pick the date with a server function GETDATE() which is actually better because it will pick the date on the server machine vb NOW() picks the date from the user machine and if he has admin rights, the date can be falsified
    e.g
    USERID='xxx', PWDdate=GETDATE()

    If you are using VB.NET, let me know and we can up! the discussion

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    strSQL = "Update dbo.tTbl_LoginPermissions SET " _
        & "LoginName = '" & StrUserName & "', " _
        & "PWD = '" & StrPWD & "', " _
        & "fldPWDDate = '" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "' " _
        & "WHERE intLoginPermUserID = 3;"
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    23

    Testing Suggestions

    @sinndho, I did put your suggested Code and date format, but is still fails with
    Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC SQL Server Driver][SQL Server] Unclosed quotation mark after the character string 'tlarse2'.
    @JAtwooki, since I am using Access as the Front-End and writing in VBA, the GETDATE() function is not working. Great idea though! I also changed the dbo., still fails. I have even ignored the dbo prefix.

    @Healdem, I have added the comma where appropriate, reference code below:
    Code:
    strSQL = "Update tTbl_LoginPermissions SET " _
            & "LoginName = '" & StrUserName & "', " _
            & "PWD = '" & StrPWD & "' ," _
            & "fldPWDDate = '" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "' " _
            & "WHERE intLoginPermUserID = 3;"
    As I do a Debug.Print strSQL, the results are good, but the error is driving me INSANE in the MEMBRANE.

    Update dbo.tTbl_LoginPermissions SET LoginName = 'tlarse2 ', PWD = '600 588 580 580 591 586 583 ' ,fldPWDDate = '2013-11-07 14:30:32' WHERE intLoginPermUserID = 3;

    I certainly appreciate the time and effort by everyone here.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you please post the script definition of the table tTbl_LoginPermissions (in SSMS, right click on the table in the Explorer pane, select "Script table as" --> "Create to" --> "New query editor", then copy the script and paste it here)?
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    23
    @Sinndho, here you go:
    USE [Regulatory]
    GO

    /****** Object: Table [dbo].[tTbl_LoginPermissions] Script Date: 11/07/2013 16:01:42 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[tTbl_LoginPermissions](
    [intLoginPermUserID] [int] NULL,
    [LoginName] [varchar](7) NULL,
    [PWD] [text] NULL,
    [fldChangePWD] [varchar](50) NULL,
    [fldExpireDays] [int] NULL,
    [fldAccessLevel] [int] NULL,
    [fldPWDDate] [datetime] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[tTbl_LoginPermissions] ADD CONSTRAINT [DF_tTbl_LoginPermissions_PWD] DEFAULT ('613 580 607 523 632 590 607 ') FOR [PWD]
    GO

  12. #12
    Join Date
    Oct 2013
    Posts
    23

    I changed my Connection features and now different Error

    Now, I am getting a Run-time error '424': Object Required
    Code:
    'Define the OLE DB connection string.
        strConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"
        'Instantiate the Connection object and open a database connection.
        Set cnn = Server.CreateObject("ADODB.Connection")
        cnn.Open strConnectionString
    
        Dim StrUserName As String, StrPWD As String
    
        '/passing variables
        StrUserName = FindUserName()
        StrPWD = EncryptKey(Me.TxtConPWD)
        
        '/Declaring the SQL expression to be executed by the server
           strSQL = "Update tTbl_LoginPermissions SET " _
            & "LoginName = '" & StrUserName & "', " _
            & "PWD = '" & StrPWD & "' ," _
            & "fldPWDDate = '" & Now() & "' " _
            & "WHERE intLoginPermUserID = 3"
            
            cnn.Execute strSQL, , adCmdText + adExecuteNoRecords
         
        Debug.Print strSQL
        
        '/close connections
        '/con.Close
        Set cnn = Nothing

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange because everything works as expected here.

    Here's the test protocol I used:

    SQL Server: 2008 R2. (10.15.2550.0)
    Access: 2003 SP3 (11.8321.8405)
    MDAC: 3.85.1132

    1) SSMS: Created the table [tTbl_LoginPermissions] according to the script you posted --> OK

    2) SSMS: Manually created a row in [tTbl_LoginPermissions] with the value 3 in the column [intLoginPermUserID]

    3) SSMS: Tried to execute the SQL statement:
    Code:
    use TestDb
    go
    Update dbo.tTbl_LoginPermissions SET 
      LoginName = 'tlarse2 '
    , PWD = '600 588 580 580 591 586 583 ' 
    , fldPWDDate = '2013-11-07 14:30:32' 
    WHERE intLoginPermUserID = 3;
    --> OK (1 row affected).

    4) Access: Created then ran the following Sub procedure:
    Code:
    Sub TestSQL()
    
        Dim strConnectionString As String
        Dim cnn As Object
        Dim strSQL As String
        Dim StrUserName As String
        Dim StrPWD As String
        
        '/Define the OLE DB connection string.
        ' strConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"
          strConnectionString = "DRIVER=SQL Server;SERVER=SOLIMAN;;DATABASE=TestDb;Trusted connection=YES"
        
        '/Instantiate the Connection object and open a database connection.
          Set cnn = CreateObject("ADODB.Connection")
          cnn.Open strConnectionString
    
    
        '/passing variables (litterals as I dont have access to FindUserName() and EncryptKey())
        ' StrUserName = FindUserName()
        ' StrPWD = EncryptKey(Me.TxtConPWD)
          StrUserName = "tlarse2"
          StrPWD = "600 588 580 580 591 586 583 "
        
        '/Declaring the SQL expression to be executed by the server
           strSQL = "Update tTbl_LoginPermissions SET " _
                  & "LoginName = '" & StrUserName & "', " _
                  & "PWD = '" & StrPWD & "' ," _
                  & "fldPWDDate = '" & Now() & "' " _
                  & "WHERE intLoginPermUserID = 3"
            
         '/Executing the SQL Statement
            cnn.Execute strSQL, , adCmdText + adExecuteNoRecords
         
        Debug.Print strSQL ' Returns:
        ' Update tTbl_LoginPermissions SET LoginName = 'tlarse2', PWD = '600 588 580 580 591 586 583 ' ,fldPWDDate = '8/11/2013 7:13:10' WHERE intLoginPermUserID = 3
    
        
        '/close connections and clean up
        cnn.Close
        Set cnn = Nothing
        
    End Sub
    --> OK (no error).
    Have a nice day!

  14. #14
    Join Date
    Oct 2013
    Posts
    23

    Mock Trial

    Thank you so much for creating a mock trial. The SSMS: Execuion of the SQL statement works for me as well. However, after careful and deliberate review, I am still receiving the Run-Time Error. There has to be something that is hindering this process that is relative to set-up, references, security or something.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you check the permissions and password of the SQL user Mickey01 used in the connection string?
    Have a nice day!

Posting Permissions

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