Database Refresh?

Hi Again,

I am trying to get a new trx no from a database table each time a new transaction group is started but i am not getting anywhere. My code is as follows

Reader.New1
Command.CommandText = "SELECT DISTINCT TrxNo FROM Sales ORDER BY TrxNo"
Reader.Value = Command.ExecuteReader
Reader.ReadNextRow
TrxNo=Reader.GetValue(0)+1
Reader.Close

Each time i call this i expect the TrxNo to be incremented by 1 but it is not. Should the database table be refreshed each time or am i missing something else

I have tried using ASC and DESC at the end of the select statement but it does not work.

All help much appreciated

Joe

Please try this:

You may also like to consult the help: SQL.

i will try to explain better;

i want to add every row of a table control into a table in a database.
my initial code was like this

For i = 0 To sTblOrders.RowCount-1
Timex=sTblOrders.Cell("OrderTime",i)
ProdNo=sTblOrders.Cell("ProductCode",i)
ProdPrice=sTblOrders.Cell("ProductPrice",i)
Qty=sTblOrders.Cell("Qty",i)
'add row to database
ItemNo =i
txt= "('" & VanNo & "','" & CustCode & "','" & TrxNo & "','" & ItemNo _
& "','" & Timex & "','" & ProdNo & "','" & ProdPrice & "','" & Qty & "','" & trxType &"')"
command.CommandText = "INSERT INTO Sales VALUES " & txt
Command.ExecuteNonQuery
Next

But it is incredibly slow. Can I update a database table directly from the table control?
i know about savecsv and loadcsv but this is not much use for this instance.

Thanks all

Joe
:sign0085:

What are the database columns? You can try something like
(Note it bears a security risk if values are arbitrary user input, as described here:
Basic4ppc SQL library reference)

It should be faster. Anyway, you can insert the rows one by one, but:
a. It is not the same problem as in your previous post (you said "I'll try to explain...")
b. Be sure to delete duplicate rows - if you read rows to the table and then inserts them back you'll have duplicates. Why not updating (if this is your case, I'm not sure)?

Thankyou Ariel,

Yes you are correct they ARE different problems. My code became a mess trying to overcome 2 coding difficulties that relate to an overall problem.

My first problem is trying to add records to a database table in the most optimum way from a hidden table control.
Then i need to read a particular field back (trxNo) and increment it by one to do the next batch of records.

This process continues over and over.

i have read the sql help and have a reasonable understanding of it. My difficulties lie in adding records from a table control.

in vb the syntax is pretty simple to load a table to a database and i guess i am looking for the same sort of result.

Thanks for your replies, can you give any further help?

cheers

Joe

Did you try to use Connection.CreateSQLTable? It creates a SQL table from a table control.

Erel said:

Did you try to use Connection.CreateSQLTable? It creates a SQL table from a table control.

Click to expand...

Thanks Erel,

Yes i did try that and i must have done something wrong, i was getting the db table but no data. i am going to try it again tonight. A bit of code re-writing i guess

Joe

:sign0104:

Thanks for all the pointers Erel and Ariel,

the code following now works. A lot faster than it was. will be a bit faster again if i create a connection first i guess. Anyway for someonelse's benefit here it is


command.CommandText="Drop Table If Exists TemporaryTable"
Command.ExecuteNonQuery

SQLConn.CreateSQLTable("StblOrders","TemporaryTable")

Command.CommandText = "INSERT INTO Sales SELECT * from TemporaryTable"

Command.ExecuteNonQuery

Reader.New1
Command.CommandText = "SELECT DISTINCT TrxNo FROM Sales ORDER BY TrxNo DESC"
Reader.Value = Command.ExecuteReader
Reader.ReadNextRow
TrxNo=Reader.GetValue(0)
TrxNo=TrxNo+1
Reader.Close