|
How about a temporary table:
create table #t2
(
order_no varchar(255),
order_line_no int,
long_description varchar(8000),
done bit
)
Insert Into #t2 (order_no, order_line_no, long_description, done)
Select order_no,
order_line_no,
long_description,
0
From #t1
Where line_type = 'p'
Declare @i int
Set @i = 1
While (Select count(*) From #t2 where Done = 0) > 1
Begin
Update t2
Set t2.long_description = t2.long_description + ' ' + t1.long_description
From #t1 t1
inner join
#t2 t2
On t1.order_no = t2.order_no
And t1.order_line_no = t2.order_line_no + @i
And t1.line_type <> 'P'
And t2.done = 0
Update t2
Set t2.done = 1
From #t1 t1
inner join
#t2 t2
On t1.order_no = t2.order_no
And t1.order_line_no = t2.order_line_no + @i
And t1.line_type = 'P'
Set @i = @i + 1
End
You could also use a cursor.
Mike
|
|
|
|
|
Hallo Forum,
I am new to this forum and I would like to get your help. I am having issues with this code.
HERE is the error I am getting: Syntax error (Missing Operator) in Query expression ' :1
Dim CustomerTrans As OleDb.OleDbTransaction = Nothing
Dim MyConnection As OleDb.OleDbConnection = Nothing
'Creat connection and the transactions Object
Try
MyConnection = New OleDb.OleDbConnection(My.Settings.dbConnectionString)
MyConnection.Open()
'Begin Transaction
CustomerTrans = Myconnection.BeginTransaction
'Insert the New receipt
Dim SQL As String = "Insert into Receipts (ReceiptDate,ReceiptTotal) values ( :1)"
Dim CMD1 As New OleDb.OleDbCommand
CMD1.Connection = Myconnection
CMD1.Transaction = CustomerTrans
CMD1.CommandText = SQL
CMD1.Parameters.AddWithValue(" ", Now.Date)
CMD1.Parameters.AddWithValue(":1", TotalPriceTxt)
CMD1.ExecuteNonQuery()
CMD1.Dispose()
i AM Using Access 2007 and I set t
ANY HELP GIVEN IS HIGHLY APPRECIATED. i TRIED ALMOST ALL I CAN THINK OF.
Thank you in advance.
|
|
|
|
|
Read this article [^](I know it is long but you can skip to the parameterised query section) it will answer your question and give you additional information on sql.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
RAH
Thanks for the quick response. Although I have learned something new in the article you posted, I still can't find what the problem is with my code.
What am I doing wrong? it looks valid syntax to me, but what do I know, I am new to this. I have been reading about INSERT INTO statement all day long and can't find whats wrong with my code.
Thanks
|
|
|
|
|
What database system? Oracle? MySQL? SQL Server?
|
|
|
|
|
I am Using MS Access 2007
|
|
|
|
|
|
But I am not using Named Parameters. I am really confused. I guess I have been reading way too long about this.
|
|
|
|
|
It looks like you think you are.
|
|
|
|
|
Should there not be a comma between the two parameter identifiers like:
Dim SQL As String = "Insert into Receipts (ReceiptDate,ReceiptTotal) values (:0, :1)"
|
|
|
|
|
I think those just aren't valid names/markers.
|
|
|
|
|
PieBALDconsult,
What do you mean by not valid names?
I have have them on my database exactly as you can see it. I have checked everything on the naming....is there anything you think I am missing? Please help!
Thanks.
|
|
|
|
|
You're more familiar with what exactly you are doing.
Typically, names aren't allowed to start with a digit.
My experience with Access (via OleDb) is that question marks (? ) are preferred, but that names can be used if they're kept in order (as yours are).
I have also only see a colon (: ) used for parameter names in Oracle.
|
|
|
|
|
PIEBALD Consult,
Thanks for your feedback. It really got me thinking. and I got it to work.
All I had to do is take the ( out. Man, I spent so much to do that.
I really appreciate how you help me focus on where I wanted.
Thank you again.
|
|
|
|
|
I'd be interested to see what you have now.
|
|
|
|
|
I was just guessing, based on the fact that there would generally be a comma separator in such expressions. However, from the further messages it seems that OP had not checked the documentation for proper format of the command.
|
|
|
|
|
Rick
Thanks for your input. I have tried that already and it didn't work for me. it gives me another error: "Data type mismatch in criteria expression"
Is the anyone out the who can suggest what I should do with this error? I will really appreciate in advance.
Thank you
|
|
|
|
|
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge.
My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back.
I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2).
Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
|
|
|
|
|
Not something I have ever needed to do. However why is explicitly dropping the table and issue, you already trap the error and have a rollback simply add the test and drop in that trap.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Similar to below? That would remove the created table (and does)
BEGIN TRANSACTION
CREATE TABLE Test (Id BIGINT)
INSERT INTO Test (Id) VALUES (1)
ROLLBACK
SELECT *
FROM information_schema.tables
WHERE TABLE_NAME = 'Test'
Danzy83 wrote: However, the tables created within the transaction are not dropped from the database. This is unusual to me.
It'd be erroneous. Have you changed the locking-options?
FWIW, it'd probably be the wisest to use a temp-table, not a real one.
|
|
|
|
|
In almost all database systems that I have worked with, DDL statements such as CREATE and DROP are not considered part of a transaction.
|
|
|
|
|
Shameel wrote:
In almost all database systems that I have worked
with, DDL statements such as CREATE and DROP are not considered part of a
transaction.
Yes but it appears that it is part of TSQL which is what the poster asked about.
|
|
|
|
|
Thanks for pointing out. I didn't know that unlike Oracle, DDL statements are transactional in SQL Server.
|
|
|
|
|
Dear All
I Change the language for the database in Sql Server 2008 ( To Arabic_CI_AS )via the optional tab in database proprieties .
but when i Insert new record( have Arabic Text) in any table the text change to ???? .
thanks for any body help me .
T.h
Thaer
|
|
|
|
|
Possibly a typeface/font issue. Check the settings of the tool you are using.
|
|
|
|