Update Where Column IN(1, 2) With Parameter

Jan 12, 2006

The problem is with @NUMERY parameter
in code behind i set

Dim dr As GridViewRow
Dim numery As String
numery = ""

For Each dr In GridView1.Rows
Dim numeros As Label = dr.Cells(0).Controls(1)
numery += numeros.Text & ", "
Next

numery = numery.TrimEnd(", ")

SqlDataSource1.UpdateParameters("NUMERY").DefaultValue = numery
'so numery will look like this 123, 65465, 54616, 56465

Update command looks like this :
UpdateCommand="UPDATE slon SET mrowka = @MROWKA WHERE (NUMER IN (@NUMERY))"

<UpdateParameters>
<asp:Parameter Name="MROWKA" />
<asp:Parameter Name="NUMERY" />
</UpdateParameters>

And because of @numery i have err: Error converting data type nvarchar to numeric.
how should i post "123, 65465, 54616, 56465" as parameter for this query ?

View 2 Replies


ADVERTISEMENT

Update With If Parameter?

Aug 17, 2006

Hi!
I want to write an update statement that updates a column in Table1 with the result of a calculation. The problem is that the calculation uses two different values (both from Table2) depending on the. It goes something like this:
UPDATE dbo.Table1 SET A = Table1.A * (
(Calculation > 0) * (SELECT PositiveModifier FROM Table2 WHERE Table2.ID = Table1.T) +
(Calculation <= 0) * (SELECT NegativeModifier FROM Table2 WHERE Table2.ID = Table1.T))
I know this syntax doesn't work, but it should give an understanding of what I want to do.
Of course I can add a where clause that only updates tables where the calculation > 0 and then another one that updates the other half, but I'm sure this can be done as a single statement.
Any tips?
Thanks!
Llorente

View 2 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 Replies View Related

Update Column Value In Whole Database (based On Column Value)?

Aug 27, 2015

How to Update Column Value in the whole data base (based on Column Value)?

View 2 Replies View Related

Need Help With Parameter Update Query

Aug 9, 2006

I am new the asp.net.
I have written the following code and cannot get it to read from my text boxes to assign the values to the parameters.  When I use a fixed value in a string variable and set the parameter to it, I can make it work, but not from the text boxes.
Any help is greatly appreciated.  Thanks
 
Sub btnUpdate_Click(ByVal s As Object, ByVal e As EventArgs)


' Connect to DB
Dim conInventory As SqlConnection
Dim cmdUpdate As SqlCommand
Dim strCommand As String
Dim test As String
test = "hello" 'my attempt to test the sql parameter
strCommand = ""

conInventory = New SqlConnection("server=server;uid=id;pwd=pwd;database=db")


cmdUpdate = New SqlCommand("Update [tw48_inventory] Set [Date]=@Date", conInventory)
cmdUpdate.Parameters.Add(New SqlParameter("@Date", SqlDbType.VarChar, 50))
cmdUpdate.Parameters("@Date").Value = test
'cmdUpdate.Parameters.Add(New SqlParameter("@Date", test)) 'works with constant string value
conInventory.Open()
cmdUpdate.ExecuteNonQuery()

conInventory.Close()


End Sub
Sub Page_Load()
' Connect to DB
Dim conInventory As SqlConnection
Dim cmdSelect As SqlCommand
Dim dtrReader As SqlDataReader

conInventory = New SqlConnection("server=server;uid=id;pwd=pwd;database=db")
conInventory.Open()

cmdSelect = New SqlCommand("Select * From tw48_inventory", conInventory)
dtrReader = cmdSelect.ExecuteReader

dtrReader.Read()

txtDate.Text = dtrReader("Date")
txtHDP.Text = dtrReader("HDP")
txtMDP.Text = dtrReader("MDP")
txtDTPA.Text = dtrReader("DTPA")
txtMAA.Text = dtrReader("MAA")
txtHepato.Text = dtrReader("Hepato")
txtKinevac.Text = dtrReader("Kinevac")
txtSuCo.Text = dtrReader("SuCo")
txtRBC.Text = dtrReader("RBC")
txtCardiolite.Text = dtrReader("Cardiolite")
txtVent.Text = dtrReader("Vent")
txtPaper.Text = dtrReader("Paper")
txtBlackInk.Text = dtrReader("Black_Ink")
txtBlueInk.Text = dtrReader("Blue_Ink")
txtYellowInk.Text = dtrReader("Yellow_Ink")
txtRedInk.Text = dtrReader("Red_Ink")
txtSharps.Text = dtrReader("Sharps")
txtBilling.Text = dtrReader("Billing")
txtEvac.Text = dtrReader("Evac")
txt5_8cc.Text = dtrReader("cc5_8")
txt20cc.Text = dtrReader("cc20")
txtMAG3.Text = dtrReader("MAG3")
txtOther.Text = dtrReader("Other")
dtrReader.Close()
conInventory.Close()

'txtDate.Focus()


End Sub

</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>EW 48' Inventory Listing</title>

<link id="Link1" type="text/css" rel="stylesheet" href="~/styles.css" runat="server" />
</head>
<body class="inventory">
<form id="inventory" runat="server">
<div>
<h2>EW 48' Inventory</h2>

<hr class="inventory"/>

<br />

<table width="70%">
<tr>
<td>
<asp:Label ID="lblDate" runat="server" Text="Date: " CssClass="inventory" />
</td>
<td>
<asp:TextBox ID="txtDate" runat="server" width="70" TabIndex="1" />
</td>
<td colspan="6">&nbsp;</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblHDP" runat="server" Text="HDP: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtHDP" runat="server" Width="50" TabIndex="2"/>
</td>

<td class="spacer">&nbsp;</td>

<td>
<asp:Label ID="lblMDP" runat="server" Text="MDP: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMDP" runat="server" Width="50" TabIndex="3"/>
</td>

<td class="spacer">&nbsp;</td>

<td>
<asp:Label ID="lblDTPA" runat="server" Text="DTPA: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtDTPA" runat="server" Width="50" TabIndex="4"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblMAA" runat="server" Text="MAA: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMAA" runat="server" Width="50" TabIndex="5"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblHepato" runat="server" Text="Hepato: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtHepato" runat="server" Width="50" TabIndex="6"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblKinevac" runat="server" Text="Kinevac: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtKinevac" runat="server" Width="50" TabIndex="7"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSuCo" runat="server" Text="SuCo: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtSuCo" runat="server" Width="50" TabIndex="8"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblRBC" runat="server" Text="RBC: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtRBC" runat="server" Width="50" TabIndex="9"/>
</td>
<td>&nbsp;</td>
<td>
<asp:Label ID="lblCardiolite" runat="server" Text="Cardiolite: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtCardiolite" runat="server" Width="50" TabIndex="10"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblVent" runat="server" Text="Vent: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtVent" runat="server" Width="50" TabIndex="11"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblPaper" runat="server" Text="Paper: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtPaper" runat="server" Width="50" TabIndex="12"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblBlackInk" runat="server" Text="Black Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBlackInk" runat="server" Width="50" TabIndex="13"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblBlueInk" runat="server" Text="Blue Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBlueInk" runat="server" Width="50" TabIndex="14"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblYellowInk" runat="server" Text="Yellow Ink: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtYellowInk" runat="server" Width="50" TabIndex="15"/>
</td>
<td>&nbsp;</td>

<td>
<asp:Label ID="lblRedInk" runat="server" Text="RedInk: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtRedInk" runat="server" Width="50" TabIndex="16"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSharps" runat="server" Text="Sharps: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtSharps" runat="server" Width="50" TabIndex="17"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblBilling" runat="server" Text="Billing: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtBilling" runat="server" Width="50" TabIndex="18"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblEvac" runat="server" Text="Evac: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtEvac" runat="server" Width="50" TabIndex="19"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lbl5_8cc" runat="server" Text="5.8 cc: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txt5_8cc" runat="server" Width="50" TabIndex="20"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lbl20cc" runat="server" Text="20 cc: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txt20cc" runat="server" Width="50" TabIndex="21"/>
</td>

<td>&nbsp;</td>

<td>
<asp:Label ID="lblMAG3" runat="server" Text="MAG3: " CssClass="inventory"/>
</td>
<td>
<asp:TextBox ID="txtMAG3" runat="server" Width="50" TabIndex="22"/>
</td>
</tr>
<tr>
<td colspan="8">&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="lblOther" runat="server" Text="Other Needs: " CssClass="inventory"/>
</td>
<td colspan="7">
<asp:TextBox id="txtOther" runat="server" Width="540"
TextMode="multiLine" Height="50" TabIndex="23"/>
</td>
</tr>
</table>

