|
After the ExecuteReader() do a rdr.Read().. it will fix your problem. First record does not get read automatically.
|
|
|
|
|
Hi, I have a Decimal(9,2) type, and I want to evaluate the fractional component of this. What I actually want to know is if there is a sql function to perform this. If I had 13.45, is there a function to return .45?
|
|
|
|
|
The following should get you what you want:
SELECT (MyDecimalColumn - CAST(MyDecimalColumn AS INT)) AS MyFractionalComponent
FROM ....
Do you want to know more?
|
|
|
|
|
I know that ADO doesnt like binary blobs of paradox tables.
i wonder if anyone knows a way to work around this
i was thinking about using Borland C++ to build a library (dll ) and then use it in C#.. but it is a lito more than i can handle rightnow..
any suggestion would be great..thank you
Elapid For The Win
|
|
|
|
|
How can i update my DataSourc(SQL SERVER) from an DataSet
Thank you
|
|
|
|
|
|
Hello,
I have three tables: Distributors, Business, Users. The Distributors table only has three fields: ID_distributor, an ID_Business, and an ID_repr (like the boss of that distributor). The Business table has information about the business (Name, Address and so on) and the User table about people accesing the web site (login name, password, and the ID_business they belong to.)
So, I want to insert a new Distributor from a web page. I need to first add a new Business record, and then (in any order) the Distributor record and the User record. (EDIT: Obviously, I want to store the first record's ID in the other two tables, to keep the relation. @@IDENTITY would help here, wouldn't it?))
And finally, I need it to return all the three IDs (distributor, business and user).
I've never written a stored procedure by hand (only designed them with Access) so I have no idea of the syntax. There are many more fields, but I'd like just to see the general structure of that stored procedure and how to use output parameters and so on.
Thanks in advance,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Believe it or not, it is basically similar to writing a VB function with some VB and SQL code inside.
You declare the procedure name and the parameters to be passed (or returned as output), and write your code inside. Below is the basic structure of it:
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default] [OUTPUT]
]
[,...n]
AS
(code starts here)
Here is a link that has some good examples of stored procedure. I believe you can start writing your own after reading it.
Creating Stored Procedure[^]
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thanks! I'll take a look at it! With a few examples, what I want to do may not be that hard.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I have the following section of code
str_SQL = "(SELECT Description.DescKey " _
& "FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE (Category.CategoryDescription)='" & GetCurrCategory & "')"
'TO REMOVE
CurrentDb.QueryDefs.Delete "qdf_q"
'Create sub query
Dim qdf_Query As QueryDef
Set qdf_Query = CurrentDb.CreateQueryDef("qdf_q", str_SQL)
DoCmd.OpenQuery ("qdf_q")
'Build base action
str_SQL = "DELETE FROM Description " _
& "WHERE DescKey " _
& "IN (qdf_q)"
'Execute Query
CurrentDb.Execute (str_SQL)
The queries with code format removed are:
SELECT Description.DescKey
FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
WHERE (Category.CategoryDescription = GetCurrCategory())
DELETE FROM Description
WHERE DescKey
IN (qdf_q)
Now, the problem I have is, the first query generates the correct table, which contains all the items I want to remove. But when I try and run the second query, It displays an error saying "Too few parameters, expected 1"
I have all the single parameter covered already, so why is it doing this?
Cheers
Cata
(Sorry, SQL n00b  )
|
|
|
|
|
Hi Cata. You're creating the select query as a temporary QueryDef object and referencing its name in your delete statement. When executing the delete statement with CurrentDb.Execute(str_SQL), the name [qdf_q] is not recognized (its scope is as a variable in your procedure); thus, Access wants to treat it as a parameterized name (in the scope of the SQL), and without explicitly assigning a parameter object with that name to the QueryDef, you're getting the "Too few parameters" error.
I would think a way to avoid this is to build in the Select statement as part of the str_SQL string (not a QueryDef) then execute str_SQL with the subquery. The resulting SQL would look something like this:
DELETE FROM Description
WHERE DescKey
IN
(
SELECT Description.DescKey
FROM (Category INNER JOIN SubCategory
ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description
ON SubCategory.SubCategoryKey = Description.SubCategoryKey
WHERE (Category.CategoryDescription = GetCurrCategory())
)Does that make sense? The variable str_SQL would hold the complete text as above, then would be executed without creating a QueryDef object.
|
|
|
|
|
The qdf_Query is the procedural object, qdf_q is an actual database query that exists within the access DB. I can see it in the query viewer.
The qdf_q query is fully populated as well, so there is no reason why it should not work.
When I tried to integrate the sub query by using SQL, it threw and INNER JOIN Syntax error. The current solution displays "Too Few Parameters, Expected 1". But when i experimented with using a table, it worked fine.
This is confusing me. A table works, but a query that produces a far simpler table, requires additional input? Why is this?
Cheers
Cata
|
|
|
|
|
Well, the only other thing I can think of is that you appear to be using a custom function in your select statement - GetCurrCategory. If this is evaluating to a name that is not recognized in your where clause, you could get the parameter error you mention.
|
|
|
|
|
But the select statement works perfectly. There is a populated query table.
What's more infuriating is that, it works with a standard table.
I know queries require parameteres, but the ones in the sub query are already filled in.
|
|
|
|
|
I've got a problem executing DTS package from ASP.Net.
I use this code:
Dim oPkg As DTS.Package
oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer("WIN2000", "UID", "password",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , ,
"Import_Sales_History")
oPkg.Execute()
oPkg.UnInitialize()
oPkg = Nothing
I used this code before, and it always worked. Now it doesn't. The problem
is that I'm not receiving any error messages, so I cannot find out what's
wrong. When I step through the code I don't see anything unusual. But in
fact it doesn't do anything, no data has been imported. When I execute the
package manually - everything is OK.
|
|
|
|
|
|
Thank you for your reply,minhpc_bk.
i have tried your suggestion ,and now i can execute the pkg quite well. while transforming data from sql server to sql server,however,when i try to execute the transformation between sql server and foxpro ,it always fails.The error handle shows it can successfully execute both ActiveX Task and Dynamic Task, but fail with transformation task.i don't know why.
|
|
|
|
|
|
Thank you so much for your help, minhpc_bk.
it is the permission problem.i now can execute my pkg with sql authentication,however,it is said that with sql authentication is not recommended in most of articles.can you recommend me some information about the permission of sql and files? any will be appriciated! thank you!![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
|
Hi Steven,
That's my pleasure. Over the past one year and half I have learnt a lot from CP, and now it's time to return something to CP. Trying to answer questions in the CP forums, in my opinion, is a good way to do that.
Very nice to know you, Steven.
|
|
|
|
|
I am trying to add and edit records in a CDatabase, CRecordset enviro,
if i comment out the update error everything goes through without a hitch but there is no data updated, with it, it shows no update,
Someone please help, I've been banging my head on the wall with this.
CSetProps is derived from CRecordset
property of int m_textcolor, m_listcolor
datab.Open( NULL,FALSE,FALSE,sDsn,TRUE);
// Allocate the recordset
CSetProps recset( &datab );
// Build the SQL statement
SqlString = "SELECT * FROM Props";
recset.Open(CRecordset::forwardOnly,SqlString, CRecordset::none);
char buff[10];
CDBVariant tvar;
tvar.m_dwType=VT_I2;
recset.GetFieldValue("textcolor",tvar);
//convert the integer for messageing
itoa(tvar.m_iVal, buff, 10);
//This messages current value so we know we are on a record
AfxMessageBox(buff);
//prepare for record to be eddited
recset.Edit();
// msdn says to set the filds null
recset.SetFieldNull(&(recset.m_textcolor), FALSE);
// set new value
recset.m_textcolor = 8;
//tried with and without
recset.SetFieldDirty(NULL);
// always errors
if (!recset.Update())
AfxMessageBox("Error updating database");
recset.Close();
datab.Close();
shotgun
|
|
|
|
|
SQLDataReader should read from the stream. When the incoming stream has image type info [for example, images from a Sql server], the reader can only read a single image. I wonder what is happening.
Apparently, a mixed stream is hard to read:
--------------------------------
while drPic.read
Response.ContentType = "image/bmp"
Response.BinaryWrite(drPic.Item("pic"))
end while
--------------------------------
It has 20 pictures, but I get only one. If I add any other fields, they get skipped.
|
|
|
|
|
I've never used image types in SQL Server, but, are all the images in the same field? Or do you get all the images, each from one field, and try to output all of them with Reponse.BinaryWrite?
If you are trying to output all of them to the browser, then probably you should generate an HTML file that contains all the images. I don't the browser supports displaying multiple images by themselves at once (ie. several URLs for JPG files, one after another)
I've never worked with it, but just an idea.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis:
Let's say you are writing out the column that contains 8 rows of, say text (string type), the Execute Reader will get you 8 rows of data for that column assuming you arranged it that way. On the other hand if the 8 rows had data of type image, the Response.Binary write seems to write just the first row and nothing else. I am a bit surprised, but trying to find why this happens?. Also if you wanted both text and image to be read out, it looks like there is something that prevents writing text and image, you get image only. I have been trying several tricks, but so far not much of a success. If I succeed I will let you know.
mysorian
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
