Correct ADO parameter 4

[RonRepp]

Hi All:

What is the correct syntax for a Text parameter in SQL.

Dim prmHTML As ADODB.Parameter
    Set prmHTML = New ADODB.Parameter
    prmHTML.Type = adVarChar
    'prmHTML.Size = 8000
    prmHTML.Direction = adParamInput
    prmHTML.Value = strMessage
    CMD.Parameters.Append prmHTML

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[BobRodes]

I would have thought that what you have works. What's wrong with it?

 

[JoeAtWork]

Here's a snippet of code to call a stored procedure in an SQL Server 2000 database (I don't know if it would work with SQL Server 2005 or greater, however)

'2147482647 is the value to use for the Size when an ADO Parameter
'object is for a Text (memo) field

    Set spUpdateProblem = New ADODB.Command
    spUpdateProblem.ActiveConnection = gcnnTechSupport
    spUpdateProblem.CommandType = adCmdStoredProc
    strProcName = gstrTechSPPrefix & "SaveProblemDesc"
    spUpdateProblem.CommandText = strProcName
    'Set up parameters
    With spUpdateProblem
        'Return value (i.e. any error codes) parameter
        Set adoParam = .CreateParameter("ReturnVal", adInteger, adParamReturnValue, , -1)
        .Parameters.Append adoParam
        Set adoParam = .CreateParameter("ID", adInteger, adParamInput, , 0)
        .Parameters.Append adoParam
        Set adoParam = .CreateParameter("Desc", adLongVarChar, adParamInput, 2147482647, "")
        .Parameters.Append adoParam
    End With

This matches up with following stored procedure:

CREATE PROCEDURE SaveProblemDesc 
	@ProblemID Int,
	@ProblemDesc Text
AS
	Declare @ErrStatus Int

/* This stored procedure saves the description (ProblemReported) of an existing Problem record.
    It is used by the Batch Actions screen, where the description is the only Problem field that can be edited by the user. */

	UPDATE Problems Set ProblemReported = @ProblemDesc WHERE ProblemID = @ProblemID

	-- Return any error that occurred
	Set @ErrStatus = @@Error
	Return (@ErrStatus)

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[RonRepp]

Bob & Joe:

I thought it would have worked, too. The same syntax works if I'm using NVarChar, but the text I'm trying to store is over 8000 characters.

Joe, I'll try your version and see if I get better results.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

Here is the full procedure. I'll also include to SP to see if someone sees something that I'm not.

More stars await...

Public Sub FillM5(ByVal strTo As String, _
                        ByVal strMessage As String, _
                        ByVal strSubject As String, _
                        strFrom)
