We help IT Professionals succeed at work.

Dealing with Memo fields in a SQL insert.

fritz_the_blank
on
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
I have written a function that automatically generates a SQL insert statement for form submission. It
assumes that the names of the inputs match the field names in the database and that a particular naming
convention is maintained (character fields start with "c", date fields with "d", boolean fields with
"b", integer/numeric fields with "i" and so forth).

It all works well with one exception: if the memo field is too long, the SQL insert does not take place.
I understand that there is a way around that (some of which use the chunk approach) but I am looking
for something that I could encorporate easily into my code. Moreover, I am looking for a solution that
works well both with Access and with SQL Server.

Here is the code:

function AddRecord(strTableName)
dim strElement, strFields, strValues, strSQL, strFormValue

strFields = ""
strValues = ""

for each strElement in Request.Form
     if left(trim(strElement), 1) = "c" or left(trim(strElement), 1) = "i" or left(trim(strElement), 1)="d"
or left(trim(strElement), 1) = "b" then
          strFormValue = replace(Request.Form(strElement),"'","''")
          strFields = strFields  &  strElement & ", "
          select case left(trim(strElement), 1)
               case "c" 'Character Data Type
                    strValues = strValues & "'" & strFormValue & "', "
               case "i" 'Numeric/Integer Data Type
                    strValues = strValues  & strFormValue & ", "
               case "d" 'Date Data Type
                    strValues = strValues & "'" & strFormValue & "', "
               case "b" 'Boolean Data Type
                    if Request.Form(strElement)="on" then
                         strValues = strValues  & "1" & ", "
                    else
                         strValues = strValues & "0" & ", "               
                    end if
               case "m" 'Memo field or long binary
                    'Need code for this
               case else
                    strValues = strValues
          end select          
     end if
next

strFields = left(strFields, Len(trim(strFields)) - 1)
strValues = left(strValues, Len(trim(strValues)) - 1)

strSQL = "INSERT into " & strTableName & "( " & strFields & ") VALUES (" & strValues & ");"

Response.Write(strSql)

ExecuteSQL strSql, strTableName

end function

Here are the supporting functions:

'====================================================

function ExecuteSQL(strSql, strTableName)

ConnectMe()
GetRecords(strTableName)

dim objCMD
set objCMD = Server.CreateObject("ADODB.Command")
set objCMD.ActiveConnection = objConnection
objCMD.CommandText = strSQL
objCMD.Execute


ClearRecordSet()
ClearConnection()

end function

'====================================================

function ConnectMe()

strDataPath = server.MapPath("accessdata.mdb")
set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                    + " Data Source= " & strDataPath & ";"_
                    + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.Open strConnectString

end function

'====================================================

function GetRecords(strTableName)

set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open strTableName, objConnection

end function

function ClearRecordSet()

if isobject(objRS) then
     if objRS.state <> 0 then
          objRS.close
     end if
     set objRS = Nothing
end if

end function

'====================================================

function ClearConnection()

if isobject(objConnection) then
     if objConnection.state <> 0 then
           objConnection.close
     end if
     set objConnection = Nothing
end if

end function

'====================================================

As far as I can tell at this point, the memo field problem should be the only real obstacle. I am currently
using the same case statement for character and date types, but I understand that I may need to change
the case statement for the date for SQL Server.


Any ideas?

Fritz the Blank

Commented:
Do you know how big your data is?
If your data is bigger than max of memo field then there is no way yo insert it in memeo field doe snot matter if you use Addchunck or not?
I am using blob fields in Oracle and I knwo is SqlServer there is the same type and it works fine.
again, what are you trying to do? What is the max lenghth of data that you are trying to insert and you can't?
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
On shorter enteries, less than a few thousand characters, all is fine. It is with longer entries (5-6 page long essays) that the trouble starts.

I am not sure how to use blob fields or how to encorporate the use of blobs in my function.

Fritz the Blank

