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

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?

 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

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

 


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

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

 


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

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

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

so what error are you getting now?
is it still asking you to supply the parameter?
Dave Yancey
My Blog

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