Dim Con As ADODB.Connection
    Dim SQL As String
    Dim CMD As ADODB.Command
    Set CMD = New ADODB.Command
    Set Con = New ADODB.Connection
    On Error GoTo AfterERR
    
    Dim H1, H2, H3, H4 As String
    Dim S As String
        
    SQL = "EM_InsertM5"

     Con.ConnectionTimeout = 3600
    Con.CursorLocation = adUseServer
    Con.ConnectionString = GetConstring("M5")
    Con.Open Con.ConnectionString
    
    Set CMD.ActiveConnection = Con
    CMD.CommandText = SQL
    CMD.CommandType = adCmdStoredProc
    CMD.CommandTimeout = 3600
    
    Dim prmTo As ADODB.Parameter
    Set prmTo = New ADODB.Parameter
    prmTo.Type = adVarChar
    prmTo.Size = 50
    prmTo.Direction = adParamInput
    prmTo.Value = strTo
    CMD.Parameters.Append prmTo
   
    Dim prmFrom As ADODB.Parameter
    Set prmFrom = New ADODB.Parameter
    prmFrom.Type = adVarChar
    prmFrom.Size = 50
    prmFrom.Direction = adParamInput
    prmFrom.Value = strFrom
    CMD.Parameters.Append prmFrom
    
    Dim prmSubject As ADODB.Parameter
    Set prmSubject = New ADODB.Parameter
    prmSubject.Type = adVarChar
    prmSubject.Size = 50
    prmSubject.Direction = adParamInput
    prmSubject.Value = strSubject
    CMD.Parameters.Append prmSubject
    
    S = strMessage
    H1 = Left(S, 4000)
    
    If Len(S) < 4001 Then
        H2 = ""
        H3 = ""
        H4 = ""
    
    End If
    
    
    Dim prmHTML As ADODB.Parameter
    Set prmHTML = New ADODB.Parameter
    prmHTML.Type = adVarChar
    prmHTML.Size = 4000
    prmHTML.Direction = adParamInput
    prmHTML.Value = H1
    CMD.Parameters.Append prmHTML
    
    If Len(S) > 4000 Then
        H2 = Mid(S, 4001, 4000)
    Else
        H2 = ""
    End If
    
    
    Dim prmHTML2 As ADODB.Parameter
    Set prmHTML2 = New ADODB.Parameter
    prmHTML2.Type = adVarChar
    prmHTML2.Size = 4000
    prmHTML2.Direction = adParamInput
    prmHTML2.Value = H2
    CMD.Parameters.Append prmHTML2
    
    If Len(S) > 8000 Then
        H3 = Mid(S, 8001, 4000)
    Else
        H3 = ""
    End If

    
    
    Dim prmHTML3 As ADODB.Parameter
    Set prmHTML3 = New ADODB.Parameter
    prmHTML3.Type = adVarChar
    prmHTML3.Size = 4000
    prmHTML3.Direction = adParamInput
    prmHTML3.Value = H3
    CMD.Parameters.Append prmHTML3
    
    If Len(S) > 12000 Then
        H4 = Mid(S, 12001, 4000)
    Else
        H4 = ""
    End If
    
    
    Dim prmHTML4 As ADODB.Parameter
    Set prmHTML4 = New ADODB.Parameter
    prmHTML4.Type = adVarChar
    prmHTML4.Size = 4000
    prmHTML4.Direction = adParamInput
    prmHTML4.Value = H4
    CMD.Parameters.Append prmHTML4
    
    Debug.Print SQL & " '" & prmTo.Value & "', '" & _
        prmFrom.Value & "', '" & _
        prmSubject.Value & "', '" & _
        prmHTML.Value & "', '" & _
        prmhtm2.Value & "', '" & _
        prmhtm3.Value & "', '" & _
        prmhtm4.Value & "'"
        
        CMD.CommandText = SQL & " '" & prmTo.Value & "', '" & _
        prmFrom.Value & "', '" & _
        prmSubject.Value & "', '" & _
        prmHTML.Value & "', '" & _
        prmhtm2.Value & "', '" & _
        prmhtm3.Value & "', '" & _
        prmhtm4.Value & "'"
    
    CMD.Execute
    
    Con.Close
    Set Con = Nothing
    Set CMD = Nothing
    
    
    Exit Sub
AfterERR:
    Call LogError(OrderID, Err.Number, Err.Description, 2)
    Exit Sub
    
                        
End Sub

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EM_InsertM5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EM_InsertM5]
GO

CREATE PROC EM_InsertM5
	(
		@To NVarChar (50),
		@From NVarChar (50),
		@Subject NVarChar (50),
		@HTML1 NVarChar (4000), 
		@HTML2 NVarChar (4000),
		@HTML3 NVarChar (4000), 
		@HTML4 NVarChar (4000) 
	)


AS

SET NOCOUNT ON

INSERT INTO 
		M5Temp
	(
		M5To, 
		M5From, 
		M5Subject, 
		M5HTML,
		M5HTML1,
		M5HTML2,
		M5HTML3
	)
VALUES     
	(
		@To,
		@From,
		@Subject,
		@HTML1,
		@HTML2,
		@HTML3,
		@HTML4
	)

Return

GO

Note: I broke up the large HTML text.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

I guess that this is the problem:

Cannot create a row of size 16946 which is greater than the allowable maximum of 8060.
The statement has been terminated.

Any thoughts?

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[gmmastros]

SQL Server stores data on the disk in 8k pages. Rows in a table cannot span multiple pages.

You can create a table that has more than 8060 defined bytes. However, if you insert and update a row that exceeds 8060, you get the error. There is nothing you can do to change this behavior.

You can redesign your table structure.

Currently, your table structure appears to be:

M5Temp
----------
M5To
M5From
M5Subject
M5HTML
M5HTML1
M5HTML2
M5HTML3

I would suggest that you add another table.

M5Temp
------------
M5Id Int Identity(1,1)
M5To
M5From
M5Subject

M5HTML
------------
M5Id Int
SequenceNumber Int
HTML nvarchar(4000)

The primary Key for M5HTML should be M5Id, SequenceNumber

Ex:

Create Table M5HTML(M5Id Int, SequenceNumber Int, HTML nvarchar(4000) Primary Key (M5Id, SequenceNumber))

Then, you could change your procedure to:

CREATE PROC EM_InsertM5
    (
        @To NVarChar (50),
        @From NVarChar (50),
        @Subject NVarChar (50),
        @HTML1 NVarChar (4000), 
        @HTML2 NVarChar (4000),
        @HTML3 NVarChar (4000), 
        @HTML4 NVarChar (4000) 
    )


AS

SET NOCOUNT ON

