Last post Jan 29, 2015 10:51 PM by Bulldog248
Jan 29, 2015 04:45 PM|Bulldog248|LINK
I'm using Visual Web Developer 2010 Express with SQL Server 2008 Express, VB. I'm trying to put the logged in username (User) and date (Date) into the updatecommand below (abbreviated for clarity) to fill fields [CreatedBy] and [CreateDate] but I'm having
trouble getting it to work. What do I need to get "User" to represent the logged in username and get "Date" to represent the current date/time? This seems like it should be simple but I'm obviously missing something.
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:CPConnectionString8 %>"
UpdateCommand="INSERT INTO [A_Capacitors] ([PART_NUMBER], [Description], [CreatedBy], [CreateDate]) SELECT [PART_NUMBER], [Description], User, Date FROM [Capacitors] WHERE [PART_NUMBER] = @PART_NUMBER>
Jan 29, 2015 08:01 PM|oned_gk|LINK
Im not sure you want update or insert
Try like this
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO TBL (col1,col2, userid, [date]) VALUES (@col1,@col2,@userid, GETDATE())">
<asp:Parameter Name="col1" />
<asp:Parameter Name="col2" />
<asp:Parameter Name="userid" />
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
SqlDataSource1.InsertParameters("userid").DefaultValue = User.Identity.Name
Jan 29, 2015 10:51 PM|Bulldog248|LINK
This, with one minor exception, worked exactly the way I wanted it to. I just had to change InsertCommand and InsertParameters to UpdateCommand and UpdateParameters. What this does is copy (insert) the existing table part into an archive table and then
update the original table. The archive table keeping track of who made the changes and when the change was made.
Thanks again and I'll mark this as the answer.