Using stored procedures to insert recorders

Hi

I am not familiar with stored procedures, but I tried to use it with this code and it worked fine

   ALTER PROCEDURE dbo.p_insert_shortleave      @Leave_Date datetime,      @Start_time datetime,      @End_time datetime,      @Reason varchar(150),      @Submission_date datetime,      @L_D_id_Priority varchar(20)AS      INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason)      VALUES (@Leave_Date, @Start_time, @End_time, @Reason)       INSERT INTO Requests (Submission_date, L_D_id_Priority)      VALUES (@Submission_date, @L_D_id_Priority)exec dbo.p_insert_shortleave 

And for the web service page I used this code and it worked fine also

Imports System.WebImports System.Web.ServicesImports System.Web.Services.ProtocolsImports System.Data.SqlClientImports System.DataImports System.DBNull Inherits System.Web.Services.WebService    Dim result As DataSet    Dim queryCommand As SqlCommand    Dim conString As String = ConfigurationManager.ConnectionStrings("").ConnectionString    Dim sqlCon As New SqlConnection(conString) <WebMethod()> _        Public Function insert_shortleave(ByVal Leave_Date As Date, ByVal Start_time As Date, ByVal End_time As Date, ByVal Reason As String, ByVal Submission_date As Date, ByVal L_D_id_Priority As String) As DataSet         Dim queryCommand As New SqlClient.SqlDataAdapter("exec p_insert_shortleave '" & Leave_Date & "','" & Start_time & "','" & End_time & "','" & Submission_date & "','" & L_D_id_Priority & "','" & Reason & "'", Me.sqlCon)         queryCommand.Fill(result)        Return result        Me.sqlCon.Close()    End Function 

And in the VB page I had wrote this code

Dim webs As New localhost.WebService    Dim ds As System.Data.DataSet    Protected Sub SubmittButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmittButton.Click        Dim Leave_date As Date        Dim Start_time As Date        Dim End_time As Date        Dim Submission_date As Date        Dim L_D_id_Priority As String        Dim Reason As String         'save form values in variables        Leave_date = LeaveDateRadDatePicker.RadControlsDir        Start_time = FromRadTimePicker.RadControlsDir        End_time = ToRadTimePicker.RadControlsDir        Submission_date = SubDateRadDatePicker.RadControlsDir        L_D_id_Priority = PriorityDropDownList.Text        Reason = ReasonTextBox.Text         'insert request to short leave        ds = webs.insert_shortleave(Leave_date, Start_time, End_time, Reason, Submission_date, L_D_id_Priority)     End Sub 

But when I click the submit button I got this error message

"Conversion from string "~/RadControls/" to type 'Date' is not valid."

 

What I am doing wrong?Sad

 

Can someone help me plzzzzzzzz?

Thanks

0
sheikha
4/28/2009 8:08:00 AM
asp.net.component-discussions 1793 articles. 0 followers. Follow

13 Replies
951 Views

Similar Articles

[PageSpeed] 52
Get it on Google Play
Get it on Apple App Store

 Notice the following lines

Leave_date = LeaveDateRadDatePicker.RadControlsDir    
Start_time = FromRadTimePicker.RadControlsDir
       
End_time = ToRadTimePicker.RadControlsDir

In these lines you are saving the RadControlDir (means Rad Control Directory Location to the string). Instead Use

LeaveDateRadDatePicker.SelectedDate (or some thing like that) refer to the documentation of the control to the get the current date and time

 

cheers

 


Dont forget to click "Mark as Answer" on the post that helped you. This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
http://www.aghausman.net
0
aghausman12
4/28/2009 8:25:09 AM

 Hi..

i think you are using RadControls.

to get date from date picker you need to use 'SelectedDate' property.

for ex.

Leave_date = LeaveDateRadDatePicker.SelectedDate


||- Paresh -||
[GTL-Ahmedabad]
0
paaresh
4/28/2009 8:49:08 AM
Hi all,   

I got this new error

 System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.NullReferenceException: Object reference not set to an instance of an object.  at WebService..ctor()

  --- End of inner exception stack trace ---

 

What should I do?????

 

0
sheikha
4/28/2009 9:51:33 AM

Hi,

This exception occours when Web service method is called over SOAP and an exception occurs. Can you please post your web service code ... 

 

cheers


Dont forget to click "Mark as Answer" on the post that helped you. This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
http://www.aghausman.net
0
aghausman12
4/28/2009 10:56:14 AM

Hi all,

 

Imports System.WebImports System.Web.ServicesImports System.Web.Services.ProtocolsImports System.Data.SqlClientImports System.Data

Imports System.DBNull

 

 Inherits System.Web.Services.WebService    Dim result As DataSet    Dim queryCommand As SqlCommand    Dim conString As String = ConfigurationManager.ConnectionStrings("").ConnectionString

    Dim sqlCon As New SqlConnection(conString) 

 

