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!
chipsterva69Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
              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 %>' )
chipsterva69Author Commented:
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 KhanSenior DeveloperCommented:
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 & ")
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Saqib KhanSenior DeveloperCommented:
in my Example The Entire Sql statement was in a String (with double quotes around).
jitgangulyCommented:
Looks like you are using a SP to pass variables. Try the same @ sign

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)

Also remember you could get some null value from Request.ServerVariables("LOGON_USER")


chipsterva69Author Commented:
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 & 
jitgangulyCommented:
In your SP

Declare @username [sometype]
chipsterva69Author Commented:
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.
chipsterva69Author Commented:
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.  
jitgangulyCommented:
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" />
jitgangulyCommented:
Never used insert parameter, guess you have to use oen of these probably

ControlParameter,FormParameter,QueryParameter or FormParameter
jitgangulyCommented:
http://www.dotnetjohn.com/articles.aspx?articleid=158

This example shows it only uses those parameters that are needed for the insert statement
CtrlAltDlCommented:
You should submit this question to ASP.Net.  Your in ASP section which is causing some confusion.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chipsterva69Author Commented:
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.
jitgangulyCommented:
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
chipsterva69Author Commented:
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:

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21803948.html

whityum ended up solving it there, but thanks to jitqanquly and ctrlaltdl for getting me up to speed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.