chipsterva69
asked on
passing ASPX server variable into SQL insert statement
Good day -
I am trying to pass the user_logon server variable from my ASPX page into a SQL statement that inserts into a database. I thought I could pass it as a DIM variable, but I can't get the SQL portion see the variable.
What I have -
<%
Dim username
username = Request.ServerVariables("L OGON_USER" )
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:statedet ailSqlData Source2 %>"
SelectCommand="SELECT * FROM [bill_info]"
InsertCommand="INSERT INTO [bill_info]
([bill_number], [sponsor], [status], [summary], [link], [state_id], [entered], [updated], [entered_user])
VALUES
(@bill_number, @sponsor, @status, @summary, @link, @state_id, (getdate()), @updated, @username)">
<InsertParameters>
<asp:Parameter Name="id" Type="String" />
<asp:Parameter Name="bill_number" Type="String" />
<asp:Parameter Name="sponsor" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:Parameter Name="summary" Type="String" />
<asp:Parameter Name="link" Type="String" />
<asp:Parameter Name="state_id" Type="String" />
<asp:Parameter Name="entered" Type="DateTime" />
<asp:Parameter Name="updated" Type="DateTime" />
<asp:Parameter Name="entered_user" Type="String" />
<asp:Parameter Name="updated_user" Type="String" />
<asp:Parameter Name="username" Type="String" />
</InsertParameters>
</asp:SqlDataSource><br /><br />
1: <%Response.Write(Request.S erverVaria bles("logo n_user"))% ></br>
2: <%Response.Write(username) %>
Note: when the page is rendered, both 1 and 2 appear the same.
The insert procedure runs perfectly fine, but when I review the data that was inserted, it turns out that the entered_user field, which should have taken the username variable, is blank. I have played with this for a bit already, and have already submitted this question to the ASP board on this site - they recommended that I ask the question here in ASP.NET.
I am trying to pass the user_logon server variable from my ASPX page into a SQL statement that inserts into a database. I thought I could pass it as a DIM variable, but I can't get the SQL portion see the variable.
What I have -
<%
Dim username
username = Request.ServerVariables("L
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:statedet
SelectCommand="SELECT * FROM [bill_info]"
InsertCommand="INSERT INTO [bill_info]
([bill_number], [sponsor], [status], [summary], [link], [state_id], [entered], [updated], [entered_user])
VALUES
(@bill_number, @sponsor, @status, @summary, @link, @state_id, (getdate()), @updated, @username)">
<InsertParameters>
<asp:Parameter Name="id" Type="String" />
<asp:Parameter Name="bill_number" Type="String" />
<asp:Parameter Name="sponsor" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:Parameter Name="summary" Type="String" />
<asp:Parameter Name="link" Type="String" />
<asp:Parameter Name="state_id" Type="String" />
<asp:Parameter Name="entered" Type="DateTime" />
<asp:Parameter Name="updated" Type="DateTime" />
<asp:Parameter Name="entered_user" Type="String" />
<asp:Parameter Name="updated_user" Type="String" />
<asp:Parameter Name="username" Type="String" />
</InsertParameters>
</asp:SqlDataSource><br /><br />
1: <%Response.Write(Request.S
2: <%Response.Write(username)
Note: when the page is rendered, both 1 and 2 appear the same.
The insert procedure runs perfectly fine, but when I review the data that was inserted, it turns out that the entered_user field, which should have taken the username variable, is blank. I have played with this for a bit already, and have already submitted this question to the ASP board on this site - they recommended that I ask the question here in ASP.NET.
ASKER
yeah, that'd help!
<asp:FormView ID="FormView1" runat="server" DataKeyNames="bill_number"
DataSourceID="SqlDataSourc e1">
<InsertItemTemplate>
Bill number:
<asp:TextBox ID="bill_numberTextBox" runat="server" Text='<%# Bind("bill_number") %>'>
</asp:TextBox><br />
Sponsor:
<asp:TextBox ID="sponsorTextBox" runat="server" Text='<%# Bind("sponsor") %>'>
</asp:TextBox><br />
Status:
<asp:TextBox ID="statusTextBox" runat="server" Text='<%# Bind("status") %>'>
</asp:TextBox><br />
Summary:
<asp:TextBox ID="summaryTextBox" runat="server" Text='<%# Bind("summary") %>'>
</asp:TextBox><br />
Link to bill (include http://):
<asp:TextBox ID="linkTextBox" runat="server" Text='<%# Bind("link") %>'>
</asp:TextBox><br />
State abbreviation:
<asp:TextBox ID="state_idTextBox" runat="server" Text='<%# Bind("state_id") %>'>
</asp:TextBox><br />
<!-- ... -->
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
<!-- ... -->
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
Text="Click here to add a new bill">
</asp:LinkButton>
</ItemTemplate>
</asp:FormView>
<asp:FormView ID="FormView1" runat="server" DataKeyNames="bill_number"
DataSourceID="SqlDataSourc
<InsertItemTemplate>
Bill number:
<asp:TextBox ID="bill_numberTextBox" runat="server" Text='<%# Bind("bill_number") %>'>
</asp:TextBox><br />
Sponsor:
<asp:TextBox ID="sponsorTextBox" runat="server" Text='<%# Bind("sponsor") %>'>
</asp:TextBox><br />
Status:
<asp:TextBox ID="statusTextBox" runat="server" Text='<%# Bind("status") %>'>
</asp:TextBox><br />
Summary:
<asp:TextBox ID="summaryTextBox" runat="server" Text='<%# Bind("summary") %>'>
</asp:TextBox><br />
Link to bill (include http://):
<asp:TextBox ID="linkTextBox" runat="server" Text='<%# Bind("link") %>'>
</asp:TextBox><br />
State abbreviation:
<asp:TextBox ID="state_idTextBox" runat="server" Text='<%# Bind("state_id") %>'>
</asp:TextBox><br />
<!-- ... -->
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate>
<ItemTemplate>
<!-- ... -->
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
Text="Click here to add a new bill">
</asp:LinkButton>
</ItemTemplate>
</asp:FormView>
chipsterva69,
Ok, notice anything missing in that section? - Nothing there for the username!
Tim
Ok, notice anything missing in that section? - Nothing there for the username!
Tim
ASKER
i know it is not in the viewable list - does still need to be in the insertTemplate even if it will be commented out so as not to appear to the user? i am a very newbie in ASPX.
TimCottee,
You need to assign it somehow. You should be able to assign it to the parameter at the point you run the insertcommand rather than including it in the template. Can you show me the bit where you actually run the insertcommand.
Tim
You need to assign it somehow. You should be able to assign it to the parameter at the point you run the insertcommand rather than including it in the template. Can you show me the bit where you actually run the insertcommand.
Tim
ASKER
timcottee - i created the following input code:
Created by:
<asp:TextBox ID="entered_userTextBox" runat="server" Text='<%# Bind("entered_user") %>'>
</asp:TextBox><br />
then commented it out, just in case that is what you intended for me to do - same result, blank entered_user field. what i am trying to accomplish is to have the field populated the ASP server variable logon_user, to capture automatically who is inserting records (or updating, for that matter, but one thing at a time...) i figured i could do this by establishing the variable with a dim statement, populate it with logon_user, then use the variable in the SQL insert statement. the variable is successfully created and populated - the response.write statement appears correct when the page is rendered. however, the SQL statement just does not see the variable for some reason... i hope that explains what i am trying to accomplish a bit better - my apologies.
Created by:
<asp:TextBox ID="entered_userTextBox" runat="server" Text='<%# Bind("entered_user") %>'>
</asp:TextBox><br />
then commented it out, just in case that is what you intended for me to do - same result, blank entered_user field. what i am trying to accomplish is to have the field populated the ASP server variable logon_user, to capture automatically who is inserting records (or updating, for that matter, but one thing at a time...) i figured i could do this by establishing the variable with a dim statement, populate it with logon_user, then use the variable in the SQL insert statement. the variable is successfully created and populated - the response.write statement appears correct when the page is rendered. however, the SQL statement just does not see the variable for some reason... i hope that explains what i am trying to accomplish a bit better - my apologies.
in the code behind or in script tags, add a function for FormView1_ItemInserting() for the form view and add this:
Protected Sub FormView1_ItemInserting(By Val sender As Object, ByVal e As FormViewInsertedEventArgs)
SqlDataSource1.InsertParam eters.Add( "username" , Request.ServerVariables("l ogon_user" ))
End Sub
change the formview tag to this:
<asp:FormView ID="FormView1" runat="server" DataKeyNames="bill_number"
DataSourceID="SqlDataSourc e1" OnItemInserting="FormView1 _ItemInser ting">
Protected Sub FormView1_ItemInserting(By
SqlDataSource1.InsertParam
End Sub
change the formview tag to this:
<asp:FormView ID="FormView1" runat="server" DataKeyNames="bill_number"
DataSourceID="SqlDataSourc
ASKER
whityum - i placed the function in the supporting default.aspx.vb file and updated the formview tag. i get an error - name 'SqlDataSource1' is not declared - in the default.aspx.vb file on line 10, where the function appears. i am guessing because there is no SqlDataSource1 params in the default file. where/how would i place the function within the main page?
is that the code you posted there default.aspx? you should put the code I posted in whatever page that you posted the code above from, the ID you posted is 'SqlDataSource1', if you type Me.S... in the code behind, it should come up in the code hint.
actually, you already had that parameter, it'd be:
SqlDataSource1.InsertParam eters["use rname"].De faultValue = Request.ServerVariables("l ogon_user" )
actually, you already had that parameter, it'd be:
SqlDataSource1.InsertParam
oops, replace the [] with () for VB
ASKER
here is the code for default.aspx.vb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub FormView1_ItemInserting(By Val sender As Object, ByVal e As FormViewInsertedEventArgs)
SqlDataSource1.InsertParam eters.Add( "username" , Request.ServerVariables("l ogon_user" ))
End Sub
End Class
i tried substituting "SqlDataSource1.InsertPara meters["us ername"].D efaultValu e = Request.ServerVariables("l ogon_user" )" for "SqlDataSource1.InsertPara meters.Add ("username ", Request.ServerVariables("l ogon_user" ))" in the default.aspx.vb and get several additional errors.
my apologies - i just don't understand how to put the following declaration:
Protected Sub FormView1_ItemInserting(By Val sender As Object, ByVal e As FormViewInsertedEventArgs)
SqlDataSource1.InsertParam eters["use rname"].De faultValue = Request.ServerVariables("l ogon_user" )
on the main processing aspx page. i have tried placing it withing <% %> tags outside of any code parts, within the first tag establishing language, etc. not sure if this helps, but the initial code, data connections, and such were set up in Visual Web Developer 2005 Express. let me know if you want to see the main page code - it is approx 260 lines of code.
thanks for the help!
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub FormView1_ItemInserting(By
SqlDataSource1.InsertParam
End Sub
End Class
i tried substituting "SqlDataSource1.InsertPara
my apologies - i just don't understand how to put the following declaration:
Protected Sub FormView1_ItemInserting(By
SqlDataSource1.InsertParam
on the main processing aspx page. i have tried placing it withing <% %> tags outside of any code parts, within the first tag establishing language, etc. not sure if this helps, but the initial code, data connections, and such were set up in Visual Web Developer 2005 Express. let me know if you want to see the main page code - it is approx 260 lines of code.
thanks for the help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
one last tweak and it all fell into place...
in the default.aspx.vb page - the function needs to read as follows:
Protected Sub FormView1_ItemInserting(By Val sender As Object, ByVal e As FormViewInsertEventArgs)
SqlDataSource1.InsertParam eters("use rname").De faultValue = Request.ServerVariables("l ogon_user" )
I had to drop the 'ed' after Insert in 'FormViewInsertedEventArgs ' in the first line.
Once I figured that out - and after correcting a real stupid user error on my part (forgot to check the second page of results to see if the info was importing...) - voila.
thanks a million, whityum
in the default.aspx.vb page - the function needs to read as follows:
Protected Sub FormView1_ItemInserting(By
SqlDataSource1.InsertParam
I had to drop the 'ed' after Insert in 'FormViewInsertedEventArgs
Once I figured that out - and after correcting a real stupid user error on my part (forgot to check the second page of results to see if the info was importing...) - voila.
thanks a million, whityum
And where do you assign the values to the parameters?
Tim Cottee