Commit A Loop Of Inserting

Dec 9, 2006

I use loop to insert few record into a table:
But the for_Loop only loop once and throw an error:
"The variable name '@res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."
What should i do to get this fix?

Code:

Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim DataSources1 As New SqlDataSource()
DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

DataSources1.InsertCommandType = SqlDataSourceCommandType.Text
DataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@res_name, @my_menu)"

Dim c As Integer
For c = 0 To selectListBox.Items.Count - 1 Step +2

DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)
DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)
DataSources1.Insert()
Next
End Sub

View 2 Replies


ADVERTISEMENT

Loop Stops Inserting !

Jun 24, 2008

for some reason my loop stops when i = 1 :s

Statement s7 = MySql.connection.createStatement();
for(int i = 0; i < 50 ; i++) {
String test = "INSERT ignore INTO `testtable` (`name`,`Item"+i+"`,`Amount"+i+"`) values ('"+getUsername()+"','"+getItem(i)+"','"+getAmount(i)+"')";
s7.executeUpdate(test);
}

Can anyone help me please???

View 7 Replies View Related

Loop Through Resultset, Inserting Into Existng Tbl

May 27, 2008

Hello! quick question.

Say I have a table, and I select the primary key as follows:

select fID from findings;

how can I loop through that resultset and execute the following insert at each iteration:

insert into owners (fID, uID) values (@curFID, 273);


where @curFID is the current record from the resultset? I've done some playing with while loops, however the tutorials I found were fairly basic and only provided examples using static loop controls. any help is appreciated!

View 2 Replies View Related

Problem Of Inserting In A Nested WHILE Loop In SProc

Jan 17, 2008

Hi friends, I've been stumped on this for almost a week now.  Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code.  I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates.  I hope I'm being clear about this.  Any ideas? IF @Edit=1 AND LEN(@Changes) > 0
BEGIN
--Split and parse changes
DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10),
@curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int

DECLARE @NewProj table (
ProjectID int,
SchoolNumber varchar(20),
ArtTeacherNumber int,
TeacherNumber int,
TeacherName varchar(40),
ProjectDescription varchar(512) NULL,
[Checksum] varchar(20) NULL)

DECLARE @Updates table (
ProjectID int,
StudentNumber varchar(12),
NewScore varchar(10) NULL,
OldScore varchar(10) NULL)

SET @curRec = 1

WHILE @curRec IS NOT NULL
BEGIN
SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0)
SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record
SET @curField = 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum
SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed.
SET @curField = @nxtField + 1

INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum])
SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk
FROM @Students Students
WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum

SET @curSRec = 1
WHILE @curSRec IS NOT NULL
BEGIN
SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)
SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score.
SET @curSField = 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number
SET @curSField = @nxtSField + 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score
SET @curSField = @nxtSField + 1

IF @curSField > LEN(@subRec)
SET @Oscr = NULL-- If no Old Score specified
ELSE
BEGIN
SET @nxtSField = LEN(@subRec) + 1
SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score
END

-- Check for errors
IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8
BEGIN
SET @UpdateErr = 1
BREAK
END

-- Update the updates table and find ProjectID from existing data table
INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore)
SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr
FROM @Students Students
WHERE Students.StudentNumber=@SNum

SET @curField = @nxtSRec + 1
SET @curSRec = @nxtSRec + 1
select * from @Updates
END
IF @UpdateErr = 1
BEGIN
BREAK
END
SET @curRec = @nxtRec + 1
END
 Thanks in advance for looking at this, 

View 3 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 4 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 10 Replies View Related

SQL 2012 :: Inserting Data Into Temp Table Using 2 While Loop

Apr 21, 2015

I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.

drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint

For some reason I cant not get 2015 data .

View 4 Replies View Related

Inserting Multiple Records Using A @start And @count With One INSERT (and No Loop)

Aug 22, 2007

Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.

For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...

declare @card_start dec(16)
set @card_start = '1234000012340000'
declare @card_count int
set @card_count = 100

drop table card_table

create table card_table (
card_number dec(16),
activated char default 'N'
)

insert into card_table
select
... ???? ....

But WITHOUT using a while-loop (or any other kind of loop). I'm looking for fast and efficient code! Thanks.

