Inserting Request.Querystring Into The Stored Procedure
Feb 2, 2008
Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure;
I have 2 pages. ( i want to pass information from a text box to the "certificate.aspx" database query) page 1 certsearch.aspx this is my script i have a label, commnad button, & textbox If txtSearchCert.Text = "" Then lblMsg.Text = "Please enter a certificate #" Else Response.Redirect("certificate.aspx" & txtSearchCert.Text) End If
page 2 certificate.aspx i am not sure what goes here.this is what i am trying Request.QueryString = (txtSearchCert.text) This is my database query on certificate.aspx page <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacsConn %>" SelectCommand="SELECT * FROM [SummaryBlue] WHERE REPORTNUMBER = ?"></asp:SqlDataSource>
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.
I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.
The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.
I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST] @URL NCHAR(255), @CONTENT NCHAR(1024), @Result bit OUTPUT AS BEGIN /***** Call Function to POST *****/ set @Result = NULL EXECUTE @Result = [dbo].[udfHttpPost] @URL, @Content END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost] (@Url [nchar](255), @Content [nchar](1024)) RETURNS [bit] WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System; using System.Configuration; using System.Collections.Generic; using System.Text; using System.Net; using System.Security; using System.Security.Permissions; using System.IO; using Microsoft.SqlServer.Server;
namespace SqlServices { public class HttpServices { [SqlFunction(DataAccess = DataAccessKind.None)] public static bool HttpPost(string Url, string Content) { Url = Url.TrimEnd(); Content = Content.TrimEnd(); string rawOutput = "";
//Get Access Right to Web WebPermission p = new WebPermission(NetworkAccess.Connect,Url); p.Assert();
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream()); PostWriter.Write(Content); PostWriter.Close();
WebResponse Resp = Req.GetResponse(); StreamReader sr = new StreamReader(Resp.GetResponseStream()); rawOutput = sr.ReadToEnd(); sr.Close(); return true; } } }
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost': System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create) at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark) at System.Security.CodeAccessPermission.Assert() at SqlServices.HttpServices.HttpPost(String Url, String Content)
Hello everyone, I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance Errors 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' protected void Button1_Click(object sender, EventArgs e) { /* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */ string Interview1 = TextBox1.Text; string Interview2 = TextBox2.Text;
//Create Sql connection variable that call the connection string SqlConnection SqlConnection = new SqlConnection(GetConnectionString());
//Create a sql command to excute SQL statement against SQL server SqlCommand Command = new SqlCommand();
// Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure;
//Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand = "Summaries";
//open the command connection with the connection string Command.Connection = SqlConnection;
// Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3);
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table. When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure. However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work. Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails... here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
=== Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans cmd.CommandText = "usp_InserOrder" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int) cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit) cmd.Parameters("@MaterialID").Value = 3 cmd.ExecuteNonQuery() trans.Commit() =====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool.
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are: tbltest
ALTER PROCEDURE AddListAndReturnNewIDValue ( @EditorId int,@CategoryID int, @ListTitle nvarchar(50),@Blurb nvarchar(250), @FileName nvarchar(50),@ByLine nvarchar(50), @HTMLCopy nvarchar(MAX),@MainStory bit, @MainStoryImageFile nvarchar(50),@Publish bit, @PublishDate smalldatetime, @ListId int OUTPUT ) AS -- Insert the record into the database INSERT INTO shortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate) VALUES (@EditorID,@CategoryID,@ListTitle,@Blurb, @FileName, @ByLine, @HTMLCopy, @MainStory, @MainStoryImageFile,@Publish,@PublishDate) -- Read the just-inserted ProductID into @NewProductID SET @ListId = SCOPE_IDENTITY()
here is the sqlDataSource <asp:SqlDataSource id="srcShortList" ConnectionString="<%$ ConnectionStrings:ShortList %>" SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist" InsertCommand="AddProductAndReturnNewProductIDValue"SelectCommandType="StoredProcedure" UpdateCommand="UPDATE shortlist SET CategoryID=@CategoryID,ListTitle=@ListTitle,Blurb=@Blurb,ByLine=@ByLine,HTMLCopy=@HTMLCopy,MainStory=@MainStory,Publish=@Publish,PublishDate=@PublishDate WHERE Id=@Id" Runat="server" > <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </SelectParameters>
Hi iam Prameela, I want to select some dynamic values from a table and store them to another table. Let me give u an example,its like: I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like: Surevy Answers Table: UID QID Option1 Option2 Survey Name---------These are the fields 1 1 1 0 Articles 1 2 0 1 Articles 2 1 1 0 Articles 2 2 0 1 articles I need to add all these Options of particular QID and store them in Survey Count table,like QID Opt1Cnt Opt2Cnt Survey Name 1 2 0 Articles 2 0 2 Articles When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased So this count should be modified in Survey Count Table,like: If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as: QID Opt1Cnt Opt2Cnt Survey Name 1 3 0 Articles 2 1 2 Articles I need a Stored Procedure for this. Please help me with this query.
Hi, I have a stored procedure. In the stored procedure, I have a table called "#temp", this table contains 50 rows that I select from other tables. Now I want to insert a row before the first row. How can I do it in SQL Server 2005? Thanks in advance.
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:
Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
I have a web app that calculates tax filing status and then stores data about the person.
Facts The insert is done through a stored procedure. All the sites that this program is being used are connecting through a VPN so this is not an external site. The duplicate records are coming from multiple sites (I am capturing there IP address). I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I cant seem to make this sql work..... SELECT UserId, UserFirstName, UserLastName, UserPhone, UserName, UserEmail FROM UserTable WHERE (@SearchCat = @SearchText) This works fine (Where UserName = @SearchText) I need the "UserName" field to beable to be; UserID, UserPhone, Etc, not all at once just based on a list box My trouble lies in the @SearchCat... I need to be able to choose which column im searching in. I have 1 list box with coulmn names in it and a text box that holds the text that is being searched for. Any help would be awsome... Thanks Neil
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
