0

I am having some trouble trying to pull the latest row from SQL tables. The user can fill out an expense form and the data from the ASP.NET text boxes gets sent to the database, but when the user submits more data I cannot pull the latest data and populate the text boxes with that data. I am using a GUID as the ID fields. I have stored the userID in the Session["New"].

Here's my SQL statement:

string str = "select * from CashFlow where userID='" + Session["New"].ToString() + "'";
Alexei Levenkov
  • 98,904
  • 14
  • 127
  • 179
Tak
  • 5
  • 1
  • 3
  • you can insert a new column called `insert_date` and set it to default to `CURRENT_TIMESTAMP`. – Tuan Anh Tran Dec 07 '15 at 03:22
  • post your codes and few rows from database – Khazratbek Dec 07 '15 at 03:22
  • https://imageshack.com/i/pboHrQbpp https://imageshack.com/i/pbRUK27Lp links screen shots of my code, thanks! – Tak Dec 07 '15 at 03:32
  • I tried inserting a new column into my table but i got an error and it wouldn't let my save and apply the changes - never mind it worked – Tak Dec 07 '15 at 03:34
  • OP. Didn't you just create the GUID for the new row? Why not just search on that? – Aron Dec 07 '15 at 03:41
  • How would I select the most recent GUID for the cashFlowID? I'm am new to this (first time doing something like this) and kinda confused. Thanks for your advice – Tak Dec 07 '15 at 04:24

6 Answers6

1

Create a new columns in your database called CreateDate and UpdateDate.

When you make Insert in the table set UpdateDate and CreateDate to DateTime.Now. When you make Update of the row in the table just set the UpdateDate to DateTime.Now

After that you will need query like this, for last created:

select * from CashFlow order by CreateDate desc

and for last updated

select * from CashFlow order by UpdateDate desc
mybirthname
  • 17,949
  • 3
  • 31
  • 55
  • I tried to write the datetime to the database, but got an error. Here is the code i used: com.Parameters.AddWithValue("@date", DateTime.Now); and the error i got was: Cannot insert an explicit value into a timestamp column – Tak Dec 07 '15 at 03:50
  • @Tak try to use DATETIME type, at least this is working in ms sql. – mybirthname Dec 07 '15 at 03:59
  • Thanks, that fixed that problem. I can now write the time to the table. Thanks! – Tak Dec 07 '15 at 04:03
  • Well the DATETIME type fix helped, I'm still messing trying to fix the load situation – Tak Dec 07 '15 at 04:14
1

Try to use the NEWSEQUENTIALID () to generate your GUID, then you can get last latest row from database when ID is GUID. As It creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.

Here is the example. CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID());

Seth
  • 11
  • 2
0

Databases generally don't have concept of "latest", so if your table does not include some sort of time stamp you can't really get latest record.

Fix - add some sort of time-stamp field to the records to be able to "order by" on that field. For example you may consider adding "autoincrement" field (you'd have to add it to PK - mysql auto-increment on a non-primary key).

Side note - please use parametirzed queries instead of constructing query with string concatenation - Parameterized Query for MySQL with C#

Approximate sample (assuming you have TimeStamp field):

var selectCommand = "SELECT * FROM CashFlow WHERE userID=@userId ORDER BY TimeStamp";
var cmd = new MySqlCommand(readCommand);
cmd.Parameters.AddWithValue("@userId", Session["New"].ToString());
Community
  • 1
  • 1
Alexei Levenkov
  • 98,904
  • 14
  • 127
  • 179
0

It sounds like you have the whole problem backwards. The point of using GUID is that the ID is generated by the user (or application in this case).

Given that the GUID is generated by the application, the last ID is the same as the ID that was last generated.

So, simply use the one in memory instead of using a database query for something you know.

Aron
  • 15,464
  • 3
  • 31
  • 64
0

You could select top 1 row after sorting and checking for username

string str = "SELECT TOP 1 OrderNr FROM Orders Where CustomerName = 'Maria' ORDER BY OrderNr DESC;";

Just make some adjustments in the string for what your need, but this should work to get the last row from lets say a customer.

Creator
  • 1,502
  • 4
  • 17
  • 30
0
 insert into CashFlow (Column names)
      OUTPUT inserted.userID
    values
   (inserted values in Column )

     store Return value in variable For fetch Latest Record By userID
solanki dev
  • 27
  • 8