<br />
<hr class="inventory"/>

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
</div>
</form>
</body>
</html>

View 1 Replies View Related

Using XML As An Update Parameter In A SqlDataSource?

Nov 6, 2006

One of the requirements of the UpdateParameters for a GridView I'm building is that the fields that are being edited via EditItemTemplates are passed back to the UpdateParameter as XML. How would I go about combining the fields from the GridView/EditItems into an XML string that I can set as an asp:Parameter?
Thanks.

View 1 Replies View Related

Need Help With SQLDataSource Update Parameter

Jun 14, 2007

I need to know how to setup the ControlParameter for a template control in my Gridview? I have a datepicker in my template and I need to know how to refer to it in the ControlParameter of the SQLDataSource control.
  <UpdateParameters>
<asp:ControlParameter Name="dp_start" ControlID="bdpPlanStart" PropertyName="SelectedValue" Type="Datetime" />  Here is the template:<asp:TemplateField HeaderText="Start" SortExpression="dp_start">
<ItemTemplate>
<%#DataBinder.Eval(Container, "DataItem.dp_start", "{0:d}")%>
</ItemTemplate>
<EditItemTemplate>
<BDP:BasicDatePicker id="bdpPlanStart" SelectedValue='<%# DataBinder.Eval(Container.DataItem,"dp_start") %>' runat="server" DateFormat="d">
</BDP:BasicDatePicker>
</EditItemTemplate>
</asp:TemplateField>  

View 1 Replies View Related

Update Parameter Question

Jul 16, 2007

I'm trying to update a database field using an update parameter for a stored procedure.  The field is defined as float in SQL Server 2000 so I'm using this line.  However, if I enter 2.25 or 2.5 or similar in txtResourceHours.Text, the value is always rounded to the nearest whole number and stored in the database like this.
cmdUpdate.Parameters.AddWithValue("@resourcehours", SqlDbType.Decimal).Value = txtResourceHours.Text
Stored Procedure Code:
@resourcehours as decimal(4),
 

View 2 Replies View Related

Update Trigger Parameter

Jan 24, 2006

Hello, is it possible to pass a variable into a trigger and update the trigger by doing so? I tried the trigger below but i get an error message saying '@current_trigger_item must be declared'. I was hoping to use @current_trigger_item to hold an item passed to it(like a regular procedure) by updating the trigger when i wanted to change the variable. Hope that make sense.

CREATE TRIGGER low_item (@current_trigger_item VARCHAR(15))on stock FOR INSERT AS

BEGIN
declare @price money
declare @item_id int
declare @item varchar(15)

select @price = price from inserted
select @item_id = item_id from inserted
select @item = item from inserted

If (
@price < (select min(price) from stock
where ((item_id <>@item_id) AND
(@item = @current_trigger_item)
)
)
)

begin print 'Lowest priced item to date'
end
END

Jill

View 5 Replies View Related

Dynamically Add Update Parameter To Formview

May 3, 2007

I have a formview with name, email, and password.  I bind all fields to sql except the password which is blank.
In my sqldatasource, I define parameters for name, email and id:
UpdateCommand="UPDATE UserProfile SET Name = @Name,Email = @Email WHERE (ID = @ID)"><UpdateParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Email" /><asp:Parameter Name="ID" /></UpdateParameters>
In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated.  If I add define a password parameter like above then if a user left the password field blank then their new is blank.  That's way I think adding it dynamically is the way.  But I am having problems with the code to add the parameter in sqldatasource_updating event.
Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.UpdatingDim password As TextBox = FormView1.FindControl Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating
Dim password As TextBox = FormView1.FindControl("tb_password1")
If Not password.Text.ToString & "" = "" Then
SqlProfile.UpdateParameters.Add(New Parameter("@Password", TypeCode.String, password.Text.ToString))
End If

End Sub Thanks

View 9 Replies View Related

Transact SQL :: Openquery Update With A Parameter?

Sep 17, 2015

I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.

This is the gist of what I need to do in a stored procedure

DECLARE    @ID1        INT,
        @SQL1        VARCHAR(8000),
        @STATUS     VARCHAR(10),
        @DATE        DATETIME;
