Insert 'Text' Datatype into database

I am trying to pass data from an ASP.Net page to a stored procedure. I am passing the data through a Business Layer to a Data Layer and then to the Stored Proceddure.

aspx codebehind:

   create(textbox1.text, textbox2.text)

Business Layer:

   Function create(ByVal name, ByVal bulkContent)
          create(name,bulkContent)
   End Function

Data Layer:

    Function create(ByVal name As String, ByVal bulkContent As ?)   <<< not sure what datatype to use for text db datatype
        ------SQL to send the Stored Procedure
    End Function

Stored Procedure:

    *** I don't Know

I am not sure on the rest of the Data Layer nor how to do a Stored Procedure to accept a text datatype. I have to use Text because VarChar(8000) is not enough room.

Can anyone help me on this one.

-J


LVL 1
JsonTerre1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RonaldBiemansCommented:
have you tried system.data.sqltypes.sqlstring
0
RonaldBiemansCommented:
you can lookup the types in the help under system.data.sqltypes namespace
0
rfgkevCommented:
Your .net code should treat it the same way as it treats a varchar, so use it as a string.

Something like:

aspx codebehind:

   create(textbox1.text, textbox2.text)

Business Layer:

   Function create(ByVal name as string, ByVal bulkContent as string)
          create(name,bulkContent)
   End Function

Data Layer:

    Public Sub create(ByVal name As String, ByVal bulkContent As string)
        ------SQL to send the Stored Procedure
        Dim oConnection As New SqlConnection(ConnectionString)
        Dim oCommand As New SqlCommand("spMyProc", oConnection)
        Dim oTransaction as SqlTransaction
        Try
      oCommand.CommandType = CommandType.StoredProcedure
      oCommand.Connection.Open()
      SqlCommandBuilder.DeriveParameters(oCommand)
      oTransaction = oConnection.BeginTransaction
      oCommand.Transaction = oTransaction

      oCommand.Parameters("@Name").Value = name
      oCommand.Parameters("@BulkContent").Value = bulkContent

      oCommand.ExecuteNonQuery()
      oTransaction.Commit()
        Catch ex As Exception
      If Not oTransaction Is Nothing Then oTransaction.Rollback()
        Finally
      If Not oTransaction Is Nothing Then oTransaction.Dispose()
      If Not oCommand Is Nothing Then
              If Not oCommand.Connection Is Nothing AndAlso Not oCommand.Connection.State = ConnectionState.Closed Then oCommand.Connection.Close()
              oCommand.Dispose()
      End If
        End Try
    End Function

Stored Procedure:

    Create Procedure spMyProc
        @Name varchar(200),
        @BulkContent text
    As
        Insert Into tblData(Name, BulkContent) Values(@Name, @BulkContent)
    GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JsonTerre1Author Commented:
Thank you very much. That worked great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.