Database Refresh?

[Smee]

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

 

[Ariel_Z]

Please try this:

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

You may also like to consult the help: SQL.

 

[Smee]

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:

 

[Ariel_Z]

What are the database columns? You can try something like

"INSERT INTO Sales (vanNo, CustNo) Values" & txt

(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)?

 

[Smee]

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

 

[Erel]

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

 

[Smee]

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

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:

 

[Smee]

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