Is It Easy To Amend An Insert Statement.
Jul 30, 2007
I am trying to amend an SQL insert statement but it is not working.
It is as follows can anyone tell me what i am doing wrong.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" DeleteCommand="DELETE FROM [useridTest] WHERE [userID] = @original_userID AND [userName] = @original_userName AND [listCopy] = @original_listCopy"
InsertCommand="INSERT INTO [useridTest] ([userID], [userName], [listCopy]) VALUES (<% user.ProviderUserKey.ToString() %>, @userName, @listCopy)"
View 1 Replies
ADVERTISEMENT
Jan 8, 2007
I'm trying to group ages in one report, and Case numbers in another. Both are in string datatype, and require more then 3 choices. Im not keen on coding, so i was wondering, how do you make an IFF statement with more then 3 choices, i need about 7, maybe even a little more. *this is for a column grouping in a matrix.
Here's what i have right now...It works..but i need to add more years.
=iif(left(Fields!ICNNo.Value, 1) = "4", "2004",
iif(left(Fields!ICNNo.Value, 1) = "5", "2005",
iif(left(Fields!ICNNo.Value, 1) = "6", "2006", "Unknown")))
Please help! thanks!
View 10 Replies
View Related
May 21, 2008
myCommand2.CommandText = "INSERT INTO testimonials(name,email,testimonial,approved,time) VALUES ('" + exp.escString(txtName.Text) + "','" + exp.escString(txtEmail.Text) + "','" + txtTestimonial.Text + "','" + false + "','" + DateTime.Now + "')"; ok the form has a field txtTestimonial.Text these strings work when i submit them thoughthats all folksthat"s (double quotes) that\"sthat\"s these failthat's that's that\'s that\'s tried up to 7 just to make sure Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'. I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind thatI tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess. Any help would be greatly appreciated. Jim
View 5 Replies
View Related
Mar 12, 2005
Im starting to learn SQL at school !!
and would like to do something like this. !! but i just cant figure that out.
i have a table that containes all my categories in it. And as you can see i have "PARENT" and "CHILD" categories.
I would like to get something like this by using a simple SELECT statement !! is it possible...
===============
=#1 PARENT ID NAME=
===============
=#1 CHILD NAME HERE
=#2 CHILD NAME HERE
=#3 CHILD NAME HERE
===============
=#2 PARENT ID NAME=
===============
=#1 CHILD NAME HERE
=#2 CHILD NAME HERE
=#3 CHILD NAME HERE
im not trying to show this in my Query Analyser but on a Web Page by using a datagrid.
View 1 Replies
View Related
Jul 20, 2005
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies
View Related
Feb 7, 2008
Hi, I was handed an old application written for sql05, it has this (kind of) statement:
Dim CommandText As String = "SELECT c.name FROM db1 c LEFT JOIN "
CommandText += "db2.dbo.Users u ON u.iKey = c.key "
CommandText += "WHERE u.cUserName = '" & User & "'"
As you can see, there are two databases, db1 and db2 and this worked fine in sql05, but when I convert each of the databases to sqlexpress, I get this error: Invalid object name 'db2.dbo.Users'.
So how can I make this work in sqlexpress? Are queries across databases not allowed?
Thanks so much for your help!
View 3 Replies
View Related
Jan 28, 2008
Please could someone tell me how to modify a sp in 2005. I r/h click on the sp, choose modify and do my changes, but when I save it it saves it under a different name.......
View 8 Replies
View Related
Dec 10, 2007
Hi,
I have a SQL database consisting of tables and views. As there as remote users, I am not able to directly amend their databases, so need to do it through my VS application.
If I want to amend one of the tables, I do it like this:
=============================================
Dim connectionstring As String = My.Settings.ConnectionString
Dim eSQL As String = ""
Using connection As New System.Data.SqlClient.SqlConnection(connectionstring)
Dim cmd As New System.Data.SqlClient.SqlCommand(eSQL, connection)
connection.Open()
eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND COLUMN_NAME = 'Company' ) " _
& "BEGIN " _
& "ALTER TABLE [Client] " _
& "ADD Company int " _
& "END "
cmd.CommandText = eSQL
cmd.ExecuteNonQuery()
End Using
=============================================
However I've now come to a point where I have no option other than to change a view (I need an extra field in it). Is there any way I can do this programmatically?
The view looks like this in SQLMSE:
====================================
SELECT dbo.Client.ID, dbo.Client.Title, dbo.Client.First, dbo.Client.Last, dbo.Client.Street, dbo.Client.Town, dbo.Client.Postcode, dbo.Client.CleanerID,
dbo.Client.Cleaner, dbo.Client.Status, dbo.Client.StatusDate, dbo.Cleaner.Tel, dbo.Cleaner.Mobile, dbo.Cleaner.WorkPhone, dbo.Client.hours,
dbo.Client.frequency, dbo.Client.BlitzDate, dbo.Client.RegStart, dbo.Client.interviewdate, dbo.Client.PayStatus
FROM dbo.Client INNER JOIN
dbo.Cleaner ON dbo.Client.CleanerID = dbo.Cleaner.ID
====================================
I just want to add an extra field to the select list.
Thanks
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Nov 20, 2007
Hi,
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
Any help would be much appreciated.
Thanks in advance,
Paul
View 3 Replies
View Related
Jul 20, 2005
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies
View Related
May 1, 2007
I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word
View 3 Replies
View Related
Jan 4, 2008
I have put together the following code, but am getting a syntax error on the Insert Into statement. Could someone kindly tell me what I am doing wrong? Thanks 1 Protected Sub SubmitButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton2.Click2 authAndSaveToDB()3 End Sub4 Private Sub authAndSaveToDB()5 On Error GoTo errhand6 If connectToDB() Then 'connection to database was successful7 'insert candidate in the database
8 insertCandidate(ddlPositions.Text, txtName.Text, txtBio.Text, conn)9 Response.Redirect("FileUpload.aspx")10
11 Else
12 Response.Write("<font color = red><strong>Database connection failure. Please try again in a few minutes or contact system administrator.</strong></font>")13 End If
14 closeDB() 'end the connection to the database
15 Exit Sub
16 errhand:17 Response.Write("<br>ERROR: " & Err.Description & "<br>")18 End Sub19 Private Sub insertCandidate(ByVal strPositions As String, ByVal strCandidate As String, ByVal strBiography As String, ByVal myconn As OleDb.OleDbConnection)20 Dim cmd As OleDb.OleDbCommand21 Dim strSQL As String
22
23 strSQL = "INSERT INTO Position (Positions,Candidate,Biography) VALUES ('" & _
24 strPositions & "'," & _
25 "'" & strCandidate & "'," & _
26 "'" & strBiography & "')"
27 cmd = New OleDb.OleDbCommand(strSQL, myconn)28 cmd.ExecuteNonQuery()29 End Sub30 End Class
View 16 Replies
View Related
Jan 31, 2008
Hi, I am retriving the value time from my database and i have the convert function on it CONVERT (varchar, Time, 8)
When i edit this and try and insert it back to the database i am losing the information. I am not sure how the insert statement shoulld read in order to do this.
Thanks Mike
View 8 Replies
View Related
Feb 14, 2008
Hi forum, currently I insert a mobile phone number into colomn OK but what I need to do is add +61 as a prefix.Users provide 055 555 555, I want to store number as +61 55 555 555 (note this also drops the 0) my SQL skills a little lacking so appreciate all good info, cheers P
View 2 Replies
View Related
Apr 3, 2004
Hi Everyone
I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.
Here is my insert:
Sub Add_Click(sender as object, e as EventArgs)
Dim conBooks As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conBooks = New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
strInsert = "INSERT INTO dbo.Books (ISBN, BookTitle, AuthorID, AuthorType, author2, author3, Series, VolNo, Synopsis, PubYear, PubDate) VALUES (@ISBN, @BookTitle, @AuthorID, @AuthorType, @author2, @author3, @Series, @VolNo, @Synopsis, @PubYear, @PubDate )"
cmdInsert = New SqlCommand( strInsert, conBooks )
' Add Parameters
cmdInsert.Parameters.Add( "@ISBN", SqlDbType.nvarchar).Value = txtISBN.Text
cmdInsert.Parameters.Add( "@BookTitle", SqlDbType.nvarchar).Value = title.Text
cmdInsert.Parameters.Add( "@AuthorID", SqlDbType.int).Value = AuthorID.SelectedItem.Value
cmdInsert.Parameters.Add( "@AuthorType", SqlDbType.char).Value = AuthorType.SelectedItem.Value
cmdInsert.Parameters.Add( "@author2", SqlDbType.int).Value = author2.SelectedItem.Value
cmdInsert.Parameters.Add( "@author3", SqlDbType.int).Value = Author3.SelectedItem.Value
cmdInsert.Parameters.Add( "@Series", SqlDbType.nvarchar).Value = Series.SelectedItem.Value
cmdInsert.Parameters.Add( "@VolNo", SqlDbType.int).Value = volumeno.Text
cmdInsert.Parameters.Add( "@Synopsis", SqlDbType.ntext).Value = txtSynopsis.Text
cmdInsert.Parameters.Add( "@PubYear", SqlDbType.bigint).Value = pubyear.Text
cmdInsert.Parameters.Add( "@PubDate", SqlDbType.nvarchar).Value = PubDate.Text
conBooks.Open()
cmdInsert.ExecuteNonQuery()
conBooks.Close()
Response.Redirect("/admin/default.aspx")
End Sub
View 4 Replies
View Related
Feb 13, 2006
Hi All,
I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1.
Here is my code...
insert into table1 (first_name, last_name, user_login, email, organization_name) values (select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID))
When I excute the code, I am getting the following error.....
Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.Server: Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near ')'.
But, If execute the following code....
select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3 wepsUsers where ru.id = wepsUsers.user_id and not exists (select user_login from table1 where user_login = ru.UserID).... I am able to see the values.
Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.
Thanks.
Srinivas.
View 3 Replies
View Related
Mar 27, 2001
Hi, Every body,
Is there anyway that we can use insert into statement to insert data into the first row of a table, or initialize the column with the new inserted data.
Thanks in Advance,
View 4 Replies
View Related
Nov 2, 2000
I am trying to do an insert in a particular View...one of the field has got an 'Apostrophe'.(Ex: SQl Server's).
When i do this i get an error. the system does not allow me to insert....
is there any way i can do an Insert with that...???
Thanks
View 2 Replies
View Related
Feb 2, 2001
How can you insert this statement in a table using Query Analyzer.
(today's date)as single statement.
Thank You,
John
View 1 Replies
View Related
Nov 17, 2005
How do I
1.) take two seperate columns and combine the data
2.) take that combined data and populate a third column.
Here is an example of what I've been trying to do:
Insert INTO Table_1 (Column1)
Select Column2 + Column3
From Table_1
I continue to get this error message:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column_2', table 'DB1.dbo.Table_1'; column does not allow nulls. INSERT fails.
The statement has been terminated.
If some one could please give me a general script that could do this I would appreciate it. I'm new to using SQL and I'm a little bit lost.
Thanks.
View 4 Replies
View Related
Sep 29, 2004
I need an insert statement that will grab the maximum of TimeGenerated from the Events table and insert it into LastEventTime in the Host Table. But it has to be inserted based on the relationship of SystemNetName.
For Example,
Host Table
--------------------------------------
SystemNetName LastEventTime
Tweety 9/24/2004
Sylvester 9/25/2004
Events Table
----------------------------------------
SystemNetName TimeGenerated
Tweety 9/24/2004
Tweety 8/26/2004
Sylvester 8/21/2004
Sylvester 9/25/2004
Sylvester 7/5/2004
This is what I had so far but didn't work:
Insert into host(LastEventTime)
Select(Max(TimeGenerated) ) from Events Where
Events.SystemNetName = Host.SystemNetName
Any Ideas?
View 5 Replies
View Related
Aug 20, 2004
Is is possible to use a Where Clause in a Insert statement in MS SQL? I keep getting a syntax error near 'Where'. Is there something wrong with this?
Insert INTO CPU (CPUNumber, Name, MaxClockSpeed, SystemNetName) Values('CPU2', 'Intel', '2392', 'TWEETY') Where 'CPU2' NOT IN (Select CPUNumber from CPU)
I want to insert into the cpu table as long as what I am inserting is not already in the table.
Desparate.
Thanks,
Laura
View 3 Replies
View Related
Jun 22, 2004
Can someone help me parse this oracle insert statement into SQL Server please?
insert into notify_template (subject, body, priority)
values
(
'
Budgeted time exceeded for task [18040]
',
'
Actual Time exceeds Budgeted time for the following task'
|| chr(10) || chr(10) ||
'Task Name : [18040]
Root Program Name : [18049]
Actual Days : [2000000100]
Budgeted Days : [2000000101]
Estimated Time : [2000000102]'
|| chr(10) || chr(10) ||
' The following resource(s) in the above task have exceeded the budgeted time:'
|| chr(10) || chr(10) ||
' [2000000784]'
|| chr(10) || chr(10) ||
'Click here : [12865]
',
2)
View 1 Replies
View Related
Jul 16, 2006
hi all
i am writing a trigger that inserts from one table to the next. i have an issue with the table being inserted into have 2 more columns than the one being inserted from. Here is the trigger just in case
CREATE TRIGGER [Insert40801] ON [dbo].[tPA10801]
FOR INSERT
AS
insert into tPA40801
select
intTimesheetKey,
chrTimesheetNumber,
TranID,
intEmployeeKey,
intEmployeeDivKey,
BatchKey,
dtePeriodStartDate,
dtePeriodEndDate,
chrStatus1,
numTotalHrsWorked,
numTotalHrsBilled,
numTotalCosts,
numTotalCharges,
numTotalRecover,
chrSignedID,
dteSignedDate,
chrApprovalID,
dteApprovalDate,
intJobKey,
intPhaseKey,
intTaskKey,
dteDate,
siCstClsificatnDDL,
UpdateCounter,
CompanyID
from tpa10801
now tpa40801 has two extra columns that tpa10801 doesnt. how would i please the sql gods and get the insert statement running? thanks alot
tibor
View 2 Replies
View Related
Sep 22, 2006
I need to insert over 600 similar records in a table. The only way I know is to write insert into <table> command 600 times. Is there another way which is easier?
Thanks in advance,
Saurav
View 13 Replies
View Related
Mar 25, 2007
my insert statement got no error but when i check my table the values were not inserted actually.. what maybe the problem?
View 12 Replies
View Related
Apr 10, 2007
Does anyone see anything wrong with this Insert
INSERT INTO dbo.Report
(LossType, Loss, Area)
SELECT [Loss Type], Loss, Area
FROM dbo.[Main Table]
I keep getting does not alow nulls insert fails
this is the Report Table
CREATE TABLE [Report] (
[Incident Report No] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[Investigators Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reported by] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Law Enforcement Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of Officer] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Evidence Seized] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associated Reports] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Corrective Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
[TypeOfIncident] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
[86_D] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
[LossType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action/Incident] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[Incident Report No]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
and the [Main Table]
CREATE TABLE [Main Table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [int] NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [int] NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Main Table] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
View 3 Replies
View Related