<WebMethod()> _        Public Function insert_shortleave(ByVal Leave_Date As Date, ByVal Start_time As Date, ByVal End_time As Date, ByVal Reason As String, ByVal Submission_date As Date, ByVal L_D_id_Priority As String) As DataSet 

        Dim queryCommand As New SqlClient.SqlDataAdapter("exec p_insert_shortleave '" & Leave_Date & "','" & Start_time & "','" & End_time & "','" & Submission_date & "','" & L_D_id_Priority & "','" & Reason & "'", Me.sqlCon)

 

queryCommand.Fill(result)        Return result        Me.sqlCon.Close()    End Function

I wrote this code in the web service

waiting for your replay

Thanks,

 

0
sheikha
4/28/2009 2:01:55 PM

Hi,

Try running the logic of this method in some other function of application. I mean to say .. try this logic without using webservice and see if it throws error. If yes then fix it their .. and paste the logic back in webservice code. 

can you please set the break point and tell us  is there any data comes in result dataset

cheers


Dont forget to click "Mark as Answer" on the post that helped you. This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
http://www.aghausman.net
0
aghausman12
4/28/2009 6:18:00 PM

 

I think its an issue of the query you used to execute the stored procedure. In sql stored procedure 'Reason' is define as a fourth parameter and you are passing it to stored procedure  as a last parameter in the dataadapter.  You need to takecare of the sequence  of the parameters.

Alternate way  to create this query is:

@Leave_Date datetime,   @Start_time datetime,   @End_time datetime,     @Reason varchar(150),      @Submission_date datetime,    @L_D_id_Priority varchar(20)

Dim queryCommand As New SqlClient.SqlDataAdapter("exec p_insert_shortleave @Leave_Date='" & Leave_Date & "',@Start_time='" & Start_time & "',@End_time='" & End_time & "',@Submission_date='" & Submission_date & "',@L_D_id_Priority='" & L_D_id_Priority & "',@Reason='" & Reason & "'", Me.sqlCon)

I hope this will solve your problem.

 

 

 

 

 

0
zeeans
4/28/2009 7:11:12 PM

Hi all,

Zeeans,

 I tried what you have suggested put still I got the same error Sad

 

aghausman12,

I set the breakpoint and I got this result:

Kindly see the link:

http://www2.0zz0.com/2009/04/29/03/441996755.jpg

Thanks all for your help Smile

0
sheikha
4/29/2009 3:55:46 AM

Hi,

Can you please set the breakpoint on web service .. instead of aspx page. I want to see what data comes in your result dataset ? 


Dont forget to click "Mark as Answer" on the post that helped you. This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
http://www.aghausman.net
0
aghausman12
4/29/2009 5:35:05 AM

Hi,

This is what I got in the web service page

http://www3.0zz0.com/2009/04/29/05/185933976.jpg 

When I set the breakpoint it automatically takes me to the VB page Tongue Tied

0
sheikha
4/29/2009 5:54:05 AM

Hi aghausman12,

 

This is the error that I got in Web Service

 

System.ArgumentNullException: Value cannot be null.Parameter name: dataSet   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)   at HD_WebService.insert_shortleave(DateTime Leave_Date, DateTime Start_time, DateTime End_time, String Reason, DateTime Submission_date, String L_D_id_Priority) in D:\Data\My Documents\Visual Studio 2005\WebSites\DTC\App_Code\HD_WebService.vb:line 23

 

0
sheikha
4/29/2009 9:07:36 AM

change declaration of the dataset to

 Dim result As New DataSet

 if problem persist than use following way to execute the sp

 Dim con As New SqlClient.SqlConnection("")Dim cmd As New SqlClient.SqlCommand("sp name", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue(
"@parametername", "value")

con.Open()

cmd.ExecuteNonQuery()

con.Close()

 

 

0
zeeans
4/29/2009 9:52:35 AM

Hi all,

 

I had figured how to solve this problem, I have to remove the last line of the stored procdure “exec dbo.p_insert_shortleave”

 

ALTER PROCEDURE dbo.p_insert_shortleave   @Leave_Date datetime,   @Start_time datetime,      @End_time datetime,     @Reason varchar(150),   @Submission_date datetime,      @L_D_id_Priority varchar(20)AS      INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason) VALUES (@Leave_Date, @Start_time, @End_time, @Reason)       INSERT INTO Requests (Submission_date, L_D_id_Priority)     VALUES (@Submission_date, @L_D_id_Priority)

  

Now it worked perfectlyYes, thank you all for your help Big Smile

0
sheikha
5/3/2009 3:54:55 AM
Reply:

Similar Artilces:

Use Stored Procedure or insert template to insert a new record?
Hi there, I am using stored procedure for all my database website before I use .net 2. When I come to .net 2, I found almost all the videoes are using SP for select functions, and using insert template to add new ones. What's the differenence and which way is better? Thanks!  >which way is better?Whenever you can, use stored procedures - preferably your own, not some of the autogenerated horrors! If you want some help in generating them - see my post in  http://forums.asp.net/p/1091006/1635955.aspx#1635955 plus the helper bits in http://forums.asp.net/p/1089533/1631113.a...

Using a stored Procedure to insert a new record
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in VB.net code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert ...

Error when attempting to insert record using stored procedure
I am trying to save user data to a sql table but keep getting the following error; Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows; Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, ...

Problem with inserting a Record using textbox and stored procedure
Below is the code for a form with several textboxes that I would like to use the values from to insert a record using a stored procedure. I am not having any sucess. After entering all the values and clicking the submit button no record gets entered. Any help would be greatly appreciated. Thanks in advance Gary <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Register_Click(ByVal sender A...

Inserting a new record from DetailsView using a stored procedure
I am using an <asp:DetailsView ...> on my <asp:Content ...> page.I have the AutoGenerateInsertButton="True".The DataSourceID for the DetailsView is a SQLDataSource.My Insert command uses a stored procedure that takes the fields a input parameters:   InsertCommand="AddMovie" InsertCommandType="StoredProcedure"I am using a stored procedure to do add a record to my table.My question is: After I click on the "New" button and add data to the DetailsView, how do the values entered get to my stored procedure? What am I missing here? ChuckChuck Ops, my stored procedure ...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Please help want to use the stored procedure to insert record to table
Please help, first time trying to use the stored procedure. Can you please modify my code below which is in asp.net to insert the record's in table(tbl_labels) using my stored procedure, i don't have problem inserting records, but i want to use my stored procedure to insert the record. Please help I never used a stored procedure. ********** CREATE PROCEDURE sp_insert_label ( @engl nvarchar, @espl nvarchar, @frlbl nvarchar, @gerlbl nvarchar ) AS INSERT INTO tbl_labels ( eng_lbl, esp_lbl, fr_lbl, ger_lbl ) VALUES ( @engl, @espl...

View Inserted Record Using Object Datasource, Details View and Stored Procedure
I am trying to display the record just inserted into a database using an object datasource, a stored procedure and a details view Below is the stored procedureALTER PROCEDURE [dbo].[Employees_Insert]( @LastName varchar(50), @FirstName varchar(30) = null, @JobTitle varchar(50))AS SET NOCOUNT OFF;INSERT INTO [dbo].[Employees] ([LastName], [FirstName], [JobTitle])VALUES (@LastName, @FirstName, @JobTitle);  SELECT  LastName, FirstName, JobTitleFROM Employees WHERE (EmployeeID = SCOPE_IDENTITY()) ________________________________________________________________...

Getting double record insertion using PD, ASA6, ODBC and stored procedures.
This is a multi-part message in MIME format. --------------7225442257FA38DBB2196E0F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I am calling a stored procedure from a PD app which always yields a double record insertion into the db, when it should only insert one row. The same stored procedure works fine from "Interactive SQL". This stored procedure simple inserts a row using an auto incremented column and also inserts a time stamp as well. It was suggested that this may be an ODBC issue however I did not find any evidence to support ...

Problem with changing Insert to use stored procedure (insert event fired but Insert not exec)
Hi all, I'm got the club and didn't like that it required full rad and write access the clubsite db.  First thing I did was change the DB to match standards that I'm use to and create stored procedures for everything.   So now that I've done that I can't get my code to work with the insert.  I know the insert event is fired because if I change the name of the page in the insert event code is goes to that page.  I also know that the insert stored procedure is not being run because sql profiler is not showing it (only the select from the following page).  Below is t...

Debug stored procedure that uses comma delimited list to insert multiple records
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correc...

Insert multiple records using one stored procedure call. SQL SERVER 7
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?   Thanks, GaryGary Gary, I'm not clear on exactly what you're asking. 28 dropdowns equates to 28 parameters in your stored procedure if you're just inserting a record. Can you provide more details? BruceBrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. Ok Chuck, never mind about the dropdowns. All Im ask is how to insert multiple records into a database u...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

how to excute stored procedure that insert record and return last inserted value
Dear all, I am using C# , asp.net and sql server 2005. Let me explain the situation. I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can Get last inserted variable values       Please help  thanks    I'm not sure I'm following completely.  If you are inserting data into SQL via this stored procedure, I'm assuming the data to be inserted is passed to the SP as SQL parameters.  Are you saying you want...

Web resources about - Using stored procedures to insert recorders - asp.net.component-discussions

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 11/25/2015 3:30:38 PM