Hi All

I have a connection, command and parameter which all work fine to execute a stored proc that has been in use for years. I have to add a 'comment/reason for change'  text into a "TEXT" column in a table in the DB.

I added last line ("@Reason") in the following Stored Proc...
CREATE PROC sp_updOfferUnlock
@OfferID INT,
@Operator VARCHAR(10),
@OfferName VARCHAR(255),
@Reason TEXT

In My VB code i added the following line to accomadate the @Reason field...

Params.Append Comm.CreateParameter("@Reason", adVarChar, adParamInput, 10000, txtReasonComments.Text)

but it is not working.  I do not know which datatype I should be using, and if or what size value I should have.
txtReasonComments.text is a multiline textbox on my form.

Please help!!
All help is very much appreciated.
Cheers
Tank

hello Tank,

I tested it in my SQL Server and it gave me no errors, I've created a simple stored procedure that takes a text parameter as input and then adds it into a table with a single field...the call to the stored procedure returns no errors, the only thing I noticed is that when you open the table from SQL you see only the first line of the text you inserted in the field (it is truncated at first CR) but if you open the recordset and loop through the records to retrieve the text in the field and display it back in the text box all the lines are returned...

please let me know...

____________________________
AndreaVB

Thanks for that. A couple of small details I left out before.
Im running VB6 with SP5
DB is MS SQL 6.5 (Not a typo
using ADO 2.7

Not too sure if this is a problem.  With your CreateParameter statement what type and size did you use to transefer the text from a textbox?

Thanks Again
Tank

I used the same type as your example, then I also tested adLongVarChar and it worked well too.

but I'm using an SQL server 2000

here's the code I've used in Visual Basic

Option Explicit Private Sub Command1_Click()     Dim c As New ADODB.Connection     Dim cmd As New ADODB.Command          c.Open "driver={SQL Server};SERVER=ServerName;UID=sa;PWD=myPassword;database=test"     Set cmd.ActiveConnection = c     cmd.CommandText = "{Call test.dbo.mysp(?)}"     cmd.Parameters.Append cmd.CreateParameter("text", adLongVarChar, adParamInput, 10000, Text1.Text)     cmd.Execute End Sub Private Sub Command2_Click()     Dim c As New ADODB.Connection     Dim rs As New ADODB.Recordset          c.Open "driver={SQL Server};SERVER=ServerName;UID=sa;PWD=myPassword;database=test"     rs.Open "SELECT * FROM test.dbo.mytable", c     Text1.Text = ""     Do While Not rs.EOF         Text1.Text = Text1.Text & rs.Fields("myfield") & vbCrLf & "---NEXT RECORD---"         rs.MoveNext     Loop End Sub

this is a form with 2 command button and a multiline text box, replace ServerName, myPassword and the database, stored procedure and table with yours

...and here's the stored procedure:

CREATE PROCEDURE mysp(@t as text) AS insert into mytable values(@t)

what's happening to your code? do you receive a particular error with a description?

____________________________
AndreaVB

I will try what you have mentioned in the morning...nearly midnight here.

I noticed that you have...

cmd.CommandText = "{Call test.dbo.mysp(?)}"

Why the "{" and "}" plus the "Call"
In my code  I have

With Comm
.ActiveConnection = adoConn
.CommandType = adCmdStoredProc
.CommandText = "sp_UpdContract"
and then my parameters

Is using "Call" the same as "Exec", as in "Exec sp_UpdContract....
Cheers
Tank

I've tested it with your syntax and it works well too

I changed the code to execute the stored procedure according to your suggestion and this is the new Command1 code:

Private Sub Command1_Click()     Dim c As New ADODB.Connection     Dim cmd As New ADODB.Command          c.Open "driver={SQL Server};SERVER=ServerName;UID=sa;PWD=myPassword;database=test"     With cmd         .ActiveConnection = c         .CommandType = adCmdStoredProc         .CommandText = "mysp"         .Parameters.Append .CreateParameter("text", adLongVarChar, adParamInput, 10000, Text1.Text)         .Execute     End With End Sub

in my side it works well as the other one...
but what is the error you are getting?

[Edited by admin on 14-06-2006 at 01:49 PM GMT]

____________________________
AndreaVB

Hi Admin

Thanks a heap for your help. I implemented the adLongVarChar and it worked no problem.  Maybe it had to do with SQL 6.5, but when I used adVarChar the application displayed a length of data error message. I must admit I totally overlooked the long varchar option.
All the best
Tank