parameter must be defined error on mysql with c#

the below code gives Parameter '@row' must be defined error on filling data adaptor what is wrong with my code ... best regards StringBuilder sql = new StringBuilder();

sql.Append("SET @row:=0;");
sql.Append("SELECT * From (");
sql.Append("Select @row:=@row+1 As Rec_No, _Col1, _Col2");
sql.Append("From _Tab1) As _T1");
sql.Append("WHERE Rec_No > 1 And Rec_No

Posted On: Tuesday 20th of November 2012 04:12:37 AM Total Views:  598
View Complete with Replies

Related Messages:

How to retrieve output parameter after Oracle INSERT?   (193 Views)
I have a DetailsView which can insert records into a table in an Oracle 10g database. It is bound to an object data source. On the INSERT command in the dataset used by the datasource I have the clause "RETURNING id INTO :NewPack", which causes Oracle to give back the allocated ID of the newly-inserted row into the parameter called :NewPack. I know this all works at Oracle level, because I use this method quite successfully when I insert rows in code (i.e. not using a DetailsView) in a different table in another part of the application. Doing it via the DetailsView causes no error or anything from Oracle or ASP.NET, but Icannot retrieve the value from the NewID parameter. On the ItemInserted event for the DetailsView, I do this: iNewPack = dsPack.InsertParameters("NewPack").DefaultValue but the value it gets is always zero. I've also tried doing the same thing on the Inserted event on the object data source - same result. The problem seems tostem from the factthat the insert is being done by the DetailsView. The parameter is of the correct datatype on the object datasource and in the dataset it is based on. Its direction is "Output" in both. What am I missing here The "DefaultValue" property seems the only likely one on the parameter that could contain the returned value. I am using just the standard Oracleprovider that comes with Visual Studio 2008 Pro. Is this a bug Should it work Is there another way to do it
How to get OUT parameter from MySQL stored procedure   (103 Views)
I'm following a datalist paging tutorial over here: which shows how I can get the value of an output parameter from the stored procedure by calling ObjectDataSourceStatusEventArgs.OutputParameters I'm trying to achieve the same thing using MySQL, and here's what my stored procedure looks like DELIMITER$$ DROPPROCEDUREIFEXISTS`outdoorventure`.`GetSubCategories`$$ CREATEPROCEDURE`outdoorventure`.`GetSubCategories` (INcidINT, OUTcnameVarChar(50) ) BEGIN SETcname=(SELECTCatNameFROM`category`WHERECatID=cid); SELECTSubID,SubName,IFNULL(SubPicture,'no_image.jpg')ASSubPictureFROM`sub_category`WHERECatID=cid; END$$ DELIMITER; This is my ObjectDataSource: This is the method that my ObjectDataSource is calling to fill it: [DataObjectMethod(DataObjectMethodType.Select)] public static List GetSubCategoriesByCatId2(int CatID, string cname) { MySqlConnection connection = new MySqlConnection(GetConnectionString()); MySqlCommand command = new MySqlCommand("GetSubCategories", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("cid", MySqlDbType.Int32).Value = CatID; command.Parameters.Add("cname", MySqlDbType.VarChar).Direction = ParameterDirection.Output; connection.Open(); MySqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection); List SubList = new List(); while (dr.Read()) { SubCategory sub = new SubCategory(); sub.SubID = Convert.ToString(dr["SubID"]); sub.SubName = Convert.ToString(dr["SubName"]); sub.SubPicture = Convert.ToString(dr["SubPicture"]); SubList.Add(sub); } dr.Close(); return SubList; } And finally, the code behind: protected void SubCategoryDataSource_Selected(object sender, ObjectDataSourceStatusEventArgs e) { Label1.Text = Convert.ToString(e.OutputParameters["cname"]); } Everything works fine, I'm able to populate my datalist, the only problem is this: Label1.Text = Convert.ToString(e.OutputParameters["cname"]);e.OutputParameters["cname"] is null, how can I get the value of my output parameter in this context
FoxPro dbf - trouble passing parameter to a Grid View.   (434 Views)
I'm using Visual Studio 2005. I have a GridView connected to a FoxPro dbf. I created a VFPOLEDB data source using Server Explorer. If I plug in a literal value in the GridView[Configure Data Source] - & test the sql - I get data returned. I've been trying to set up the Data Source toaccept a parameter value from a TextBox on the aspx page & I can't get any records returned. When I try & test the sql within the GridView | Configure Data Source - with a parameter - I get no records returned. In the GridView | Configure Data Source . . . This works: SELECT job_num, piece_num FROM filmjob WHERE (job_num ='AB123456CD') This does not work: SELECT [job_num], [piece_ord], [piece_num] FROM [filmjob] WHERE ([job_num] = ) This does not work: SELECT [job_num], [piece_ord], [piece_num] FROM [filmjob] WHERE (job_num = ) - I get an error message that says, "There was an error executing the query. Check the syntax of the command and if present the types and values of the parameters and ensure they are correct. Attempted to read or write protected memory. This is often an indication that other memory is corrupt." . . . or I just get no records returned. I know it has something to do with the sql that VS 2005 is creating in order to receive the parameter but I don't know how to pass the parameter. I've used the gridview for SqlServer databases & Access databases . . . and haven't had a problem . . . but this is FoxPro . . .. I hope I've given all the necessary information. I'd greatly appreciate any help.
Passing string parameter into method   (185 Views)
I am using ASP.NET 2.0 with C#. I am trying to pass in a string value in my method which a field value in my Oracle database. Here is what I am trying to accomplish. 1). Send string value into query string 2). Execute the query and return the corresponding value that has the string value in that record. example: PAss in my name and it should return my age Here is my code snippet GetValue.aspx.cs Page_load() MyName = "Bill Cosby"; DropdownList1.SelectedValue = GetSomething.MyAge(MyName); My Class public class GetSomething { // Getting my age public static string MyAge(string _name) { string ageValue = string.Empty; OracleConnection Conn = new OracleConnection(); Conn.ConnectionString = ConfigurationSection.DbConnectionString; OracleCommand cmd = new OracleCommand("SELECT NAME, AGE FROM person WHERE NAME = " + _name + " ", Conn); cmd.CommandType = CommandType.Text; Conn.Open(); OracleDataReader reader = cmd.ExecuteReader(); try { while (reader.Read()) { ageValue = reader["AGE"].ToString(); } } finally { Conn.Close(); Conn.Dispose(); } return ageValue; } } When i execute these lines of code here is the error is recieve " ORA-00904: "Bill Cosby": invalid identifier ". What is it that I am doing wrong
Can I use a ControlParameter for a stored procedure's parameters? (SqlDataSource,Oracle)   (184 Views)
I have anaspx page which isa survey. The user fills in various values, and then thosevalues are to be saved as the survey resultson the Oracle DB. The examples below are not the survey itself, but they're similar to what I would betrying to do. The goal is to avoid explicitly writing all that code for creating a connection, a command, and then adding the parameters one by one. (Several of these surveys have many, many input survey values; one even has over 100 input parameters, and I was hoping that "declarative" code would be an improvement over procedural code). Is it possible to connect visual controls which are within the ItemTemplate of a FormView with within of a SqlDataSource I've been trying different things in the ControlParameter's ControlID attribute; I'm getting error messages such as "Could not find control 'TextBox1' in ControlParameter 'TESTNUM'."and Could not find control 'Form1.TextBox1' in ControlParameter 'TESTNUM' and Could not find control 'Form1$TextBox1' in ControlParameter 'TESTNUM' and Could not find control 'Form1%TextBox1' in ControlParameter 'TESTNUM' Here's what's in my Oracle package: createorreplacepackageLAMTESTPKG_3IS PROCEDURELAMTEST_INPUTS(testnuminnumber,num2innumber,msginvarchar2); ENDLAMTESTPKG_3; Here's the FormView visual control on ASPX page: 18 100 World! The names of the three visual controls are TextBox1, TextBox2, and TextBox3.I've initialized their Text properties using the Properties window while in Design mode. (namely, 18, 100, " World!")Here's what's in the SqlDataSource: Here's the full error message: Server Error in '/Test_StoredProces' Application. Could not find control 'TextBox1' in ControlParameter 'TESTNUM'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Could not find control 'TextBox1' in ControlParameter 'TESTNUM'.Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: Could not find control 'TextBox1' in ControlParameter 'TESTNUM'.] System.Web.UI.WebControls.ControlParameter.Evaluate(HttpContext context, Control control) +1725436 System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control) +46 System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control) +103 System.Web.UI.WebControls.SqlDataSource.LoadCompleteEventHandler(Object sender, EventArgs e) +40 System.Web.UI.Page.OnLoadComplete(EventArgs e) +2063076 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1875 Version Information:Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Updating a foxpro parameter   (164 Views)
I'm stumped trying to issue an update statement as with VFPOLEDB you have to use question marks instead of @var. So my statement update orders_web set office=,noun= where inv_nbr= has got me baffled because my datakeyname is inv_nbr, not a question mark. Any ideas
CLOB Size when specifying parameters   (198 Views)
! I wonder if someone could help me with their expertise. I have writen a data services class which is responsible for extracting OUT parameters from my Oracle stored procedures in CLOB format. I am using the MS Oracle Client as dealing with CLOB suits me as it conicides with my workflow message formats. However, my question is, what is the maximum output value for a CLOB size when building parameters If I exclude it it fails saying it needs to be specified How do I specify this THe actualy error I get is saying I have a character overflow for my (very small) xml string, this is because just to keep .net happy I set an arbitary value of 4000 (which I assume means 4000 characters which I have exceeded). Incidently I increased this to 10000 and the procedure brings back nothing but nulls !!!!! I am very confused ! Perhaps someone could benefit me with their expereince !!!
Deriving parameters using Oracle.DataAccess.Client (ODP.NET)   (274 Views)
, In an effort to resolve my other posted issue, I started porting the DAAB to use ODP.NET instead of System.Data.OracleClient. I am wondering if there is any way with Oracle.DataAccess.Client to achieve the System.Data.OracleClient.OracleCommandBuilder.DeriveParameters() functionality of deriving all the parameters in a stored procedure command
what are the paramters to be set in parameter file for data warehousing   (200 Views)
Hi what are the parameters to be set in paramter file for data ware housing
v$parameter   (102 Views)
What column in the v$parameter tells you the number os CPU's
the max length of the varchar2 parameter in oracle stored function   (198 Views)
I created a stored function. The function has a parameter. The parameter type is varchar2. I used jdbc called the stored function. The parameter in java is string. I want to know the max length of the varchar2 parameter in oracle. Would you tell me please help.
Table storage parameter   (198 Views)
Can somebody tell me is there any formula available for 1.Deciding the table storage parameter . 2.If my approx. database size is known to me. 3.My approx. table size is known. I am more interested in knowing the formula if any.
sqlnet.ora parameter keeps changing - please help   (296 Views)
I'm not a DBa so please excause me this is a stupid question. but my dba has asked me to investigate whether backups are changing the authentication parameter in one of is ORACLE servers sqlnet.ora files. It keeps change from authentication NTS to none and the dba has to change it daily. I don't thinks its a backup thing but is there anything DB side that can cause this TIA
session parameter values   (123 Views)
Is there a dynamic view that displays local session parameter values
problems with db_writer_processes parameter   (377 Views)
Hallo, my DB is running with two CPUs. For more efective writing data to RAID 5 discs I try to start 4 db_writer_processes. I can start 2 but not 4 of them. Is there any rule I do not now Please help me! Christian Oracle9i Release - Production PL/SQL Release - Production CORE Production TNS for 32-bit Windows: Version - Production NLSRTL Version - Production
pass parameter into sqlloader .ctl   (145 Views)
I am on Unix-Solaris with Oracle 9i I want to pass a parameter (timezone) into a sqlloader control file. The line I need the parameter to go into is: PHP Code: MKT_PRICE_DT"new_time(to_date(:MKT_PRICE_DT,'YYYY-MM-DDHH24:MI:SS'),'$1','GMT')", Our timezone changes from EDT to EST and I want to automate this. I tried using: `date +'%Z'` also tried hardcoding a parameter in the shell script: timezone=$(date +'%Z') I keep getting errors. Any advice or assistance is helpful.
parameter file   (125 Views)
How can we know that which parameter file the instance is using. Is there any dictionary view..
Odbc parameterized query.   (205 Views)
I'm trying to create a paramerterized query for an odbc connection but I can't get it to work I'm must be missing something or it is not possible with a reader. Heres is the code I have so far: publicTGetSingleEntity(stringid) { Ttemp=newT(); using(OdbcConnectionconnection=newOdbcConnection(connectionString)) { connection.Open(); OdbcCommandcommand=newOdbcCommand(null,connection); //using(OdbcCommandcommand=connection.CreateCommand()) { //command.CommandText=GetBarCommandTextForSingle(id); //command.Parameters.Add("",OdbcType.NVarChar).Value=id; command.CommandText="Select*fromTablewheretheID='@id'";//thisquerydoesnotwork. //command.CommandText="Select*fromBPRAttorneyInfowherezk_idnum_p='"+id+"'";//thisquerydoes. command.Parameters.AddWithValue("@id",id); command.Prepare(); //OdbcParameteridParam=newOdbcParameter("@id",OdbcType.NVarChar,200); //idParam.Value=id; //command.Parameters.Add(idParam); //command.Prepare(); //command.Connection.Open(); using(OdbcDataReaderreader=command.ExecuteReader()) { objecttempEntity=(object)temp; if(reader.Read()) { //DoSomething } return(T)(object)tempEntity; } } } } This might not be a problem as long as mvc paramertized action input. If it doesn't I need to figure out how to paramertize my query properly.
How to call a stored procedure in sqlyog enterprise that accepts a parameter of varchar   (221 Views)
How to call a stored procedure in sqlyog enterprise that accepts a parameter of varchar For e.g i have a following stored procedure CREATE DEFINER=`akhan`@`` PROCEDURE `GetCampaignTotalBouncedEmails`(p_MailChimpCampaignID varchar(50))BEGIN Bla bla bla END And i am calling it in the query window as: call GetCampaignTotalBouncedEmails(mycampaign_id_here); But it results 0 0 0 0 But when i writes call GetCampaignTotalBouncedEmails( ' ' ); Colons with empty parameters it shows the result of '0' '250' '236' '13' in four columns etc etc So what is the correct syntax to call it..........
How to clear oracle parameter collection?   (150 Views)
Hi , I am getting this exception: The OracleParameter is already contained by another OracleParameterCollection. How to remove previous parameter Please help me.