Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 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!
 

View 3 Replies


ADVERTISEMENT

Passing Parameters For SqlDataSource From Code Behind

May 25, 2007

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  

View 8 Replies View Related

SQLDataSource Parameters Passing Null

Aug 23, 2007

 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>

View 3 Replies View Related

Passing Parameters To Sqldatasource Stored Procedure

Aug 22, 2006

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.

View 4 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter

Mar 28, 2007

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.
 
Code:
.
.
<td style="width: 3px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"
Width="533px">
<Columns>
<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"
SortExpression="LogId" />
<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />
<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />
<asp:TemplateField>
<ItemTemplate>
<span>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>
</ItemTemplate>
</asp:TemplateField>
 
</Columns>
<HeaderStyle BackColor="#608FC8" />
<AlternatingRowStyle BackColor="#FFFFC0" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"
SelectCommand="spViewLog" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Stored Procedure:
ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )
 
AS
SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')
RETURN
 
I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.
 Any guidance would be much appreciated.
Thank you
Lee
 
 

View 2 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

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 

View 2 Replies View Related

Setting Command Parameters For SQLDataSource

Dec 26, 2007

I have an SQLDataSource. The SQL is
 SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records.
e.Command.Parameters("@Printed1").Value = Nothing           'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False
What am I doing wrong???
Thanks
Craig

View 10 Replies View Related

Execute SQL Task Passing Parameters To A Restore Command

Mar 22, 2007

Hi,

I'm very new to SSIS and I€™m trying to do the following in a SQL task

RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10

I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?

Many thanks

Martin



View 4 Replies View Related

Setting SqlDataSource Update Command And Parameters Dynamically C#

Aug 31, 2007

Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
 
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View 7 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Passing Value To SqlDataSource Dynamically

Jul 10, 2006

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?

View 4 Replies View Related

Passing Variable Value Through Environment Variable

Feb 15, 2008



Hi All!

I have a parent package that contains two children... The second child depends on the succes of the first child.

THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...

Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...

I tried to execute coth children as Out Of Proc as well as In Proc...The same

Would anybody have an idea how to resolve this problem?

Thanks in advance for any help!

Vladimir

View 5 Replies View Related

Passing Arrays As Parameter (SqlDataSource)

Jan 12, 2007

My sql-string looks like this:

 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?

View 2 Replies View Related

Passing ANY Value To The &<InsertParameters&> Section Of A SQLDataSource

Jun 6, 2007

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  

View 5 Replies View Related

Passing SqlDataSource Properties To Class

Nov 14, 2005

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

View 3 Replies View Related

Passing Variables In An SQL Command

Apr 29, 2008

I have an SSIS package. In my control flow I have an Execute SQL Task and a data flow task. In my Execute SQL Task in the SQL Statement I have (select dbo.to_date(getdate()) as process_date) its a direct input with a result set. It gets Getdate as (processed_date) is declared. I have set no parameters, but I have set a Result Set. Also, I have set a global variable in the scope where I passes the date (processed_date)

In my Data Flow, I have an OLE DB data source with the following SQL statement in my SQL Command.
I am trying to pass down the variable as the ? .

It works when I pass it only to: lr.processed_date = ?

But I get an error when I pass it down to the : and ? between cav.begin_date and cav.end_date




SELECT distinct
acc.account_num,

FROM
cust_version_slow cvs

inner join cust_acco_version cav
on cus.cust_id = cav.cust_id

and ? between cav.begin_date and cav.end_date

inner join bia.dbo.acct acc
on cav.acco_id = acc.acco_id

inner join bia.dbo.account_version_slow avs
on acc.account_id = avs.account_id
and ? between avs.begin_date and avs.end_date

inner join bia_org ('02','2,3,4,5,6,7,9,10') boh
on cvs.branch_code = boh.branch_code

inner join accou_version_profit accM4
on acc.acco_id = accM4.acco_id
and ? between accM4.begin_date and accM4.end_date

where

lr.processed_date = ?



I need to pass down the same variable (processed_date) which is ? in all the ? in red


Thanks,

Delovan

View 11 Replies View Related

Passing SqlDataSource Object An Array As A Parameter

Feb 22, 2007

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!"; }

View 3 Replies View Related

Passing A Parameter To A USE Command Within A Cursor

May 27, 2008

Hi everyone I am trying to pass a parameter to a USE command within the body of a cursor. I keep getting an error. Here is my code.




Code Snippet
DECLARE @SQLText AS NVARCHAR(4000)
Declare @SQLText2 as NVARCHAR (2000)


DECLARE Cursor_Trial CURSOR FOR SELECT name FROM sys.sysdatabases order by name
Declare @name as varchar(255)
Open Cursor_Trial
FETCH NEXT FROM Cursor_Trial INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Use @name --------This is where I'm getting the error


Set @SQLText2 = @name
select @SQLText2, * from sys.database_principals
where type_desc='DATABASE_ROLE'
and name = 'xxxxx'

FETCH NEXT FROM Cursor_Trial INTO @name

END

CLOSE Cursor_Trial
DEALLOCATE Cursor_Trial


As you can see I am trying to pass the next database name into the body of the cursor so that the query will pull the role information only for the current database in the loop. Any thoughts or tips? Is there a better way to accomplish what I'm trying to do here?


View 1 Replies View Related

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've tried       InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")"  Ive tried        InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )"   Is there any easy way to do this? or Can I set it like @color = t_color?  Thanks in advance for ANY help JSP turning ASP (Maybe)Dan 

View 4 Replies View Related

Passing Parameter To Stored Procedure Call Within SqlDataSource

Aug 3, 2007

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>   

View 5 Replies View Related

ASP: SqlDataSource - Select Command

Aug 10, 2007

I am using  <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:
SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC">
 I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE".  In  this situation how would the syntax be written.... I tried:
LastName LIKE '%" & LastName & "%'"
But I get an error???? Any suggestions, please...
Thanks !!

View 4 Replies View Related

SqlDataSource Insert Command....

Oct 12, 2007

Hello all,
I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception...
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"                ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>"                 SelectCommand="SELECT * FROM [ServiceAgents]"                InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)">                                <InsertParameters>                    <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/>                    <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/>                    <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/>                    <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/>                    <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" />                   </InsertParameters>            </asp:SqlDataSource>
The table has got the fields that needed for insert command, for example:
            <table>                    <tr>                        <td>State:</td>                        <td>                            <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator1"                            runat="server"                            ControlToValidate="TextBox_state"                            Display="Static"                            ErrorMessage="Please enter a state." />                        </td>                    </tr>                                        <tr>                        <td>City:</td>                        <td>                            <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator2"                            runat="server"                            ControlToValidate="TextBox_city"                            Display="Static"                            ErrorMessage="Please enter a city." />                        </td>                    </tr>                                        <tr>                        <td>Agent:</td>                        <td>                            <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator3"                            runat="server"                            ControlToValidate="TextBox_agent"                            Display="Static"                            ErrorMessage="Please enter a agent." />                        </td>                    </tr>                                        <tr>                        <td>Phone:</td>                        <td>                            <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator4"                            runat="server"                            ControlToValidate="TextBox_phone"                            Display="Static"                            ErrorMessage="Please enter a phone No." />                        </td>                    </tr>                                        <tr>                        <td>Equipment:</td>                        <td>                            <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator5"                            runat="server"                            ControlToValidate="TextBox_equip"                            Display="Static"                            ErrorMessage="Please enter a equipment." />                        </td>                    </tr>                                        <tr>                        <td></td>                        <td>                            <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry"                             BackColor="#FFFBFF"                             BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"                            Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/>                          </td>                    </tr>                </table>
And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like:
Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated.
But i actually input all the required text in the textbox.... Any idea guys?
Thanks

View 2 Replies View Related

SqlDataSource Select Command

Nov 15, 2007

I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!

View 1 Replies View Related

SqlDataSource And Insert Command

Nov 20, 2007

I get the following error, when I try to create a company. Any help would be appreciated. Error Code: Cannot insert the value NULL into column 'CompanyName', table 'Telemetry.dbo.Company'; column does not allow nulls. INSERT fails.The statement has been terminated. Here is my aspx file: <table>     <tr ><td style="width:110px"><b>Company Name:</b></td><td ><asp:TextBox ID="CompanyName" Text="" runat="server" width="250px"/></td></tr>    <tr><td ><b>Phone Number:</b></td><td ><asp:TextBox runat="server" ID="CompanyPhone" Text=""  Width="250px"/></td></tr>                        <tr><td ><b>Company E-mail:</b></td><td ><asp:TextBox runat="server" ID="CompanyEmail" Text="" Width="250px"/></td></tr>                        <tr><td ><b>Street Address:</b></td><td><asp:TextBox runat="server" ID="AddressStreet" Text="" Width="250px"/></td></tr>            <tr><td ><b>City :</b></td><td><asp:TextBox runat="server" ID="AddressCity" Text="" Width="200px"/></td></tr>            <tr><td ><b>State/Province:</b></td><td><asp:TextBox runat="server" ID="AddressState" Text="" Width="150px"/></td></tr>            <tr><td ><b>Zip Code:</b></td><td><asp:TextBox runat="server" ID="AddressZip" Text="" Width="150px"/></td></tr>            <tr></tr>                </table>          <asp:Button ID="Submit" runat="server" OnClick="Submitinfo" />     <br />    <asp:SqlDataSource ID="sqlCreateCompany" runat="server" ConnectionString="<%$ ConnectionStrings:SqlServer1 %>"        InsertCommand="INSERT INTO Company(CompanyName, CompanyPhone, CompanyEmail, AddressStreet, AddressCity, AddressState, AddressZip) VALUES (@CompanyName, @CompanyPhone, @CompanyEmail, @AddressStreet, @AddressCity, @AddressState, @AddressZip)"        SelectCommand="SELECT [CompanyID], [CompanyName], [CompanyPhone], [CompanyEmail], [AddressStreet], [AddressZip], [AddressState], [AddressCity] FROM [Company]"  >                <InsertParameters>            <asp:FormParameter Name="CompanyName"  FormField="CompanyName"/>            <asp:FormParameter Name="CompanyPhone" FormField="companyPhone" />            <asp:FormParameter Name="CompanyEmail" FormField="CompanyEmail" />           <asp:FormParameter Name="AddressStreet" FormField="AddressStreet" />                       <asp:FormParameter Name="AddressCity" FormField="AddressCity" />            <asp:FormParameter Name="AddressState" FormField="AddressState" />            <asp:FormParameter Name="AddressZip" FormField="AddressZip" />                    </InsertParameters>            </asp:SqlDataSource> .....Behind the code.............. protected void Submitinfo(object sender, EventArgs e)    {        //TextBox t = (TextBox)FormView1.FindControl("CompanyName");        sqlCreateCompany.Insert();    }  .........Database Company Table Design..............CompanyID    int    UncheckedCompanyName    varchar(100)    UncheckedCompanyPhone    varchar(50)    CheckedCompanyEmail    varchar(100)    CheckedAddressStreet    varchar(100)    CheckedAddressCity    varchar(50)    CheckedAddressState    varchar(2)    CheckedAddressZip    varchar(5)    CheckedCompanyLogo    varchar(100)    Checked  

View 2 Replies View Related

Parameters Passing

Mar 14, 2008

Hi,
 how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
 
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);


}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn);  //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");

View 2 Replies View Related

Passing Parameters To DTS In C#

Apr 17, 2004

I have a dts package file. I am able to execute the file thro C#. I want to pass userid, password, datasource through program. How to do this?

DTS.PackClass package = new DTS.PackClass();

string filename = @"c:abc.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTSPack";
object pVarPersistStfOfHost = null;

package.LoadFromStorageFile(filename, password, packageID,
versionID, name, ref pVarPersistStfOfHost);
package.Execute();
package.UnInitialize();
package = null;


Thanks
Jtamil

View 2 Replies View Related

HELP! Passing Parameters

May 30, 2006

Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

View 1 Replies View Related

Transact SQL :: Passing C++ / C# Array To INSERT Command

Aug 13, 2015

I have to send an array, which are generated in C++ (or C#) to an SQL-INSERT. Something like this:

INSERT INTO Table1
(Col1,Col2)
VALUES(:a,:b)
// a and b are my arrays//:a=A,B,C,D.....//:b=1,2,3,...

Is there any way to do something like my prototype in MS SQL Server? If yes how should be my INSERT? 

View 3 Replies View Related

Using Sqldatasource Insert Command Manually

Aug 4, 2006

hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks

View 2 Replies View Related

Runtime Sqldatasource Delete Command

Aug 12, 2006

i need to dyanamically generate my SQL commands so to do that i am generating sqldatasource commands programmatically rather declaratively. SELECT commands seems to work fine but DELETE isnt doing anything, here is my code:
 
SqlDataSource sdsConsultant = new SqlDataSource();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadDataSet();  //it just loads dataset used by formview
initializeSDS();
}


}
protected void initializeSDS()
{
string strConnection = ConfigurationManager.ConnectionStrings["myDB"].ToString();
sdsConsultant.ConnectionString = strConnection;

//SELECT
sdsConsultant.SelectCommand = "SELECT * FROM Consultants WHERE (id=@id)";
QueryStringParameter id = new QueryStringParameter("id", "id");
sdsConsultant.SelectParameters.Add(id);

//DELETE
sdsConsultant.DeleteCommand = "DELETE * FROM Consultants WHERE (id=@id2)";
QueryStringParameter id2 = new QueryStringParameter("id2", "id");
sdsConsultant.DeleteParameters.Add(id2);

//UPDATE
Page.Controls.Add(sdsConsultant);
FormView1.DataSource = sdsConsultant;
FormView1.DataBind();
}
 
my formview control looks like:
<asp:FormView DefaultMode="Edit" ID="FormView1" runat="server" DataKeyNames="id"
OnItemDeleted="FormView1_ItemDeleted"
OnItemDeleting="FormView1_ItemDeleting">
 
the DELETE doesnt execute and the OnItemDeleted event doesnt do anything either. am i doing something wrong here? plz help
 

View 2 Replies View Related







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