Column type (ADO - SQL) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Column type (ADO - SQL)

Author

Topic

thiruna
Starting Member

41 Posts

Posted - 2002-07-18 : 10:52:42

hello,

I have the ASP code as follows....

dim tCMD, tRS
set datacon = new ADOHelper
Set tRS = Server.CreateObject("adodb.Recordset")
Set tCMD = Server.CreateObject("adodb.Command")
tCMD.ActiveConnection = datacon.GetConnectionString()
tCMD.CommandText ="lesson_update"
tCMD.CommandType =adCmdStoredProc
tCMD.Parameters.Append tCMD.CreateParameter("lesson_id", adInteger, adParamInput, 4, lesson_id)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_title", advarchar, adParamInput, 200, lesson_title)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_div", adInteger, adParamInput, 4, lesson_div)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_proj", adInteger, adParamInput, 4, lesson_proj)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_location", adInteger, adParamInput, 4, lesson_location)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_phase", adInteger, adParamInput, 4, lesson_phase)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_dept", adInteger, adParamInput, 4, lesson_dept)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_date", addbdate, adParamInput, 8, lesson_date)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_desc", advarchar, adParamInput, 2000, lesson_desc)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_actions", adChar, adParamInput, 40000, lesson_actions)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_learnt", adChar, adParamInput, 40000, lesson_learnt)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_approved", adBoolean, adParamInput, 1, lesson_approved)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_complete", adBoolean, adParamInput, 1, lesson_complete)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_image_filename", advarchar, adParamInput, 100, lesson_image_filename)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_image_skip", adInteger, adParamInput, 4, lesson_image_skip)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_author", advarchar, adParamInput, 200, lesson_author)

set tRS =tCMD.Execute

And Stored procedure as

CREATE PROCEDURE [lesson_update]
(
@lesson_id int,
@lesson_title varchar(200),
@lesson_div int,
@lesson_proj int,
@lesson_location int,
@lesson_phase int,
@lesson_dept int,
@lesson_date datetime,
@lesson_desc varchar(2000),
@lesson_actions as text,
@lesson_learnt as text,
@lesson_approved bit,
@lesson_complete bit,
@lesson_image_filename varchar(100),
@lesson_author varchar(200) ='UNOSCEL',
@lesson_image_skip int
)
AS
SET NOCOUNT ON
IF @lesson_image_skip =1

BEGIN
UPDATE lessons SET division_no =@lesson_div, project_no =@lesson_proj, country_no =@lesson_location, phase_no =@lesson_phase, department_no =@lesson_dept, date_time =@lesson_date, lesson_title =@lesson_title, lesson_description =@lesson_desc, actions_taken =@lesson_actions, lesson_learnt =@lesson_learnt,
approved =@lesson_approved, complete =@lesson_complete, author =@lesson_author
WHERE lesson_no =@lesson_id
END
ELSE

BEGIN
--This means the record isn't in there already, let's go ahead and add it
SELECT 'Record added'
UPDATE lessons SET division_no =@lesson_div, project_no =@lesson_proj, country_no =@lesson_location, phase_no =@lesson_phase, department_no =@lesson_dept, date_time =@lesson_date, lesson_title =@lesson_title, lesson_description =@lesson_desc, actions_taken =@lesson_actions, lesson_learnt =@lesson_learnt,
approved =@lesson_approved, complete =@lesson_complete, image_name =@lesson_image_filename, author =@lesson_author
WHERE lesson_no =@lesson_id
END

SET NOCOUNT OFF
GO

whenever i execute this query, it stops at tCMD.execute with the error message 

"Error converting data type varchar to int."

Even i tried with adLongVarchar, but then it stops at 
tCMD.Parameters.Append tCMD.CreateParameter("lesson_actions", adChar, adParamInput, 40000, lesson_actions)
 
with the error message 
"Parameter object is improperly defined. Inconsistent or incomplete information was provided."
 
Could anyone help me to sort out this problem...?

Many thanks in advance
thiru

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 11:01:24

You can't use adChar with a length of 40,000. Either change the length to 8000 or less or use adLongVarchar.

thiruna
Starting Member

41 Posts

Posted - 2002-07-18 : 11:22:10

