MS SQL Server :: SELECT @@IDENTITY During INSERT STATEMENT Error

SELECT Insert Statement
Hello All,

INSERT INTO [SPIResConv5].[dbo].[Batch]
([BATCH_NO],
[OPENDT]
,[USERID]
,[MODULE]
,[USERBATCH]
,[RESORT_ID]
)
Select [BATCH_NO],
[OPENDT] = getdate(),
'Hwells' as [USER_ID],
'1 CASH RECEIPT' as [MODULE],
[USERBATCH],
Resort_ID = Case Resort_ID
when 2 then 'Ell'
when 3 then 'CSI'
when 12 then 'Ell2'
when 13 then 'ATR'
end
from TransactionTempToTransaction
where Resort_ID = 3 or Resort_ID = 2 or Resort_ID = 12 or Resort_ID = 13

needing help in modifing the statement above

The first would be to grap from the batch table the last [BATCH_NO] and add one number to it-and insert --just that number €“for all records imported to Batch Table. Example: last record was 50 then 51 is the batch for all recordes imported.

Second: [USERBATCH] is a varchar, but need to insert based on current date: Say for example -today 2/23/2007 but need to insert in format = '2/23 LBX'

Last and again thanks for the help-

Resort_ID-For the Batch table only need to import how many rows,
Based on how many different resorts or in the table TransactionTempToTransaction
For example-- if there are two diffrent resorts €“ only import two rows
With the last field Resort_id being the only difrrent field showing the resort_id. If six different resorts or in tabe TransactionTempToTransaction then six rows.

Thanks for your

JK

View Replies !

Select Statement Insert
I have a select statemnet that I want to insert new rows if the data is not found from table 1 into table 2. How can I add onto this statement? What is the sql code neede?

Select *
From tbl_Data_OpenOrders
WHERE EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND
tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND
tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE)

Lisa Jefferson

View Replies !

Insert From Parameters And Select Statement
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo

View Replies !

How To Use Select Statement In Insert Query
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this

View Replies !

Using A Select Statement To Only Insert In Certain Rows
Hey,

I am not sure how to really explain this, but I'll give it a try.

I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement:

SELECT Column1, Column2, Column3
FROM #Temp1

The result set is:

Column1---Column2---Column3
99--------6756756---55555
44--------55---------NULL

Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this:

Column1---Column2---Column3
NULL------NULL------55555
99--------6756756---NULL
44--------55---------NULL

Another example:

The returned result now is:

Column1---Column2---Column3---Column4
99---------6756756---55555-----NULL
42---------55---------NULL------12345

So I need:

Column1---Column2----Column3----Column4
NULL-------NULL-------55555------NULL
NULL-------NULL-------NULL-------12345
99---------6756756----NULL-------NULL
44---------55----------NULL-------NULL

Does this make sense, and/or is it even possible?

I know it could be more of a presentation thing, but I would like to know how to do it in the code behind.

Thanks

View Replies !

Insert..Select Statement Problem
Hi,

I am trying to run the following insert statement, but am gettng an error. The table I want to insert to
(DimensionMonthTime_hold) has an
Identity column defined for its key.

The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by.

I've tried to specify the column names on the insert line, but haven't figure that out.

Any suggestions?

Thanks

Jim

----------------------------

Insert into DimensionMonthTime_hold
select distinct substring(period,1,4) + substring(period,6,2),
substring(period,1,4) ,
case substring(period,6,2)
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
else 1
end,
substring(period,6,2),
case substring(period,6,2)
when '01' then 'First Quarter'
when '02' then 'First Quarter'
when '03' then 'First Quarter'
when '04' then 'Second Quarter '
when '05' then 'Second Quarter'
when '06' then 'Second Quarter'
when '07' then 'Third Quarter '
when '08' then 'Third Quarter '
when '09' then 'Third Quarter '
when '10' then 'Fourth Quarter'
when '11' then 'Fourth Quarter'
when '12' then 'Fourth Quarter'
end,
case substring(period,6,2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April '
when '05' then 'May'
when '06' then 'June'
when '07' then 'July '
when '08' then 'August '
when '09' then 'September '
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end,
1,
getdate()
from transaction
where Account_type ='A'

------------------------

View Replies !

Trigger For INSERT --&&> SELECT Statement
Hello,

I have a trigger on a table named Store. The trigger updates the longitude and latitude on store based on the zip. Simple right? Well, I'm trying to import data and of course the trigger is not updating the data as triggers are not on a row by row basis with multi row inserts.

Here is the error message I'm receiving:

Msg 512, Level 16, State 1, Procedure SetLongLat, Line 17

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How would one go about resolving this issue? I've includes my snippets below:

Trigger:

Code Block
ALTER TRIGGER [dbo].[SetLongLat]
ON [dbo].[Store]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Long float
DECLARE @Lat float

SELECT @Long = LONGITUDE, @Lat = LATITUDE
FROM Zipcode..ZipcodeLite
WHERE ZIP_CODE = (SELECT Zip FROM Inserted)

UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id

IF UPDATE(Zip)

BEGIN

UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
END

END

Thanks for your help!
Nathan

View Replies !

Trouble With An Insert Into/select Statement

I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:

NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate

ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email

DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender

I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.

I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.

What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.

The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)

