How To Prevent SQl Injection When Using XSD Dataset And Store Procedure
Hi All,
I am new at this. I found using xsd with store procedure come really handy. However, I am afraid of SQl Injections. How can I tighten up my database's security??
Thanks all
Ken
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageID int, @DateAdded datetime, @ImageName nvarchar(64), @ImagePath nvarchar(64), @IsApproved bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE [syl_AvatarImages] SET [DateAdded] = @DateAdded, [ImageName] = @ImageName, [ImagePath] = @ImagePath, [IsApproved] = @IsApproved WHERE [AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
How To Prevent SQL Injection Attacks
Hi, On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg% There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable. Can anyone help, perhaps point me in the direction of furthur resources on the subject? Thanks Ben
View Replies !
What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full ------------ I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp ex: create proc ex @orden varchar(100) @criterio varchar(100) as declare consulta varchar(4000) set consulta=N'select pais from paises where '+@criterio' order by '+@orden ------------ I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)? ----- They told me this coding is not good (dynamic sql) because it can give control to the user? I ask, how does it give control to use? what ar sql injection attack and how to prevent them? I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right? can u help me?
View Replies !
Dataset Store Procedure Return Values
Hi All, I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter. Thanks in advance. Glenn
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
Prevent An Unused Dataset From Querying?
Say you have 2 datasets for 2 tables, respectively. If one table is hidden, why does the query for its dataset still execute when it's not needed anywhere else? I looked through the properties of the table, but i didn't come across anything that I think would work. Does anyone have some ideas as to how to stop the dataset from querying the database?
View Replies !
Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
View Replies !
Store A Dataset In An Image Field Using SQL
Hi, does anyone know a way, using native SQL, to store a result of a query in an image field of a certain table. The case is we have a selfmade replication to communicate with several SQL servers in stores, this replication is over a telephone line. So we collect all the data using SQL statements and store them in a separate table as an image field. This is done know through a Delphi application that streams the resultset to a image field. Thanks in advance,
View Replies !
Limit To The Number Of Rows A Dataset Can Store?
hI, I am using visual c# 2003 and sqlserver 2000 and i am trying to query a column in the sql server and store it into a dataset but i got an error msg: The number of rows for this query will output 90283 rows. -------------------------------------------------------------------------------- Query : SELECT L_ExtendedPrice, COUNT (*) AS Count FROM LINEITEM GROUP BY L_ExtendedPrice ORDER BY Count DESC"; --------------------------------------------------------------------------------- Error msg : An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. ---------------------------------------------------------------------------------- is there a limit to the number of rows a dataset can store?
View Replies !
Efficient Store Proc For Paging Very Large DataSet Using Cursor Approach
This approach I found very efficient and FAST when compared to therowcount, or Subquery Approaches.This is before the advent of a ranking function from DB such asROW_NUMBER() in SQL Server 2005 and the likes of it. SoThis one works with SQL2000What do you think?==The Generic paging Cursor Approach in Stored Procedure/*Generic Paging Routine using Cursor approach.--------------------------------------------Built to use with ASPNET custom paging. Just pass the parametersyou your query and it builds the dynamic SQL to return only therequested page.This seems to be working for me. I tried the other two pagingapproaches (1) Paging By RowCount (which has some errors on sorting)and (2) Paging by Subquery (which is too slow). This proceduregoes to show that cursors are not necessarily evil at all times.DON'T FORGET TO HANDLE SQL-INJECTION in your code!ONE CAVEAT/restriction: Primary key type is set to INT. I normallyuse identity column anyway.NOTE: This returns 2 results: 1 for dataset 1 for the total countwhich is useful when consumed by ASPNET or the like.References:http://www.thecodeproject.com/aspne...p?select=820618*/CREATE PROCEDURE uspPagingCursor (@Fields VARCHAR(1000) = '*',@Tables VARCHAR(1000) ,@PK VARCHAR(100) ,@PageSize INT,@PageNumber INT = 1,@Sort VARCHAR(1000) = '',@Filter VARCHAR(2000) = '' ,@Group VARCHAR(1000) = null)AS/*Find the @PK type*/DECLARE @PKTable varchar(100)DECLARE @PKName varchar(100)DECLARE @type varchar(100)DECLARE @prec intIF CHARINDEX('.', @PK) 0BEGINSET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))ENDELSEBEGINSET @PKTable = @TablesSET @PKName = @PKEND/*This is the part removed from orig code for speed.I know my @type is INT always.SELECT @type=t.name, @prec=c.precFROM sysobjects oJOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertypeWHERE o.name = @PKTable AND c.name = @PKNameIF CHARINDEX('char', @type) 0SET @type = @type + '(' + CAST(@prec AS varchar) + ')'*/SET @TYPE = ' int 'DECLARE @strPageSize varchar(50)DECLARE @strStartRow varchar(50)DECLARE @strFilter varchar(1000)DECLARE @strGroup varchar(1000)/*Default Sorting*/IF @Sort IS NULL OR @Sort = ''SET @Sort = @PK/*Default Page Number*/IF @PageNumber < 1SET @PageNumber = 1/*Set paging variables.*/SET @strPageSize = CAST(@PageSize AS varchar(50))SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) ASvarchar(50))/*Set filter & group variables.*/IF @Filter IS NOT NULL AND @Filter != ''SET @strFilter = ' WHERE ' + @Filter + ' 'ELSESET @strFilter = ' WHERE TRUE '/* SET @strFilter = '' */IF @Group IS NOT NULL AND @Group != ''SET @strGroup = ' GROUP BY ' + @Group + ' 'ELSESET @strGroup = ''/*Execute dynamic query*/EXEC('DECLARE @PageSize intSET @PageSize = ' + @strPageSize + 'DECLARE @PK ' + @type + 'DECLARE @tblPK TABLE (PK ' + @type + ' NOT NULL PRIMARY KEY)DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FORSELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup+ ' ORDER BY ' + @Sort + 'OPEN PagingCursorFETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PKSET NOCOUNT ONWHILE @PageSize 0 AND @@FETCH_STATUS = 0BEGININSERT @tblPK (PK) VALUES (@PK)FETCH NEXT FROM PagingCursor INTO @PKSET @PageSize = @PageSize - 1ENDCLOSE PagingCursorDEALLOCATE PagingCursorSELECT ' + @Fields + ' FROM ' + @Tables + ', @tblPK tblPK ' +@strFilter+ ' and ' + @PK + ' = tblPK.PK ' + @strGroup + ' ORDER BY '+ @Sort)EXEC('SELECT (COUNT(' +@Pk +') - 1)/' + @strPageSize + ' + 1 AS PageCountFROM ' + @tables + @strFilter)GO==Sample Stored Procedure calling the above proc uspPagingCursor/************************************************** ***********Description:This simply returns page data from tbTransactions Table.Returns:Result Set (WHICH web like ASPNET can consume )Notes:This invokes the generic paging procedure uspPagingCursor.************************************************** ***********/CREATE PROCEDURE uspGetTransactionsPage@PageSize INT,@PageIndex INT = 1,@SortField VARCHAR(1000) = '',@QueryFilter VARCHAR(2000) = ''ASDECLARE @FieldNames VARCHAR(1000)DECLARE @TableNames VARCHAR(1000)DECLARE @PrimaryKey VARCHAR(1000)DECLARE @JoinExpr VARCHAR(1000)IF @SortField = '' SET @SortField = 'SubmitDate DESC'/* an identity/unique column is needed for Paging to work */SET @PrimaryKey = 'rowid'SET @TableNames = ' tbTransaction, tbResponseCode , tbUser'/* Put your SQL SELECT Here */SET @FieldNames ='rowid,MerchantTransactionId,MerchantIdProcessor,TransactionOrigin,SubmitDate,ExpirationDate,TransactionAmount,CustomerName,AccountNumber'/* Put your SQL SELECT JOIN Here */SET @JoinExpr =' tbTransaction.ResponseCode *= tbResponseCode.ResponseCodeand tbTransaction.EmployeeId *= tbUser.userid'IF @QueryFilter = ''SET @QueryFilter = @JoinExprELSESET @QueryFilter = @JoinExpr + ' AND ' + @QueryFilterEXEC uspPagingCursor @FieldNames, @TableNames, @PrimaryKey,@PageSize, @PageIndex, @SortField, @QueryFilter, NULLGO
View Replies !
Call Store Procedure From Another Store Procedure
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it: I want to create a table and to insert the result of the store procedure to it. This is the code: Pay attention to the underlined sentence! ALTER PROCEDURE [dbo].[test] AS BEGIN SET NOCOUNT ON; DROP TABLE tbl1 CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) ) INSERT INTO tbl1 (first_name,last_name) VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen') END PLEASE HELP!!!! and God will repay you in kind! Thanks!
View Replies !
Putting Many Bcp Codes In A Store Procedure And Using Scheduler To Run The Procedure
hi, Can anyone advice if what I am doing is right, I have bcp code to import data from a text file into a 9 tables. create the following store procedure which will call other procedures to run create p_activate AS execute p_truncate_tbls -- this will truncate all 9 tables execute p_drop_all_indexes -- this procedure drop all indexes from 9 tables execute p_bcp_to_all_tbls -- this procedure will import data from txt files execute p_create_all_indexes -- this procedure will create index in 9 tables Go My question is that is this a good way to do in order to use bcp, as you see, I am getting rid of the old data in the tables,then dropping the indexs, then migrating data from text files into the sql tables, finally, i am building the indexes again....The amout of data are 9 GB....I have not tried this process yet, I 'd like to have feed back from wiz experience sql people to advice me if what I am doing is rigth, If there is anyone who has a better method, please do not hesitate to drop me an email, Iwould really appreciate your input... regards Ali
View Replies !
Returning A Dataset From A Procedure
Hi all I have written a SQL Procedure to return all the results from a table and am writing a function to run the procedure (So that it is easier to use within my app). I have kinda got a bit confused. :oS The SQL procedure Gallery_GetAllCetegoryPictures has one input variable (CategoryID) and returns a table procedure is something like SELECT * FROM Gallery WHERE CategoryID = @CategoryID. The code im having trouble with is below: Function GetAllCategoryPictures(ByVal CatID As Integer) As DataSet Dim MyConnection As SqlConnection Dim MyCommand As New SqlCommand Dim MyParameter As SqlParameter MyConnection = New SqlConnection(AppSettings("DSN")) MyConnection.Open() MyCommand.CommandText = "Gallery_GetAllCetegoryPictures" MyCommand.CommandType = CommandType.StoredProcedure MyParameter = MyCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4) MyParameter.Direction = ParameterDirection.Input MyParameter.Value = CatID MyCommand.ExecuteNonQuery() End Function OK so that executes the procedure now I want to return the data within a dataset. Do I need to use a data reader? if so how do I do it? Thanks
View Replies !
Using A Stored Procedure With A Dataset
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error: Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) my code looks as follows for the dataset:<script runat="server">Sub ListSongs() ' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer 'retrieve albumn ID for track listings intID4 = Int32.Parse (Request.QueryString("id")) ' Create Instance of Connection myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open() 'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4) ' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) 'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs") 'Bind the datatable object called dtsongs to our Datagrid: dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html> Any help or advice would be greatly appreciated. Thank You - Jason
View Replies !
Dataset Stored Procedure Return Value
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem. Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to. Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e) {GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C"); }
View Replies !
Dataset With Existing Stored Procedure
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
View Replies !
No Output From The Stored Procedure In The Dataset
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
View Replies !
Populating A DataSet From A Stored Procedure
Hi all, Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere. Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this. This is what I have so far:- 1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 } As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data. Any help is greatly appreciated. Slainte, Sean
View Replies !
Stored Procedure - Return DataSet
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
View Replies !
Dataset Stored Procedure Problem
I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.
View Replies !
Retrieve A Dataset From Stored Procedure
Hi, Can anyone please help me solve this problem. My functions works well with this stored procedure: CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO But when I applied the same function to the following stored procedure CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO I received this message Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error: Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter(); Line 34: sqlDA.SelectCommand = sqlComm; Line 35: sqlDA.Fill(dataSet); Line 36: Line 37: return dataSet; The function I am using is returning a DataSet as below: public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue) { DataSet dataSet = new DataSet(); SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString()); SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect); sqlComm.CommandType = CommandType.StoredProcedure; for (int n=0; n<paramName.Count; n++) { sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n])); } SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = sqlComm; sqlDA.Fill(dataSet); return dataSet; } If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure? Thanks.
View Replies !
Fill DataSet Using Stored Procedure
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
View Replies !
Return One Dataset Instead Of Many From A Stored Procedure
Hello everyone, I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop). I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset: ALTER PROCEDURE dbo.GetVal AS Declare @fname varchar(50) Declare @lname varchar(50) Declare @grname varchar(100) Declare @isgroup int Declare @id int Declare @ListName varchar(200) DECLARE guests_cursor CURSOR FOR SELECT id, fname, lname, grname, b_isgroup FROM guests OPEN guests_cursor -- Perform the first fetch. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS =0 BEGIN if (@isgroup=1) Select @grname+'('+@lname+', '+@fname+')' as ListName else Select @lname+', '+@fname as ListName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup END CLOSE guests_cursor DEALLOCATE guests_cursor GO can somebody help me please. Thanks in advance
View Replies !
From DataSet To SqlDataReader In A CLR Stored Procedure
Hi all, I'm writing a CLR stored procedure that just execute a query using 2 parameters. SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet? How can I obtain a SqlDataReader from a DataSet? Dim command As New SqlCommand(.......).....Dim ds As New DataSet()Dim adapter As New SqlDataAdapter(command)adapter.Fill(ds, "MyTable")... 'manipulating the ds.Tables("MyTable") At this moment I have to send the table...but ds.Tables("MyTable").CreateDataReader() just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(... Help me please!
View Replies !
How To Run SQL Store Procedure Using Asp.net
I have asp.net web application which interface with SQL server, I want to run store procedure query of SQL using my asp.net application. How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server. for Instance Dim connections as string, Dim da as dataset, Dim adpt as dataadapter. etc if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application thank you maxmax
View Replies !
Store Procedure?
Hi all, I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters??? Thanks. Kabir
View Replies !
Store Procedure
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
View Replies !
Store Procedure Help....
Ok, I am creating a web app that will help a company to help with inventory. The employee's have certain equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee; to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only those employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem I am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when I click on the select it redirect and only shows the first record not the one I selected, and when I change it, a employee that doesn't have equipment will not show up totally. I am confused why this isn't working.... I am very new to this.... so i am sure its something simple... CREATE PROCEDURE dbo.inventoryinfo @EmployeeID int, @SerialNo varchar(50) AS IF(@EmployeeID IS NULL)BEGIN(SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID,E.EmployeeID, E.cwopa_id, eq.IPAddress,eq.Location, eq.DataLine3, eq.DataLine2, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo,eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.CarrierFROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (eq.SerialNo=@SerialNo))END Else IF( @SerialNo is NULL)BEGIN (SELECTE.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2,eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber,eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (E.EmployeeID = @EmployeeID)) end ELSE IF (@EmployeeID is not Null) and (@SerialNo is not Null)BEGIN(SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID,E.EmployeeID, E.cwopa_id, eq.IPAddress,eq.Location, eq.DataLine3, eq.DataLine2, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo,eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.CarrierFROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo))ENDGO
View Replies !
Store Procedure Maybe????
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me. Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally. SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo EmployeeID and SerialNo are primary keys. I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work CREATE PROCEDURE dbo.inventoryinfo AS DECLARE @EmployeeID int DECLARE @SerialNo varchar(50) IF( @SerialNo is NULL) SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID ELSE SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
View Replies !
SQL Store Procedure In Asp.net
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance. NetAdventure
View Replies !
Store Procedure Help
I am trying to loop through a string of ID's and check if theproductname for the ID is 'Chai' if so change it to 'Tea'. This is inthe northwind db - products table. call it exec sp_UpdateProduct'1,2,3'It does not work, any ideas - syntax, etc....CREATE PROCEDURE dbo.sp_UpdateProduct@IDs varchar(200)ASBEGINdeclare @sStr1 varchar(200)declare @iStrLen1 intdeclare @sParseElm1 char(2)declare @sStatus varchar(200)declare @count intset @sStr1= @IDsset @iStrLen1= len(@sStr1)SET @count = 0while charindex(',',@sStr1) <> 0beginselect @sParseElm1 = substring(@sStr1,1,CHARINDEX(',',@sStr1 ) - 1)set @sStatus = 'select productname from products where productid='+@sParseElm1if @sStatus = 'CHAI'update products set productname = 'TEA'elseupdate products set productname = 'CHAI'set @sStr1 = substring(@sStr1, CHARINDEX(@sStr1,',') + 1, @iStrLen1)endendGO
View Replies !
Help With A Store Procedure
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this? Thanks Lystra
View Replies !
Get ID From Store Procedure
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following: CREATE PROCEDURE AddEmployee (@efname nvarchar(20), @elname nvarchar(20) ) AS insert into tblEmployeeName (EmployeeFName, EmployeeLName) values (@efname,@elname) GO Thanks.
View Replies !
STORE PROCEDURE And DTS
I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt. Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible? Thanks
View Replies !
Store Procedure
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS Tot_work_all number(12,2); Bid_tot number(12,2); Ewo number(12,2); Overruns number(12,2); Underruns number(12,2); Contr_tot_all number(12,2); sContractType ae_contract.contr_type%type; BEGIN select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0)) into Tot_work_all from valid_item Where db_contract = db_id; Select sum(Contq * Contr_Price) into Bid_tot From Valid_item Where nvl(New_Item,'N') <> 'Y' and db_contract = db_id; Select sum(Qtd * Contr_price) into Ewo From Valid_item Where nvl(New_item,'N') = 'Y' and db_contract = db_id; Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns From Valid_item Where Qtd > Nvl(Projq,0) and db_contract = db_id and nvl(New_Item,'N') = 'N'; IF LAT_type <> 'R' THEN Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Projq,0) < Contq and db_contract = db_id and nvl(New_Item,'N') = 'N'; ELSE Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Qtd,0) < Contq and db_contract = db_id and nvl(New_Item,0) = 'N'; end if; Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0) +NVL(underruns,0); IF Contr_tot_all = 0 THEN Select Contr_type into sContractType from ae_contract where db_contract = db_id; IF sContractType = 'A' OR sContractType = 'T' THEN --If the divisor is zero here, it's not an error. update ae_contract set perc_compu = 0 where db_contract = db_id; ELSE --If the divisor is zero here, it would be an error update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; Else --Here we have a real number to calculate, so go ahead and do your stuff! update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; END;
View Replies !
Store Procedure
I have file name book.sql. In this script there are 30 procedures with prefix ssp. When I run with iSQL at blank database, there are store procedure not work name: "ssp_populatechilrenofaccount" with paramter @nodeaccount. After compile using Enterprise Manager this procedure can work. Why? Note: I use SQL Server 6.5 using SP4
View Replies !
Store Procedure
Hi, All, I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month Month salefromagent directsale total Jan 1100 2300 3400 Feb 800 500 1300 .......... Dec --------------------------------- I know we can handle this in the program use two queries. But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue. Betty
View Replies !
Store Procedure Help
Hi all, I need to write a sales store procedure. The sales summary is basically group by agentCode. But the problem is some agents have subagents. i.e., in the sales table. one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales. what should I do in the store procedure. Thanks Betty.
View Replies !
Store Procedure
Hi, I follow the example for store procedure from this link to deal with large amount of data and it work great. http://www.4guysfromrolla.com/webtech/042606-1.shtml CREATE PROCEDURE [dbo].[usp_PageResults_NAI] ( @startRowIndex int, @maximumRows int ) AS DECLARE @first_id int, @startRow int -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that -- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = employeeID FROM employees ORDER BY employeeid -- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows SELECT e.*, d.name as DepartmentName FROM employees e INNER JOIN Departments D ON e.DepartmentID = d.DepartmentID WHERE employeeid >= @first_id ORDER BY e.EmployeeID SET ROWCOUNT 0 GO my problem is how do i do the Sort for this. Any help would be appreciated. Thanks Ddee
View Replies !
Store Procedure
i am running this procedure, but i have problems with the result. the main idea is to get the closest point to one sample. even though i have a restriction with the distance, the result that i got is strange becuase my restrcitionis 20 feet and i have result from 50 to 350 feet. This is the code ALTER PROCEDURE [dbo].[closestDH] -- radius in feet @radius float AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT SAMPLEID,(SELECT HOLEID FROM ( SELECT HOLEID, (sqrt(square(Shovel_A.EAST - EAST ) + square(SHOVEL_A.NORTH - NORTH) )) AS distance FROM (SELECT HOLEID, EAST, NORTH FROM dbo.Shovel_Hole WHERE (dbo.Shovel_Hole.shotname = dbo.Shovel_A.polygono)) as samepattern ) AS matched WHERE (distance <= (select min(distancecalc) from ( SELECT (sqrt(square(Shovel_A.EAST - EAST ) + square(SHOVEL_A.NORTH - NORTH))) AS distancecalc FROM (SELECT HOLEID, EAST, NORTH, RL FROM dbo.Shovel_Hole WHERE (dbo.Shovel_Hole.shotname = dbo.Shovel_A.polygono)) as samepattern ) as ***)) AND (distance <= @radius) ) as DH, polygono FROM dbo.SHOVEL_A ORDER BY DH, polygono END
View Replies !
Store Procedure
hello sir i want to write store procedure. I write following code-- drop Proc Analyst CREATE PROC Analyst @UserID varchar(20), @StockName varchar(50) AS INSERTtAnalystAssignHistory ( AnalystAssignHistoryCode, SecurityCode, ISIN, StockName, UserID, IsDummyEnabled, AnalystAssignDate, StatusCode, CreatedBy, CreateDate, UpdatedBy, UpdateDate ) SELECTCOALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1, SecurityCode, ISIN, StockName, 'ShahVis', IsDummyEnabled, AnalystAssignDate, 'A', 'Gayatri', GETDATE(), NULL, NULL FROMtAnalystAssignHistory WHEREUserId = @UserID and StockName = @StockName GO EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.' In this procedure i want to runtime insert ShahVis i.e REassignuserid. it will always change so i want runtime insert this field. and created by this is store in globaly i want to retrive this runtime. Please help me.
View Replies !
Store Procedure
iam using store procedure to return certain no. of columns frm table.the query is SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ") i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records. i tried this in query analyzer as well it runs normally returing rows. only in store procedure it gives me blank record. any suggestions what is wrong ?
View Replies !
Store Procedure
hello iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank. if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel. the stroe procedure is CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100)) AS SELECT * FROM employee_master WHERE empuser_id IN (@ID) and this is the statement that i use in my .aspxpage to call store procedure Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn) cmd.SelectCommand.CommandType = CommandType.StoredProcedure cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100)) cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID") any suggestions wht may be wrong
View Replies !
Store Procedure
hello iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' " TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page. depending on querystring the record is selected.
View Replies !
Store Procedure
1. can a stored procedure call itself? if else how ?> 2. how to run a job for a stored procedure to exucte every day.
View Replies !
Datareader Insted Of Dataset Sored Procedure
i m writing a stored procudrue to update my data that is onther table.and i pass the parameter in my vb code,when i pass the data that is insert only first record of data but second record insert the eroor will come is data reader is colsed. now insted of data reade i have to use data set how can i use that and update my data is ontehr table.?below i written my vb.net2005 code. Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString()) ' con.Open() ' Dim ggrnid As String ' Dim acceptqty As String ' Dim itemid As String ' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox) ' ggrnid = grnid.Text ' Dim sWhere As String = grnid.Text ' If (Not String.IsNullOrEmpty(sWhere)) Then ' For Each s As String In sWhere ' ' 'Dim iRowIndex As Integer = Convert.ToInt32(s) ' Dim sqldtr As SqlDataReader ' sqlcmd = New SqlCommand ' sqlcmd.Connection = con ' sqlcmd.CommandType = CommandType.Text ' sqlcmd.CommandText = "select acceptqty,itemid from grndetail where grnid='" & Trim(ggrnid) & "'" ' datacommand = CommandType.StoredProcedure ' 'datacommand("aaceptqtygrn", con) ' Dim cmd As New SqlCommand("aaceptqtygrn", con) ' sqldtr = sqlcmd.ExecuteReader() ' 'dataset = datacommand. ' 'sqldtr = sqlcmd.ExecuteScalar ' If sqldtr.HasRows = True Then ' While sqldtr.Read() ' acceptqty = sqldtr.Item("acceptqty") ' itemid = sqldtr.Item("itemid") ' cmd.CommandType = CommandType.StoredProcedure ' cmd.Parameters.AddWithValue("@acceptqty", acceptqty) ' cmd.Parameters.AddWithValue("@itemid", itemid) ' sqldtr.Close() ' cmd.ExecuteNonQuery() ' End While ' 'sqldtr.Close() ' 'cmd.ExecuteNonQuery() ' 'Next sqldtr.HasRows ' End If ' Next s ' sqldtr.Close() ' con.Close() ' End If ' End If Catch ex As Exception MsgBox(ex.Message) End Try
View Replies !
Reporting Services Stored Procedure Dataset
I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it. Thanks!
View Replies !
|
TRACKER