SET        @ID1 = 350719;
SET        @STATUS = 'COMPLETED';
SET        @DATE = GETDATE();
SELECT    @ID1;
SELECT  @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID =  ' + CAST(@ID1 as nvarchar(30)) + ''')'
SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE;
EXEC (@SQL1);

View 5 Replies View Related

Update Failing Due To Carriage Return In Parameter Value

Dec 15, 2006

When I make a call to a stored procedure to update a record, the update does not happen.  No error is thrown, there are just no rows updated.  When I look at SQL Server Profiler, this is what I see:
exec TTN_Update_RecurringIssueVehicle @RecurringIssueVehicleID='00962233-6EC3-42CE-ABBD-1851F1176D63',@RecurringIssueID='66FE821A-9881-4368-B975-5B04975C5E16',@RecurringIssueProblemID='ABED20CD-CB37-4491-903F-553555EEE47C',@MarketID=1,@VehicleNumber=268,@AddedDate=N'12/14/2006 3:30:00 PM',@AddedBy=12,@ResolvedDate=N'12/15/2006 10:19:45 AM',@ResolvedBy=12
 
It seems to be putting a carriage return in the middle of one of my date parameter values, and also seems to be putting those values in double quotes.  If I try to run this in SQL Management Studio, it throws an error.  If I take out the carriage return, and fix the quotes, it succeeds.  Here is my VB.Net code:
 
<DataObjectMethod(DataObjectMethodType.Update)> _
Public Function Update_RecurringIssueVehicle( _
ByVal RecurringIssueVehicleID As Guid, _
ByVal RecurringIssueID As Guid, _
ByVal RecurringIssueProblemID As Guid, _
ByVal MarketID As Integer, _
ByVal VehicleNumber As Integer, _
ByVal AddedDate As DateTime, _
ByVal AddedBy As Integer, _
ByVal ResolvedDate As DateTime, _
ByVal ResolvedBy As Integer) As Integer
dbCmd.CommandText = "TTN_Update_RecurringIssueVehicle"
dbCmd.Parameters.Clear()
dbCmd.Parameters.AddWithValue("@RecurringIssueVehicleID", RecurringIssueVehicleID)
dbCmd.Parameters.AddWithValue("@RecurringIssueID", RecurringIssueID)
If RecurringIssueProblemID <> Guid.Empty Then dbCmd.Parameters.AddWithValue("@RecurringIssueProblemID", RecurringIssueProblemID)
dbCmd.Parameters.AddWithValue("@MarketID", MarketID)
dbCmd.Parameters.AddWithValue("@VehicleNumber", VehicleNumber)
dbCmd.Parameters.AddWithValue("@AddedDate", AddedDate.ToString())
dbCmd.Parameters.AddWithValue("@AddedBy", AddedBy)
If ResolvedDate <> DateTime.MinValue Then dbCmd.Parameters.AddWithValue("@ResolvedDate", ResolvedDate.ToString())
If ResolvedBy <> -1 Then dbCmd.Parameters.AddWithValue("@ResolvedBy", ResolvedBy)
Dim retVal As New SqlParameter("@RetVal", SqlDbType.Int)
retVal.Direction = ParameterDirection.ReturnValue
dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
Return CInt(retVal.Value)
End Function
 
Any ideas are greatly appreciated - thanks

View 4 Replies View Related

SqlDataSource Update Doesn't Work When Using Parameter

Feb 3, 2007

Hi all:
I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater.  The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it.  The page connects and displays correctly.
My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database.  The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below).  In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5.  That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it.  It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it.  I am extremely frustrated - any ideas would be greatly, greatly appreciated.  Thanks!
Bruck
The table I'm pulling from and updating looks like this:
SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int)
Here's my ASPX for the main data source:
<asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)">
<UpdateParameters>

<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" />
</UpdateParameters>
And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later):
<asp:Repeater ID="rptTaskSelections" runat="server">

<HeaderTemplate><table border="0"></HeaderTemplate>

<ItemTemplate>

<tr class="abctr"><td class="normal"><asp:DropDownList ID="cbRatings" runat="server"></asp:DropDownList><asp:HiddenField ID="hTaskId" Runat="server" Visible="false" Value='<%# Eval("TASK_ID") %>' /><asp:HiddenField ID="hRating" Runat="server" Visible="false" Value='<%# Eval("RATING_ID") %>' /> <%# Eval("TASK_ID") %></td></tr>
</ItemTemplate>