View 3 Replies View Related

SQL Server 2012 :: Inserting Dummy Records Using Loop Statement

Jul 17, 2015

I have the following attributes in this Table A.

1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)

I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this

LOCATION_ID
1

Serial Number
SN-01

KeyID
E1210

Reference_Address
83

SourceTime
2015-05-21 00:00:00 000

SourceValue
6200

View 7 Replies View Related

COMMIT TRAN Does Not Commit

Mar 28, 2008

Microsoft SQL Server Management Studio Express

select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount

Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.

sa privileges

What am I missing?

View 1 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

MS SQL Commit?

Sep 14, 2004

Do delete/update statements in MS SQL Server need a "commit" (or equivalent) run after them as they do in Oracle?

View 1 Replies View Related

Explicit Commit

Jun 8, 1999

Hi ,
I've tried to switch MS-Sql/Server 6.5 on explicit_transactions
without success.
(set implicit_transaction on/off)

What is the exact syntax for doing that ?

Herve

(PS: Thanks Gregory for your quick answer )

View 1 Replies View Related

Explicit Commit

Jun 3, 1999

Hi All,

I don't know MS-SQLserver internal system at all. I 've just used Oracle
a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo)
you can switch off the implicit transaction by using
the option AUTOCOMMIT ON/OFF.

How can switch off the implicit transaction system on MS-SQLServer ?

In advance thanks,

Herve

View 1 Replies View Related

Commit Transactions

Aug 21, 2002

l also use the

begin transaction
select ........etc
commit

structure when l wrtite queries.My problem is that if l close the query analyser it asks me to commit transaction before l exit. Why?


How do you check for uncommitted trans and commit them?

View 1 Replies View Related

Commit Like Syntax

Aug 17, 2005

I built this in SQL query analyzer to update all records with the 1/1/02 date:

update tbl.EMPPOS set EFFECT_DATE = '2005-01-01'
where EFFECT_DATE = '2002-01-01';

when I run the query it updates and shows records in the lower window, but the tbl isn't altered. What is wrong with my syntax ??

thanks,

View 1 Replies View Related

How To Commit The Transaction

Apr 14, 2008

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

View 1 Replies View Related

Commit And Rollback

Dec 15, 2007

I have a cursor loop through a set of records that looks something like this.

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @iID

WHILE @@FETCH_STATUS = 0

BEGIN

update table 1

update table
......

FETCH NEXT FROM database_cursor
INTO @iID

END

CLOSE database_cursor
DEALLOCATE database_cursor

Is there a way i could put all the UPDATE statements within a transaction. either everything goes or nothing.THnaks

Thanks,

View 3 Replies View Related

How To Force A Commit In A Sp

Jul 20, 2005

I've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!

View 3 Replies View Related

Commit Error

Oct 9, 2007

I have an SSIS package that iterates through a thousand or so download text files, parses them and inserts the results into a database via a Stored procedure and an OLE DB Command.

For the most part this process works without any issues, yet I keep obtaining random errors on a DT_STR (500) column. I have reviewed the data extensively and this column - which is the same across all of the rows - does not appear to be any different.

The rest of the rows before and after the error rows all insert properly but these rows consistently fail in the OLE DB task with the following error:

