I'm trying to do a basic update query which is working on other pages but not on this page. Dim uid As Integer = CInt(Session("uid"))
Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn)
With cmd.Parameters
cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text)
cmd.Parameters.AddWithValue("@uid", uid)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.
So it doesn't seem to read my updated textfield or something im clueless.
Hi Guys, I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT Quantity, CAST(Gallons AS VARCHAR(10)) + ' ' + Style, Colour FROM ORDER_TANK_LINK LEFT JOIN TANKS ON TANKS.TankID = ORDER_TANK_LINK.TankID LEFT JOIN COLOURS ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID This One is Broken:SELECT Quantity, CAST(Gallons AS VARCHAR(10)) + ' ' + Style, Colour FROM ORDER_TANK_LINK ON ORDER_TANK_LINK.OrderID = 1 LEFT JOIN TANKS ON TANKS.TankID = ORDER_TANK_LINK.TankID LEFT JOIN COLOURS ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it. The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int. Thanks in advance!
Is it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation the worst software documentation I have EVER SEEN?! Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it. Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.
SELECT * FROM dbo.wce_contact WHERE (Mail1Date IS NOT NULL) AND (Mail2Date IS NULL) AND (Mail3Date IS NULL) AND (Mail4Date IS NULL) AND (Mail5Date IS NULL) AND (Mail6Date IS NULL) AND (Mail7Date IS NULL) AND (Mail8Date IS NULL) AND (Mail9Date IS NULL) AND (Mail10Date IS NULL) AND (Mail11Date IS NULL) AND (Mail12Date IS NULL) AND (Mail14Date IS NULL) AND (Mail15Date IS NULL) AND (Mail16Date IS NULL)
or(IDStatus LIKE '') or (NOT (Task LIKE '%x%')) or (ExpressEmail LIKE '%@%') or (WebSite NOT LIKE '') or (Unsubscribe = 0) or (Artwork LIKE '')
Basically the parts in red work best when they use the 'or' criteria.
My problem.
I run the query together black+red and get a different result than expected. Is there a way of saying 'do the black part first and its an imperative that the red part follows those rules on the black part of the query?' eg:
IDStatus must be LIKE '' when running the black part of the query and expressemail must be like '%@%' on the black part of the query but not on red part of the query. So it doesnt matter that the expressemail must have idstatus like '' whilst having an @
When I try to add a parameter called findby to the order by part of a query like this: dim q1 as string="select store+' '+customer+' '+left(customer,len(customer))" q1=q1+"+replicate('.',30-len(customer))+' '+cdate as a" q1=q1+" from tblcustomers" q1=q1+" where store='65' and customer like @lookfor" 'eventually want @findby where this says customer q1=q1+" order by @findby" with command1.parameters: .Add(New SQLParameter("@lookfor", textbox1.text+"%")) .Add(New SQLParameter("@findby", dropdownlist2.text)) 'dropdownlist2.text="customer" which is the name of a column end with I get this server error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Are parameters not good for names of things in a query but ok for values of what those names represent? If not, what am I doing wrong? Thank you very much.
I am new to Parameterize query ... and i want to use above for inserting XML data and to retrive the same as it contains some special character so by using string it is changed.
can anyone show me where i've done wrong in my coding? because i can't seems to find the error. I've looked through forums and google but just can't understand what they are on about as i'm kind of a beginner. Please help me...thanks in advance...(i dont have any stored pocedure, just using a connectionstring called connectionstringnews)HERES THE ERRORParameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Parameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied.Source Error:
Line 37: txtMessage.Text)Line 38: con.Open()Line 39: cmd.ExecuteNonQuery()Line 40: con.Close()Line 41: 1 Imports System.Web.Configuration 2 Imports System.Data.SqlClient 3 Partial Class News_Articles_Default 4 Inherits System.Web.UI.Page 5 6 Protected Sub btnPost_Click( _ 7 ByVal sender As Object, _ 8 ByVal e As System.EventArgs) _ 9 Handles btnPost.Click 10 11 Dim cs As String 12 cs = WebConfigurationManager _ 13 .ConnectionStrings("ConnectionStringNews") _ 14 .ConnectionString 15 Dim insertNews As String 16 insertNews = "INSERT news " _ 17 + "(newsid, author, date, articles) " _ 18 + "VALUES(@newsid, @author, @date, @articles);" 19 20 Dim con As SqlConnection 21 con = New SqlConnection(cs) 22 Dim cmd As SqlCommand 23 cmd = New SqlCommand(insertNews, con) 24 25 Dim newsid As String 26 newsid = Request.QueryString("news") 27 28 cmd.CommandText = insertNews 29 cmd.Parameters.Clear() 30 cmd.Parameters.AddWithValue("newsid", _ 31 newsid) 32 cmd.Parameters.AddWithValue("author", _ 33 txtAuthor.Text) 34 cmd.Parameters.AddWithValue("date", _ 35 DateTime.Now) 36 cmd.Parameters.AddWithValue("articles", _ 37 txtMessage.Text) 38 con.Open() 39 cmd.ExecuteNonQuery() 40 con.Close() 41 42 End Sub 43 44 45 End Class 46
Hi, Below are two methods o passing a parameterized query, are these the same, or is one open to sql injection attacks more than the other?Option 1 - through code behindDim testDataSource As New SqlDataSource()testDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringName").ToString()testDataSource.UpdateCommandType = SqlDataSourceCommandType.TexttestDataSource.InsertCommand = "INSERT INTO test(id) VALUES (@id1)"testDataSource.InsertParameters.Add("@id1", TextBox1.Text) Option 2: through sqldatasource on page and control parameters<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"InsertCommand="INSERT INTO test(id) VALUES (@id1)" <InsertParameters><asp:ControlParameter ControlID="TextBox1" Name="id1" Type="Int32" /></InsertParameters></asp:SqlDataSource> Feedback would be great, thanks!
I am trying to use the following SQL query to return a set of values:SELECT id, submit_date, company_name, request_type, status FROM tblRequestForms WHERE request_type IN (@RequestType) AND status IN (@Status) ORDER BY id ASCI have tried passing an array of string values to both @RequestType and @Status, but It does not work. Is there any way to pass multiple values like this using parameters?
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.
Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?
I am having an issue with a Parameterized Query in Sql Ce 3.5 The Query resembles select * from sometable where ((ID = @someId) or (NAME like @someName))
The first part of the query runs fine, the second returns no results when it should.
What I was hoping is that there is some way to run a server trace against the SqlCe file to see the actual query that is ran with the params replaced.
Hi all, I am using the below parameterized query and get an error while executing it....can anyone please spot the error. Any help will be appreciated. I have gone cross-eyed now looking at it all day. The error I get it isParameterized Query '(@Re_UK_Eligible nvarchar(4000),@Re_Aus_Eligible nvarchar(33),@R' expects parameter @Re_JobType_Temp, which was not supplied. sqlStmt = "UPDATE Re_Users SET Re_UK_Eligible=@Re_UK_Eligible,Re_Aus_Eligible=@Re_Aus_Eligible,Re_Can_Eligible=@Re_Can_Eligible,Re_USA_Eligible=@Re_USA_Eligible,Re_Address1=@Re_Address1,Re_Address2=@Re_Address2,Re_Address3=@Re_Address3,Re_City=@Re_City,Re_Postcode=@Re_Postcode,Re_Country=@Re_Country,Re_Homephone=@Re_Homephone,Re_Mobile=@Re_Mobile,Re_JobType_Per=@Re_JobType_Per,Re_JobType_Temp=@Re_JobType_Temp,Re_JobType_Con=@Re_JobType_Con,Re_Hours_Full=@Re_Hours_Full,Re_Hours_Part=@Re_Hours_Part,Re_Sector=@Re_Sector,Re_StepTwoDone=1 WHERE Re_UserCount=" + Session["ReUserIdentity"]; cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReConnectionString"].ConnectionString); cmd = new SqlCommand(sqlStmt, cn); cmd.CommandType = CommandType.Text;
//Insert UK if (chkUK.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", DBNull.Value)); } if ((chkUK.Checked == true) && (UKRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", UKRadioButtonList.SelectedItem.Text)); }
//Insert AUS if (chkAUS.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", DBNull.Value)); } if ((chkAUS.Checked == true) && (AUSRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", AUSRadioButtonList.SelectedItem.Text)); }
//Insert CAN if ((chkCAN.Checked == false)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", DBNull.Value)); } if ((chkCAN.Checked == true) && (CANRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", CANRadioButtonList.SelectedItem.Text)); }
//Insert USA if (chkUSA.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", DBNull.Value)); } if ((chkUSA.Checked == true) && (USARadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", USARadioButtonList.SelectedItem.Text)); }
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader
public SqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd;
adapter.Fill(dataset); return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null.
If txtLink1 happens to be empty, I want @link1 to enter null. The column in the Sql Server allows for nulls, but I get an error message that says no value was supplied. In short, how do I supply a null value using a parameterized query?
#2: For debugging purposes, how can I view what my SQL string looks like (with all the values entered) before it gets submitted to the database? When I view the string, it still contains the placeholder values (@link1) instead of the actual values.
I have a parameterized query. The parameters contain data from my tables. Some of the parameters could include single quotes. The single quotes are wreaking havoc in my parameterized query. How can I replace single quotes with double quotes inside of my SQL stored procedure?
I know that it's something similar to REPLACE(@variablename, '''''', ''''''''), but I can't get the number of quotes right.
All of the examples that I am seeing are converting the quotes inside of an application. This is not an option for me, as I am calling this stored procedure from a SQL job that will run daily.
I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:
SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')
However, when I test this on my PDA, I get the following error:
SQL Execution Error.
Executed SQL statement: SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%') Error Source: SQL Server Mobile Edition ADO.NET Data Provider Error Message: @StreetName : deerbrook - FormatException
Does anyone know how to add wildcards to a parameter?
I am hoping someone could help me understand why this is happening and perhaps a solution. I am using ASP.NET 2.0 with a SQL 2005 database. In code behind, I am performing a query using a parameter as below: sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)" objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text)) The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query. The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way: sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text) I also get NO records retuned using the same criteria. What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning? Thanks!
Hi, Im struggling with this insert statement, I want to use with a AJAX validation Post Form page. Its quite straght forward, if a search query returns null the insert these values. The search query does work, what I mean by that is that txt field values seem to pass for search but not insert. Any help out there cheers Paul if (RowCount == 0) {String strSQL = "INSERT INTO Mail_List (FirstName, Email) VALUES( @FirstName, @Email )";
try {mySqlConn = new SqlConnection(strSqlConn); mySqlConn.Open();SqlCommand cmd = new SqlCommand(); cmd = new SqlCommand(strSQL, mySqlConn);cmd.Parameters.AddWithValue("@FirstName", Request.Form["FirstName"]);cmd.Parameters.AddWithValue("@Email", Request.Form["Email"]); cmd.ExecuteNonQuery(); lblStatus.Text = "Registration Successful"; }
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
joy mundy alluded in her webcast that it is possible to dynamically specify a table name in a parameterized ole db source query. is this true? if so, how can it be done?
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from (select * from `kcf`) as refTable where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?
I am using the procedure below :-
CREATE PROCEDURE mailtest AS
declare @sql varchar (255)
SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON
In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
We have a tree structure containing section names. Each node is a section name and each section can have subsections. I have to copy the tree structure but need to maintain the parent-child relationship established within the id / parent_id fields. How do i acheive this?For example i have the treeSection 1 |-Section 1.1Section 2 |-Section 2.1The "Section" table contains 3 fields: id, parent_id, and caption. ID is the identity of the section record and parent_id contains NULL or the ID of this record's parent to create a child. So "Section 1" (id=1, parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1" (id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).I would like to copy this sucture to create 4 new sections but they need to maintain their id/parent_id relationships BUT with new IDs. For this i created the following stored procedure:----------------CREATE PROCEDURE [dbo].[CopySection] AS -- Declare a temporary variable table for storing the sections DECLARE @tblSection TABLE ( id int, parent_id int, caption varchar(max), ) DECLARE @newAgendaID int, @newSectionID int; DECLARE @tid int, @tparent_id int, @tcaption varchar(max);BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Copy the desired sections into the local temp variable table INSERT INTO @tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id; -- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID DECLARE c1 CURSOR FOR SELECT * FROM @tblSection ORDER BY parent_id FOR UPDATE OF parent_id; OPEN c1; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; WHILE @@FETCH_STATUS = 0 BEGIN -- Insert the new Section and record the identity INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@tparent_id, @tcaption); SET @newSectionID = SCOPE_IDENTITY(); -- Update the temp variable table with the new identity from the newly created real section in tblSection -- Update all temp variable records to point to the new parent_id UPDATE @tblSection SET parent_id = @newSectionID WHERE parent_id = @tid; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; END CLOSE c1 DEALLOCATE c1 END----------------The critical "UPDATE @tblSection" part doesnt seem to update the temp variable table with the @newSectionID (the actual section identity obtained after inserting a real record into the tblSection table). So in the end the inserted records into tblSection still point to the incorrect parent_id instead of the copied record's parent_id.Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.
