Error in SP using Dreamweaver

Hi,
I am calling a stored procedure from DreamWeaver, but I am getting an error,
perhaps someone can help (It has to do with the last field (text), if I use
nvarchar instead works fine.
SP:
CREATE procedure Addtemplatestocase
@.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
@.LtrBody text
as
INSERT
INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
GO
Error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Any help is appreciated.
AleksI use Dreamweaver all the time and use the integrated environment. Are you
using DW to try to create an SP? or calling an update sp? I would not
recomend this.
for this question:you seem to be attempting to insert a value that is larger
than the field size.
thanks
kes
"Aleks" wrote:

> Hi,
> I am calling a stored procedure from DreamWeaver, but I am getting an erro
r,
> perhaps someone can help (It has to do with the last field (text), if I us
e
> nvarchar instead works fine.
> SP:
> CREATE procedure Addtemplatestocase
> @.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
> @.LtrBody text
> as
> INSERT
> INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
> VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
> GO
>
> --
> Error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Any help is appreciated.
> Aleks
>
>|||I know.
The stored procedure is in the Database (MS SQL 2000) I am using DW to call
the SP and pass on the paramters.
I am copying from a text field to a text field, so this should not happen. I
don't know what I am doing wrong.
Aleks
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>I use Dreamweaver all the time and use the integrated environment. Are you
> using DW to try to create an SP? or calling an update sp? I would not
> recomend this.
> for this question:you seem to be attempting to insert a value that is
> larger
> than the field size.
> thanks
> kes
> "Aleks" wrote:
>|||ok, let's try and break it down.
1. can you do the same operation out side of dream weaver?
"Aleks" wrote:

> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>|||sorry this this posted back auto...
1. try it out of deamweaver
a. try it in wisql
b. create a cfm or asp.net quick template using the sqlname space
2. if they work it may have to do with the odbc driver. I've found that the
odbc namespace does not work as well as oledb or sqldb, but you don't have a
choice with DW. This is philosophy, but vs.net does work better with
asp.net.
I'm not a MS only person, i've used DW since it was HomeSite and still do
for all my CFM stuff and some asp.net where the code was not created in
vs.net, it works ok, but does not connect to sql as well.
thanks
kes
"Aleks" wrote:

> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>

Error in printreport method of Crviewer

Hi!

I used vb6.0 and crystal Report 11 ,. I integrated the report to VB form. By calling the report in vb th viewreport method working fine . But the .printreport method have the problem " Method 'printreport' of Object 'ICrystalreportviewer11' Failed"

What is the problem and how to solve the problemMake sure that the printer is properly connected to the system

Error in Calling Stored Procedure

I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.

However, when I try to run this in the ASP.Net application, error occured.

This error message is {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

Calling the SP via a Class

1Public Function ExecuteStoredProcedure(ByVal NameAs String,ByVal Para()As String,ByVal Value()As String)2If Para.Length <> Value.LengthThen Exit Function3 SqlCmd =New SqlCommand4 SqlCmd.Connection = SqlConn5 SqlCmd.CommandText = Name6 SqlCmd.CommandType = CommandType.StoredProcedure7For iAs Integer = 0To Para.Length - 18'SqlCmd.Parameters.AddWithValue(Para(i), Value(i))9Dim pAs New SqlParameter(Para(i), SqlDbType.NVarChar)10 p.Direction = ParameterDirection.Input11 p.Value = Value(i)12 SqlCmd.Parameters.Add(p)13Next14 SqlCmd.ExecuteNonQuery()15End Function

The Code behind file

1Protected Sub btnSubmit_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnSubmit.Click2If txtFromDate.Text =""OrElse txtToDate.Text =""Then3 trMsg.Visible =True4 Exit Sub5 End If67 Dim tsAs New Thread(AddressOf GenerateReport)8 ts.Start()910 MultiView1.ActiveViewIndex = 111 lblConfirmationMessage.Text ="Report generating in progress." & vbNewLine &"You can continue using other functions."12End Sub1314 Protected Sub GenerateReport()15Dim userNameAs String =CType(Session("User"), clsUser).UserName16Dim fromAs String = txtFromDate.Text1718Dim connAs New clsConnector19 conn.OpenConnection()20'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';")21 conn.ExecuteStoredProcedure("gen_report", _22New String() {"@.name","@.remark","@.by","@.fromdate","@.todate"}, _23New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text})24 conn.CloseConnection()25End Sub26

Thanks!

Are u saving Date as string into a column with data type smalldatetime. In your code it seams that you r passing date value . Can u check this one.

|||

try to set timeout to bigger value

SqlCmd =New SqlCommand
4 SqlCmd.Connection = SqlConn
5 SqlCmd.CommandText = Name
sqlcmd.CommandTimeout=300

and maybe it will help, how long it take to create results for this procedure in Query analyzer? your timeout should be a little longer than this time.

|||

The data type is "datetime".

And I use the Now.ToString("yyyy-MM-dd HH:mm:ss") for passing the value.

|||So definitely it won't work. Try to pass it as a date object into the database. Moreover if your conversion of date in different format other than the one defined in db wil create error even if you pass data as dateobject. So try to pass data into DB in the relevent datatype itself|||

Actually, all the input parameter in the SP is nvarchar, but in different size. So, I use String in the code-behind file.

I also tried to set the connection timeout to 300, but the error message is still the same as the original one.

|||

did you tried to run the sp directly in query analyzer by passing the parameters you are passing from UI?

thanks,

satish.

|||

I've just tried to run the SP in the query analyzer and in the UI using the same set of parameters. It works in the query analyzer but not in the UI.

Also, the error is not due to the Thread. Because I tried to run the GenerateReport() without using the Thread and the same error occured.

One more point to mentioned is that I've set the timeout period to 300 seconds in the connection string, however, the UI show me that error message within 1 minute. Is that really timeout?

Thanks

|||

Hello,

Most likely, the Sql Connection problem to me.

Have you tried to debug step by step?

I don't feel good with this line on your function:

SqlCmd.Connection = SqlConn

Why don't you get the all the code from other class into your calling page, and debug it?

Just opinion!

|||

here i write some sample of stored procedure

i think this will help u

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
con.Open()

Dim DA As New SqlDataAdapter("select acceptqty,itemid from grndetail where grnid='" & Trim(ggrnid) & "'", con)
Dim ds As New DataSet
DA.Fill(ds)

Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1 Step 1

Dim cmd1 As New SqlCommand
cmd1.CommandText = "aaceptqtygrn"//name of stored procedure
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Connection = con
cmd1.Parameters.AddWithValue("@.itemId", ds.Tables(0).Rows(i).Item("itemid"))
cmd1.Parameters.AddWithValue("@.acceptqty", ds.Tables(0).Rows(i).Item("acceptqty"))
cmd1.ExecuteNonQuery()
Next

con.Close()

|||

Hi yipych,

I have tried your code and its working for me. I think its the problem with your connection string or some other connection related issues. I have placed the sample code here.

http://www.igold.in/forum/samples/WebVb100.zip

Let me know if it helps you

Error handling when calling external sql-Files

I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaulHi

Look at using the -b flag when you call osql, there is more information in
Books Online.

John

<lvpaul@.gmx.net> wrote in message
news:1128067412.114048.12840@.g44g2000cwa.googlegro ups.com...
I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaul|||Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul|||Hi

You may want to try using EXEC for your select statement

declare @.errorcode int
EXEC ('select * from abc')
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

John

<lvpaul@.gmx.net> wrote in message
news:1128491173.083475.231000@.o13g2000cwo.googlegr oups.com...
Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul