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 into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID) Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)
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, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString End Function Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process. Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help. Thanks, Jaime
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
Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .
I have three tables in this shipping scenario -- one to record the Sender's Info, one to record the Recipient's Info, and one to record the Shipment Info, and I perform the inserts in that order. I want to get the last inserted USERID (which is an INT, and the PK) from both the Sender's table and the Recipient's table so that I can record that info as a foreign key in my Shipment table. Everything compiles and runs. However, every time I run it, "2" is inserted in my foreign key columns in the Shipment Info table. I can't figure out where that 2 is coming from. So I analyzed the SP in query analyzer, and when I run it it shows: "(0 row(s) returned)@RETURN_VALUE=0" Can anyone help me with this? I'm so close I think: First, my SP's: *********************************************************** ALTER PROCEDURE dbo.INSERT_NEW_SENDER(@firstname nvarchar(50), @lastname nvarchar(50), @email nvarchar(50),@phone nvarchar(50))ASBEGIN TRANSACTIONDECLARE @NewID intSET NOCOUNT ONINSERT INTO SHP_USER(email, firstname, lastname, phone) VALUES(@email, @firstname, @lastname, @phone)SET @NewID = CONVERT(int, SCOPE_IDENTITY())COMMIT TRANSACTIONGO ************************************************************** ALTER PROCEDURE dbo.SP_INSERT_NEW_RECIPIENT(@firstname nvarchar(50),@lastname nvarchar(50),@company nvarchar(50),@division nvarchar(50),@address1 nvarchar(50),@address2 nvarchar(50),@city nvarchar (50),@state nvarchar (50),@zip nvarchar (50),@country nvarchar(50),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(50),@address_type nvarchar(50),@recipient_type nvarchar(50),@NewID int OUTPUT)AS/* SET NOCOUNT ON */ INSERT INTO SHP_recipient(firstname, lastname, company, division, address1, address2, city, state, zip, country, phone, fax, email, address_type, recipient_type) VALUES(@firstname, @lastname, @company, @division, @address1, @address2, @city, @state, @zip, @country, @phone, @fax, @email, @address_type, @recipient_type)SET @NewID = CONVERT(int, SCOPE_IDENTITY())RETURN ********************************************************************** And my .NET stuff: //NEW USER INSERT SqlCommand myCommand; SqlDataReader myReader; int sender_userid = 0; int recipient_userid = 0; //int NewID; myCommand = new SqlCommand("INSERT_NEW_SENDER", conn); myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@firstname", str_firstname)); myCommand.Parameters.Add(new SqlParameter("@lastname", str_lastname));myCommand.Parameters.Add(new SqlParameter("@phone", str_phone)); myCommand.Parameters.Add(new SqlParameter("@email", str_email));myCommand.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand.UpdatedRowSource = UpdateRowSource.OutputParameters; myCommand.Connection.Open(); myReader = myCommand.ExecuteReader();sender_userid = (int)myCommand.Parameters["@NewID"].Value; myCommand.Connection.Close(); //RECIPIENT INSERT SqlCommand myCommand2; SqlDataReader myReader2; myCommand2 = new SqlCommand("SP_INSERT_NEW_RECIPIENT", conn); myCommand2.CommandType = CommandType.StoredProcedure; //myCommand2.Parameters.Add(new SqlParameter("@userid", recipient_userid));myCommand2.Parameters.Add(new SqlParameter("@firstname", str_recip_firstname)); myCommand2.Parameters.Add(new SqlParameter("@lastname", str_recip_lastname));myCommand2.Parameters.Add(new SqlParameter("@company", str_company)); myCommand2.Parameters.Add(new SqlParameter("@division", str_division));myCommand2.Parameters.Add(new SqlParameter("@address1", str_recip_addr1)); myCommand2.Parameters.Add(new SqlParameter("@address2", str_recip_addr2));myCommand2.Parameters.Add(new SqlParameter("@city", str_city)); myCommand2.Parameters.Add(new SqlParameter("@state", str_state));myCommand2.Parameters.Add(new SqlParameter("@zip", str_zip)); myCommand2.Parameters.Add(new SqlParameter("@country", str_country));myCommand2.Parameters.Add(new SqlParameter("@phone", str_recip_phone)); myCommand2.Parameters.Add(new SqlParameter("@fax", str_fax));myCommand2.Parameters.Add(new SqlParameter("@email", str_recip_email)); myCommand2.Parameters.Add(new SqlParameter("@recipient_type", str_recipient_type));myCommand2.Parameters.Add(new SqlParameter("@address_type", str_Address_Type)); myCommand2.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand2.UpdatedRowSource = UpdateRowSource.OutputParameters; myCommand2.Connection.Open(); myReader2 = myCommand2.ExecuteReader();recipient_userid = (int)myCommand2.Parameters["@NewID"].Value; myCommand2.Connection.Close(); try { conn.Open(); //INSERT data into the SHP_SHIPMENTS tableSqlCommand cmd = new SqlCommand("INSERT INTO SHP_SHIPMENTS(sender, recipient, ship_method, rma, payment_method, must_pay, project, business_unity, office, attachments, shipment_description,shipment_notes,date_created) VALUES(@sender_userid, @recipient_userid, @ship_method,@rma,@payment_method,@must_pay,@project,@business_unity,@office,@attachments,@shipment_description,@shipment_notes, GetDate())", conn); cmd.Parameters.Add(new SqlParameter("@sender_userid", sender_userid)); cmd.Parameters.Add(new SqlParameter("@recipient_userid", recipient_userid));cmd.Parameters.Add(new SqlParameter("@ship_method", str_ship_method)); cmd.Parameters.Add(new SqlParameter("@rma", str_rma));cmd.Parameters.Add(new SqlParameter("@payment_method", str_payment_method)); cmd.Parameters.Add(new SqlParameter("@must_pay", str_must_pay));cmd.Parameters.Add(new SqlParameter("@project", str_project)); cmd.Parameters.Add(new SqlParameter("@business_unity", str_business_unit));cmd.Parameters.Add(new SqlParameter("@office", str_office)); cmd.Parameters.Add(new SqlParameter("@attachments", str_attachments));cmd.Parameters.Add(new SqlParameter("@shipment_description", str_shipment_description));cmd.Parameters.Add(new SqlParameter("@shipment_notes", str_shipment_notes)); cmd.ExecuteNonQuery();
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement. Alternatively, is there a more elgant approach that will return the same set of recordsets? Any help would be much appreciatedThanks ALTER PROCEDURE dbo.OPA_GetMenuItemsASDeclare @i tinyint ,@tc tinyintSet @i = 1 /* Select for top level menu items*/ SELECT id, label, url, sortFROM mainNavORDER BY sort Set @tc = @@rowcount while @i <= @tc beginSet @i = (@i + 1) /* Select for submenu itemsSELECT id, label, url, sort, mainNavIdFROM SubNavWHERE (mainNavId = @i)ORDER BY mainNavId, sortend RETURN
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciated Thanks
ALTER PROCEDURE dbo.OPA_GetMenuItems AS Declare @i tinyint , @tc tinyint Set @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sort FROM mainNav ORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
begin Set @i = (@i + 1)
/* Select for submenu items*/ SELECT id, label, url, sort, mainNavId FROM SubNav WHERE (mainNavId = @i) ORDER BY mainNavId, sort end
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String
'Get the users IP address strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
I can not get this stored procedure to delete my records...
I have a contact table RecordID FirstName LastName etc
and a Address table
RecordID Street Zip Town Country
And a Relation table
RecordID ContactID AddressID CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name], qs.last_execution_time, qs.execution_count AS [Execution Count] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = DB_ID() AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
Hi, I am having trouble inserting 2 fields in a row using a stored procedure. This works fine: Exec ('Insert Into NumbersPull (Number)'+ @SQL) but when I try to insert another value into field 2 it errors out: I try this: Exec ('Insert Into NumbersPull (Number,resultID) Select ('+ @SQL + '),' + @resultID' ) and get this error: ERROR: Line 2: Incorrect syntax near ')'. Thanks, Doug
Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine. My StoredProcedure : CREATE PROCEDURE insert_test @id int ,@Rubrik char(25), @Info char(60) , @Datum datetime ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO The StoredProcedure works fine in the SQL Query Analyzer
My Code; int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'"); SqlCommand myCommand = new SqlCommand("insrt_test", myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now ); Even tried AddWithValues Dont get any error.....
OK I have a stored procedure that inserts information into a database table. Here is what I have so far: I think I have the proper syntax for inserting everything, but I am having problems with two colums. I have Active column which has the bit data type and a Notify column which is also a bit datatype. If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns. I tried using the set command, but it will give me a xyntax error implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:xxxxxxxx 7 -- Create date: 10/31/07 8 -- Description:Insert information into Registration table 9 -- ============================================= 10 ALTER PROCEDURE [dbo].[InsertRegistration] 11 12 @Name nvarchar(50), 13 @StreetAddress nchar(20), 14 @City nchar(10), 15 @State nchar(10), 16 @ZipCode tinyint, 17 @PhoneNumber nchar(20), 18 @DateOfBirth smalldatetime, 19 @EmailAddress nchar(20), 20 @Gender nchar(10), 21 @Notify bit 22 23 AS 24 BEGIN 25 -- SET NOCOUNT ON added to prevent extra result sets from 26 -- interfering with SELECT statements. 27 SET NOCOUNT ON; 28 29 INSERT INTO Registration 30 31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify) 32 33 VALUES 34 35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify) 36 37 --SET 38 --Active = 1 39 40 END 41 GO
I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below ALTER PROCEDURE [dbo].[AddNewFundAllocation] @Payment_ID Int,@Receipt_ID Int,@Fund_ID Int,@Amount_allocated money,@DateEntered datetime,@EnteredBy nvarchar(50)ASSELECT (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_paymentsFROM tblReceiptsFunds INNER JOIN tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@Payment_IDIF (total_allocations<total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)
ELSE BEGIN PRINT 'You are attempting to allocate more to funds than your total payment.' END I get the following error when I try and save the stored procedure: Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_allocations'. Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_payments'.
I'm trying to insert the details in the "registration form" using stored procedure to my table. My stored procedure is correct, but I dunno what is wrong in my code or I dunno whether I've written correct code. My code is below. Please let me know what is wrong in my code or my code is itself wrong..... protected void RegisterSubmitButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds;Integrated Security=SSPI"); SqlCommand cmd; cmd = new SqlCommand("registeruser", conn); SqlParameter par = null; par= cmd.Parameters.Add("@fname", SqlDbType.VarChar, 30); par.Value = RegisterFirstNameTextBox; par = cmd.Parameters.Add("@lname", SqlDbType.VarChar, 30); par.Value = RegisterLastNameTextBox; par = cmd.Parameters.Add("@uname", SqlDbType.VarChar, 30); par.Value = RegisterUserNameTextBox; par = cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 20); par.Value = RegisterPasswordTextBox; par = cmd.Parameters.Add("@email", SqlDbType.VarChar, 40); par.Value = RegisterEmailAddressTextBox; par = cmd.Parameters.Add("@secque", SqlDbType.VarChar, 50); par.Value = RegisterSecurityQuestionDropDownList; par = cmd.Parameters.Add("@secans", SqlDbType.VarChar, 40); par.Value = RegisterSecurityAnswerTextBox; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("Execption adding account. " + ex.Message); } finally { conn.Close(); } }
Hi, I have two tables "people' and "dept". What I need is a stored procedure to insert into both tables. I need first, insert into "people" table, and then, insert into "dept" table since the first insert returns people id(peo_id), which is an input parameter for "dept" table. Here is my stored procedure, but I got error:Create PROCEDURE [dbo].[insert_people] @peo_last_name varchar(35), @peo_mid_initial varchar(1) = null,@peo_first_name varchar(10), @peo_address1 varchar(50) = null, @peo_city varchar(30) = null, @peo_state varchar(2) = null, @peo_zip varchar(10) = null, @peo_ph1 varchar(30) = null, @peo_ph2 varchar(30) = null, @peo_email varchar(40) = null, @dept_id int, @peo_id int
AS SET @peo_id = (INSERT INTO people (peo_last_name, peo_mid_initial, peo_first_name, peo_address1, peo_city, peo_state, peo_zip, peo_ph1, peo_ph2, peo_email) VALUES (@peo_last_name, @peo_mid_initial, @peo_first_name, @peo_address1, @peo_city, @peo_state, @peo_zip, @peo_ph1, @peo_ph2, @peo_email)) INSERT INTO dept (dept_id, peo_id) VALUES (@dept_id, @peo_id) GO Could somebody help out? Thanks a lot!
I have a table with UserID, UserName, UserPassword I have a stored procedure as follows:ALTER PROCEDURE UserInsert @UserName varchar(50), @UserPassword varchar(50) AS BEGIN INSERT Users (UserName, UserPassword) VALUES (@UserName, @UserPassword) END I have a GridView bound to the Users Table and seperate textboxes (UserName, UserPassword) on a webform. Couple of Questions... 1. how and/or what code do I use to execute the Stored Procedure to insert what is in the textboxes into the Table using a button click event? 2. Since UserID is autogenerated on new records....does UserID need to be in the code? Many Thanks
Hi, I need to insert a new user if the user (user_login) does not exist in the table (abcd_user) using a stored procedure. I created a stored procedure called "insert_into_abcd_user". Here is the complete strored procedure... CREATE PROCEDURE [dbo].[insert_into_abcd_user] ( @first_name [VARCHAR](30), @last_name [VARCHAR](30), @email [VARCHAR](60), @user_login [VARCHAR](50)) AS INSERT INTO [dbo].[abcd_USER] ([first_name], [last_name], , [user_login])VALUES (@first_name, @last_name, @email, @user_login) I need to to insert a new user if the user (user_login) does not exist int the table (abcd_User). Any one shade on my code? I appreciate your help in advance.
INSERT INTO Table_1 SELECT Source_Table.Field_1, Source_Table.Field_2, Source_Table.Field_3, ???? OUTPUT parameter returnet from a stored procedure ??????? FROM Source_Table
I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:- //BEGIN ALTER PROCEDURE [dbo].[pendingcol] @cuser varchar(100) AS Declare @sqls nvarchar(1000)
This is the code i am using to call my stored procedure using VB.NET:- //BEGIN 'variables Dim user As String user = Profile.UserName
'connection settings Dim cs As String cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True" Dim scn As New SqlConnection(cs)
'parameters Dim cmd As New SqlCommand("pendingcol", scn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000) cmd.Parameters("@cuser").Value = user
'execute scn.Open()
cmd.ExecuteNonQuery()
scn.Close() //END
Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says " The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "
As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!
Can anyone please enlighten me on where i may be going wrong? Cheers
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Hello, I have created a web page with a FormView that allows me to add and edit data in my database. I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements. I've created a stored procedure to do the INSERT and return to new identity value. My question is this: Can I configure the control to use this stored procedure? If so, how? Or do I have to write some code for one of the event handlers (inserting, updating???) Any help would be appreciated. -brian
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
I'm doing this more as a learning exercise than anything else. I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key. If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr
In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl? Any help here is appreciated.
