7 Replies - 21400 Views - Last Post: 22 August 2010 - 06:41 PM Rate Topic: -----

#1 de212  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 20-August 10

parameter for sqldatasource inserting using c#

Posted 20 August 2010 - 06:46 AM

hi, i am new in asp.net in c#. I face some problem in setting the parameter when i want to set the sqldatasource insert parameter to some other value, eg: the current time and date when i store it into the database.
here is my code for asp.net

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            InsertCommand="insert into qna(quser, ques, date) values (@quser, @ques, @date)"
            SelectCommand="SELECT qid, quser, ques, date FROM qna" >
            <InsertParameters>
                <asp:ControlParameter Name="quser" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox1" Name="ques" PropertyName="Text" Type="String" />
            </InsertParameters>
            </asp:SqlDataSource>


i want to set the @date to today's date and time, what should i type to set the value of date to be the system's current date and time.

here is the code that i try but it show me error, hope anyone could me help me out here..thanks..=)

 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlDataSource2.InsertParameters["@date"].DefaultValue = DateTime.Now;;
        SqlDataSource2.Insert();
    }


Is This A Good Question/Topic? 0
  • +

Replies To: parameter for sqldatasource inserting using c#

#2 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: parameter for sqldatasource inserting using c#

Posted 20 August 2010 - 10:03 AM

View Postde212, on 20 August 2010 - 05:46 AM, said:

here is the code that i try but it show me error, hope anyone could me help me out here..thanks..=)

 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlDataSource2.InsertParameters["@date"].DefaultValue = DateTime.Now;;
        SqlDataSource2.Insert();
    }


If you look closely at this code snippett that you provided, you have two semi-colons at the end of the first statement. Not sure if this was just a typo when you created this thread, but that will give you an error. There should only be one semi-colon to end each statement.

Also, here's another approach. Being that you are just entering in the current date and time for each record that gets inserted, you can set this value directly in your sql insert statement, like this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            InsertCommand="insert into qna(quser, ques, date) values (@quser, @ques, convert(varchar, GETDATE(), 101))"
            SelectCommand="SELECT qid, quser, ques, date FROM qna" >
            <InsertParameters>
                <asp:ControlParameter Name="quser" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox1" Name="ques" PropertyName="Text" Type="String" />
            </InsertParameters>
            </asp:SqlDataSource>


Notice I replace @Date parameter with convert(varchar, GETDATE(), 101) in the insert statement. Now the current date and time will automatically get inserted with each record and you can eliminate the line of .net code that sets the date value. Hope that this information is helpful.
Was This Post Helpful? 1
  • +
  • -

#3 de212  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 20-August 10

Re: parameter for sqldatasource inserting using c#

Posted 21 August 2010 - 01:03 AM

thank you keakTheGEEK for the help, it really works..=)...
would you mind me to ask about something about it again?
let say..
if the @quser parameter, i want to use the system's value.
for example: i want the @quser = User.Identity.Name;
is that possible? last time i did it but i m using VB...
now i have to using c# and i couldn't found anyway to set the values as the parameter.
do you know any c# code to set the @quser as the system's users name..?
thanks..=)
Was This Post Helpful? 0
  • +
  • -

#4 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: parameter for sqldatasource inserting using c#

Posted 21 August 2010 - 04:20 PM

Hi de212,

Sure this is possible. The way that I would probably do this would be to use your datasource's Inserting event. Datasources in .NET have several events related to them that you can take advantage of to do some additional processing before/after the event occurrs. The Inserting event occurs before the insert takes place. So, for example you could do this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            InsertCommand="insert into qna(quser, ques, date) values (@quser, @ques, convert(varchar,GETDATE(), 101))"
            SelectCommand="SELECT qid, quser, ques, date FROM qna" OnInserting="On_Inserting">
            <InsertParameters>
                <asp:ControlParameter Name="quser" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBox1" Name="ques" PropertyName="Text"  Type="String" />
            </InsertParameters>
</asp:SqlDataSource>



Notice the new OnInserting="On_Inserting" attribute. OnInserting specifies that you want to handle the inserting event, "On_Inserting" is the name of the function that you want called when that event occurrs. You can actually set the function name to whatever you want. Then in the code behind (your aspx.cs file) you implement the "On_Inserting" function:
private void On_Inserting(Object sender, SqlDataSourceCommandEventArgs e) {

    e.Command.Parameters["@quser"].Value = Page.User.Identity.Name;

 }



Now whenever your SqlDataSource1 inserts data, that On_Inserting event will fire every time right before the insert event takes place and will assign that value to the @quser parameter. Hope this helps...
Was This Post Helpful? 0
  • +
  • -

#5 de212  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 20-August 10

Re: parameter for sqldatasource inserting using c#

Posted 22 August 2010 - 07:50 AM

thanks again keakTheGEEK,but it shows me this error after compile..

Compiler Error Message: CS0122: 'qna.On_Inserting(object, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)' is inaccessible due to its protection level

the source error there highlighted is

Line 124: &nbsp;<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Line 125: Text="Button" style="color: #FF0000" />
Line 126: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
Line 127: ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
Line 128: InsertCommand="insert into qna(quser, ques, date) values (@quser, @ques, convert(varchar,GETDATE(), 101))"


i dont know whats wrong with it..

This post has been edited by de212: 22 August 2010 - 07:51 AM

Was This Post Helpful? 0
  • +
  • -

#6 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: parameter for sqldatasource inserting using c#

Posted 22 August 2010 - 12:27 PM

I think the problem is with the function:
private void On_Inserting(Object sender, SqlDataSourceCommandEventArgs e) 
{  
    e.Command.Parameters["@quser"].Value = Page.User.Identity.Name;  
 } 



try changing private to protected:
protected void On_Inserting(Object sender, SqlDataSourceCommandEventArgs e) 
{  
    e.Command.Parameters["@quser"].Value = Page.User.Identity.Name;  
} 



If changing it to protected doesn't work then try public. Hopefully that is what the problem is. If not, then we can try something else...

This post has been edited by keakTheGEEK: 22 August 2010 - 12:27 PM

Was This Post Helpful? 0
  • +
  • -

#7 de212  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 20-August 10

Re: parameter for sqldatasource inserting using c#

Posted 22 August 2010 - 05:45 PM

thank you keakTheGEEK, i did it.
after i changed to protected it got a error,
there say quser must have a controlID, so i simply assign a textbox for it..
finally its work..

=)..thanks alot for helping so now i can go on my project..
Was This Post Helpful? 0
  • +
  • -

#8 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: parameter for sqldatasource inserting using c#

Posted 22 August 2010 - 06:41 PM

Yeah, an asp:ControlParameter expects a web control as input. You could have used asp:Parameter to resolve the error also. I am happy to hear that you got it working now.

Happ Coding =)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1