Declare @M5Id Int

INSERT INTO 
        M5Temp
    (
        M5To, 
        M5From, 
        M5Subject
    )
VALUES     
    (
        @To,
        @From,
        @Subject
    )

Select @M5Id = Scope_Identity()

If RTrim(@HTML1) <> ''
  Insert Into M5HTML(M5Id, SequenceNumber, HTML)
  Values (@M5Id, 1, @HTML1)

If RTrim(@HTML2) <> ''
  Insert Into M5HTML(M5Id, SequenceNumber, HTML)
  Values (@M5Id, 2, @HTML2)

If RTrim(@HTML3) <> ''
  Insert Into M5HTML(M5Id, SequenceNumber, HTML)
  Values (@M5Id, 3, @HTML3)

If RTrim(@HTML4) <> ''
  Insert Into M5HTML(M5Id, SequenceNumber, HTML)
  Values (@M5Id, 4, @HTML4)

Of course, if you can, I would encourage you to upgrade your database to SQL2005 so that you can start using the nvarchar(max) data type. It will make your life much easier. Trust me.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Disferente]

In SQL 2000 you could also use data type image or text, as with those you can store more. I have had no problem storing 850kb of data in a text field.

 

[RonRepp]

Hi all:

if I use the Text field type, it works when shoving it through Query Analyzer.

However, when using VB6 code, I get the same error. Is my type not right?

Dim prmHTML As ADODB.Parameter
    Set prmHTML = New ADODB.Parameter
    [b]prmHTML.Type = adVarChar[/b]
    prmHTML.Size = 8000
    prmHTML.Direction = adParamInput
    prmHTML.Value = strMessage
    CMD.Parameters.Append prmHTML

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

Thanks to all of you. I hope I didn't miss any stars.

This is what finally worked:

Public Sub FillM5(ByVal strTo As String, _
                        ByVal strMessage As String, _
                        ByVal strSubject As String, _
                        strFrom)
                        
On Error GoTo AfterErr
    Dim Con As ADODB.Connection
    Dim SQL As String
    Set Con = New ADODB.Connection
    
    Con.ConnectionTimeout = 3600
    Con.CursorLocation = adUseServer
    Con.ConnectionString = GetConstring("M5")
    Con.Open Con.ConnectionString
    
    SQL = "EM_InsertM5 "
    SQL = SQL & " '" & strTo & "', "
    SQL = SQL & " '" & strFrom & "', "
    SQL = SQL & " '" & strSubject & "', "
    SQL = SQL & " '" & strMessage & "' "
    
    Debug.Print SQL
    
    Con.Execute SQL
    
    Con.Close
    Set Con = Nothing
    Exit Sub
AfterErr:
    MsgBox OrderID & " " & Err.Description
    

                        
End Sub

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EM_InsertM5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EM_InsertM5]
GO

CREATE PROC EM_InsertM5
	(
		@To NVarChar(50) ,
		@From NVarChar(50) ,
		@Subject NVarChar(50) ,
		@HTML1 Text
	)


AS

SET NOCOUNT ON

INSERT INTO 
		M5TempNew
	(
		M5To, 
		M5From,
		M5Subject,
		M5HTML1
	)
VALUES     
	(
		@To ,
		@From  ,
		@Subject  ,
		@HTML1
	)


RETURN

GO

Thanks again.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[dilettante]

Oof! But what about the SQL Injection potential in this approach?

 

[gmmastros]

Just a couple comments...

You should change your sql to this:

    SQL = "EM_InsertM5 "
    SQL = SQL & " '" & [!]Replace([/!]strTo[!], "'", "''")[/!] & "', "
    SQL = SQL & " '" & [!]Replace([/!]strFrom[!], "'", "''")[/!] & "', "
    SQL = SQL & " '" & [!]Replace([/!]strSubject[!], "'", "''")[/!] & "', "
    SQL = SQL & " '" & [!]Replace([/!]strMessage[!], "'", "''")[/!] & "' "

You see, if your html code includes a single quote, the query will fail because a singe-quote is supposed to mark the beginning and end of your data. By replacing 1 single-quote with 2, the data will be inserted properly.

Also, it appears as though you have settled on the Text data type. I suggest you change this to [!]n[/!]Text instead. Text is only capable of storing ASCII characters where ntext can store ASCII and UNICODE characters. The way you handle text and ntext data is the same (meaning you will not lose any functionality). Unicode does require twice the storage space as ASCII, but storage space is cheap.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

If you want to use ADO with text or image datatypes with column values of greater than 8000 bytes you must use the ADO GetChunk method. Also see How To Read and Write BLOBs Using GetChunk and AppendChunk.