• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1627
  • Last Modified:

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!
0
chipsterva69
Asked:
chipsterva69
  • 6
  • 6
  • 2
  • +2
2 Solutions
 
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 %>' )
0
 
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.
0
 
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 & ")
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib KhanSenior DeveloperCommented:
in my Example The Entire Sql statement was in a String (with double quotes around).
0
 
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")


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

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

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

This example shows it only uses those parameters that are needed for the insert statement
0
 
CtrlAltDlCommented:
You should submit this question to ASP.Net.  Your in ASP section which is causing some confusion.
0
 
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.
0
 
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
0
 
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now