Hi,
I have a stored procedure which expects a Null value if a user does not enter something into a TextBox. The query should return all rows if this case.
The stored procedure works as expected in Query Analyzer, but my application does not seem to send a Null and no rows are returned.
Any ideas?
Thanks.
ps I am using SqlDataSource and GridView, this is the first time I have tried to connect to a database with asp.net.
Hello,I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.Thanks in advance for any help.
View 2 Replies View RelatedHello,
I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.
Thanks.
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies View RelatedHi all;
How can I show the Name & Age of the selected student's ID in the appropriate TextBox(s) (txtName & txtAge)?
BTW:
My "ShowStudent" stored procedure:
ALTER PROCEDURE ShowStudent
@ID int
AS
SELECT ID, Name, Age FROM Student WHERE ID=@ID
RETURN
And this is the code for "Show" button:
' Which SP? Connection?
Dim cmdSelect As New SqlCommand("ShowStudent", con)
cmdSelect.CommandType = CommandType.StoredProcedure
'-----[ Spacifay SP parameters ]-----
'ID
cmdSelect.Parameters.Add("@ID", SqlDbType.Int, 4)
cmdSelect.Parameters("@ID").Value = CType(txtID.Text, Integer)
'Open Connection
con.Open()
'-----[ Execute the select command ]-----
cmdSelect.ExecuteReader()
'----------------------------------------
'Close connection
con.Close()
'========================================================
End Sub
So, do I have to use an OUTPUT parameter in the stored procedure? If Yes, How to get its (the parameter) value and bind it to the appropriate TextBox?
Hope my question is clear!!
Thanks in advanced!
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
I have successfully Create a Site, Inserting Updating and
Deleting information in my DB all with Stored Procedures, But I need the
ability to pass their username into my Stored Procedures. How and where do I
code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName
varchar(50)
As
Select *
From table
Where username
= @username
All of the
data is tied to the user in one way or another but I do not know what code to
put in my page?
Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?
View 1 Replies View RelatedI have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View 2 Replies View RelatedHi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View 8 Replies View RelatedHey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
I want to pass and array of ids to a procedure for inserting i a relation table.
I found some examples in other posts, but had problems getting them to work.
I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.
(I´m using sql server 2000), had some problem with examples with strpos then.
Any hints ?
peace.
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 RelatedHi, that's the problem:
I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.
When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:
exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1'
The fields are defined as follows:
<SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters>
The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'.
I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
Thanks in advance,
Anderson
I have the following stored procedure
ALTER PROCEDURE dbo.TextEntered
@searchString varchar(30)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN
doesn't work!!!!
I would like to produce the following where s is @searchString:
Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME
Any ideas how I might acomplish this I have tried almost everything !
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies View Relatedmake it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
Hello, I'd greatly appreciated if someone can help me out on this problem:
I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure:
@strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers
WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB??
Your help is greatly appreciated~
Thank you
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
View 1 Replies View RelatedHi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs.
The stored procedure works fine when run in SQL Server.
Can anyone help ? Please.
complete = True
Set rst = New ADODB.Recordset
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "usp_CompleteUID_sel (" & complete & ")", UIDCon, , , adCmdStoredProc
Do While Not rst.EOF
lst_pastUIDs.AddItem (rst.Fields(1))
rst.MoveNext
Loop
rst.Close
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
Thanks in advance.
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hey people,
I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):
foreach PersonID in (SELECT id FROM person)
{
MyStoredProcedure(PersonID);
}
Multiple rows will be returned from the select statement.
The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?
Thanks to you all!
Hello
I am pretty new to reporting services. Now i have something like this. on the .cs file, i have a fullName lets say
String fullName = "John Smith",
Now I want to pass this string to the report's textBox and show this value on this textbox.
How can i do this?
1.How should i write in .cs file?
2. How should i set the expression for the textbox?
Thank you very much. It is very urgent! Please help!
Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure.
Thanks, M.
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
View 6 Replies View RelatedHi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View 1 Replies View RelatedWell, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
)
AS
BEGIN
SET NOCOUNT ON
EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
----------------------
THis works fine inside Sql Server 2005 Query analyser.
But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.