How To Retrieve Large String From Stored Procedure?
May 4, 2007
Hello! This is my scenario...
Development - Visual Studio 2005
Database - MS SQL 2005
I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.
This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.
So... Does anyone here can help me out? Thanks in advance.
View 6 Replies
ADVERTISEMENT
Jan 15, 2007
I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated. CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates] @UserName NVARCHAR(256), @StartingDate datetime, @EndDate datetimeASDECLARE @UserId AS UNIQUEIDENTIFIERSET NOCOUNT ONSELECT @UserId=UserId FROM aspnet_users WHERE UserName=@UserNameSELECT @UserName as UserName, SUM (timeentryDuration) AS TotalDuration,SUM (timeentryOvertime) AS TotalOvertimeHoursFROM aspnet_starterkits_TimeEntryWHERE aspnet_starterkits_TimeEntry.TimeEntryUserId=@UserId AND TimeEntryDate between @StartingDate and @EndDate
View 1 Replies
View Related
Jul 27, 2004
Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
myConn.Close()
'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If
Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael
View 4 Replies
View Related
Apr 8, 2006
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 1 Replies
View Related
Apr 30, 2006
I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?
View 1 Replies
View Related
May 10, 2004
Hi to all!
Is there a system stored procedure that retrieve info or name about an object?
I must know if a database contains a specific table and i must know if there is a specific DTS.. before execute it..
someone could help me??
thx a lot!
Ale.
Sorry for my poor english!!
bye! =)
View 2 Replies
View Related
May 18, 2007
Hi,
I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO
View 1 Replies
View Related
Jan 17, 2008
Im a bit confused on my programming skills. I know how to add a user and password using the Membership.CreateUser class. ' Define database connection
Dim conn As New SqlConnection("Server=localhostSqlExpress;" & _ "Database=lnp;Integrated Security=True") ' Create command
Dim comm As New SqlCommand( _ "SELECT oldLogId, oldPassword FROM userDetails WHERE (oldLogId = N'chuck') OR (oldLogId = N'top_dawg')", conn) ' Open connection
conn.Open()
' Execute the command
Dim reader As SqlDataReader = comm.ExecuteReader()
' Do something with the data
While reader.Read()
Dim partPassword = reader.Item("oldPassword") ' Adds underscore character and first 3 letters of password for distinct USER ID
Dim newUserID = reader.Item("oldLogId") & "_" & partPassword.SubString(0, 3) ' Create User if Valid
Try
Membership.CreateUser(newUserID, partPassword)
Catch ex As MembershipCreateUserException
' Code that is executed when an exception is generated ' The exception's details are accessible through the ex object
usersLabel.Text &= "<p><strong>Exception: " & newUserID & "</strong><br />" & ex.ToString() & "<br /><strong>Message: </strong><br />" & ex.Message & "</p>" End Try End While usersLabel.Text &= "<p><strong>END OF RECORDS</strong></p>" ' Close the reader and the connection
reader.Close()
conn.Close() But now I want to do something in a Stored Procedure USE [lnp]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_createPortingDetailsWithUsers]-- Add the parameters for the stored procedure here @passUserId NVarChar(50), @passPassword NVarChar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;Membership.CreateUser(@passUserId, @passPassword)Select SCOPE_IDENTITY()ENDBut when I try to execute this in SQL SERVER MANAGEMENT STUDIO EXPRESS, I get an error near the line MEMBERSHIP.CREATEUSER(@passUserId,@passPassword)Msg 102, Level 15, State 1, Procedure sp_createPortingDetailsWithUsers, Line 11Incorrect syntax near 'Membership'. What I want to do is create a user with a UserID and Password that I am passing from a table and then retrieve the USERID (unique identifier) when I create the new user. Can anyone suggest or show a tutorial on what I am doing wrong? I appreciate any assistance in advance,Chuck
View 4 Replies
View Related
Jan 5, 2006
Hi,
I am trying to return the id of the newly added row after the insert
statement in my stored procedure. I was told to "RETURN
SCOPE_IDENTITY()", which i did. The problem is i do not know how to get
this value in asp.net?
I added this code below in my business layer. myDAL refers to an object
of my DAL layer. In my DAL layer, i have a addPara() method which will
help me dynamically add as many parameters. Someone please advise me on
what to do. Thanks
myDAL.addPara("@RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)
Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)
)
As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GO
View 3 Replies
View Related
Jun 22, 2004
Hi all,
I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:
ie:
SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats
...etc...
The SP is currently called as below:
SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("@FromDate", Request["FromDate"]),
new SqlParameter("@ToDate", Request["ToDate"]),
};
My question is this: How do I retrieve these variables?
I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.
Thanks in advance,
Pete
View 5 Replies
View Related
Feb 9, 2006
Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE ProjStatus
6 In Progress
3 Complete
4 On Hold
The stored procedure is as follow:
SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)
dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()
myConnection.Close()
</script>
<asp:datagrid id="dgProjSumm" runat="server"
BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
View 3 Replies
View Related
Jun 16, 2001
I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong?
appreciate for any suggestion!
...mike
View 1 Replies
View Related
Apr 28, 2008
Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
Thanks in advance
Ade
View 5 Replies
View Related
Aug 25, 2006
Hi All,
In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.
CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
As
Begin
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
Begin
select '1'
End
Else
Begin
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'
End
End
Thanks & Regards
Bijay
View 4 Replies
View Related
Jul 20, 2005
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies
View Related
May 6, 2007
As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post, http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.
View 3 Replies
View Related
Apr 2, 2008
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View 12 Replies
View Related
May 24, 2007
Apologies if this post doesn't belong here.
We are in the process of moving steps from a large SQL job to a web portal. The intent here is to allow the user to manually trigger the jobs to run. The problem I am having is in determining a way to run these job steps in the background, thus allowing the browser to take control while the step runs. These jobs can take enough time that forcing the browser to wait is not an option.The steps have been consolidated into stored procedures so in order to run a step, one would call the sproc. What I would like to do is run this in a separate thread and let that thread end on its own while the server transfers back to the client. Is something like this possible? My understanding is that the thread would be orphaned once the browser takes control again. Thanks in advance for any advice.
View 9 Replies
View Related
Jun 19, 2015
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies
View Related
Jan 15, 2008
Dear All!
I use a "Execute SQL Task" to call a Stored Procedure. My Stored Procedure have an input parameter with type: ntext. And in "Execute SQL Task", I set variable in Parameter Mapping as following:
Variable Name: User::xmlDocument (this variable is a String to store xml data)
Direction: Input
DataType: NVARCHAR
ParameterName: 0
When length of "User::xmlDocument" is too large then error is happen but on the contrary, "Execute SQL Task" run successfully.
So, Can you show me how to pass large data into stored procedure using "Execute SQL Task"?
I am looking forward to hearing from you
Thanks
View 20 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Apr 5, 2006
Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname) and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corressponding to surname and firstname.The code to make this work looks like this:---------------------------------------- Dim conn As SqlConnection Dim comm As SqlCommand Dim param As SqlParameter Dim param2 As SqlParameter Dim param3 As SqlParameter Dim param4 As SqlParameter Dim objDataset As DataSet Dim objAdapter As SqlDataAdapter conn=NewSqlConnection("blah, blah") comm = New SqlCommand 'set properties of comm so it uses conn & recognises which stored proc to execute comm.Connection = conn comm.CommandText = "SPSearchTest3" comm.CommandType = CommandType.StoredProcedure 'create input parameter, set it's type and value param = comm.CreateParameter param.ParameterName = "@empid" param.Direction = ParameterDirection.Input param.Value = txtPatID.Text param2 = comm.CreateParameter param2.ParameterName = "@LastName" param2.Direction = ParameterDirection.Input param2.Value = txtSurname.Text comm.Parameters.Add(param) comm.Parameters.Add(param2) conn.Open() objAdapter = New SqlDataAdapter(comm) objDataset = New DataSet objAdapter.Fill(objDataset) dgrdRegistration.DataSource = objDataset dgrdRegistration.DataBind() conn.Close()------------------------------------While the stored procedure is this:------------------------------ @EmpID int, @LastName nvarchar(20) ASSELECT EmployeeID, LastName, Firstname, BirthDate, Address, title, addressFROM employeesWHERE (DataLength(@EmpID) = 0 OR EmployeeID = @EmpID)AND (DataLength(@LastName) = 0 OR LastName = @LastName)------------------------------This will work if I search using EmployeeID and Surname or only by EmployeeID, but I don't get any results if I search only for Surname, even though I know the record(s) exits in the db and I've spelled it correctly. Can someone point out where I'm going wrong?(Incidentally if I have a procedure with has only one parameter 'surname' or 'employeeID', it works fine!)Thanks very much and sorry about the long-winded post.
View 6 Replies
View Related
Feb 24, 2008
HiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT
)
ASSELECT * FROM
(
SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
View 2 Replies
View Related
Mar 29, 2004
Up till now I've used SP's for updates and only ever needed to return error messages.
Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)
It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.
Hope that makes sense.
Thanks
Mark
View 4 Replies
View Related
Nov 28, 2005
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies
View Related
Apr 3, 2008
This procedure gives a error : " Msg 245, Level 16, State 1, Procedure YAMAN, Line 16
Conversion failed when converting the nvarchar value 'user' to data type int. "
How can i return string value
ALTER procedure [dbo].[YAMAN]
(@username varchar(20),@active varchar(20))
as
begin
if exists (select username from aspnet_Users where username=@username)
begin
if @active=(select active from aspnet_Users where username=@username)
return 'already exist'
else
begin
update aspnet_Users set active=@active where username=@username
return 'update'
end
end
else
return 'user does not exist'
end
Yaman
View 2 Replies
View Related
Nov 2, 2006
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
CREATE proc ChangeHIS
as
--Declare Variables
Declare @LastName varchar,
@FirstName varchar,
@MI varchar,
@ID varchar,
@Dept varchar,
@intCount int,
@UserName varchar,
@strTemp varchar
--Create Temporary Table
CREATE TABLE [EmployeeAudit].[dbo].[tmpTable] (
[UPUPRF] varchar (10),
[UPTEXT] varchar (50)
)
select [UPUPRF], [UPTEXT] from tblHIS into tmpTable
GO
And something dealing with the below code as well.
@tmpString = RTRIM(LTRIM(@tmpString))
If charindex(@tmpString, ",") > 0
--'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'
@LastName = Left(@tmpString, charindex(@tmpString, ","))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, ",") + 1)))
--'Michael J - 78672 - SR MIS SUPPORT SPEC'
@FirstName = Left(@tmpString, charindex(@tmpString, " "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " ") + 1)))
If charindex(@tmpString, "-") > 1
--'J - 78672 - SR MIS SUPPORT SPEC'
@MI = Left(@tmpString, 1)
@tmpSting = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - 2)
End
--'- 78672 - SR MIS SUPPORT SPEC'
@ID = Left(@tmpString, charindex(@tmpString, " - "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " - ") + 3)))
--'SR MIS SUPPORT SPEC'
@Dept = @tmpString
End
Hope someone can point me in the right direction
View 13 Replies
View Related
Nov 19, 2007
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com
I then want to pass Str_email back to an asp.web page
Can this be done in a stored procedure ?
View 5 Replies
View Related
Jul 20, 2005
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
View 4 Replies
View Related
Dec 8, 2006
I'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?
View 6 Replies
View Related
May 27, 2005
Hello,
I was wondering if it's possible to pass in a comma separated string
"12,14,16,18" and use it in a stored procedure with "IN" like this:
@SubRegions varchar(255) <-- my comma separated string
SELECT *
FROM myTable
WHERE tbl_myTable.SubRegionID IN (@SubRegions)
It tells me it has trouble converting "'12,14,16,18'" to an INT. :(
View 2 Replies
View Related
Aug 2, 2005
Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work. I'm baffled since I know how to pass an integer to a stored procedure. In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db. If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure. Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below. Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo)) End Function----------------------------------------
******************* DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------
View 3 Replies
View Related
