Updateing the value of a SQL Paremeter

[putrtek]

I have a Field called 'worklog'. Each time the field is updated I want to add some text to the Beginning of the field before it is sent to the Database.

for example: The Text the User Typed in is
"Here is some sample text"

Text I want to update the data base with is
"Entry made on 7/3/2007 by John Smith: Here is some sample text"


In my code behind I'm using the SqlDataSource1_Updating sub to access the value of the text box using the FindControl method. I am able to add text to the string and display ALL the text in the trace log. However when I try to set the Defaultvalue of the SQL Paremeter that updates the database I only get the value that was originally in the text field and not the Contatenated text.

 Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating

        Dim MyWorkLog As String = ""
        Dim MyTempWorkLog As String = ""
        MyTempWorkLog = CType(EditIncidentSummaryForm.FindControl("WorkLog"), TextBox).Text
        MyWorkLog = "<b>Entry Made by:</b> " & Context.User.Identity.Name & "<br/>" & MyTempWorkLog
        'Write the value of MyWorkLog to the TRACE LOG
        Trace.Write(MyWorkLog.ToString) 'this works I get ALL the text

        SqlDataSource1.UpdateParameters("WorkLog").DefaultValue = MyWorkLog.ToString
        'The above does NOT work I only get the text from MyTempWorkLog

    End Sub

Can anybody tell me what I'm doing wrong?

Thanks in Advance for any assistance.



Mark Buckley

http://www.swiftriverphotos.com

 

[Kalisto]

I dont use the SqlUpdater, but Im guessing that you are telling it to use the value of MyWorkLog as the Default value. Is there another property like Value ?

K

 

[putrtek]

Unfortunately not, there is no other properties like 'value' or 'text'. Is there another way to do this?

Mark Buckley

http://www.swiftriverphotos.com

 

[putrtek]

Can anyone tell me what I;m doing wrong here? Is there a better way to do this?

Thanks in Advance for any assitance or direction you can give me.

-MARK-



Mark Buckley

http://www.swiftriverphotos.com

 

[jmeckley]

does the WorkLog parameter directly tie to a control? if so then is probally ignores your changes sticks with the controls values.

if your markup looks like this

<asp:ControlParameter Name="WorkLog" ControlID="WorkLog" PropertyName="Text" />

change to

<asp:Parameter Name="WorkLog" DefaultValue="" />

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[putrtek]

Jason,

Thank you very much for your response. You have helped me several times before in the past and your advise has always been right on.

I have changed my Paremeter as you suggested and it has not helped.

I had this code ...

<asp:Parameter Name="WorkLog" Type="String"  ConvertEmptyStringToNull="true" />

And as you suggested I changed it to this...

  <asp:Parameter Name="WorkLog"  DefaultValue="" />

I'm still getting the same response. If I write the MyWorkLog variable to the Trace Log and I see all the additional text that I want in addition to the text the user typed in. However my Datbase only updates with the text that the users types in.

Thanks again or your expert assistance on this.

-MARK-

Mark Buckley

http://www.swiftriverphotos.com

 

[jmeckley]

remove the WorkLog parameter from markup then update the following code

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
   Dim textBox as TextBox = CType(EditIncidentSummaryForm.FindControl("WorkLog"), TextBox)
   Dim MyTempWorkLog As String = "<b>Entry Made by:</b> " & Context.User.Identity.Name & "<br/>" & textBox.Text

   e.Command.Parameters.Add("WorkLog", SqlDataType.VarChar).Value = MyWorkLog
End Sub

I'm not update to speed on CommandParameters, so you may need to tweak the Add() function/default value. But you get the idea.

in summary. remove the command parameter from the markup and dynamically add the parameter when you execute the update query.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[putrtek]

Jason,

Thanks... I'll try this and let you know...

-MARK-

Mark Buckley

http://www.swiftriverphotos.com

 

[putrtek]

Jason,

So far I have still been unable to get this to work.

As you suggested I have commented out the "workLog" Update Paremeter in my Aspx file.

In my code behind I have replaced this line

SqlDataSource1.UpdateParameters("WorkLog").DefaultValue = MyTempWorkLog.ToString

with the following line

  SqlDataSource1.UpdateParameters.Add("WorkLog", TypeCode.String, MyTempWorkLog)

As you mention your syntax wasn't quite right. The 'SqlDataType.VarChar' parameter was giving me a error. After some work with Google I was able to find the second line that does not error, but also doesn't seem to work.

I've actaully tried multiple differant variations of the above line Both with and without the 'TypeCode.String' paremter and so far none have worked.

I also tried all of these versions... all of which I seen on the internet... none have worked so far.

SqlDataSource1.UpdateParameters.Add("WorkLog", TypeCode.String, MyTempWorkLog.ToString())
SqlDataSource1.UpdateParameters.Add("WorkLog", TypeCode.String, MyTempWorkLog.ToString)
SqlDataSource1.UpdateParameters.Add("WorkLog", TypeCode.String, MyTempWorkLog)
SqlDataSource1.UpdateParameters.Add("WorkLog", MyTempWorkLog.ToString())
SqlDataSource1.UpdateParameters.Add("WorkLog", MyTempWorkLog.ToString)
SqlDataSource1.UpdateParameters.Add("WorkLog", MyTempWorkLog)

I'm pretty much stumped at this point...

Mark Buckley

http://www.swiftriverphotos.com

 

[jmeckley]

not

SqlDataSource1.UpdateParameters

but

e.Command.Parameters.Add("WorkLog", SqlDBType.VarChar).Value = MyWorkLog

also note is [tt]SqlDBType[/tt] not [tt]SqlDataType[/tt]

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[putrtek]

Using your line as written I get the following error:


Too many arguments to 'Public MustOverride Function Add(value As Object) As Integer'.
[/color red]


Mark Buckley

http://www.swiftriverphotos.com

 

[jmeckley]

try this instead

Dim p as SqlParameter = new SqlParameter(WorkLog", SqlDBType.VarChar)
p.Value = MyWorkLog
e.Command.Parameters.Add(p)

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[putrtek]

We are getting closer I think...

If I comment out the "workLog' parameter in the ASPX file AND I Commnet out the Add.Parameter line in the code behind the in thoery I should NOT see a "@worklog" parameter in the Trace Log Correct?

I'm using the following code to view all the Parameters being passed

' This Routine will loop thru all the SQL Parameters and display them in the Trace Log' assumig TRACE is ENABLED
        '   Trace.Warn is printed in RED thus these lines will stand out making them easier to find.
        Trace.Warn("###################  Begin Display of SQL Parameters  ###################")
        For x As Integer = 0 To e.Command.Parameters.Count - 1
            Trace.Write(e.Command.Parameters(x).ParameterName)
            Trace.Write(e.Command.Parameters(x).Value)
        Next
        Trace.Warn("###################  End Display of SQL Parameters  ###################")
    End Sub

When I comment out both lines I mention above I STILL show a @workLog field in the Trace Log...

Mark Buckley

http://www.swiftriverphotos.com

 

[jmeckley]

post the markup/code related to the datasource object. anything that references, or is referenced by the object. leave out styling and other text that doesn't relate to data processing.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[putrtek]

Jason,

I fianlly was able to figure out what was causing the problem. The field on the form was Bound to a the datasource like so...

     <asp:TextBox ID="WorkLog" runat="server" TextMode="MultiLine" Width="100%"  
Rows = "5" Text='<%# Bind("WorkLog") %>'></asp:TextBox>

Once I removed the Bind everything worked as it should. So your LAST peice of code DID work.

Dim p as SqlParameter = new SqlParameter(WorkLog", SqlDBType.VarChar)
p.Value = MyWorkLog
e.Command.Parameters.Add(p)

Thanks Again for your expert assistance.

Mark Buckley

http://www.swiftriverphotos.com