<FooterTemplate></td></tr></table></FooterTemplate>
</asp:Repeater>
And here's the page load and submit VB:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not Page.IsPostBack Then


'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()


For i = 0 To rptTaskSelections.Items.Count - 1



cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentRating = rptTaskSelections.Items(i).FindControl("hRating")



cbCurrentRating.DataSource = sqlRatingscbCurrentRating.DataTextField = "RATING"cbCurrentRating.DataValueField = "ID"cbCurrentRating.DataBind()cbCurrentRating.SelectedValue = hCurrentRating.Value


Next

End If
End Sub
Protected Sub btnSubmitRateTasks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmitRateTasks.Click

'UPDATE RATINGS HERE

Dim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentTaskId As HiddenField

For i = 0 To rptTaskSelections.Items.Count - 1


cbCurrentRating = rptTaskSelections.Items(i).FindControl("cbRatings")hCurrentTaskId = rptTaskSelections.Items(i).FindControl("hTaskId")


sqlTaskSelections.UpdateParameters.Item("pTaskId").DefaultValue = hCurrentTaskId.ValuesqlTaskSelections.UpdateParameters.Item("pTaskRating").DefaultValue = cbCurrentRating.SelectedValue
sqlTaskSelections.Update()

Next

Response.Redirect("nextpage.aspx")
End Sub
 

View 3 Replies View Related

Assign Session Variable Value To Update Parameter

Jun 12, 2007

Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.
code snippet:   <UpdateParameters><asp:Parameter Name="hrs_credited" />
<asp:Parameter Name="updater_id" DefaultValue="<%$ Session("User_ID")%>" Type="Int32"/>
<asp:Parameter Name="activity_id" />
<asp:Parameter Name="attendee_id" /></UpdateParameters>
The error message that I receive is:
 Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:DevelopmentCMEdataentryattendance.aspx 29 
Does anyone have an idea how to assign the session var value to the parameter?
Thanks!

View 1 Replies View Related

Convert Column And Update Column

Jun 20, 2000

Hello,

I'm using SQL Server 7. I have an invoice table. The invoice table has a datetime column called InvoiceDate. The InvoiceDate column contains the following date format:
5/3/00
I would like to use the InvoiceDate column to update the char (6) column called zInvoiceDate as a formatted date field like yymmdd.

The following syntax did not work:
SET zInvoiceDate = Convert([ARInvoiceID],GetDate()12)

Any suggestions please :)
Thanks,
Denise

View 4 Replies View Related

Update Column Value Based On Value In Another Column?

Jan 29, 2008

Hi.

I'm writing a web application with VS2005 andSQL Server 2005 express edition.

I have an SQL table:

Table name:
statistics

Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage

When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:

UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)

This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)

Any help would be appreciated.

View 2 Replies View Related

Update 1 Column With Data From Another Column

Mar 12, 2007

Hello,

I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.

View 6 Replies View Related

SqlDataSource.Update With Session Variable As Input Parameter

May 25, 2007

I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID".  Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
 
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>

View 1 Replies View Related

Parameter As Column Name?

Sep 14, 2007

 Greetings,     I am developing a search form that uses a DDL to select a column name and a text box to define a search term.  Code follows:      1 cmdSearch = New SqlCommand( "select * from sites where @columnName like @sTerm order by site_name", conSites)
2
3 cmdSearch.Parameters.Add( "@columnName", SqlDbType.Text).Value=ColumnParam
4 cmdSearch.Parameters.Add( "@sTerm", SqlDbType.Text).Value=searchParam
  The problem I'm running into is that the @variable appears to imply single quotes about the value.  This is fine for @sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @columnName and the query returns no values. If I manually substitute an appropriate column for @columnName sans single quotes, it works. How can I eliminate the implied single quotes from @columnName so that the query will read it properly? 

View 4 Replies View Related

How To Use Parameter Value As A Column Name

Aug 8, 2006

I am feeding in @RsCategory varchar (25) into my stored procedure. I was hoping to make the RsCategory = the column name from the table I would like to select data from.

How can I use @RscCategory as the column name value in the select statement? (or can I?)

select @RsCategory as displaydata

From tTable T

where T.MyOtherParams=@MyOtherParams

Any help would be greatly appreciated!

View 2 Replies View Related

Passing Parameter To OLE DB Command To Perform Update On Paradox Table

Aug 23, 2007



Dear All -
iam facing a problem for passing a parameter to update paradox table statment in the OLE DB Command

Briefly an OLE DB Command exist between OLE DB Source and OLE DB destination , all i want it to send the data that flow from the OLE DB Source to the update paradox table statment in OLE DB Command ,

i use the parameter annotation which is "?" to refer to the parameter in the update where clause and it sucess when i use the OLE DB Command to connect to SQL Server DB but when i use it with the Paradox Table an error appear saying that there is more than one data source column with the name "?" The data source column names must be unique.

i dont know what that means is it means that "?" it consider "?" as a column or what or is there another way to send parameter to Update statment for the paradox table differ from the SQL Server Update statment which is the "? "

i hope someone help me as soon as possible because i need it in work sOOon
Thanks,
Maylo

View 2 Replies View Related

SQL Parameter Update Not Updating Changed Null/blank Values

Oct 24, 2006

I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.

SqlConnection1.Open()

strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "

cmdCategoriesUpdate.CommandText = strSQLu

With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()

View 3 Replies View Related

How To Retrieve Value From Particular Column By Using Parameter

Jan 5, 2008

suppose a table has 4 column,
 
Name     x         y             z        z1
A          1          2             3        3
B          0          2              0       3
C         1           0             3        3
if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then   Z column And Z1 column should be displayed. plz help to create sql query.
 
 

View 3 Replies View Related

Passing Column As Parameter

Aug 1, 2014

I have a table with month names as columns, below is the structure

CREATE TABLE [MonthlyTarget]
(
[Jan] [int] NULL,
[Feb] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,

[Code] ....

I will store some integer values in these columns,

I want to pass these column names as a parameter,and pull the data from the column.

How to do this..?

View 1 Replies View Related

Where Clause - Column Name As Parameter

Sep 20, 2014

In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?

So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?

I do not want to use dynamic SQL string concatenation...

View 5 Replies View Related

How To Pass Column Name Trough A Parameter

Jul 12, 2004

Hello ,please help me
Is it possible to pass a column name through a parameter
something like this
create procedure dbo.paramerers
@parameter varchar
as
select @parameter from table1 where....

because it doesn't give me an error but it also doesn't work

View 1 Replies View Related

Passing Column Name As A Query Parameter?

Dec 19, 2006

Hi,

Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question.


Doesn't Work!!


Code:


SELECT

1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)






Works, but I need to pass in the column name dynamically.


Code:


SELECT

1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'


FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID

WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)

View 1 Replies View Related

Updating A Column With Input Parameter?

May 19, 2014

Is it possible to assign to a column a value passed as a parameter?

When I run the proc I get the following error :

Msg 245, Level 16, State 1, Procedure Transfer, Line 17

Conversion failed when converting the varchar value '@ID' to data type int.

----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)

[code].....

View 1 Replies View Related

Parameter As Table Column Values

Sep 21, 2007

can we define a stored procedure parameter which holds a table's column values?
And can we loop in it in a stored procedure?

If yes, could you give a little sql code sample?

Thanx

View 3 Replies View Related

Column Name As Parameter Dynamic Sql Alternative

Feb 11, 2008

I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.

Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.

View 2 Replies View Related

Pass Column Name Using Parameter In SQL Statement...

Oct 19, 2007

Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...

View 5 Replies View Related

Passing Table/column Name As Parameter

Jul 20, 2005

I know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou

View 2 Replies View Related

WHERE Clause Using Different Column Passed By Parameter

Jul 30, 2007

Hi all,

I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.

Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.


DECLARE @strSearchType varchar(2)

DECLARE @strSearchValue varchar(15)

DECLARE @SearchField varchar(15)

set @strSearchType = 'PN'

set @strSearchValue = '1234567'


IF @strSearchType = 'PN'

begin

set @SearchField = 'PART_NUMBER'

end

ELSE

begin

set @SearchField = 'INTERNAL_SKU'

end

SELECT

SKU as 'SKU',

PART_NUMBER as 'PartNumber',

DESCRIPTION as 'Description'

FROM SKUs

WHERE @SearchField = @strSearchValue

FOR XML PATH('SKU'), ROOT('Response')


Thanks,

ST

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved