function returning CRecordset - Database Discussion Boards

Creating a FireBird Database Programmatically

goatstudio6-May-05 21:13

6-May-05 21:13

Dear all,

I am using FireBird .Net Adapter and able to create FB database using below code (C#):

Hashtable parameters = new Hashtable();
parameters.Add("User", UserName);
parameters.Add("Password", Password);
parameters.Add("Database", @FilePath);
parameters.Add("ServerType", 1);

FbConnection.CreateDatabase(parameters);

After creating the database, I am able to open it using FBConnection for the first time. But after that, whenever I open the database, I am getting this error:

internal gds software consistency check (Internal error code (165))

Any idea? Thanks. Smile | :)

regards,
vic

How to convert to Float

Yulianto.6-May-05 16:29

Yulianto.

6-May-05 16:29

select (1/2)

How to get the result 0.5? Thanks

<italic>Work hard, Work effectively.

Re: How to convert to Float

Colin Angus Mackay6-May-05 22:23

Colin Angus Mackay

6-May-05 22:23

You need to cast one of the values to a float, then the whole calculation will be a float. For example:

SELECT (cast(1 as float)/2)

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: How to convert to Float

Yulianto.8-May-05 16:23

Yulianto.

8-May-05 16:23

Thank you Colin. One more thing, how to cast float with scale of two (two digits to the right of the decimal point).

<italic>Work hard, Work effectively.

longblob column in MySQL database table

kd83416-May-05 10:49

kd8341

6-May-05 10:49

I am using DataSet to insert a data row which contains a 6MByte binary file in "Data" column.

According to the MySQL manual for 4.0, I define the "Data" column as "longblob", which suppose to store up to 4GByte file. But it just doesn't work.

With the exact same code, if my data is less than 100KByte, it works fine.

Can anybody tell if I have to config anywhere else to make this work?

From MySQL 4.0, the maximum allowed length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory.

I just wonder what "client/server protocol" refers to? How can I figure out the maximum packet size?

mysql output parameters

jetset326-May-05 8:30

jetset32

6-May-05 8:30

Hi,

Mikey just sorted out my first problem, the next one is this.

In my stored procedure in sqlserver i used OUTPUT Parameters, im not sure i can do this in MYSQL as im not using stored procedure;

this is my code;

' the connection string
myconnection = New MySqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

' the query
strSQL = "SELECT ?SubDesc=SubDesc, ?SubName=SubCatName FROM SubCategory WHERE SubcatID = ?SubID;"
mycommand = New MySqlCommand(strSQL, myconnection)

' Input Parameter
Dim parameterSubID As MySqlParameter = New MySqlParameter("?SubID", SubCatID)
mycommand.Parameters.Add(parameterSubID)

' Output Parameter
Dim parameterSubCatDesc As MySqlParameter = New MySqlParameter("?SubDesc", MySqlDbType.String, 500)
parameterSubCatDesc.Direction = ParameterDirection.Output
mycommand.Parameters.Add(parameterSubCatDesc)

Dim parameterSubName As MySqlParameter = New MySqlParameter("?SubName", MySqlDbType.String, 50)
parameterSubName.Direction = ParameterDirection.Output
mycommand.Parameters.Add(parameterSubName)

' execute read
myconnection.Open()
mycommand.ExecuteNonQuery()
myconnection.Close()

Label1.Text = CStr(parameterSubCatDesc.Value)
Label2.Text = CStr(parameterSubName.Value)

I dont know if anyone can help me on this? Mike what about this one!

Cheers

Jetset

Re: mysql output parameters

jetset326-May-05 8:31

jetset32

6-May-05 8:31

i forgot to say the problem,

the code runs but nothing is returned in the labels

function returning CRecordset

scoroop6-May-05 8:02

scoroop

6-May-05 8:02

Is there any way to create function that can return a recordset? i tried to do it but...

i forgot... the other thing is how to "catch" returned recordset? does CRecordset have any copy constructor or sth? OMG | :OMG:

SQL Reporting

vkallavi6-May-05 7:47

vkallavi

6-May-05 7:47

Can somebody tell me how to get this done on SQL reporting.

I need to print the column headings vertically like this H
E
A
D
I
N
G

instead of this - HEADING

thanx

Stored Procedure headache

Vodstok6-May-05 3:14

Vodstok

6-May-05 3:14

I wrote a stored procedure to do a simple update to a table in one of my dbs:

ALTER PROCEDURE usp_cmrapprupd<br />
(@id nvarchar(50),@name int,@appr nvarchar(1))<br />
AS<br />
UPDATE approvers SET approver_identifier = @name, approved_by_approver = @appr<br />
<br />
WHERE identifier = @id

now, with the exception of @id being an nvarchar, this stored procedure is identicle to another one for another table. the only difference between the two tables is that one uses integers as the id, and the other uses nvarchars.

the update on the int table works just dandy, but the one listed about keeps giving me the following error:
Violation of PRIMARY KEY constraint 'PK_approvers'. Cannot insert duplicate key in object 'approvers'.

What is giving me a headache is how am I violating primary key constraints if i am doing an update rather than insert?

Update:
OKay, I discovered a consistancy. The table uses both identifier and approver_identifier as primary keys. If i try to update something with a single instance of identifier, it works (example, identifier 1-1 has 3, 1-1 with approver_identifier of 1, 1-1 with approver_identifier of 2, etc, i get the error. however, if i have an identifier of 4-1 and only an approver_identifier of 1, the update works)

Any ideas?
______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

