DB/Reporting :: Stored Procedure Test For Null/empty Uniqueidentifier
Sep 6, 2008
I have a stored procedure (SQL Server Express) that returns a count of records:
SELECT COUNT(AdminUserID) AS CountEm
FROM Licenses
WHERE (AdminUserID = @AdminUserID);
Table "Licenses" has a field "CompanyID" which is a uniqueidentifier.
I need to add a clause to the WHERE to exclude records from the count where "CompanyID" is null/empty.
In VB code I'd do this with "If Not (TheCompanyID.Equals(Guid.Empty)) Then"
How would I do this in a stored procedure?
View 2 Replies
ADVERTISEMENT
Jan 19, 2011
i have a stored procedure to update a table. not all fields are required and some can be blank.
for example, title is not required, but when it's blank i get an error
Dim ptitle As New SqlParameter("@title", SqlDbType.NVarChar, 20)
ptitle.Value = objFormat.CheckSQL(title)
myCommand.Parameters.Add(ptitle)
ex {"Procedure or Function 'sp_UpdateUser' expects parameter '@title', which was not supplied."} System.Exception
how can i fix it so that it allows for nulls if the data field is blank?
View 6 Replies
Mar 16, 2009
My application is asp.net with vb. In my page I have a textbox for passing date. If I didn't enter date and on clicking submit, I have to pass null value to the stored procedure. I tried following codes such as DBNull.Value and DateTime.MinValue. In that case instead of null ,"#12:00:00#" is passing. I have to pass Null.
View 3 Replies
Apr 9, 2012
i have a stored procedure to insert data into table create procedure insertdata
[Code]...
View 11 Replies
Jul 19, 2009
I am using a stored procedure to add simple data to my db. My problem is if I want to add multiple items at the same time, I find myself calling the sp over and over again. This seems as though it is not the right way. I want to be able to pass multiple values to my stored proc.
I want to add say a persons name. I would like to capture the data I need into a block var such as name(x) and pass that to my procedure and in my procedure write as many fields in as I need.
I have an sp that takes my var name and adds it to my table. I do not want to call this procedure 300 times if I want to add 300 names etc. I would like to pass the whole block name() and all 300 at a time and in my sp write each like
name(0)
then write
name(1)
View 4 Replies
Mar 3, 2011
I have a stored procedure which updates a database using the parameters I supply but I'm having trouble passing a NULL to the stored procedure
The field I need to make NULL is a DateTime field
DB.Parameters.AddWithValue("@date", NULL)
This gives me the error 'NULL' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead
[Code]...
View 3 Replies
Mar 17, 2008
I am trying to test a number of stored procedures through SQL Server Query Analyzer. But am stuck when I try to test stored procedures that contain Output parameters.
Code:
DECLARE @Counterbigint
DECLARE @GUIDuniqueidentifier
exec usp_tbl_User_Add @Counter, @GUID, 'UserName', 'First Name','Surname',
[Code]....
When I put the Select statement though in the 'usp_tbl_User_Add' stored procedure in works fine and gives me the correct values.
View 2 Replies
Mar 19, 2009
We connecting a stored procedure in a crystal report.
In the query analyzer we can call the SP with: execute test 'example'
If we run the crystal report we get the possibillity to fill in the parameter for this SP. The report looks fine but how can we send this parameter in the crystal report script??
View 3 Replies
Apr 26, 2010
I have been banging my head against a brick wall for 2 days on this one. Someone please help, I am sure I am doing something stupidly wrong.I have a stored procedure that accepts 2 inputs and has 4 outputs. The SP checks the users database against a given username and password.
[Code]...
View 3 Replies
Feb 18, 2009
I have an app written in vb2005 that allows users to search a sql database for a store name. Once a store is selected, it then presents the user with 12 different tabs, each with a DataGridView showing various bits of info about the store. It's a large and convoluted database, so I'm using stored proceedures to pull the data for each tab. Here's a sample of one of the subs that populates the tabs.[code...]
Now, this works just fine to populate the grids on the tabs, and the same code is copied into each of the subs that fill the grids (changing the stored proc, parameter and grid names, of course). The problem I'm having is how to move changes made to the data in the grid back into the database.
I have stored procs that do this (and the procs do work), I just can't seem to figure out how to trigger the stored proc when the user makes a change.
If it matters, there is only a grid on each tab, I did not use a binding source as I am trying to keep an abstraction layer between the data and the app. I did create a dataset, which is how I gain access to the stored procs, but I left the grids unbound and (as seen in my code) bind them at run time.
View 2 Replies
May 29, 2008
I have a stored procedure on an oracle database called : GET_HVE_PACKAGE_VERSIONS This basically calls versions of PLSQL packages as stores the versions into a table. I am having big problems trying to call this procedure. I have tried everything. I am currently able to create and update etc etc using the executeNonQuery method and I thought that I would be able to call a procedure them same way, obviously not
the whole project depends on me being able to call procedures against the database.
[Code]...
View 5 Replies
Mar 10, 2009
I have created a search form based on my SQL database to search for individual records based on 3 types of criteria. The form is up and running but I want to be able to add some more code. I have assigned Stored Procedure to each click event for each button. What I wantr to be able to do is after value is added to text box and button is clicked if there are no reults to display than a message box is displayed with adequate message detailing this. Also is it possible to add a count of records to the form. Once value has been enetered ij tyext box and stored procedure runs and fills dataset with required data can I display a box to show number of records from this search.
[Code]...
View 1 Replies
Sep 11, 2009
The store procedure which give me the information I need is:
USE [UCSMIS]
GO
SET ANSI_NULLS ON
[Code].....
When I try to add it to my report it gives an error, is it because of the temp table and how should I go about it?
View 2 Replies
Aug 29, 2011
I have a stored procedure to update a table, but needs a couple of values from another table.
the first two selects get the value from the table and then are used in the update statement.
The select statments:
Select @iStatusDropDownValueID = iDropDownValueID
From DropDownValue
Inner Join DropDownValueType On DropDownValue.iDropDownValueTypeID =
[Code].....
First, the values that are retrieved by the first two select statements are always the same. So they could be passed in by the code itself. I don't know that this will speed things up at all, just make the entire stored procedure better and easier to read.
Second, if the "Value Name" should change this store procedure will break (which is possible, but not often).
I am looking for any insight into the Best Practices for this situation.
View 2 Replies
Feb 13, 2009
I have a states separated by commas and would like to query it out from stored procedure, how can i do it? let me explain below.
The normal query is:
CODE:
But in my procedure, the procedure as below and the @State value as
CODE:
The procedure above does not work because when I run the state list as
CODE:
If I change it to single quote, it error. how do you do it?
View 1 Replies
Jan 8, 2010
I have a custom class Y which includes a public property X as list(of string) which won't always have data
In my code I am trying to do the following dim x as list(of string) = Y.X if not x.count=0 then do somthing
The problem is that when Y.X has never been set, it causes an error. What would the proper way to check for
Y.X be? I haven't really worked with lists too much.
View 2 Replies
Jun 29, 2010
Given
view source
<embed id="highlighter_64051_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.dreamincode.net/syntax/scripts/clipboard.swf" id="highlighter_64051_clipboard" type="application/x-shockwave-flash"
title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_64051" menu="false" src="http://www.dreamincode.net/syntax/scripts/clipboard.swf" width="16" height="16"></embed>
View 2 Replies
Jun 6, 2009
I would like to test if ListBox1 is empty then test again until ListBox1 is NOT empty If ListBox1 is NOT empty then msgBox �You did it� and start over again, until i click a stop button
The problem is that when I start my program the listBox is ALWAYS empty and after 1-4 sec data will arrive in listBox1, but my loop (for testing if listBox1 is empty) is freezing up the application, because it�s always true and just looping infinity
View 11 Replies
Jun 28, 2010
This must've have been asked before but I couldn't locate it. In a mixed code project (VB and C#) we were debugging some old Visual Basic code where a statement as follows could be found:
If Request.Params("xxx") <> "" Then
'do something
I considered this a bug as Request.Params could be null, in which case the statement would've become false which wasn't the idea. So I thought. I just found out, -- probably for the tenth time and I will keep forgetting -- that the following two statements are not equal, while Nothing in VB should be equal to null in C# (thought I):
if(String.Empty == null) // always false
If String.Empty = Nothing Then ' always true
Should I dismiss this as a typical Microsoft backward compatibility effort, or should I consider this a huge error in the VB.NET compiler? Does anybody know the Microsoftean opinion on this oddity?
View 3 Replies
Jan 28, 2010
how to test vb code for null values. I tried this but it does not seem to work:[CODE...]
View 5 Replies
Dec 21, 2011
How do i get the following code to throw two exceptions, IOException, and UnauthorizedAccessException, when deleting a file?I am trying to test a delete procedure and its associated exceptions. However, I cannot set the ACL appropiately to cause either exception for FileSystem.DeleteFile.
NequePorro
As
String
= "Neque porro quisquam est
[code]....
View 4 Replies
Oct 24, 2010
Question is: How to test for null value of a field in an ADO recordset.Using Visual Studio 2010 Ultimate - VB.NET
ADO Connection to ODBC database
I am working with a table that contains about 10,000 records of which I need to do additional processing on about 1500. The records I need to process are qualified by the field'Cataloged' = True. The problem I have is the table allows for True, False or Null values (I have no direct control of the table to change from allowing Null) for the field I need to test.I run into errors when the value of the field is Null and need to figure out how to determine its Null state prior to trying to qualify the field as True or False value to prevent the errors.
View 4 Replies
May 17, 2010
I have this code, how can I test the URL which is stored in My.Settings.MyLink to make sure the link is valid before it tries to get the elements of my xml file.
Dim myXMLTextReader As New Xml.XmlTextReader(My.Settings.MyLink)
myXMLTextReader.ReadStartElement("ATypes")
Do While myXMLTextReader.Read()
[Code]....
View 1 Replies
Jun 23, 2009
i have a date control on form (vb.net) and by default its value is current date. i want to make its value to null or empty so that no date is shown and user choose from the drop down date calendar.
View 9 Replies
May 7, 2010
I am getting the attached error when any cell in a row is empty. How to avoid this error and to accept the null values?
View 4 Replies
Jan 10, 2011
[Code]...
I want to use a stored procedure to populate a label on my asp.net page and a dropdownlist @tourname will be populated into a single label while @tourdepartures will be multiple dates, that i want in a dropdownlist however when i run my sp i get this error
[Code]...
View 3 Replies
Jun 17, 2009
I am getting product id value some times null and empty from databasehere how i check if valule null and empty
productid = IIf(IsDBNull(TempDT.Rows(0).Item("productid")) =
True,
"", TempDT.Rows(0).Item("productid"))
[code].....
View 11 Replies
Mar 7, 2012
I'm deserializing some XML from an old application into an object in my current application. The old XML often has empty elements (<SomeElement />) which are currently deserialized as empty strings (""). I agree that this is the most appropriate behaviour, but it is a minor irritant; I'd rather they were deserialized as Nothing or ideally ignored - the effect would be the same. Is there a means of ignoring these elements? Or can I have them deserialized as Nothing?
View 2 Replies
Sep 7, 2009
I want use a stored procedure in my project but I have no idea about it. Please could you tell me a little bit about how to use I can use one in my vb.net application ?
View 2 Replies
Dec 22, 2011
I have noticed the following but unable to understand why.Whenever a procedure is called in .net it takes more time than when it is called subsequently.Even if a procedure is called after some interval it takes more time than it takes if it is called quite frequently. i am not asking about sql query or sql command. any user definded function or user defined method takes much more time to finish when it is called occasionaly. but when the same procedure is called quite often it finishes a lot faster. why?
View 1 Replies
