|
I am writing a class to talk to a database I've written, the database has a stored function which takes one parameter and returns a value, it works fine when I execute it just with MySql however when I use the class I have written I get no error but the value that is returned from "ExecuteScalar" is null. The executeNoReturn Works perfectly for a stored procedure that adds some data to the DB.
Any suggestions would be appreciated
public class Parameter
{
private string mName;
private object mValue;
public Parameter(string nameIn, object valueIn)
{
name = nameIn;
value = valueIn;
}
public string name
{
get
{
return mName;
}
set
{
mName = value;
}
}
public object value
{
get
{
return mValue;
}
set
{
mValue = value;
}
}
}
public class Db
{
MySqlConnection conn;
public Db()
{
conn = new MySqlConnection("Database=test;Data Source=localhost;User Id=root;Password=alexa");
conn.Open();
}
public object executeSingleReturn(string storedName, List<parameter> parameters)
{
object returnObject = new object();
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = storedName;
foreach (Parameter a in parameters)
{
command.Parameters.AddWithValue(a.name, a.value);
}
returnObject = command.ExecuteScalar();
return returnObject;
}
public void executeNoReturn(string storedName, List<parameter> parameters)
{
MySqlCommand command = new MySqlCommand();
MySqlTransaction trans;
trans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = trans;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = storedName;
foreach (Parametera in parameters)
{
command.Parameters.AddWithValue(a.name, a.value);
}
command.UpdatedRowSource = System.Data.UpdateRowSource.None;
command.ExecuteNonQuery();
trans.Commit();
}
~Db()
{
conn.Close();
}
}
|
|
|
|
|
Hi all,
I need some help in query design...Following is the scenerio
table1
|length|breadth|height|dimen|
|20|20|20|20x20x20|
|40|20|20|40x20x20|
|50|50|20|50x50x20|
|20|20|70|20x20x70|
the user in stage 1 of the app inserts only the len,bre,height... in cycle 2 i have to calculate the dimen(lenxbredxheight).How can i do it using a single query..Do i need to use cursors ..If yes then how????
Thanks in adv...
When you fail to plan, you are planning to fail.
|
|
|
|
|
Would this not be enough? Why do you think you need to use Cursors?
update table1
set dimen = length*breadth*height
------------------------------------------------------------
"The only true wisdom is in knowing you know nothing." --Socrates
|
|
|
|
|
Try this in stage 2:
<br />
update table1<br />
set dimen = cast(length as varchar(2)) + 'x' + cast(breadth as varchar(2)) + 'x' + cast(height as varchar(2))<br />
from table1
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Hi,
While you inserting the first three data i.e length,breadth,height do like
this
insert into table1(length,breadth,height,dimen)values
(length,breadth,height,length*breadth*height)
Or
First insert
insert into table1(length,breadth,heightvalues
(length,breadth,height)
in the second cycle just update the table.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Hello everybody
I finally have a bit of time to look into a problem which has been annoying me for some months, without a solution so far. I'm hoping some clever guru will take pity on me and help resolve it. 
The situation:
I have created a custom control that uses a data source to display dates on a web site, and allow the user to select a range of dates.
However, I have different problems depending on the data source I use.
- Access database using a dataset generated in VS 2005: selecting a range works beautifully
However, I then can't overwrite the Access file on my ISP's web server, and besides, it's overkill for a handful of records, so I tried solution number 2:
- XML file loaded into a dataset created in my code: the control "forgets" the data source when the user selects a range.
The control in its buggy XML incarnation is visible here: http://www.eburrows.co.uk/apt111/availabilitybug.aspx.
While its working Access dataset-based version is here:
http://www.eburrows.co.uk/apt111/availability.aspx
Of course, full source code will be provided to anyone kind enough to volunteer their expertise!
Thanks!
|
|
|
|
|
Hi! Just a basic question but I don't know the answer =)
Please help
So I have a stored procedure with 2 select query
SELECT A,B FROM table1
SEleCT C,D FROM table2
In my typed dataset, how do I retrieve values in table2 using da.Fill() or should I be using another code for this?
da.Fill(dt);
when I can't use somethign like da.Fill(dt2);
Thank you very much.
Gerri
|
|
|
|
|
you need to fill a dataset with the output from the stored procedure
so:
dim ds as dataset
da.fill(ds)
then the dataset will have 2 tables in it (normally)
and you can select the 2 tables by there index
dim dt1 as datatable = ds.tables(0)
dim dt2 as datatable = ds.tables(1)
sorry for the vb.net code but should be easly translated
hope this helps
If my help was helpfull let me know, if not let me know why.
The only way we learn is by making mistakes.
|
|
|
|
|
Thank you. I would just like to ask. I have a Report.xsd file and uses this:
DataAccess.dsReportManagementTableAdapters.rpt_RetrieveTableAdapter da = new DataAccess.dsReportManagementTableAdapters.rpt_RetrieveTableAdapter();
DataAccess.dsReportManagement.rpt_RetrieveDataTable dt = new DataAccess.dsReportManagement.rpt_RetrieveDataTable();
da.Fill(dt);
How come my Fill() doesn't take a DataSet as a parameter?
Thank you.
Gerri
|
|
|
|
|
because you don't use a dataadapter but a tableadapter
from the looks of it you are trying to extract data from a xml file (on a side note xml isn't the ideal way for datastorage) I have never tryed this before but look of you cant find a xmldataadapter (or somthing like that) normally that should accept a dataset as parameter
If my help was helpfull let me know, if not let me know why.
The only way we learn is by making mistakes.
|
|
|
|
|
Please assist me in inserting special characters into a sql table whose column datatype is set to varchar. I actually developed a VB script which extracts data from xml file and inserts into sql database. In the xml file i have data where description of a field has text like phone ® (i.e text representation of registered trademark symbol). This is causing error when parsing the xml file and it says ERROR: Reference to undefined entity 'reg'. Any help would be appreciated.
|
|
|
|
|
why don't u change column DB type to nvarchar !! 
|
|
|
|
|
I have a sql statement wich gets the columnnames from several table by simply selecting nothing and putting that into a datatable (vb.net) off wich I read the columns
it works fine with one exception
when I have the same columnname more than once an number is added behind the column name
for instance if I have the columnname 'country' twice I will get 'country' and 'country1'
is there anyway to force it to display 'project_country' and 'client_country' or something like that so that I know from wich table the column is comming.
I use select * so giving the columns a unique name in the select statement is impossible. I use the select * because there is the posibilaty that a column is added after deployment of the main programme. Not using the select * would mean I would have to alter the sqlstatement to include this column and that would defeat the whole purpose of the programme I'm currently creating.
any help would be appriciated
after further investigation I found out that this is a vb.net problem so could an admin please move this post there, thank you
If my help was helpfull let me know, if not let me know why.
The only way we learn is by making mistakes.
modified on Wednesday, January 09, 2008 10:37:55 AM
|
|
|
|
|
Follows are the query which returns Budget, FortheMonth, YeartoDate, ProjectToDate column through subQuery....Which i use column in subQuery it will not sum/Calculate the amount when i hard code values which are in VW_FN_ANASTUDYSETUP.RootIDs column then it calculate....please guide me in my query...thanks
SELECT ANA_ID, RootIDs, ANA_PARENT_ID, DESCRIPTION, BUD, FORTHEMONTH, YEARTODATE, PRJTODATE, ISNULL(BUD, 0) - ISNULL(PRJTODATE, 0) AS BLCOMP
FROM (SELECT ANA_ID, RootIDs, ANA_PARENT_ID, DESCRIPTION,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(b.bamnt) AS BUDGET
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_gldbud b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS BUD,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS FORTHEMONTH
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS FORTHEMONTH,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS YEARTODATE
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS YEARTODATE,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS PRJTODATE
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS PRJTODATE
--(a.ANA_ID IN (0000000012,0000000013,0000000014,0000000015))) AS PRJTODATE
FROM VW_FN_ANASTUDYSETUP
WHERE (LEVEL_ID = '3') AND (BA_ID = '5')) Q_Main
|
|
|
|
|
how to define a result for accessing the select statement from the sp;
|
|
|
|
|
jagadeeshkumar2106 wrote: how to define a result for accessing the select statement from the sp;
What do you mean 
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Are you talking about storing the records to a dataset?
|
|
|
|
|
Hi,
I am using two temp tables of same name e.g. #temp in two diffrent sored procedures of diffrent name e.g sp1 and sp2, in SQL server 2005. I am just not sure whether there will any problem when users will be accessing the two stored procedures simultanously.
Please help.
Kalam.
|
|
|
|
|
y u can simultaneously bt not concurrently........
manoj Kumar Jha
"Learn to smile at every situation. See it as an opportunity to prove your strength and ability."
|
|
|
|
|
Temp tables only have scope within a particular session. If a stored procedure is invoked by 2 different processes, each process will have its own temp tables.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
You can use as many temp table you want.But after doing transaction you have to delete those temp table.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Hello,
I have created database with name say XYZ in Microsoft SQL Server. My client uses same database, to avoid development phase reflection on database, i created exact replica of database and did the required modification. After finalising the changes i want that changes to be reflected in original database(XYZ).
Please tell me how can i replace existing databse with some other databse?
I tried usin script but table data is not getting copied.
I want to know how can i replace the exact copy of one database with another???
Thank you very much.
|
|
|
|
|
Restore the new copy of the database over the existing one.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
Seconded. SQL Compare is very good.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
