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"> </td>
</tr>
<tr>
<td colspan="8"> </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"> </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"> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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> </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> </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"> </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
ADVERTISEMENT
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
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
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
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
Jan 12, 2006
The problem is with @NUMERY parameterin code behind i setDim dr As GridViewRowDim numery As Stringnumery = ""
For Each dr In GridView1.RowsDim 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, 56465Update 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
View Related
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
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
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
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
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
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
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
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
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
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
May 22, 2015
I have an expression based project connection manager in an existing package and updating Packet Size parameter.
After update and clicking on OK, Packet Size value reverts back to 0 (which is old value). This connection manager is used by lot of Dataflow tasks in my package and recreating the connection manager and updating the reference is not a viable option.
View 2 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Oct 18, 2006
Dear Friends,
I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.
SELECT DIR FROM DIRECCAO
BIG QUERY with DIR input parameter.
How can I do?
Thanks.
View 4 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Mar 2, 2007
I have to create a stored procedure where the criteria is: "All", specific value and, all except one value so far this is what I have:@Status varchar (50) -- as parameter from a dropdown boxDECLARE @NewStatus varchar(50)
SET @NewStatus =
CASE
WHEN @Status ='All' AND @Status <> 'All but closed' THEN '%'
WHEN @Status <>'All' AND @Status <> 'All but closed' THEN @Status
END
and in the stored procedure
.........
WHERE Status like @NewStatus I am a little confused as to how could I return all values except those that have the value "Closed" Thanks.
View 2 Replies
View Related
Mar 23, 2008
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetAllArticlesByCatID" TypeName="articlesTableAdapters.tblArticleTableAdapter">
<SelectParameters>
<asp:QueryStringParameter Name="articlecatid" QueryStringField="catid" Type="Int32" />
what goes here?
</SelectParameters>
</asp:ObjectDataSource>
I'm using this to populate some templates I'm using.
I need to add a parameter for the query:
SELECT tblArticle.ArticleCatID, tblArticle.ArticleID, tblArticle.ArticleText, tblArticle.ArticleTitle, tblArticle.Author, tblArticle.ShortDesc, tblArticle.WebSiteID, tblWebSite.SiteURL, tblWebSite.PageRankFROM tblArticle INNER JOIN tblWebSite ON tblArticle.WebSiteID = tblWebSite.WebSiteIDWHERE (tblArticle.ArticleCatID = @articlecatid) and tblArticle.Active=@active
This is from a tableadapter. How to I get the True value passed into this thing? When an article is initially saved, the active value is set to false until the article is reviewed. Once everything is ok, then the status is set to actve. So, although everything is sitting there in the table for the article, the directory will not pull it up with other search results already approved.
View 2 Replies
View Related
Jul 11, 2007
Hi,Is it possible to create a query with an IIf Function but using a parameter in the expression?This is what I'm trying to do, except it doesn't work:SELECT Related_Area_Id, Course_Code, Course_Public_Title, Course_Level, Course_WBLTTG_OnlyFROM dbo.CourseWHERE (Related_Area_Id = @CurriculumArea) AND (Course_Level = @CourseLevel) AND (Course_WBLTTG_Only = IIf(@Apprenticeship = True, 1, 0)) Are there any other solutions?Thanks
View 2 Replies
View Related
Aug 28, 2014
I am working with a Report Designer where I am using SQL to get data which user wants to see so I am using parameters.In my SQL database, name and surname columns can not be NULL, but nick can be NULL.The problem is with 3rd parameter ${Nick}. I need to do some condition like this: if user lefts 3rd parameter empty, it should select people where table.nick is NULL, otherwise it should select people by entered nick. But I do not know how to rework my query.
SELECT
table.name,
table.surname,
table.nick
[code]....
View 4 Replies
View Related
Jul 20, 2005
Hi,The following parameter query SQL statement on a sql analyzer gives theerror message given below.Select * from contact where cus_cust_no = ?[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax errorThanks*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Nov 2, 2006
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
View 3 Replies
View Related
May 13, 2007
Hello,
I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress
Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3.
How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table.
My idea was to do it like this:
select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress)
This doesn't seems to work. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
thanks
View 4 Replies
View Related
Mar 4, 2008
Im struggling with this, I think its probably quite simple. I have a table that looks like this:
RecordId
PictureId
KeywordId
111
212
313
421
522
623
725
817
932
1044
I want to be able to run a query for a distinct listing of PictureIds that have KeywordId=1 and Keyword=2. When trying a simple query like:select PictureId from PicturesKeywords where (keywordid=1 and keywordid=2) group by PictureId
It obviously returns nothing. What am I doing wrong?
View 12 Replies
View Related
Dec 13, 2000
I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:
"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."
There is nothing wrong with the table name.
I cant understand what the problem is ?????
Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.
Thanks,
Ruaidhri
Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb
Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400
dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function
View 1 Replies
View Related
Mar 22, 2006
Hi all I am trying to create a parameter in sql getting an error message dont know why?/
View 6 Replies
View Related
