none
Need to replace QueryDef with something that can pass TEXT(MAX) RRS feed

  • Question

  • As far as I can tell, QueryDefs can't handle TEXT(MAX) so I need to convert the following qDef into something else that can.
    Can someone please convert this to an ADODB.Command or something that will accept a param like NVarChar(MAX), or point me to documentation that will show me? I'm having to weed through so much unrelated examples and I don't know the ADODB library yet, or any others for VBA data transfer to SQL Server besides ADO. If someone does suggest or show an example, if you also know of good documentation for the datatypes that params can be mapped to for this library and how to construct the query to be executed, that would be awesome too.

    Dim db As DAO.Database
    Dim qDef As DAO.QueryDef
    Dim qDefSQL As String

    qDefSQL = "PARAMETERS parMsgToRecipient TEXT(255); " & _
    " INSERT INTO ServiceRequest (Revisions) " & _
    " VALUES (parMsgToRecipient) "

    Set db = CurrentDb
    Set qDef = db.CreateQueryDef("", qDefSQL)

    qDef!parMsgToRecipient = "Some text here that exceeds 255 chars."

    qDef.Execute

    Thanks for the help!
    Tuesday, March 15, 2016 9:03 PM

Answers

  • HTHP,

    Reference types:

    Field type reference - names and values for DDL, DAO, and ADOX

    Att,


    Antero Marques

    • Marked as answer by HTHP Wednesday, March 16, 2016 8:51 PM
    Tuesday, March 15, 2016 9:24 PM
  • I see that the ? is used as a place holder for cmd.Parameter(0) of the cmd.Parameters collection. I thought I would be able to infer how to scale an example with one param like this, but I am a bit confused. How do you insert multiple different input params? Do I use multiple ?(question mark) marks? And if so, how do I know which param is linked to which question mark?

    IIRC, you use multiple question marks for multiple parameters, and they are numbered in sequence.  I believe you can also use named parameters, using code along these lines:

        Dim cmd As ADODB.Command
        Dim lngAffected As Long
        Dim strMyMessage As String
        
        strMyMessage = "My long text here ..."
    
        Set cmd = New ADODB.Command
        With cmd
            Set .ActiveConnection = CurrentProject.AccessConnection
            .CommandType = adCmdText
            .CommandText = "PARAMETERS parMsgToRecipient LongText; INSERT INTO ServiceRequest(Revisions) VALUES([parMsgToRecipient])"
            .Parameters.Append .CreateParameter("parMsgToRecipient", adLongVarChar, adParamInput, Len(strMyMessage), strMyMessage)
            .Execute lngAffected
        End With
        
        Debug.Print "#Records affected = " & lngAffected
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by HTHP Wednesday, March 16, 2016 8:50 PM
    Wednesday, March 16, 2016 4:27 PM
  • Dirk, thanks. I was able to make a working procedure from your examples.

    Albert, I considered that, but wanted to learn how to use another object model that I could duplicate the functionality of the QueryDef I originally posted. Something that I could build a SQL string, include params, and execute once without having to iterate through a loop. If I had to do a bulk insert, the DAO.Recordset wouldn't work unless it accepts params(which I don't know).

    Antero, thanks for the link. Super helpful for the datatypes part of my question!

    Below is what I created to duplicate the original QDef. For anyone who is wondering a similar question to mine. It includes param creation for 2 different datatypes. All works well, inserted records without trouble with all Chars I put in the msgToRecipient string:

    Dim CustomerID As Long
    CustomerID = 12345
    Dim msgToRecipient As String
    msgToRecipient = "Some text here that exceeds% 255 chars."

    Dim cmd As New ADODB.Command
    Dim affected As Long

    cmd.ActiveConnection = CurrentProject.AccessConnection
    cmd.CommandType = adCmdText
    cmd.CommandText = "INSERT INTO ServiceRequest (CustomerIDRef, Revisions) " & _
    " VALUES([parCustomerId], [parMsgToRecipient]) "

    cmd.Parameters.Append cmd.CreateParameter("parCustomerId", adInteger, adParamInput, , CustomerID)

    cmd.Parameters.Append cmd.CreateParameter("parMsgToRecipient", adVarWChar, adParamInput, Len(msgToRecipient), msgToRecipient)

    Debug.Print cmd.Parameters("parCustomerId")
    Debug.Print cmd.Parameters("parMsgToRecipient")
    Debug.Print cmd.CommandText
    cmd.Execute affected

    Debug.Print affected

    Thanks everyone!
    • Marked as answer by HTHP Wednesday, March 16, 2016 8:50 PM
    Wednesday, March 16, 2016 8:50 PM

All replies

  • HTHP,

    Reference types:

    Field type reference - names and values for DDL, DAO, and ADOX

    Att,


    Antero Marques

    • Marked as answer by HTHP Wednesday, March 16, 2016 8:51 PM
    Tuesday, March 15, 2016 9:24 PM
  • qDefSQL = "PARAMETERS parMsgToRecipient TEXT(255); " & _
    " INSERT INTO ServiceRequest (Revisions) " & _
    " VALUES (parMsgToRecipient) "

    Set db = CurrentDb
    Set qDef = db.CreateQueryDef("", qDefSQL)

    qDef!parMsgToRecipient = "Some text here that exceeds 255 chars."

    qDef.Execute

    Hi HTHP,

    I never use QueyDefs. In most cases I use something like:

            CurrentDb.Execute qDefSQL

    Imb.

    Tuesday, March 15, 2016 9:37 PM
  • I have to avoid constructing a string to be executed. To avoid SQL injections, and the maintenance of handling chars that mess up the string, I need to find a way to insert via paramaters. The qDef example I provided is perfect. Except it won't send anything over TEXT(255) and I need to send TEXT(MAX) as a parameter. I'm trying to figure out how to use ADO instead, or something.
    Tuesday, March 15, 2016 10:13 PM
  • I have to avoid constructing a string to be executed.

    Hi HTHP,

    Just curious, is there a special reason for this?

    Imb.

    Tuesday, March 15, 2016 10:30 PM
  • I did touch on that: "To avoid SQL injections, and the maintenance of handling chars that mess up the string, I need to find a way to insert via paramaters."

    I've got some people who think it is fun to mess with some of the input fields that have built strings. So i'm trying to learn how to avoid giving the user that option. And I want something consistant that can insert exact text from different sources without having to worry about that text containing Chars that will mess up the string construction or SQL syntax.

    I think I can create a param in a ADODB.Command object, but how do I insert that param into my SQL string like you can in the QueryDef? I'm still trying to figure that out.
    Tuesday, March 15, 2016 10:59 PM
  • I've got some people who think it is fun to mess with some of the input fields that have built strings. So i'm trying to learn how to avoid giving the user that option. And I want something consistant that can insert exact text from different sources without having to worry about that text containing Chars that will mess up the string construction or SQL syntax.

    Hi HTHP,

    I think there are other approaches.

    All SQL-strings in my applications are constructed dynamically. The users never need to type in any text, because the SQL-string are constructed on the base of the context in which the user works, added with information from dynamical menus. No mess up possible.

    You can try to think in that direction.

    Imb.

    Tuesday, March 15, 2016 11:53 PM
  • I did touch on that: "To avoid SQL injections, and the maintenance of handling chars that mess up the string, I need to find a way to insert via paramaters."

    I've got some people who think it is fun to mess with some of the input fields that have built strings. So i'm trying to learn how to avoid giving the user that option. And I want something consistant that can insert exact text from different sources without having to worry about that text containing Chars that will mess up the string construction or SQL syntax.

    I think I can create a param in a ADODB.Command object, but how do I insert that param into my SQL string like you can in the QueryDef? I'm still trying to figure that out.

    This ought to work:

        Dim cmd As ADODB.Command
        Dim lngAffected As Long
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = CurrentProject.AccessConnection
        cmd.CommandType = adCmdText
        cmd.CommandText = "INSERT INTO ServiceRequest (Revisions) VALUES(?)"
        cmd.Parameters(0) = "Your long text here ..."
        cmd.Execute lngAffected
        
        Debug.Print "#Records affected = " & lngAffected
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 16, 2016 12:16 AM
  • I have to avoid constructing a string to be executed. To avoid SQL injections, and the maintenance of handling chars that mess up the string, I need to find a way to insert via paramaters. The qDef example I provided is perfect. Except it won't send anything over TEXT(255) and I need to send TEXT(MAX) as a parameter. I'm trying to figure out how to use ADO instead, or something.

    From what I can tell, you'd have to specify your parameter's data type as LongText, but even then it doesn't seem to work.  I find that even with a verified parameter type of LongText, it won't let me assign a value longer than 255 characters.  So it seems the implementation of LongText parameters is incomplete.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 16, 2016 12:18 AM
  • Thanks Dirk. I see that the ? is used as a place holder for cmd.Parameter(0) of the cmd.Parameters collection. I thought I would be able to infer how to scale an example with one param like this, but I am a bit confused. How do you insert multiple different input params? Do I use multiple ?(question mark) marks? And if so, how do I know which param is linked to which question mark?
    Wednesday, March 16, 2016 1:13 PM
  • I see that the ? is used as a place holder for cmd.Parameter(0) of the cmd.Parameters collection. I thought I would be able to infer how to scale an example with one param like this, but I am a bit confused. How do you insert multiple different input params? Do I use multiple ?(question mark) marks? And if so, how do I know which param is linked to which question mark?

    IIRC, you use multiple question marks for multiple parameters, and they are numbered in sequence.  I believe you can also use named parameters, using code along these lines:

        Dim cmd As ADODB.Command
        Dim lngAffected As Long
        Dim strMyMessage As String
        
        strMyMessage = "My long text here ..."
    
        Set cmd = New ADODB.Command
        With cmd
            Set .ActiveConnection = CurrentProject.AccessConnection
            .CommandType = adCmdText
            .CommandText = "PARAMETERS parMsgToRecipient LongText; INSERT INTO ServiceRequest(Revisions) VALUES([parMsgToRecipient])"
            .Parameters.Append .CreateParameter("parMsgToRecipient", adLongVarChar, adParamInput, Len(strMyMessage), strMyMessage)
            .Execute lngAffected
        End With
        
        Debug.Print "#Records affected = " & lngAffected
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by HTHP Wednesday, March 16, 2016 8:50 PM
    Wednesday, March 16, 2016 4:27 PM
  •  Consider using a recordset

          
    Dim rst      As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset("ServiceRequest", dbOpenDynaset, dbSeeChanges)
    
       With rst
          .AddNew
          !Revisions = "your long text here"
          .Update
      End With

    You can omit the dbSeeChanges and dbOpenDynaset if you not using sql server here.

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com


    Wednesday, March 16, 2016 6:41 PM
  • Dirk, thanks. I was able to make a working procedure from your examples.

    Albert, I considered that, but wanted to learn how to use another object model that I could duplicate the functionality of the QueryDef I originally posted. Something that I could build a SQL string, include params, and execute once without having to iterate through a loop. If I had to do a bulk insert, the DAO.Recordset wouldn't work unless it accepts params(which I don't know).

    Antero, thanks for the link. Super helpful for the datatypes part of my question!

    Below is what I created to duplicate the original QDef. For anyone who is wondering a similar question to mine. It includes param creation for 2 different datatypes. All works well, inserted records without trouble with all Chars I put in the msgToRecipient string:

    Dim CustomerID As Long
    CustomerID = 12345
    Dim msgToRecipient As String
    msgToRecipient = "Some text here that exceeds% 255 chars."

    Dim cmd As New ADODB.Command
    Dim affected As Long

    cmd.ActiveConnection = CurrentProject.AccessConnection
    cmd.CommandType = adCmdText
    cmd.CommandText = "INSERT INTO ServiceRequest (CustomerIDRef, Revisions) " & _
    " VALUES([parCustomerId], [parMsgToRecipient]) "

    cmd.Parameters.Append cmd.CreateParameter("parCustomerId", adInteger, adParamInput, , CustomerID)

    cmd.Parameters.Append cmd.CreateParameter("parMsgToRecipient", adVarWChar, adParamInput, Len(msgToRecipient), msgToRecipient)

    Debug.Print cmd.Parameters("parCustomerId")
    Debug.Print cmd.Parameters("parMsgToRecipient")
    Debug.Print cmd.CommandText
    cmd.Execute affected

    Debug.Print affected

    Thanks everyone!
    • Marked as answer by HTHP Wednesday, March 16, 2016 8:50 PM
    Wednesday, March 16, 2016 8:50 PM