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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
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 |
![]() |
so what error are you getting now?
is it still asking you to supply the parameter?
Dave Yancey
My Blog
![]() |
0 |
![]() |
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 |
![]() |