Commented:
Using Blob field is not as easy as varchar fields.
But if you want to insert something more than max of varchar (In oracle ,32K) then you have to use blob or either keep it as a file on file system.
For usign blob field then you need to change the field type to blob, clob or ... (depends on your database) and then insert and retrive it through specific functions same as addchunk, getchunck and ...
Let me know if you want more information about blob fields.
But think about put limitation on the size of text they enter or keep it in text file on OS.

Commented:
> I understand that there is a way around that (some of which use the chunk approach) but I am looking for something that I could encorporate easily into my code.

The GetChunk and AppendChunk methods don't have "mystic" effects; you can regard them as "softer" methods than getting/setting the value at once (in case of network problems or streaming).

objRecordset.AddNew
objRecordset("ColName") = Value
objRecordset.Update

will not cause any problems with Text fields (and I'ld do it that way).

In your case, strFields should contain the Text field(s) the last. In case of "m", you would append to a second fields and values string, and after all, append both to the main fields and values strings.

Commented:
For Memo fields you need to treat it as though it was a stored procedure and pass each value as a parameter.

I have tried to use as much of your original code in my example.  Let me know if you can't follow where to put the variables or if you have any questions.

Set Com = Server.CreateObject("ADODB.Command")
Set Com.ActiveConnection = ObjConnection

Com.CommandText = "INSERT INTO " & strTableName & "(" &_
            strFields & ") VALUES (" & _
            strValues & ")"

' ***
' Each value should be represented by a ?
' If you have 6 fields for example then values should look like this
' VALUES (?, ?, ?, ?, ?, ?)
' ***

' Each value is then appended as a parameter to your command depending on
' the variable type

' For long integers
Set Param = Com.CreateParameter("FIELDNAME", adBigInt, adParamInput, , lngVarName)
Com.Parameters.Append Param

' For integers
Set Param = Com.CreateParameter("FIELDNAME", adInteger, adParamInput, , iVarName)
Com.Parameters.Append Param

' For bytes
Set Param = Com.CreateParameter("FIELDNAME", AdTinyInt, adParamInput, , byVarName)
Com.Parameters.Append Param

' For booleans
Set Param = Com.CreateParameter("FIELDNAME", adBoolean, adParamInput, , bVarName)
Com.Parameters.Append Param

' For strings
if (strVarName = "") then
     Set Param = Com.CreateParameter("FIELDNAME", adInteger, adParamInput, , NULL)
else
     Set Param = Com.CreateParameter("FIELDNAME", adInteger, adParamInput, , strVarName)
end if
Com.Parameters.Append Param

' For dates
Set Param = Com.CreateParameter("FIELDNAME", adDate, adParamInput, , dtVarName)
Com.Parameters.Append Param

' For memos
if (len(memVarName) = 0) then
     Set Param = Com.CreateParameter("FIELDNAME", adVarChar, adParamInput, 1, "")
else
     Set Param = Com.CreateParameter("FIELDNAME", adVarChar, adParamInput, len(memVarName), memVarName)
end if
Com.Parameters.Append Param

' AND NOW LET'S RUN IT!
Com.Execute

CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Hi All and thank you for your input; I am just getting a chance to look at it now.

BTW, I am hoping that whatever solution works can be incorporated with the following function that dynamically updates records as well:

function ChangeRecord(strTableName, strField, strOperator , strCondition)

dim strElement, strFields, strFormValue, strSetString, strSQL

strSetString =""
for each strElement in Request.Form
     if left(trim(strElement), 1) = "c" or left(trim(strElement), 1) = "i" or left(trim(strElement), 1)="d" or left(trim(strElement), 1) = "b" then
          strFormValue = replace(Request.Form(strElement),"'","''")
          strSetString = strSetString & strElement & "="
          select case left(trim(strElement), 1)
               case "c" 'Character Data Type
                    strSetString = strSetString & "'" & strFormValue & "', "
               case "i" 'Numeric/Integer Data Type
                    strSetString = strSetString  & strFormValue & ", "
               case "d" 'Date Data Type
                    strSetString = strSetString & "'" & strFormValue & "', "
               case "b" 'Boolean Data Type
                    if Request.Form(strElement)="on" then
                         strSetString = strSetString  & "1" & ", "
                    else
                         strSetString = strSetString & "0" & ", "               
                    end if
               case else
                    strSetString = strSetString
          end select    
     end if
next

strSetString = left(strSetString, Len(trim(strSetString)) - 1)
strSQL = "UPDATE " & strTablename & " SET " & strSetString & " WHERE " & strField & strOperator & strCondition

'response.write(strSQL & "<P><P>")

ExecuteSQL strSql, strTableName

end function

Fritz the Blank

Commented:
Following the solution I presented, as a stored procedure, you would only need to change the Com.CommandText line.
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Derek Erb,

I was hoping not to have to use stored procedures. I understand that they are more efficient, but I have certain constraints that won't allow me to use them. Is there a way to rework your suggestion so that stored procedures aren't necessary?

Robbert,

I have three questions for you:

Is using objRS.update and objRS.addnew preferable to creating a SQL command and then executing it?

Are you saying that if I use the .update and .addnew methods that I won't run into the large memo field issue?

Is there a simple way to employ the .getchunck and .addchunck methods to my function?

Fritz the Blank

Commented:
My example does not actually use stored procedures.  There are no stored procedures actually in the database.  The ASP code works with the query (INSERT INTO or UPDATE) as it would with a stored procedure which is much better optimized.

1) Using a command object EXECUTE is preferable to .update and .addnew.

