Passing A String Into The InsertCommand Of SqlDataSource At The @color Character
Apr 27, 2007
Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by
String t_color = "red";
and then calling the insert
SqlDataSource1.Insert();
here is insert command:
InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"
I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?
hi everyone i have a SqlDataSourceand i want execute two T-Sql (two InsertCommand)but not successHow did i do this? Thanks 1 Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted2 Dim SDSTemp As SqlDataSource = Nothing3 Try4 SDSTemp = New SqlDataSource(CnStr, "")5 SDSTemp.InsertCommandType = SqlDataSourceCommandType.Text6 SDSTemp.InsertCommand = "INSERT INTO Table1 (id,t1,t2) VALUES (@id,@t1,@t2)"7 Dim id As String = e.Command.Parameters("@id").Value.ToString8 SDSTemp.InsertParameters.Add("id", id)9 SDSTemp.InsertParameters.Add("t1", CType(FormView1.FindControl("TextBox1"), TextBox).Text)10 SDSTemp.InsertParameters.Add("t2", CType(FormView1.FindControl("TextBox2"), TextBox).Text)11 SDSTemp.Insert()12 13 SDSTemp.InsertCommand = "INSERT INTO Table2 (id) VALUES (@id)"14 15 SDSTemp.InsertParameters.Add("id", id)16 17 SDSTemp.Insert()18 Catch ex As Exception19 Message.Text = ex.Message.ToString20 End Try21 End Sub22
I have a process that inserts a new record using the InsertCommand of a SqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:
I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID) This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this. As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question. Argg! Any help is appreciated. I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.
Hi SQL experts How can I make charaters change to color such as red if some condition happen in a column of database tables? Using IF.... function? Thanks Daniel
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
When using the back color property for SSAS 2008 R2, is there a good way to match the number to the desired color? I found some color pickers online, but the numbers don't match the same colors in SSAS. How can I best determine the number needed for the color I want?
Hello Following is my code which is not working. <asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource> I want to pass the value of Session("ID") into that query. How can I do that?
SelectCommand="SELECT * FROM Table1 WHERE Field1 IN @target"
And my parameter looks like this:
<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" /> This code gives me a syntax error near @target. Someone got a solution?
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
Ok-I'm new at this, but just found out that to get data off a form and insert into SQL I can scrape it off the form and insert it in the <insertParameter> section by using <asp:ControlParameter Name="text2" Type="String" ControlID="TextBox2" PropertyName="Text"> (Thanks CSharpSean) Now I ALSO need to set the user name in the same insert statement. I put in a UserName control that is populated when a signed in user shows up on the page. But in my code I've tried: <asp:ControlParameter Name="UserName" Type="String" ControlID="LoginName1" DefaultValue="Daniel" PropertyName="Text"/> and I get teh errorDataBinding: 'System.Web.UI.WebControls.LoginName' does not contain a property with the name 'Text'. So I take PropertyName="Text" out, and get the error:PropertyName must be set to a valid property name of the control named 'LoginName1' in ControlParameter 'UserName'. what is the proper property value? SO.....BIG question...Is there a clean way to pass UserName to the insert parameters? Or ANY value for that matter? Id like to know how to write somehting likeString s_test = "test string";then in the updateparameter part of the sqldatasource pass SOMEHITNG like (in bold) <asp:Parameter Name="UserName" Type="String" Value=s_test /> Thanks in advance...again! Dan
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter. I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter? Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure" DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="locationStationId" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="locationStationId" Type="String" /> <asp:Parameter Name="locationType" Type="String" /> <asp:Parameter Name="locationName" /> <asp:Parameter Name="division" Type="String" /> </InsertParameters>
Hi,I have a UserControl called DataPage with the following public property:private SqlDataSource sqlDataSource;public SqlDataSource SqlDataSource{ get { return this.sqlDataSource; } set { this.sqlDataSource = value; }}In a web form i create an instance of the DataPage UserControl and assign the SqlDataSource properties:<%@ Page MasterPageFile="~/MasterPages/Page.master" Inherits="IMS.Pages.ModelType" Title="Model Types" %><asp:Content ID="Content" ContentPlaceHolderID="ContentPlaceHolder" runat="Server"> <ims:DataPage ID="DataPage" runat="server"> <SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="IMSConnectionString" DeleteCommand="DeleteModelType" DeleteCommandType="StoredProcedure" InsertCommand="InsertModelType" InsertCommandType="StoredProcedure" SelectCommand="SelectModelType" SelectCommandType="StoredProcedure" UpdateCommand="UpdateModelType" UpdateCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="ModelTypeID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ModelTypeID" Type="Int32" /> <asp:ControlParameter ControlID="UpdateModelTypeTextBox" Name="ModelType" PropertyName="Text" Type="String" /> </UpdateParameters> <SelectParameters> <asp:Parameter Name="ModelTypeID" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:ControlParameter ControlID="InsertModelTypeTextBox" Name="ModelType" PropertyName="Text" Type="String" /> </InsertParameters> </SqlDataSource> </ims:DataPage></asp:Content> Then in the UserControl I try to call the SqlDataSource.Insert() method, and I get the following error:"The SqlDataSource control 'SqlDataSource' does not have a naming container. Ensure that the control is added to the page before calling DataBind."Now, based on the error message it sounds like that SqlDataSource is not contained in either the web form or the usercontrol. Any ideas, thanks very much for any help people.Grant
Hi, I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine. Now, instead of the default value i want to pass the current logged in user name as a parameter. How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure. Thanks, M.
Hi, I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following: 1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated. Thanks in advance!!
DataTable dt = new DataTable(); DataTable dt2 = new DataTable(); DataView dv = new DataView(); foreach(ListItem liOptions in ListBox1.Items) { if(liOptions.Selected) { SqlDataSource1.SelectParameters.Add("Parameter1", liOptions); dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); dt2 = dv.Table; dt.Merge(dt2); dt2.Dispose(); SqlDataSource1.SelectParameters.Clear(); } } if (dt.Rows.Count > 0) { Graph(dt); //Pass original datatable (dt) to Graph(); dt.Columns.RemoveAt(2); //Reformat datatable (dt) and remove last column before binding to Gridview1 GridView1.DataSource = dt; GridView1.DataBind(); } else { errorMessage.Text = "No data was returned!"; }
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex color code to be used inside SSRS for a conditional color expression?
Hello, I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related. The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated) When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan
I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?
I want to read a String - character by character.I mean If the string is 'SAMPLE' then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality. I hope I am clear.Is there a way that I can read it?
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.
Here are the function and the stored procedure wher eit occurs:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.
I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.
How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)