Any help would be appreciated,
Thank you!!

View Replies !

Problem With SELECT And INSERT T-sql Statement

hello everybody

I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue:

We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems.

I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10:

select * from cweb.DBNAME.dbo.TableNAME

Or

insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c10.DBNAME.dbo.TableNAME

All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements.

So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails

C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this:

insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2

It fails with the following 2 error messages:

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID.

I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid.

So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server

So in summary the problem is this:

1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange)

2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I have been searching thru google and forums but havent found any solutions yet.

Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_?

thanks a lot

Helkyn

View Replies !

Select And Insert Statement Merge Together
is there anyway i can merge select statement and insert statement together?

what i want to do is select few attributes from a table and then directly insert the values to another table without another trigger.

for example, select * from product and with the values from product, insert into new_product (name, type, date) values (the values from select statment)

View Replies !

IDENTITY INSERT Error
Hey all, I have been working with Northwind on SQL Server Express
Trying to learn Transact-SQL. I am stuck at this point from code in the instruction manual. I don't know if I have a permission set to something that I need or what but I get the following error that is outlined at the bottom of the code. Any help would be appreciated.

USE Northwind
GO

ALTER PROC spInsertDateValidatedOrder
@CustomerIDnvarchar(5),
@EmployeeIDint,
@OrderDatedatetime= NULL,
@RequiredDatedatetime= NULL,
@ShippedDatedatetime= NULL,
@ShipViaint,
@Freightmoney,
@ShipNamenvarchar(60)= NULL,
@ShipAddressnvarchar(40)= NULL,
@ShipCitynvarchar(15)= NULL,
@ShipRegionnvarchar(15)= NULL,
@ShipPostalCodenvarchar(10)= NULL,
@ShipCountrynvarchar(15)= NULL,
@OrderIDintOUTPUT

AS

DECLARE @InsertedOrderDatesmalldatetime

--Test to see if supplied date is over seven days old. If so
--replace with NULL value otherwise, truncate the time to be midnight.

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
BEGIN
SELECT @InsertedOrderDate = NULL
PRINT 'Invalid Order Date'
PRINT 'Supplied Order Date was greater than 7 days old'
PRINT 'The value has been reset to NULL'
END

ELSE

BEGIN
SELECT @InsertedOrderDate = CONVERT(datetime, (CONVERT(varchar,@OrderDate, 112)))
PRINT 'The Time of Day in Order Date was truncated'
END

--create the new record
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@OrderDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)

--Move the identity value from the newly inserted record into output variable.

SELECT @OrderID = @@IDENTITY

Errors received,
Msg 8101, Level 16, State 1, Procedure spInsertDateValidatedOrder, Line 46
An explicit value for the identity column in table 'Orders' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Thanks

View Replies !

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
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 Replies !

Insert Statement With Multiple Select Statements
hi

first of all is it possible? if so, what am i doing wrong with this

INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)

SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)

the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tibor

View Replies !

T-SQL Select Statement Slows Down When Insert Into Is Put In Front
Hi

We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.

Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.

I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be.

Anybody else ever had this problem?

We are using SQL Server 2005 SP2 64 bit.

Art99

View Replies !

Trouble Converting SELECT To INSERT Statement
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.

==============

use reporting
go

SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog]

===============

Any help much appreciated.

View Replies !

Identity Question: Why Is Value Still Used When There Is An INSERT Error
Hi

I'm just wondering why the IDENTITY value is still used when you attempt
an INSERT that fails?

I placed a Foreign Key Constraint on my table. When I put bogus data in
the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used!

For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!!

why does the Identity get incremented when the Insert fails?

Angel

View Replies !

Instead Of Insert - Identity Null Error
I'm atempting to use an Instead of Insert Trigger on a view to insert a record into a base table. The base table boundaryline has an identity primary key defined. The following is the error I'm receiving:

The column 'id1' in table 'dbo.ParcelBoundaries1' cannot be null.
Could not insert a record in the database.

The view is as follows:

SELECT IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION,
IS_CONFLICT, TYPE, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI, id1
FROM dbo.BoundaryLine

The trigger is as follows:

CREATE TRIGGER TR_ParcelBoundaries1 on ParcelBoundaries1
INSTEAD OF INSERT
as

BEGIN

INSERT INTO BoundaryLine
(IS_TAXLOT,
IS_OTHER,
IS_CARTOGRAPHIC,
IS_RAILROAD_ROW,
IS_IRRIGATION_ROW,
IS_STREET_ROW,
IS_CLOSING_ROW,
IS_CONSTRUCTION,
IS_CONFLICT,
GMPID,
BEARING,
DISTANCE_LEGAL,
DISTANCE_CALCULATED,
GEOMETRY,
GEOMETRY_XLO,
GEOMETRY_XHI,
GEOMETRY_YLO,
GEOMETRY_YHI)
Select 'YES', 'NO','NO','NO','NO','NO','NO','NO','NO', GMPID, BEARING, DISTANCE_LEGAL,
DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI
from
inserted

END

Any thoughts would be greatly appreciated?

View Replies !

Case Statement Error In An Insert Statement
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 Replies !

Insert Record Into Temporary Table From A Select Statement
Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chua

View Replies !

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

here is my code:

Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View Replies !

'Identity Insert Off' Not Detected, Validation Error
I am trying to capture the rows that fail during an insert.

The insert is an OLE DB command component.

I have config'd errors to redirect to a flat file.

My problem is this

I need to override the identity, fine if you are using 'fast load' but then you can't capture the redirected rows.

So I am using the regular 'table or view' data access mode, but then I lose the 'keep identity' checkbox.

At this point I add in another OLE DB command "SET IDENTITY_INSERT table OFF" before the actual insert ODB component.

But now it won't validate. I changed the task 'delay validation' property to true, but still it is failing.

It seemingly cannot detect this statement.

So how then can I capture error rows from an insert where I need to switch the identity off?

[This is a one off historic load I am working on]

View Replies !

'Cannot Insert Duplicate Key' Error With Identity Column As PK

I guess there is first for everything...I had never seen error like this before

Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated.

In this case Primary Key is Identity column. I do not include Idenity column in Insert statement.

Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it.

I see this error randomly.

How should I debug it

View Replies !

Select Statement (Advvance Button Insert, Update, Deltete
Hello All
I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully

Soo the question is: When creating a SQLDataSource in the wizard you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??

thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.

View Replies !

Stored Procedure - SELECT INSERT Statement - Good Practice?
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.

I would like to only import data since the last import so I was thinking of my logic to be like this:

INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))

Does this make sense? Or do you all suggest a different mannger of accomplishing this?

View Replies !

GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!
Thanks,
Randy

View Replies !

Insert Row In Table With Identity Field, And Get New Identity Back
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?

TIA,

barkingdog

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View Replies !

Insert Statement Error
Hi All,

I am trying to run an insert statement and getting an error.

Insert statement:

insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master

Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Any idea why? Thanks.

View Replies !

Error In Insert Statement ........Help Out
Hi
All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error

" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."

And
code is as follows

Code Snippet

Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)

            connection()
            myCommand = New SqlCommand("INSERT INTO Purchase_Header(PH_Voucher_Date,PH_Bill_No,PH_Bill_Date,PH_GRN_No,PH_GRN_Date,PH_Type_Of_Purchase_Id,PH_Supplier_Id,PH_Total_Purchase_Value,PH_Total_Product_Value,PH_Bill_Paid_On,PH_Bill_Due_On,PH_No_Of_Sub_Bills,PH_No_Of_Sub_Bills_Received,PH_Bill_Status,PH_Payment_Status,PH_Apportionment_Status,PH_Goods_Receipt_Status,PH_Sub_Bill_Receipt_Status,PH_Cash_Credit_Indicator,PH_Total_Tax_Paid) VALUES('" & System.DateTime.Today & "'," & BillNo & ",'" & BillDate & "'," & GRNNO & ",'" & GRNdate & "'," & TOP & "," & Supplier & "," & TotalPurchasevalue & "," & TotalProductValue & ",'" & BillPaid & "','" & BillDue & "'," & NoOfSBills & "," & noofbreceived & "," & billstatus & "," & paymentstatus & "," & appstatus & ",'" & recitstatus & "','" & sbillstatus & "' ,'" & ccindicator & "',0)", myConnection)
            myCommand.ExecuteReader()

            MsgBox("Data saved successfully")
            myConnection.Close()
and table structure is as follows and for date types i am passing them through date variables from textbox

INSERT INTO [MoneyBee].[dbo].[Purchase_Header]
           ([PH_Voucher_Date]
           ,[PH_Bill_No]
           ,[PH_Bill_Date]
           ,[PH_GRN_No]
           ,[PH_GRN_Date]
           ,[PH_Type_Of_Purchase_Id]
           ,[PH_Supplier_Id]
           ,[PH_Total_Purchase_Value]
           ,[PH_Total_Product_Value]
           ,[PH_Bill_Paid_On]
           ,[PH_Bill_Due_On]
           ,[PH_No_Of_Sub_Bills]
           ,[PH_No_Of_Sub_Bills_Received]
           ,[PH_Bill_Status]
           ,[PH_Payment_Status]
           ,[PH_Apportionment_Status]
           ,[PH_Goods_Receipt_Status]
           ,[PH_Sub_Bill_Receipt_Status]
           ,[PH_Cash_Credit_Indicator]
           ,[PH_Total_Tax_Paid])
     VALUES
           (<PH_Voucher_Date, datetime,>
           ,<PH_Bill_No, varchar(15),>
           ,<PH_Bill_Date, datetime,>
           ,<PH_GRN_No, numeric,>
           ,<PH_GRN_Date, datetime,>
           ,<PH_Type_Of_Purchase_Id, numeric,>
           ,<PH_Supplier_Id, numeric,>
           ,<PH_Total_Purchase_Value, numeric,>
           ,<PH_Total_Product_Value, numeric,>
           ,<PH_Bill_Paid_On, datetime,>
           ,<PH_Bill_Due_On, datetime,>
           ,<PH_No_Of_Sub_Bills, numeric,>
           ,<PH_No_Of_Sub_Bills_Received, numeric,>
           ,<PH_Bill_Status, numeric,>
           ,<PH_Payment_Status, numeric,>
           ,<PH_Apportionment_Status, numeric,>
           ,<PH_Goods_Receipt_Status, bit,>
           ,<PH_Sub_Bill_Receipt_Status, bit,>
           ,<PH_Cash_Credit_Indicator, varchar(6),>
           ,<PH_Total_Tax_Paid, numeric,>)

Thanks
Avinash

View Replies !

Error:: The Insert Statement ...
hi

i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.

here is a view of my code:

Code Snippet

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
        VragenTableAdapter VraagAdapter = new VragenTableAdapter();
        lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));

        if (Vraag_ID == 1)
        {
            pnlVraag1.Visible = true;
        }

        if (Vraag_ID == 2)
        {
            pnlVraag2.Visible = true;
        }

    }
    protected void btnVraag1_Click(object sender, EventArgs e)
    {
        //make a new user
        GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
        DateTime Moment = System.DateTime.Now;
        GebruikerAdapter.GebruikerToevoegen(Moment);
        int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
        this.ViewState.Add("gebruiker_id", GebruikerID);

        //send answer
        AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
        AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);

        //go to 2nd question
        Response.Redirect("Default.aspx?vraagid=2", true);
    }

    protected void btnVraag2_Click(object sender, EventArgs e)
    {
        //get back the user from question 1
        int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);

        //send answer
        AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
        AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);

when i run debug it allways stops here

        //go too 3th question
        Response.Redirect("Default.aspx?vraagid=3", true);
    }
}

can somebody plz help me ? =(

ps: sorry for my bad english

View Replies !

Select Statement Error
             Receive Error: when I Run Select Statement Asking to Declare @Country
            Not sure how to Declare the variable '@Country'. Any help would be appreciated... Thank You.
Here is the Code:
____________________________________________________________________________________________________________
Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
                   If Not IsPostBack Then
                          Dim conCommerce As SqlConnection                          Dim cmdSelect As SqlCommand                          Dim CategoriesCounter As SqlDataReader                          Dim StylesSource As SqlDataReader
                          conCommerce = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
                          conCommerce.Open()
                         cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)
                         StylesSource = cmdSelect.ExecuteReader()
                         txtCounter1.DataSource = StylesSource                         txtCounter1.DataTextField = "Country"
                         txtCounter1.DataBind()
                         StylesSource.Close()                         conCommerce.Close()
                    End If
______________________________________________________________________________
Error Message:
Must declare the variable '@Country'. 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: Must declare the variable '@Country'.
Source Error:
Line 220:                                                                 cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)Line 221:Line 222:                                                                          StylesSource = cmdSelect.ExecuteReader()Line 223:Line 224:

View Replies !

Error With C# Select Statement
hi i have copied this from my other page where it works fine and i cant understand what is going wrong! maybe one of your guys can point out what i cant see! herei s my code
string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID);
int intCustID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"].ToString());lblCustomerID.Text = Request.QueryString["qsnOrderCustID"].ToString();lblOrderID.Text = Request.QueryString["orderID"].ToString();

SqlConnection myConn = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx");
//This is the sql statement.string sql = "SELECT [del_address], [del_post_code], [del_time] From tbl_del WHERE order_ID = " + intOrderID;

//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader
dr.Read();
try
{
lblDelTime.Text = Convert.ToString(dr["del_time"].ToString);
lblDelAddy.Text = dr["del_address"].ToString();lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text != "")
{
lblDelDate.Visible = true;lblDelTime.Visible = true;
Label1.Visible = true;Label2.Visible = true;
}
}catch (Exception except)
{lblerror.Text = Convert.ToString(except);
}

Regards
Jez

View Replies !

SQL Select Statement Error
Please help me debug this statement:Select vVehRegNAddr.RegContChar1 From (((vVeh LEFT OUTER JOINvVehBrand ON vVeh.Plate = vVehBrand.Plate) LEFT OUTER JOINvVehRegNAddr ON vVeh.Plate = vVehRegNAddr.Plate) LEFT OUTER JOINvVehLegal ON vVeh.Plate = vVehLegal.Plate) Where ((vVeh.Active = 1)AND (vVeh.VUse NOT IN ('C/G','H/D','SNO','SNX')) AND (vVeh.LExpDt ='20050715'))ERROR MESSAGE:"Error in selection of Aggregates:If ANY aggregate functions(Count, Sum,...) are chosen,All fields mustbe aggregate that are not in Grouping"

View Replies !

MDX - Select Statement Error
hi,

i am trying to query data on a Analysis Services Server 2000 from DTS with the statement below.

select
{
[measures].[Attendance Estimative]
} on columns ,

{
{ [Specialties].{Specialty Group].members }
*
{ [Specialties].{Local Specialty].members }
}
from vCube_OP_financialPlanning

it gives me the following Error:

Unable to open cellnet
Formula error - duplicate dimensions across (independent) axes - in a <set> base objectSpecialties

i understand that this error is caused by calling dimension [Specialties] twice.

But how can i retrieve the data like:

Specialty Group____Local Specialty____Attendance Estimative
Specialty A________Local Specialty 1__45
Specialty B________Local Specialty 3__23
Specialty B________Local Specialty 4__78

Basically, i am looking to flatten the MDX resultset to be able to save it on a table or serve it to Access users.

thanks in advance,

nicolas

View Replies !

Select Statement Error
In SQL Sever 2005, I am trying to retrieve data using this simple query from a table called 'User':

select *
from dbo.User
where UserID='sam'

And, I keep getting the following error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'User'.

Thank you for your help

View Replies !

Syntax Error On Insert Statement
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue:
        sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection)        sqlCommand.ExecuteNonQuery()        sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection)        sqlCommand.ExecuteNonQuery()
The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error:
Incorrect syntax near the keyword 'Bulk'.
Anyone have any ideas, thanks

View Replies !

Syntax Error On INSERT Statement
Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.

View Replies !

SQL Insert Statement Error That I Cannot Figure Out
I am receiving the following error when trying to insert values from textboxes on a registration form:

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30205: End of statement expected.

Source Error:

Line 19: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Line 20:
Line 21: Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Line 22: Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
Line 23: dbCommand.CommandText = queryString

My code is below. Can anyone help me figure out what is wrong with my code?

<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Sub btnSubmit_Click(sender As Object, e As EventArgs)

Dim UCde as string = txtUserID.Text
Dim UserID as string = txtUserID.Text
Dim Password as string = txtPassword.Text
Dim Email1 as string = txtEmail.Text
Dim FirstName as string = txtFirstName.Text
Dim LastName as string = txtLastName.Text
Dim AdminLevel as string = dropAccountType.SelectedItem.Value

Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open()
dbCommand.ExecuteNonQuery()
dbConnection.Close()
End Sub

Thanks for your help.

Chris

View Replies !

Syntax Error In INSERT INTO Statement.
Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb"
Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
        Dim connstring As New OleDbConnection(DBString)
        connstring.Open()
        Dim searchstring As String = "SELECT * FROM Costings1"
        Dim da As New OleDbDataAdapter(searchstring, connstring)
        Dim DS As New DataSet()
        Dim dt As DataTable = DS.Tables("Costings1")
        da.FillSchema(DS, SchemaType.Source, "Costings1")
        da.Fill(DS, "Costings1")
        Dim cb As New OleDbCommandBuilder(da)
        da.InsertCommand = cb.GetInsertCommand
        da.UpdateCommand = cb.GetUpdateCommand
        Dim dr As DataRow = DS.Tables("Costings1").NewRow
        dr("Key") = TextBox1.Text
        dr("CODE") = TextBox2.Text
        dr("Element") = TextBox3.Text
        etc...
        DS.Tables("Costings1").Rows.Add(dr)
        da.Update(DS, "Costings1")   <<<<<<<<<<<Syntax error in INSERT INTO statement.

There are no spaces in the field names.

View Replies !

Insert Into Statement Syntax Error
Hi,

I have a problem as shown below

Server Error in '/ys(Do Not Remove!!!)' Application.

Syntax error in INSERT INTO statement.

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.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:

Line 8: <Script runat="server">
Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
Line 10: SqlDataSource1.Insert()
Line 11: End Sub ' InsertAuthorized
Line 12: </Script>
Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +88
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +410
System.Web.UI.WebControls.SqlDataSource.Insert() +13
ASP.authorizing_aspx.InsertAuthorized(Object Source, EventArgs e) in C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx:10
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

And part of my program is as shown

<Script runat="server">

Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)

SqlDataSource1.Insert()

End Sub ' InsertAuthorized

</Script>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DIP1ConnectionString %>" ProviderName="<%$ ConnectionStrings:DIP1ConnectionString.ProviderName %>"

InsertCommand="Insert Into Authorize (Army ID,Tag No,Vehicle ID,Vehicle Type,Prescribed Route,Start Time, End Time) VALUES (@ArmyID, @TagNo, @VehicleID, @VehicleType, @PrescribedRoute, @StartTime, @EndTime)">

<insertparameters>

<asp:formparameter name="ArmyID" formfield="ArmyID" />

<asp:formparameter name="TagNo" formfield="TagNo" />

<asp:formparameter name="VehicleID" formfield="VehicleID" />

<asp:formparameter name="VehicleType" formfield="VehicleType" />

<asp:formparameter name="PrescribedRoute" formfield="PrescribedRoute" />

<asp:formparameter name="StartTime" formfield="StartTime" />

<asp:formparameter name="EndTime" formfield="EndTime" />

</insertparameters>

</asp:SqlDataSource>

Anybody can help? thanks

View Replies !

Error:: The Insert Statement Conflicted With ..
hi

i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.

here is a view of my code:

Code Snippet

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
        VragenTableAdapter VraagAdapter = new VragenTableAdapter();
        lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));

        if (Vraag_ID == 1)
        {
            pnlVraag1.Visible = true;
        }

        if (Vraag_ID == 2)
        {
            pnlVraag2.Visible = true;
        }

    }
    protected void btnVraag1_Click(object sender, EventArgs e)
    {
        //make a new user
        GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
        DateTime Moment = System.DateTime.Now;
        GebruikerAdapter.GebruikerToevoegen(Moment);
        int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
        this.ViewState.Add("gebruiker_id", GebruikerID);

        //send answer
        AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
        AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);

        //go to 2nd question
        Response.Redirect("Default.aspx?vraagid=2", true);
    }

    protected void btnVraag2_Click(object sender, EventArgs e)
    {
        //get back the user from question 1
        int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);

        //send answer
        AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
        AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);

when i run debug it allways stops here

        //go too 3th question
        Response.Redirect("Default.aspx?vraagid=3", true);
    }
}

can somebody plz help me ? =(

ps: sorry for my bad english

View Replies !