Even i tried using adLongVarChar but in vain....

thanks
thiru

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 11:29:51

You know what...I think it's because you're passing two text/long varchar values. I don't think ADO can handle it. Try passing only one value and see if it works. If that's the case you'll need to modify this procedure accordingly.

BTW, do you absolutely need to pass both of them as text? If one of the values is always less than 8000 characters, you can pass it as varchar from ADO...you can still keep the table structure as text columns, just change the data type for the stored procedure.

thiruna
Starting Member

41 Posts

Posted - 2002-07-18 : 11:41:35

I have tried changing one as adLongVarChar and other as adVarchar but getting error message "Parameter object is improperly defined " and stops at the line where adLongVarChar is defined....

If i use adVarChar in both fields, then i stop at tCMD.exceute with the error..."erro converting Varchar to int..."

I dont know what to do and still trying with different options....

Thanks
thiru

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 11:53:40

Some other things to try:

1. Include the "@" in the variable name in your ADO code. I don't think it should matter but you've got nothing to lose.

2. Make sure that you create each ADO parameter in the same order as the stored procedure has them. Right now one or two of the parameters are out of sequence. Again, it shouldn't matter but you never know.

3. What version of MDAC are you running? If it's less than 2.5, definitely upgrade to at least 2.6. You can get the package here:

http://www.microsoft.com/data/download.htm

Look for 2.6 or 2.7, either the RTM packages or SP1 or SP2 versions. Download and install, and restart your computer afterwards even if it doesn't prompt you. Also try applying these to the SQL Server itself.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-07-18 : 12:16:19

You could also try using the 'Refresh' method of the command object to print out all the datatypes and check them to make sure you've got the right ones;

 objComm.CommandText = "lesson_update"
objComm.CommandType = adCmdStoredProc


    objComm.Parameters.Refresh
   %>
   <Table Border=1>
   <TR>
      <TD><B>PARAMETER NAME</B></TD>
      <TD><B>DATA-TYPE</B></TD>
      <TD><B>DIRECTION</B></TD>
      <TD><B>DATA-SIZE</B></TD>
   </TR>
   <% For Each param In objComm.Parameters %>
   <TR>
      <TD><%= param.name %></TD>
      <TD><%= param.type %></TD>
      <TD><%= param.direction %></TD>
      <TD><%= param.size %></TD>
   </TR>
   <%
    Next

   %>
   </TABLE>

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-07-18 : 12:28:30

Robvolk is dead-on on this:

quote:

2. Make sure that you create each ADO parameter in the same order as the stored procedure has them. Right now one or two of the parameters are out of sequence. Again, it shouldn't matter but you never know.

The order you create your ADO parameters DOES matter.

The last two ADO parameters you create are:
tCMD.Parameters.Append tCMD.CreateParameter("lesson_image_skip", adInteger, adParamInput, 4, lesson_image_skip)
tCMD.Parameters.Append tCMD.CreateParameter("lesson_author", advarchar, adParamInput, 200, lesson_author)

and the last two SP parameters are:
@lesson_author varchar(200) ='UNOSCEL',
@lesson_image_skip int

When your ASP page passes "lesson_image_skip" it is converted to a varchar (either by ASP handling the datatype as a variant or automatically by SQL Server).

The problem occurs with the last parameter: you're ASP code is passing a varchar and the SP expects an int. Thus you get the error message: "Error converting data type varchar to int."

thiruna
Starting Member

41 Posts

Posted - 2002-07-19 : 00:39:08

Muffinman,

you are absolutely right....Yesterday whole night i was working by passing values to stored procedure with different options and finally got through when i replaced parameter order which matches the variables declared in the stored procedure....

Thanks for all your help....
I really appreciate the SQL team for setting up this discussion forum...

I am sure that i will post a message to you all to view my project once it gets completed....

Many thanks
thiru

thiruna
Starting Member

41 Posts

Posted - 2002-07-19 : 00:42:49

Also i need to say everyone that i have used only the
adchar with 20000bytes instead of adLongvarchar.

when i tried to replace adchar with adLongvarchar, it stops with the error message "paarameter is invalid...."

Thanks
thiru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources