asked on

passing ASP server variable into SQL insert statement

Good day -

I am trying to pass the user_logon server variable from my ASP 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 - at the top of the page, under the language declaration (VB):

<%
Dim username
username = Request.ServerVariables("LOGON_USER")
%>

Further down the page is my SQL

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 %>) )

I get the following error information:

Incorrect syntax near '<'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '<'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near '<'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +549
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +173
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +624
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +745
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +163
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +118
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +176
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837

I did the original page development in MSoft's Visual Web Developer 2005, in case that matters.

Any help would be greatly appreciated!

Guy Hengel [angelIII / a3]

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 %>' )

chipsterva69

ASKER

i tried that combination already, and while it does not raise any errors, it also does not pass the variable - here is the insert result...

Edit Delete 7 hs59 test test test test AL 4/4/2006 10:40:05 AM <%= username %>

it interprets the tag as a string, i guess.

Saqib Khan

Try..

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 & ")

Saqib Khan

in my Example The Entire Sql statement was in a String (with double quotes around).

SOLUTION

jitganguly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chipsterva69

ASKER

okay - tried both...

for jitqanquly --- got the following error when I used @username
Must declare the scalar variable "@username".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@username".

* as for nulls - the only people inserting on this application are in the office network, so we can reinforce the log in

for adilkhan --- when I tried double quotes, I got a 'poorly formed tag' error - the entire insert statement is already in double quotes. I tried single quotes, and got the following - again, the tag was inserted as a string, not the variable info (of course, now I remember that single quotes in a SQL statement enforce the enclosed info is inserted as a string... duh - sorry!).

Edit Delete 8 hs60 test test test test AL 4/4/2006 11:12:56 AM & username &

jitganguly

In your SP

Declare @username [sometype]

chipsterva69

ASKER

jitqanquly ---

okay... no more errors, but now the value is blank. it isn't seeing the tag as a string, but it still isn't getting the value of the variable. i know the value is there, because I have a test tag that will show the value if the page sees it.

here is the code snippet that i have in place:

<%
Dim username
username = Request.ServerVariables("LOGON_USER")
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:statedetailSqlDataSource2 %>"
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.ServerVariables("logon_user"))%></br>

2: <%Response.Write(username)%>

Note: at the bottom, both 1 and 2 appear the same.

chipsterva69

ASKER

just to add... it appears that no matter what I set the variable to be (i have tried plain text, numbers, etc.), the SQL statement does not see it. the asp command response.write, however, does see it, and faithfully presents the variable. the trick appears to be how to set up the variable so that it can be inserted into the SQL statement. this is all probably overly obvious to those with a lot more experience, but it is pretty frustrating to me.

jitganguly

Not sure what you are doing, but could use only whatever parameters needed for the insert statement from here

<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" />

jitganguly

Never used insert parameter, guess you have to use oen of these probably

ControlParameter,FormParameter,QueryParameter or FormParameter

jitganguly

http://www.dotnetjohn.com/articles.aspx?articleid=158

This example shows it only uses those parameters that are needed for the insert statement

ASKER CERTIFIED SOLUTION

CtrlAltDl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chipsterva69

ASKER

CtrlAltDl - will do.

jitqanquly - thanks for all your help up to this point. i will post whatever the solution turns out to be and close the question out.

jitganguly

So whats the result ? Though AS.net would be a better palce to post it, but this TA is most popular and you will get prompt answer here only.

Good luck

chipsterva69

ASKER

i don't know enough about ASP.NET to say if the eventual solution to my issue was more ASP or ASP.NET, the final solution was as follows:

in the supporting default.aspx.vb page, add a function for FormView1_ItemInserting() for the form view and add this:

Protected Sub FormView1_ItemInserting(ByVal sender As Object, ByVal e As FormViewInsertEventArgs)
SqlDataSource1.InsertParameters("username").DefaultValue = Request.ServerVariables("logon_user")

End Sub

change the formview tag to this:
<asp:FormView ID="FormView1" runat="server" DataKeyNames="bill_number"
DataSourceID="SqlDataSource1" OnItemInserting="FormView1_ItemInserting">

you can view the thread on how this was fixed here:

https://www.experts-exchange.com/questions/21803948/passing-ASPX-server-variable-into-SQL-insert-statement.html

whityum ended up solving it there, but thanks to jitqanquly and ctrlaltdl for getting me up to speed