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 ?
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
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?
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
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.
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>
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),
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) ) ) )
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
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);
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
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">
<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()
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!
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)
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)
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.
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);
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?
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?)
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
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.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
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.
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...
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
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)
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)
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.
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...
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
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.