[OLE DB Command [35549]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".

When I inserted the rows into an errors table, I found that the error code, -1071607698, is not defined and the only thing I could find online was a reference to:


DTS_E_COMMANDDESTINATIONADAPTERSTATIC_UNAVAILABLE

which appears to be a DTS error and not an SSIS error.

I have added tasks to explicitly verify the length of the field and that field actually inserts without any issues into the error table - which has the exact same column definition as the target table.

I am at a complete loss as to how to proceed - does anybody have an idea?

View 4 Replies View Related

Commit/rollback

Dec 21, 2007

when doing an 'update table set field1 = 'N',using sql query analyzer, is the update committed immediately? If it isn't commited can a 'rollback' be executed, and what is the format of the rollback command?

View 4 Replies View Related

Two Phase Commit

Jun 15, 2006

How to implement two pase commit in SQL Server 2000, Is there Database Link link Oracle available here ?

View 5 Replies View Related

Commit And Rollback?

Apr 4, 2007

Hi,



I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:



Product:

-----------

productID

(...)



Acessories:

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

acessID

(...)



ProductAcess:

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

productID

acessID



So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

View 1 Replies View Related

Force A Commit

Sep 3, 2007

Hi,

My data flow has several transformations:
1. Search an employee, if the employee already exists, update it, otherwise insert it.
2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.

At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....

the question is, Its possible to force a commit?


Thanks!

View 5 Replies View Related

Forcing DB Changes To Commit In SQL CE !!!

Sep 9, 2007

This is a really wide spread - more than a time discussed - on SQL CE MSDN Forums - Issue !!!
Is there any way i can commit changes which happens during runtime (when i am developing the application) such as inserts/updates and deletes to the .sdf DB on the machine ?????

View 34 Replies View Related

Commit && Rollback Logic In VB.NET

Oct 27, 2006

I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur. I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.TIA for any opinions, options, etc.  Tom

View 2 Replies View Related

Help WTrans Rollback Commit

Jun 11, 2002

I first must delete any existing log for my current record.

Then verify that the "exec @res = gmw_updatesynclog..." has not failed and a delete log entry has been successfully written as verified by the gmw_updatesynclog's return of int 16.

If all is well then I incrment my counter and delete the record, here in testing I'm just updateing the activity code to del so I can find them, in live I'll just be deleting them.

--My error
Server: Msg 266, Level 16, State 2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
-- end my error

...excerpt from my proc and nested cursor....
------------------------------ begin calls to be deleted
if @rectype = 'C' and (len(rtrim(@notes))<20 or @notes is null)
begin transaction

delete gmtlog -- delete any older del log for this recid
where frecid = @recid
and fieldname = 'zzzDel'
and tableid ='"'
if @@error = 0 -- 0 or 1 rows affected...

exec @res = gmw_updatesynclog 'cal',@recid,'MASTER','D'

if @res <> 16 rollback transaction
goto endd --cleanup and fetch next record

if @res = 16 -- increment count_call_dels
set @count_call_dels = @count_call_dels +1
update cal -- update in test delete in live
set actvcode = 'DEL'
where recid = @recid
commit --transaction
------------------------------ end calls to be
... end excerpt...

TIA

JeffP...

View 2 Replies View Related

Begin Commit Transactions

Mar 14, 2001

Many times i write stoted procedures with transaction blocks.
I have delete a row after begin transaction and in continue i
read from table the select statement get back the deleted row:

begin tran
delete mytable
where id = @myid
and seqid = 3

select sum(balance)
from mytable
where id = @myid

............
...............
commit tran
.... OR
rollback tran

the sum(balance) function has calculate the balance of row 3
I use SQL 7.0

Thanks
Renato

View 1 Replies View Related

Need Some Help On Savepoint,Commit And Rollback

Oct 9, 2000

Hi,
I have a procedure of 6500 lines in which i have given a save point at the beginning of the procedure. And am storing the error number in a variable through out the procedure using select of @@error. And at the end if my @error_number is not zero then am rolling back the tranasction else commit the transaction.

Its giving me the error
Msg 266, Level 16, State 2, Line 5437
Transaction count after execute indicates that a commit or rollback
transaction statement is missing. Previous count = 0, Current count = 1.

View 1 Replies View Related

If @@trancount &> 0 Commit Tran

Apr 28, 2003

Hi,

Reviewing the MSSQL process info screen, I am seeing the same process appear a numer of times. It is always the same, being

'IF @@TRANCOUNT > 0 COMMIT TRAN'

Sometimes, there can be up to a hundred of these processes (listed in the process info screen). They generally have a 'sleeping' status, but nonetheless, I would like to see these processes disappear if they are not being used.

I have checked in all of the stored procedures and triggers in the application, and none have this sql statement.

When I run profiler, I get these entries, but the profiler says they belong to either SQL Enterprise Manager or 'Microsoft Windows 2000 Operating System', and not to the application I am running.

Does anyone know where these transactions come from? Can I prevent these from appearing? If no, what is the impact (other than sql server having to maintain a connection).

Thanks,

Jim

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved