Sorry it's taken me so long to reply but I haven't been abel to get to my code, I'm flat out. So I'll just give you an example of what I'm trying to do:
I created this application at work, but it uses the same technique I'm trying to use on my website with the text data type... it just isn't working.
my TSQL
CREATE procedure JobAdd
@PositionTitle nvarchar(50),
@ReferenceNo nvarchar(10),
@Location nvarchar(30),
@ClosingDate smalldatetime,
@JobDescription nvarchar(1000),
@Requirements nvarchar(1000),
@Email varchar(75),
@JobID int output
AS
INSERT INTO JobVacancies
(
PositionTitle,
ReferenceNo,
Location,
ClosingDate,
JobDescription,
Requirements,
Email
)
VALUES
(
@PositionTitle,
@ReferenceNo,
@Location,
@ClosingDate,
@JobDescription,
@Requirements,
@Email
)
SELECT @JobID = @@IDENTITY
GO
Something like that...
My vb .net code
PrivateFunction AddVacancyDetails() As Int32
Dim myConnection AsNew SqlConnection(ConfigurationSettings.AppSettings("strSqlConnectionString"))
Dim myCommand = New SqlCommand("jobAdd", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim paramPosition As SqlParameter = myCommand.Parameters.Add("@PositionTitle", SqlDbType.NVarChar, 50)
paramPosition.Value = txtPosition.Text
Dim paramLocation As SqlParameter = myCommand.Parameters.Add("@Location", SqlDbType.NVarChar, 30)
paramLocation.Value = txtLocation.Text
Dim paramClose As SqlParameter = myCommand.Parameters.Add("@ClosingDate", SqlDbType.SmallDateTime)
paramClose.Value = dtClose.SelectedDate.ToShortDateString()
Dim paramRef As SqlParameter = myCommand.Parameters.Add("@ReferenceNo", SqlDbType.NVarChar, 4)
paramRef.Value = txtRef.Text.ToUpper
Dim paramDescription As SqlParameter = myCommand.Parameters.Add("@JobDescription", SqlDbType.NVarChar, 1000)
If txtJobDescription.Text = "" Then
paramDescription.Value = DBNull.Value
Else
paramDescription.Value = txtJobDescription.Text
EndIf
Dim paramRequirements As SqlParameter = myCommand.Parameters.Add("@Requirements", SqlDbType.NVarChar, 1000)
If txtReq.Text = "" Then
paramRequirements.Value = DBNull.Value
Else
paramRequirements.Value = txtReq.Text
EndIf
Dim paramEmail As SqlParameter = myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 75)
Dim at AsString
at = txtEmail.Text.IndexOf("@")
If at = -1 Then
paramEmail.Value = txtEmail.Text & "@goldenwest.org.au"
Else
paramEmail.Value = txtEmail.Text
EndIf
Dim paramJobID As SqlParameter = myCommand.Parameters.Add("@JobID", SqlDbType.Int)
paramJobID.Direction = ParameterDirection.Output
myConnection.Open()
myCommand.ExecuteNonQuery()
Return Convert.ToInt32(paramJobID.Value)
EndFunction
This is just an app I made at work but it's the same way I'm trying to make my site except I want to use the text data type for the body
The above is to add a new article, I haven't tried that with the text data type because i haven't got past being able to populate the labels on the page with the information from the table. This is what I got for my stored procedure:
TSQL
CREATE procedure jobGetDetails
@JobID int,
@PositionTitle nvarchar(50) output,
@ReferenceNo nvarchar(10) output,
@Location nvarchar(30) output,
@ClosingDate smalldatetime output,
@JobDescription nvarchar(1000) output,
@Requirements nvarchar(1000) output,
@Email varchar(75) output
AS
SELECT
@JobID = JobID,
@PositionTitle = PositionTitle,
@ReferenceNo = ReferenceNo ,
@Location = Location,
@ClosingDate = ClosingDate ,
@JobDescription = JobDescription,
@Requirements = Requirements,
@Email = Email
FROM JobVacancies
WHERE JobID = @JobID
IF @@ROWCOUNT = 0
SET @JobID = 0
GO
I use the same way to populate the page except the other way around e.g.
PrivateSub ShowEdit(ByVal JobID As Int32)
Dim myConnection AsNew SqlConnection(ConfigurationSettings.AppSettings("strSqlConnectionString"))
Dim myCommand = New SqlCommand("jobGetDetails", myConnection)myCommand.CommandType = CommandType.StoredProcedure
Dim paramJobID As SqlParameter = myCommand.Parameters.Add("@JobID", SqlDbType.Int)
paramJobID.Value = JobID
Dim paramPosition As SqlParameter = myCommand.Parameters.Add("@PositionTitle", SqlDbType.NVarChar, 50)
paramPosition.Direction = ParameterDirection.Output
Dim paramRef As SqlParameter = myCommand.Parameters.Add("@ReferenceNo", SqlDbType.NVarChar, 4)
paramRef.Direction = ParameterDirection.Output
Dim paramLocation As SqlParameter = myCommand.Parameters.Add("@Location", SqlDbType.NVarChar, 30)
paramLocation.Direction = ParameterDirection.Output
Dim paramClose As SqlParameter = myCommand.Parameters.Add("@ClosingDate", SqlDbType.SmallDateTime)
paramClose.Direction = ParameterDirection.Output
Dim paramDescription As SqlParameter = myCommand.Parameters.Add("@JobDescription", SqlDbType.NVarChar, 1000)
paramDescription.Direction = ParameterDirection.Output
Dim paramReq As SqlParameter = myCommand.Parameters.Add("@Requirements", SqlDbType.NVarChar, 1000)
paramReq.Direction = ParameterDirection.Output
Dim paramEmail As SqlParameter = myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 75)
paramEmail.Direction = ParameterDirection.Output
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
txtPosition.Text = paramPosition.Value
txtRef.Text = paramRef.Value
txtLocation.Text = paramLocation.Value
txtEmail.Text = paramEmail.Value
If paramReq.Value Is DBNull.Value Then
txtReq.Text = ""
Else
txtReq.Text = paramReq.Value
EndIf
If paramDescription.Value Is DBNull.Value Then
txtJobDescription.Text = ""
Else
txtJobDescription.Text = paramDescription.Value
EndIf
dtClose.SelectedDate = paramClose.Value
txtEmail.Text = txtEmail.Text.Remove(txtEmail.Text.IndexOf("@"), 18)
If dtClose.SelectedDate < dtClose.TodaysDate Then
lblStatus.Text = "Status:" & " " & "Expired Vacancy"
Else
lblStatus.Text = "Status:" & " " & "Current Vacancy"
EndIf
EndSub
What happens is, when I try to create a stored procedure like above, I can't Save it because I get an error telling me I can't use the text data type as an argument.
I know all the code looks daunting lol, but it's not the actual application in question, just the technique I used.
Thanks for all your help tekmaven,
Slade
Offline 
). Cool stuff :p . 
Unsolved 
