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 RepliesI 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]...
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?
This code calls a store procedure that takes input parameters. With this code I'm confused as to how the parameters are passed to the stored procedure. The parameters are first stored into 2 string variables (strCategoryName & strOrderYear). They are then copied/moved to an sql parameter prm.value. But the stored is stored in an sqlcommand (cmd ). I can't understand how the stored procedures knows what the parameter values are.
Private Sub btnWithParameters2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnWithParameters2.Click
Dim cnn As SqlClient.SqlConnection = _
New SqlClient.SqlConnection("Data Source=SKYROCKET;" & _
"Initial Catalog=Northwind;Integrated Security=SSPI")
[Code] ......
I see this asked many times on hereI'm copying this question that I found on here.Is there any way to pass a null value (I mean a value that the sql will interpret as null) to a stored procedure when using:tableAdapter.Fill(dataSet.table_name, param1 (as string),param2 (as string) etc...Which is the most painless way to do this(assuming that the sp stays inside the database!!)
View 4 Repliesthis is my coding when i run this coding for display the content in textboxes from the database by click event in datagrid. "Procedure or Function 'sp_developer_display' expects parameter '@developerid', which was not supplied."this error will display my sp in sql express is ALTER PROCEDURE dbo.sp_developer_display(
[Code]...
Im trying to pass parameters to a stored procedure in SQL but for some reason its storing the parameter(s) in the columns....
My code
Public Function AddRec(ByVal textbox1 As String, ByVal textbox2 As String, ByVal textbox3 As String, ByRef Creation As Date)
'Adding a new Record to SQL
Dim Conn As New SqlConnection
[code]....
the addwithvalue all the textbox`s have a value in which i would expect but for some reason when i go to db in SQL it actually shows the @Username,@Account,@Password,@DatCreated not the values im passing in please see the attached image.
i have a stored procedure to insert data into table create procedure insertdata
[Code]...
I am building a three tierd app, and therefore have all my data access methods in a class by itself.This class only calls stored procedures, basically, I need to pass in the following:
1) stored procedure name as string
2) parameters as IDataParameter() -- an array of parameters
3) other fields ( not important to this discussion)
If I use a GridView on my web form and choose an ObjectDataSourceObjectDataSource, and then choose my business object and method, since the method is a call to a stored procedure with a parameter array, I don't know how to pass it values in the IDataParameter format.The Method Signature listed on the wizard is "RunSP(String spname, IDataParameter[] params), returns SqlDataReader" which basically is calling a method called RunSP with two parameters, of which the second parameter is a parameter array for a stored proc.
I have created a database with on table that is very similar in setup to ProductCostHistory from the Adventureworks database. Using the adventureworks database as an example, when the procedure is given the parameters of date and productID it will change the EndDate of the currently active cost to the date value and insert a new record with productID as the ProductID and Date as the StartDate, leaving EndDate null for the newly inserted record. I am looking for a way to create a simple form with two textboxes and a button. textbox1 accepts the ProductID parameter and textbox2 accepts the Date value. when the button is pressed, the stored procedure is executed and the table is updated. This seems pretty simple and I am very familiar with tieing stored procedures to a table in a dataset when each prcedure handles only a single simple action (either, SELECT, INSERT, UPDATE, or DELETE but not a single procedure that combines UPDATE and INSERT as the procedure described above does). I've done some reading and played with this on myown but can not find the simplest straightforward way of accomplishing this seemingly simple task.
View 2 RepliesI have an asp.net page that contains a javascript function. I am using a vb.net code behind to run a stored procedure against the database to pull back address information. Results can be 1 or more rows. I need to pass the results to the javascript for processing. My code is as follows:
VB.NET
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
[Code].....
How can I pass stored procedure name dynamically to Linq-to-SQL, vb.net and get the result into a datatable.
View 2 RepliesI have a form that I'm running a stored procedure in and I'd like to pass the results to a datagridview and then offer the user the ability to export those results to a flat text file.
View 1 RepliesI 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?
Trying to convert XML file to string and pass to stored procedure.
Dim fs As FileStream = File.Open(fileUpload1.PostedFile.FileName, FileMode.Open, FileAccess.Read)
Dim buffer(fs.Length) As Byte
fs.Read(buffer, 0, fs.Length - 1)
txtRawXML.Text = System.Text.ASCIIEncoding.ASCII.GetString(buffer)
When I tried to upload file I got error. The error description is 'A name contained an invalid character.'. Could not find prepared statement with handle 0. Could not find prepared statement with handle 0. sp_xml_removedocument: The value supplied for parameter number 1 is invalid. The statement has been terminated.
how to Pass datatable as a parameter to a SQL Server stored procedure
View 3 RepliesI want to passing field in database to list view with stored procedure.[code]...
View 3 RepliesI have an application that I'm building which pulls dates from a database. The start value is stored as payPeriodStart date and I add 7 days to get the end date. What I need to know is, is it possible to store those values and pass them to a Stored Procedure? Currently my stored procedures have "static" dates in them ie[code]...
View 14 RepliesI am developing a complicated search form, and I would like to know best method of passing search criteria to stored procedures.Possible options that I know are, 1) Create sql statements in Visual Basic and send statements to SP as strings to execute2) Pass search fields to stored procedure and build command in SP (Stored procedure) in database?
View 9 RepliesI want to execute a method on VB.Net to return a date which is in the stored procedure. I tried using ExecuteScalar but it doesnt work it retruns error
'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query'
below is the code
Public Function GetHolidaydate(ByVal struserID as String) As DateTime
Dim objArgs1 As New clsSQLStoredProcedureParams
objArgs1.Add("@userID", Me.Tag)
[Code]....
I have a quick Questiom On a ASP.net form, I need to add a textbox to update a date field in MS SQL server via stored procedure. Using a text box datemissed
[Code]...
i need to pass null value but error dim drf as date
[Code]....
Dim Upd As String ="UPDATE sakit set tanggalsakit'" & drf & "' WHERE orang= '" & id& "'
I have a task to create a web service to receive client-side app's http request(with rpc={json data} in the end), deserilize it and put he parameter in stored Procedure in order to retrive data from sql server. the procedure query and client-side's app are already there and the return data to client-side app is JSON too
View 1 RepliesThe 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?
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.
Im a student doing an assignment, how do i pass the value from a sub procedure to a function procedure....i want to pass the value from decSubtotal to a function procedure named CalculateDiscount; check out my code--
[Code]...
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 RepliesI 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 RepliesI am currently building an ASPX webpage for internal use. Basically, there are two buttons on this page and I need each one to kick off a different stored procedure on our local SQLServer based on the needed task. these stored rocedures do quite a bit of work and take 3-5 minutes to finish running. In neither case do I need any kind of gridview or other output from these stored procedures, as they are simply crunching/cleaning data that will populate some tables that the user will then use to complete their task. There are also no inputs or parameters needed.
So i'm OK at ASPX and pretty good at SQL, but have no idea on the VB code behind page. This is what I have ben able to put together based on 3 days of googling. i think i am close but can't quite get across the line.
CODE:
I'm not sure if you need to see any of my ASPX or my SQL. both work fine on their own, but i am not able to get these buttons to work.
I have a stored procedure that when I run it within my application it times out, but when I use Management Studio and pass in the same exact parameters, the stored procedure executes under a second. I had this exact same issue with the same stored procedure before, and all I did was recompile it, and it fixed the problem, but I do not want to keep having to recompile this stored procedure every few days or so. Has anyone else ran into this issue before?
Another note I would like to mention, is that I'm currently working on a test database, so I'm not inserting, deleting or updating any of the records that the Stored Procedure is using. So the database itself is not changing, but for some reason the Stored Procedure is showing degradation, and at the most we have 2 users in the database at a time.
Below is the code I use to call the Stored Procedure from my app.
cmd.CommandText = "sp__RECAP_SELECTION_GET_GRID_RECORDS_RECAP_TYPES"
cmd.Parameters.Add("@FK_KitchenID", SqlDbType.SmallInt).Value = cmbKitchenCustomer.KitchenID_SelectedPrimaryKey
[Code]....
As I mentioned, if I run the Stored Procedure within Mangement Studio, it runs with no problems. Also if I recompile the stored procedure, it runs fine within the app, but after a few days, the stored procedure starts to timeout again within the app and I need to recompile it again.