A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005

 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).

A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin":
System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".
System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
   at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas)
.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[insertlogin].

 ***************************************************************

all i am trying to do is :  creating a SP in VS using managed code and then trying to execute it. But every time i get the above error.

 If you can tell me how to edit connection string in this that would be very helpful. At present i am using :   Using conn As New SqlConnection("context connection=true")

 

I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""

but i get this error

 

""  Msg 10327, Level 14, State 1, Line 1
ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem."""

 *********************************************************************

Plz help 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/16/2007 2:14:17 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
2381 Views

Similar Articles

[PageSpeed] 16

It looks like you didn't declare the parameter @log in your proc.

CREATE PROCEDURE insertLogin(
            @log NVARCHAR(256)
)
........................


If everything happens for a reason what is the reason for this error?
0
mpaterson
8/16/2007 9:37:06 AM

 Public Shared Sub insertlogin(ByVal log As SqlString, ByVal pas As SqlString)
        ' Add your code here
        Using conn As New SqlConnection("context connection=true")
            Dim insertlogdet As New SqlCommand()
            insertlogdet.CommandText = "insert into login (loginid,password) values(@Log,@Pas)"
            Dim logval As New SqlParameter("@Log", SqlDbType.VarChar, 50)
            Dim pasval As New SqlParameter("@Pas", SqlDbType.VarChar, 50)

            logval.Value = log
            pasval.Value = pas


            insertlogdet.Parameters.Add(logval)
            insertlogdet.Parameters.Add(pasval)

            insertlogdet.Connection = conn
            conn.Open()
            insertlogdet.ExecuteNonQuery()
            conn.Close()

        End Using
    End Sub

 

 

this is what i did in my project and i got the SP created in server explorer

 

I get the SP in server explorer but when i execute it ask for the values. After i enter values it gives the above error..

 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/16/2007 3:35:24 PM

Try changing

insertlogdet.Parameters.Add(logval)
insertlogdet.Parameters.Add(pasval)

To

insertlogdet.Parameters.AddWithValue("@log",logval)
insertlogdet.Parameters.AddWithValue("@pas",pasval)


Dave Yancey
My Blog
0
Diamsorn
8/16/2007 3:56:24 PM

 

This didnt worked. Now the code is as follows.

 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub insertlogin(ByVal log As SqlString, ByVal pas As SqlString)
        ' Add your code here
        Using conn As New SqlConnection("context connection=true")
            Dim insertlogdet As New SqlCommand()
            insertlogdet.CommandText = "insert into login (loginid,password) values(@Log,@Pas)"
            Dim logval As New SqlParameter("@Log", SqlDbType.VarChar, 50)
            Dim pasval As New SqlParameter("@Pas", SqlDbType.VarChar, 50)

            logval.Value = log
            pasval.Value = pas


            insertlogdet.Parameters.AddWithValue("@Log", logval)
            insertlogdet.Parameters.AddWithValue("@Pas", pasval)

            insertlogdet.Connection = conn
            conn.Open()
            insertlogdet.ExecuteNonQuery()
            conn.Close()

        End Using
    End Sub
End Class

 

But i am still getting the first error.

 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/16/2007 10:53:56 PM

Give this a shot.  Basically what we had going on was we were setting the parameters twice, but yet the value wasn't being passed in. 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub insertlogin(ByVal log As SqlString, ByVal pas As SqlString)
        ' Add your code here
        Using conn As New SqlConnection("context connection=true")
            Dim insertlogdet As New SqlCommand()
            insertlogdet.CommandText = "insert into login (loginid,password) values(@Log,@Pas)"
            insertlogdet.Parameters.AddWithValue("@Log", log)
            insertlogdet.Parameters.AddWithValue("@Pas", pas)

            insertlogdet.Connection = conn
            conn.Open()
            insertlogdet.ExecuteNonQuery()
            conn.Close()

        End Using
    End Sub
End Class

 
Dave Yancey
My Blog
0
Diamsorn
8/17/2007 12:59:45 AM

 

This didn't work as well.

 however i am writing this in test.sql under test Scripts folder

exec insertlogin
 select * from login where loginid="salman"

 
And then i select BUILD <project name> from build menu

 
It shows succeeded. Then when i execute the SP from server explorer its prompt for the VALUES after that it give the first error. STILL THE SAME ERROR

 

Is there anything that i nedd to make sure with SQL Server 2005. I have done

sp_configure 'clr enabled', 1
reconfigure
GO

 

 

plz help 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/17/2007 11:31:28 AM

can you post your code for us as it is now.  I'm not understanding why its asking you for values if you are using the AddWithValue method for adding your parameters.


Dave Yancey
My Blog
0
Diamsorn
8/17/2007 12:47:36 PM

SOLUTION EXPLORER

THIS CODE ID FOR insertlogin.vb :: 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub insertlogin(ByVal log As SqlString, ByVal pas As SqlString)
        ' Add your code here
        Using conn As New SqlConnection("context connection=true")
            Dim insertlogdet As New SqlCommand()
            insertlogdet.CommandText = "insert into login (loginid,password) values(@Log,@Pas)"



            insertlogdet.Parameters.AddWithValue("@Log", log)
            insertlogdet.Parameters.AddWithValue("@Pas", pas)

            'insertlogdet.Connection = conn
            'conn.Open()
            'insertlogdet.ExecuteNonQuery()
            'conn.Close()
            SqlContext.Pipe.ExecuteAndSend(insertlogdet)

        End Using
    End Sub
End Class

 

------------------------------------------------------------------------------------------------------------------------- 

 

This CODE is for TEST.SQL

 

-- Stored procedure
-----------------------------------------------------------------------------------------
 exec insertlogin
 select * from login where loginid="salman"


--------------------------------------------------------------------------------------------------------------------------------------------------

 

THIS IS CONNECTION STRING FOR PROJECT

 

Data Source=H\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True  

with PERMISSION LEVEL :: SAFE 

 

 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/17/2007 7:16:54 PM

so what error are you getting now?

is it still asking you to supply the parameter?


Dave Yancey
My Blog
0
Diamsorn
8/17/2007 8:21:24 PM
Dear  Dave,
 
Thanks for your help. It was very helpful. The changes you made to the code, was fine. I used the code below that was given by you. And deployed the project and its working fine.
 
Thank you so much  for your help dear. Have a rocking weekend.
 
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub insertlogin(ByVal log As SqlString, ByVal pas As SqlString)
' Add your code here
Using conn As New SqlConnection("context connection=true")
Dim insertlogdet As New SqlCommand()
insertlogdet.CommandText = "insert into login (loginid,password) values(@Log,@Pas)"
insertlogdet.Parameters.AddWithValue("@Log", log)
insertlogdet.Parameters.AddWithValue("@Pas", pas)

insertlogdet.Connection = conn
conn.Open()
insertlogdet.ExecuteNonQuery()
conn.Close()

End Using
End Sub
End Class


 


Hitinder Bawani

MCTS - WEB
ASP.NET
0
hitinderb4u
8/17/2007 10:50:52 PM
Reply:

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Crystal for VS.NET 2005 and logon on SQL SERVER 2005
Hello everybody,   I need to interface a webapplication with a crystal report on a SQL server 2005 database I'm using the reportdocument class to create my report which I then map to the Crystalreportviewer. In my code i've a method which sets the logon information for all the tables used in the report. This is the code: private void SetLogonDetailsForReport(ref ReportDocument repDoc) { int i; for (i = 0; i <= repDoc.Database.Tables.Count - 1; i++) { TableLogOnInfo logOnInfos = new TableLogOnInfo(); logOnInfos.ConnectionInfo.ServerName =  <<server address>&g...

Using VS.net 2005 and SQL Server 2005 for real projects?
We are working on the design of our new web site, which we initially targeted for ASP.NET 1.1.  We also planned on using SQL Server Reporting Services from SQL Server 2000.  However, I have been reading about ASP.NET 2.0, as well as vs.net 2005 and SQL Server 2005, including the fact that they are planned for release in Novemeber.  My questions is - should we consider using the beta products for our project, with the thought that they are relatively stable and production versions will be out in a few months?  I do see a number of attractive features in these new releases ...

SQL Server 2005 CTP June and VS .NET 2005 Rc1?
Is SQL Server 2005 CTP June is compatible with VS .NET 2005 RC 1?Thanks for your helpRegardsDavid No I think the SQL Server 2005 June CTP uses the Visual Studio 2005 July CTP while the SQL Server 2005 September CTP uses Visual Studio 2005 September CTP.  Try the link below and use the drop down list to find your CTP compatiblity and download the SQL Server 2005 September CTP if you need it.  Hope this helps.http://channel9.msdn.com/ctpmadness/Default.aspxhttp://www.microsoft.com/sql/2005/productinfo/ctp.mspxKind regards,Gift Peddie I've just downloaded CTP September and it work...

I cannot stop Visual Studio.NET 2005 from looking for SQL Server 2005 instead of SQL 2000
I would like to know how to set SQL2000 as the default database server when running any webapplication. I have installed Visual Studio.NET and SQL Server 2000 locally in my laptop.  But anytime I want to run a webapplication I am getting the following error:System.Data.SqlClient.SqlException was unhandled by user code  Class=20  ErrorCode=-2146232060  LineNumber=0  Message="An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server doe...

VS.NET 2005 beta 1 and SQL Server 2005 beta 3
Hello, I can't get Visual Studio .NET 2005 beta 1 and SQL Server 2005 beta 3 without there being issues between the two. They both require their own version of the .NET framework 2 beta, and then the other problem doesn't work correctly. Should I install VS.NET 2005 and then install a later build? Where can I get the newest build? BrianBrian"Trust in the Lord and do what is good; dwell in the land and live securely. Take delight in the Lord, and He will give you your heart's desires" (Psalm 37: 3-4). If you're talking about SqlServer 2005 Beta2, then yse they can wo...

Web admin tool in vs.net 2005 in combination with SQL server 2005
Hi,a couple of weeks I became the proud owner of the Beta experience DVD's filled with a lot of stuf. I got a new laptop from my new employer and wanted to try out the new stuff because we're working already with the beta 2 pieces on our current assignment (winforms 2.0 in that case :-) ).I installed SQL server 2005 that shipped and after that vs.net 2005 without the SQL server 2005 Express edition. I created a new website and wanted to try out the new administration tool for setting up everything for my security because I wanted to play with the new login controls.It gave me some timeout er...

VS.NET 2005 July CTP and SQL Server 2005 Deverloper Edition
The latest version of SQL Server 2005 Developer Edition is the June CTP.  I wanted to install the July CTP of VS.NET 2005 Pro because of the changes but it doesn't like the June CTP of SQL Server.  Currently on my box i'm just using the Management Studio of SQL Server and not the database engine itself.  Anyone know if they are going to release the July CTP version of SQL Server?  Will it be in the next Code Drop for the July CTP of VS.NET?  Anyone know when that will be?...

Installing .NET Framework, Sql Server 2005 Express, and Visual Studio 2005 Professional
Will someone please offer some advice, a walkthrough, or something to help me through this?  I installed all three of these pieces (.NET, SQL Server 2005 Express, and VS 2005 Professional) onto a WinXP box.  I was unable to get it to work.  It kept throwing SQL errors when I tried to run the security setup for the Personal Website Starter Kit.  So, I uninstalled everything except .NET and reinstalled.  At this point I was able to get to the setup, but unable to add users.  It doesn't throw errors or anything, it just doesn't do anything.  I really re...

Creating a .NET Stored Procedure in Sql Server 2005 Express Edition
Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer?  Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure? Regards... Shashi Kumar Nagulakonda.  Shashi Kumar NagulakondaXinthe Technologies I can't help you with the first question. The answer to the second question is obviously it depends.  As far as I know (and I know very little about .NET sps), given the same task, the T-SQL...

How to design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications
Hi,   Can we design SQL Server 2005 Reporting Services Reports in VS.NET 2005 web applications. If so how they can be designed. Plz help me if any one know the solution. Thanx in advance, Vidya     Are you asking if you can use VS.Net 2005 to design reports, yes, both client and server reports are built using VS.Net. If you are asking if you can use a web application to build a report, well yes you could, but you would have you design it.  All sql server reports are is an xml file, so if you can build a front end that will generate the xml in the proper ...

Visual Basic .NET 2005 Express & Sql Server 2005 Express remote server
Hi,  I want to make a component library in Visual Basic.NET and connect to a remote Sql Server. When I create a new DataSet with the wizard, I can only connect to a .mdf file, but not to a Sql Server. With Visual Web Developer I can connect to a Sql server. What is the difference between these enviroments ? How can I do the same with Visual Basic.NET ?  Thanks in advance. Visual Basic .NET Express cannot connect to SQL Servers. The Visual Web Developer is the only express edition that can utilize SQL Server. You need to upgrade to Visual Studio 2005 Standard or higher.Cheers,&n...

VS.NET 2005 vs VS.NET 2003
I have used VS.NET 2003 extensively and exploited all features like drag and drop functions for creating components, drag and drop data access components...For example VS.NET 2003 allows creating sqldataadapter for table mapping , stored proc parameters mappings etc..We can drag pretty much any object on component designer....Not it seems like all that gone...VS.NET 2005, there is no way we can drag the typed dataset, sqldata adapter, sqlcommand on forms designer . There is no concept of component. There is no component designer any more....Biggest flaw i found is sqldatasource and...

VS.NET 2003 to VS.Net 2005
Hi   I just converetd my VS.Net 2003 project to VS.NET 2005 using the convert wizard in Visual Studio 2005 Web Application Project. It compiled with no build errors and a few warnings. When Iam trying to view the project, then it gives me 404 error. Can someone help ? Thanks Could you confirm you are using WAP (Web Application Projects) versus the built-in WSP (Web Site model)? If you have a project file after converting, then you are using WAP. Could you post the actual 404 error you got? Also, did you set your start page in the converted project?  -Mike-...

Web resources about - A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005 - asp.net.sql-datasource

Why aren’t user-defined operators more common?
... by technophiles and answered by users at Stack Exchange , a free, community-powered network of 100+ Q&A sites . Where have all the user-defined ...

Breaking the SQL Barrier: Google BigQuery User-Defined Functions
... analytics service that uses SQL as its query interface. As part of our BigQuery 1.8 launch , we are announcing support for executing user-defined ...

Compiler Warnings: Calling the User-Defined Default Constructor from Objective-C++ Code
Interoperating between Objective-C and C++ code requires careful work as constructor calls vary, depending on which compiler you use.

AT&T User-Defined Network Cloud Reportedly Going Live in June
While regulations aren't yet in place for non-copper networks, AT&T's all-IP network will begin offering some services as soon as June, says ...

AT&T expands its user-defined network cloud program by adding Alcatel-Lucent and Fujitsu
... announces Alcatel-Lucent (Euronext Paris and NYSE: ALU) and Fujitsu Network Communications Inc. as the newest vendors to join its User-Defined ...

AT&T Expands Its User-Defined Network Cloud Program By Adding Alcatel-Lucent and Fujitsu
... announces Alcatel-Lucent (Euronext Paris and NYSE: ALU) and Fujitsu Network Communications Inc. as the newest vendors to join its User-Defined ...

Patent US6266674 - Random access information retrieval utilizing user-defined labels - Google Patents ...
Method and apparatus for storing information wherein a user defines labels which relate to the stored information and the user defines a data ...

News: PCalc adds user-defined constants with iCloud sync
TLA Systems has released an update to PCalc for iOS adding support for user-defined constants and new functions from the Mac OS X version. PCalc ...

Add user-defined constants with PCalc RPN Calculator for iPhone and iPad
... to hit the App Store and continues to be one of the leading calculators on the market. It was recently updated to introduce a huge user-requested ...

AT&T Adds Amdocs And Juniper To Its "User-Defined Network Cloud" Supplier Program
DALLAS, April 22, 2014 /PRNewswire/ AT&T Adds Amdocs And Juniper To Its 'User-Defined Network Cloud' Supplier Program.

Resources last updated: 1/13/2016 3:59:52 AM