2) Using the CreateParameter and AppendParameter methods you will not have the large memo field problem

3) The GetChunk and AddChunk methods do not work reliably on larger memo fields.  I used those methods for 2 or 3 years and had to rewrite the code on all of my sites to use the system you see here.
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Derek,

I am trying to integrate your code. I haven't done it for all data type just yet, but I am just trying to get it to work. Here's what I have so far:

function NewAddRecord(strTableName)

dim strDataPath, objConnection, strConnectString, Com, Param, strElement
dim strFields, strValues, strSQL, strFormValue

strDataPath = server.MapPath("accessdata.mdb")
set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                    + " Data Source= " & strDataPath & ";"_
                    + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
objConnection.Open strConnectString

Set Com = Server.CreateObject("ADODB.Command")
Set Com.ActiveConnection = ObjConnection

strFields = ""
strValues = ""

for each strElement in Request.Form
     if left(trim(strElement), 1) = "c" or left(trim(strElement), 1) = "i" or left(trim(strElement), 1)="d" or left(trim(strElement), 1) = "b" then
          strFormValue = replace(Request.Form(strElement),"'","''")
          strFields = strFields  &  strElement & ", "
          select case left(trim(strElement), 1)
               case "c" 'Character Data Type
                    strValues = strValues & "'" & strFormValue & "', "
                    if (strVarName = "") then
                         Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , NULL)
                    else
                         Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , strVarName)
                    end if
                    Com.Parameters.Append Param

               case "i" 'Numeric/Integer Data Type
                    strValues = strValues  & strFormValue & ", "
                    Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , iVarName)
                    Com.Parameters.Append Param

               case "d" 'Date Data Type
                    strValues = strValues & "'" & strFormValue & "', "
                    Set Param = Com.CreateParameter(strElement, adDate, adParamInput, , dtVarName)
                    Com.Parameters.Append Param

               case "b" 'Boolean Data Type
                    if Request.Form(strElement)="on" then
                         strValues = strValues  & "1" & ", "
                    else
                         strValues = strValues & "0" & ", "               
                    end if
                    Set Param = Com.CreateParameter(strElement, adBoolean, adParamInput, , bVarName)
                    Com.Parameters.Append Param
               
               case "m" 'Memo Data Type
                    if (len(memVarName) = 0) then
                         Set Param = Com.CreateParameter("FIELDNAME", adVarChar, adParamInput, 1, "")
                    else
                         Set Param = Com.CreateParameter("FIELDNAME", adVarChar, adParamInput, len(memVarName), memVarName)
                    end if
                    Com.Parameters.Append Param


               case else 'Leave it alone, not wanted in SQL statement (hidden fields and etc)
                    strValues = strValues
                   
          end select          
     end if
next

strFields = left(strFields, Len(trim(strFields)) - 1)
strValues = left(strValues, Len(trim(strValues)) - 1)

strSQL = "INSERT into " & strTableName & "( " & strFields & ") VALUES (" & strValues & ");"

Response.Write(strSql)

Com.CommandText = strSQL
Com.Parameters.Append Param
Com.Execute


end function

Unfortunately, I am getting a series of errors. Can you get this to work on your side?

Fritz the Blank

Commented:
> Is using objRS.update and objRS.addnew preferable to creating a SQL command and then executing it?

There is a small overhead, expressing in 2 or 4 milliseconds or so. Let it be 9 ;-)

> Are you saying that if I use the .update and .addnew methods that I won't run into the large memo field
issue?

Yes, there's no such issue with .update. - You won't need the function you're currently developping, and don't need to hide single quotes in strings you want to insert.

> Is there a simple way to employ the .getchunck and .addchunck methods to my function?

I wouldn't use them, and never used (had to use) .appendchunk (also see:
PRB: AppendChunk from ADO or RDS Doubles Binary Field Size
http://support.microsoft.com/support/kb/articles/Q200/2/61.ASP - don't use .appendchunk with scripting languages).

You can simply state,
objRS("myText") = Request.Form("sdf")

But anyway :-)
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
This is an interesting tidbit from Microsoft:

"The size limit of each form field that is retrieved in the Request object is 102,399 bytes. The error occurs when you exceed this limit."

So, the reason that the insert and update wasn't working was because of the upper limit...

I am going to test the code with something smaller than 102,399.

Fritz the Blank
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Okay,

With a memo field value that is slightly smaller than the 102,399 limit works fine.


The question now becomes how to deal with larger values. MS Knowledgebase Article ID: Q273482 offers a javascript workaround, but I am interested in seeing what other options there are.

Fritz the Blank

Commented:
Oh shitty. What about using SA-FileUp, www.softartisans.com.
You can also Request.BinaryRead, and transform some of the results to text, by Script but that's real work. Look at http://www.pstruh.cz/help/ScptUtl/library.htm - there: PureASPUpload v1.5.

Commented:
As regards your code example you do not need the strValues variable and should not be using it.  The values passed to the SQL string should simply be a chain of ? characters one representing each field.  The values themselves are 'inserted' in to the command by the .AppendParam method.

Your SQL string should look the same as it does but each of your strValues calls should be simply a:

strValues = strValues & "?,"

Then at the end you remove the last comma with a:

strValues = Left(strValues, Len(strValues)-1)

Then you can maintain your SQL String with

"INSERT into " & strTableName & "( " & strFields & ") VALUES (" & strValues & ")"

BTW you can get rid of that trailing semi-colon ";" which is for Access.  Access does not require it, not having it does not change Access' performance and (most importantly) if ever you upsize your database to an SQL Server you'll have to go through all of your code and remove the trailing semi-colons.

HOWEVER:  Based on your final messages are you dealing with a Memo field or a BLOB field?  Is someone actually going to type more than 102,399 characters on your form before pressing the submit button?  Is this something they are copying and pasting in to the field from somewhere else?
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Derek Erb and Robbert,

I apologize that this has gone on so long. I intend to post a second question with points to make sure that I show my appreciation to both of you.

Robbert,

I agree with you! what a pain in the behind.

Derek Erb,

Please excuse my being a little slow on the uptake. I am new to programming, and this function is about the most sophisticated thing that I have written so far. As a result, I am not following how your code works. I would like to try to implement it, and if I can get it running, study it. Would it be too much trouble for you to post my function with your modifications so that I can look at it?

Thank you both!!



Commented:
Well, if you're reaching these limits you either have to split form fields to be posted via client-side script (which cannot be scalable as you can never know how large the form post is -- on the other hand, why not: 5 hidden form field should suffice...), or treat the form post as binary upload.
As for the latter, you would a) use a component (which is relatively simple if you take one that comes with good documentation), or you would do that by script (PStruh's PureASPUpload, linked above, is (almost) ready to work "as is", and comes - as the component SaFileUp - with samples, uploading form contents to a database).
Sure it's hard days, but absolutely manageable.

There are several components with documentation, maybe see:
http://www.google.com/search?q=upload+asp+database&btnG=Google+Search

Commented:
Ask and ye shall receive:

set objConnection=Server.CreateObject("ADODB.Connection")

' I have changed your connect string to remove the unnecessary bits
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
              Server.MapPath("accessdata.mdb")
objConnection.Open strConnectString

Set Com = Server.CreateObject("ADODB.Command")
Set Com.ActiveConnection = ObjConnection

strFields = ""
strValues = ""

' Fill the strValues counter and the fields list
for each strElement in Request.Form
     strFields = strFields & strElement & ","
     strValues = strValues & "?,"
next

' Remove trailing commas
strFields = Left(strFields, Len(strFields)-1)
strValues = Left(strValues, Len(strValues)-1)
     
' SQL String
Com.CommandText = "INSERT INTO " & strTableName & "(" & _
            strFields & ") VALUES (" & _
            strValues & ")"
           
' DEBUG DISPLAY SQL String
Response.Write "INSERT INTO " & strTableName & "(" & _
          strFields & ") VALUES (" & _
          strValues & ")"

' Append parameters
for each strElement in Request.Form
     if left(trim(strElement), 1) = "c" OR _
     left(trim(strElement), 1) = "i" OR _
     left(trim(strElement), 1)="d" OR _
     left(trim(strElement), 1) = "b" then
     
          ' Get form value
          strFormValue = replace(Request.Form(strElement),"'","''")

          ' Append parameter
          select case left(trim(strElement), 1)
               case "c" 'Character Data Type
                    if (strVarName = "") then
                         Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , NULL)
                    else
                         Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , strFormValue)
                    end if
                    Com.Parameters.Append Param

               case "i" 'Numeric/Integer Data Type
                    Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , strFormValue)
                    Com.Parameters.Append Param

               case "d" 'Date Data Type
                    Set Param = Com.CreateParameter(strElement, adDate, adParamInput, , strFormValue)
                    Com.Parameters.Append Param

               case "b" 'Boolean Data Type
                    if Request.Form(strElement)="on" then
                         strFormValue = TRUE
                    else
                         strFormValue = FALSE
                    end if
                    Set Param = Com.CreateParameter(strElement, adBoolean, adParamInput, , strFormValue)
                    Com.Parameters.Append Param

               case "m" 'Memo Data Type
                    if (len(memVarName) = 0) then
                         Set Param = Com.CreateParameter(strElement, adVarChar, adParamInput, 1, "")
                    else
                         Set Param = Com.CreateParameter(strElement", adVarChar, adParamInput, len(strFormValue), strFormValue)
                    end if
                    Com.Parameters.Append Param

               case else 'Leave it alone, not wanted in SQL statement (hidden fields and etc)
                    strValues = strValues

          end select          
     end if
next

' RUN IT!
Com.Execute
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
DerekErb,

I can't run the code without getting errors. Did it work on your side?

Robbert,

I am posting a question with points for you now.

Fritz the Blank

Commented:
What exactly are the errors are you getting?  Can you copy and paste them in?

I can't run your code on my system as I would first need to create your database and everything around it.

CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Hey Derek,

I am using any static html page that calls an ASP page with this function. The only hitch is that the field names need to follow the naming conventions both on the static page and in the database. So if you have a database with a table with the fields cFirstName, cLastName, dDateAdded, bVerified, and mComments you have everything you need for testing.

Here is what I am getting:

Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/aspTest/constants.inc, line 268

                   if (strVarName = "") then
                        Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , NULL)
                   else
                        Set Param = Com.CreateParameter(strElement, adInteger, adParamInput, , strFormValue)
                   end if
                   Com.Parameters.Append Param


Line 268 is the first Set Param above.

Fritz the Blank

Commented:
The above error means that the settings for that field in the table do not allow null values.

You need to change the definition settings for the field to allow nulls and to allow zero-length strings.
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
D'uh!!!
Sorry about that; I made the changes to the table. I don't get errors any more but the function doesn't add the record either. Will this code work with an MS Access back end?

-Fritz the Blank

Commented:
If there is no error after the Com.Execute line then it should have added the record.  Check the display of the SQL string to make sure.  Close and re-open Access to refresh the tables and make sure the record was added.

Does this function work with an Access backend?  Go to our site JazzFrance.com and check out the Forums section.  The code you are looking at is part of my programming of the forums/newsgroup section and allows everyone to enter long questions and responses.  The backend for the Forums was originally Access 97 and was only recently replaced by SQL Server 2000 however the code was not modified at all.
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Hey Derek,

Here is the string that is created:
INSERT INTO Users(cUserName,cPassword,iLevel,cNotes,dDateAdded,bVerified,submit1) VALUES (?,?,?,?,?,?,?)

Just to make sure that it was not a refreshing issue, I closed out of Access before I submitted the page and reopened afterwards. Still no record.

BTW, I wonder why sumbit1 is in there? The code should only include fields beginning with c, d, b, i, or m, right? Perhaps it's the inclusion of the submit1 field in the insert statement that is causing the grief....

Fritz the Blank

Commented:
You could be right but I don't know what submit1 is as I haven't seen your form.

It sounds as though submit1 is your Submit button. If that is the case then you need to modify the following code in your first loop:

' Fill the strValues counter and the fields list
for each strElement in Request.Form
  if (strElement <> "submit1") then
    strFields = strFields & strElement & ","
    strValues = strValues & "?,"
  end if
next

CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
You're right that it is the submit button, but the code starts off like this:

for each strElement in Request.Form
    if left(trim(strElement), 1) = "c" OR _
    left(trim(strElement), 1) = "i" OR _
    left(trim(strElement), 1)="d" OR _
    left(trim(strElement), 1) = "b" then

so, the submit button should not be included then, right?

Fritz the Blank

PS, I've increased the points a little.

Commented:
You have the if condition in the SECOND loop but not in the FIRST loop.

Why are you trimming the strElement, which is a form object name which you have created, before taking the first left character?

I have made a global var which is am calling cElement so as to make the code more legible.

You will need to modify your first loop as such:

' Fill the strValues counter and the fields list
for each strElement in Request.Form
  cElement = left(strElement,1)
  IF (cElement = "c") OR _
     (cElement = "i") OR _
     (cElement = "d") OR _
     (cElement = "b") THEN
       strFields = strFields & strElement & ","
       strValues = strValues & "?,"
  END IF
next

Although it is not necessary I think it would be more legible if you second loop was modified as:

' Append parameters
for each strElement in Request.Form
  cElement = left(strElement,1)
  IF (cElement = "c") OR _
     (cElement = "i") OR _
     (cElement = "d") OR _
     (cElement = "b") THEN
   
         ' Get form value
         strFormValue = replace(Request.Form(strElement),"'","''")

         ' Append parameter
         select case cElement
...

That should do it!
CERTIFIED EXPERT
Top Expert 2005

Author

Commented:
Hey Derek,

We are getting near the bottom of this. I just need to clean a few things up. When this is done, I think it will be a fine way to automate collecting data from a form and automatically generate the SQL for inserts and updates.

Thank you once again for you help.

Frtiz the Blank