DataSource Controls :: Run Stored Procedure Using Linq?
Feb 12, 2010
Is it posible to run a stored procedure using linq? I dont mean one that returns a table i just mean one that runs a set of transactions exec sp_MyProc @Var or something?
I am having problems returning a string value through a stored procedure/linq to a label. Everything works fine if I return an integer value but I cant get a string value to bind to a label. I get the error message
Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) +201 Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +66 [InvalidCastException: String "System.Data.Linq.SqlClient.SqlPr" to 'Integer' is invalid‚]
This is the stored procedure ALTER PROCEDURE test AS Select ProductName From Products
This is the vb page Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim db As New DataClassesDataContext Dim product As Integer = db.test.ToString Label1.Text = product End Sub
This is the aspx page <form id="form1" runat="server"> <asp:Label ID="Label1" runat="server" Text=""></asp:Label> </form>
I started learning LINQ, So i thought of just creating gridview with sorting and paging using objectdatasource using LINQ. So i got this link
[URL] which Brian Orrel tell us how to do using IQueryable Interface. but i have nearly 300 lines of stored procedure which joins nearly 20 tables. so i thought of creating a stored procedure and using that instead of IQuerytable.
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
I am trying to re-write an existing asp.net website using MVC with Linq to SQL. A page we have is used to insert new contracts and uses usp_contract_insert to insert a new contract into t_contract as well as t_project. On my dbml file i have dragged t_contract on and then I try to drag usp_contract_insert on top of that and I get the error :- Microsoft Visual Studio
One or more selected database objects return a schema that does not match the schema of the target data class. Nothing has been added to the designer. The stored procedure returns the id of the last project which was inserted. I don't quite follow this and unsure how to go about hooking my Controller up using Linq to SQL to call this stored procedure
I am using LINQ to SQL for inserting, updating my DB now i am using stored procedures to do the same,but i dint find any advantages or less code in using stored proc what is the main diff between using stored proc and not using stored proc?
I created a database on SQL Server 2005 developer edition with MS SQL Server Mangement Studio Express. Now I created code that accessess a stored procedure named 'pS_TableData'. However I have one problem the code raises an exception "Could not find stored procedure 'pS_TableData 'Letters''."the code looks like this:
[Code]....
Note I am using (for now) the same connection that I used to create my database so in essence my web site is the administrator. I tried a simple SELECT from a table with the same connection and it raised no exceptions!When I run EXECUTE ps_TableData 'Letters' in SSMS it has no . I also tried EXECUTE ps_TableData 'Letters' as the command paramated for SqlCommand but it had the same effect
I call this stored procedure with a dataset. It always returns a 0 but should return a 1 or 2. Does anybody know why it isn't returning a 1 or 2.
ALTER PROCEDURE dbo.sp_InsertLinkVote @LinkId int, @LinkVoter nvarchar(50), @LinkVotesDateTime datetime, @LinkVotesGoodBad bit AS /* SET NOCOUNT ON */   If EXISTS (SELECT * FROM LinkVotes WHERE LinkId=@LinkId and LinkVoter = @LinkVoter )RETURN 1 ELSE INSERT INTO dbo.LinkVotes (LinkId,LinkVoter,LinkVotesDateTime,LinkVotesGoodBad)VALUES (@LinkId,@LinkVoter,@LinkVotesDateTime,@LinkVotesGoodBad)RETURN 2
Here is were a call it in my code behind. Dim VoteCheck As Integer Dim InsertVote As New DataSetStoredProceduresTableAdapters.QueriesTableAdapter VoteCheck = InsertVote.sp_InsertLinkVote(LinkId, User.Identity.Name, DateAndTime. Now, True) '0 is a good vote. If VoteCheck = 0 Then
It seems to insert and not insert correctly, I just cannot get it to return the correct value.
I am developing a page in ASP.Net where I have the user enter a letter into a textbox. They then click a submit button and I all of the userids that start with that letter are to be displayed. So I need the SP to go through the database and match first letters with what was entered. Here is what I have as my code for the SP:
CREATE PROCEDURE dbo.exp2 @testchar Char(1) declare @flag as char(2) set @flag = @testchar + '%' select userid from ex_database where userid like @flag
So I'm working with SQL Server 2005 and I'm having trouble saving a stored procedure. (I've only used an Apache server before, and for nothing as complex as the project I'm working on)
Nonetheless, the problem is that I don't know where to actually save the stored procedure I create. In my Object Explorer, I go to the database I want to create it in, expand Programmability, right click on Stored Procedures and choose to create a new one. That's all nice, but when I'm ready to save and test it from my webpage, it asks where to save it. This is probably dumb of me, but I don't know where to. I try to save it on the desktop and click/drag to the Stored Procedures folder in my database, but all that does is open it, not actually store it in the database. Maybe I'm getting the point of stored procedures wrong? Let me know what I should do to save it in the right place so I can access it.
I'm trying to create a stored procedure that does two things:1st - Insert a new record in the contactus table.2nd - Gets the last primary key value from table contactus.I've created the following Stored Procedure but this is giving me an error when being executed.
I have a button which the admin will click on every new acad year to update the student's study level.
I have written a SP to do this process, however, it was working fine. But I faced problem when I did testing.
This is my student update study level stored procedure:
UPDATE Student SET acadLevel = (CASE WHEN acadLevel < '3' THEN convert(char,acadLevel + 1) ELSE convert(char,'G') end ) WHERE acadLevel between '1' and '4';
Error: Conversion failed when converting the varchar value 'G' to data type int.