Re: Stored Procedure headache

Vodstok6-May-05 3:32

Vodstok

6-May-05 3:32

Okie dokie... Iguess this works the same as venting to a stranger.. Putting things out for others to see makes you look it over in a new light. I figured it out:

ALTER PROCEDURE usp_cmrapprupd<br />
(@id nvarchar(50),@name int,@appr nvarchar(1))<br />
AS<br />
UPDATE approvers SET approved_by_approver = @appr<br />
<br />
WHERE identifier = @id AND approver_identifier = @name

Apparently, the proc didnt know which field to update (which makes sense when you dont specify both identifiers....)

This procedure works like a charm. Thanks for letting me complain Smile | :)

______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

mysql adding parameters in asp.net vb

jetset326-May-05 3:07

jetset32

6-May-05 3:07

hi all,

I wonder if anyone can help with this one, alls im trying to do is add a parameter to a query, i have tried for days but cannot get this to work!!

heres my code;

myConnection = New MySqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

strSQL = "SELECT SubCatID,CategoryID,SubCatName,Multibuy,SubCatPic FROM SubCategory WHERE CategoryID = ?CategoryID AND Multibuy='Single' ORDER BY SubCatName, SubCatID;"
Command = New MySqlCommand(strSQL, myConnection)
myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)

Command.Parameters.Add("?CategoryID", 1)
Command.Parameters("?CategoryID").Direction = ParameterDirection.Input

I get the error ;
Parameter '?CategoryID' must be defined

i have tried everyway i think possible, PLEASE i hope someone can help or point me in the right direction!

Thanks.
Jetset

Re: mysql adding parameters in asp.net vb

Mike Ellison6-May-05 5:10

Mike Ellison

6-May-05 5:10

Hi there. Do you need to issue the .Prepare() method after setting up your Command?

Re: mysql adding parameters in asp.net vb

jetset326-May-05 5:19

jetset32

6-May-05 5:19

.Prepare method?

Out of all the searches Ive done on google ive not seen this mentioned before?

could you expand on this?

Thanks.

Re: mysql adding parameters in asp.net vb

Mike Ellison6-May-05 5:37

Mike Ellison

6-May-05 5:37

Hi there. I may have offered that too soon - I saw it used in some MySql .NET Connector examples and thought it may be something to look at. As I look at the .chm documentation, it indicates that the method is not yet supported (?) But then there is a different section that describes the following:

4.3.2. Preparing Statements in MySQL Connector/NET
To prepare a statement, create a command object and set the .CommandText property to your query.

After entering your statement, call the .Prepare method of the MySqlCommand object. After the statement is prepared, add parameters for each of the dynamic elements in the query.

After you enter your query and enter parameters, execute the statement using the .ExecuteNonQuery(), .ExecuteScalar(), or .ExecuteReader methods.

For subsequent executions, you need only modify the values of the parameters and call the execute method again, there is no need to set the .CommandText property or redefine the parameters.

So now I'm not sure which is right.

Re: mysql adding parameters in asp.net vb

jetset326-May-05 5:46

jetset32

6-May-05 5:46

Mike,

Thanks alot, you have helped me out loads with that, my parameter code was correct but becuase I wasnt using the execute read method it wasnt working!

It is now!

Thanks alot

Jet set Laugh | :laugh:

Data export to excel\csv file

mansir 1235-May-05 22:26

mansir 123

5-May-05 22:26

Hi all,

i want to export the result of a query to an excel/csv file.if nyone has an idea about it, please let me know. need it badly..

thanks & regards,
manasi

Re: Data export to excel\csv file

scoroop6-May-05 8:10

scoroop

6-May-05 8:10

if csv is ok for you just create a normal txt file with values seperated by comas and change file extension to csv.

Re: Data export to excel\csv file

S Douglas12-May-05 0:50

S Douglas

12-May-05 0:50

This works with ADO,

CString sql = _T("SELECT * INTO [Excel 8.0;DATABASE=" + strExcelFileLoc + <br />
   "].[" + strWorksheetName + "] FROM [" + strTable + "]");<br />
<br />
DB->Execute(sql.AllocSysString());

The results are outputted strait to the excel file specified by strExcelFileLoc.

Hope this helps....

-------------------------------

Two counts of rows with different criteria

Luis Alonso Ramos5-May-05 19:34

Luis Alonso Ramos

5-May-05 19:34

Hello,

I have a child table that contains rows related to a parent table. I would like, in a single SELECT query for the parent table, to have two counts, one for rows in the child table that match a certain criteria, and another that match some other criteria.

A simple example, a table of Classrooms, with a child table Students, where each student is either male of female; and the query should return ClassroomID, NumMales, and NumFemales.

I can easily get only one count (males for example) using GROUP BY with something like:

SELECT COUNT(StudentID) AS NumMales FROM Students
    GROUP BY Sex WHERE Sex = 0

but I can't find a way to get two different aggregate functions to work on the same table.

In my case it's actually a bit more complicated, because I have three tables related. But a little pointer on how to do it with two tables should help me get started.

Any ideas? Thanks!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: Two counts of rows with different criteria

Dan_P5-May-05 19:48

Dan_P

5-May-05 19:48

Like this:

SELECT     COUNT(StudentID) AS Females,<br />
                          (SELECT     COUNT(StudentID) AS Males<br />
                            FROM          dbo.Students<br />
                            WHERE      (Sex = 'M')) AS Males<br />
FROM         dbo.Students AS Students_1<br />
WHERE     (Sex = 'F')