Database

Re: Multiple INSERTs in a Stored Procedure

17-Nov-04 6:58

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!

Edbert P17-Nov-04 19:31

Edbert P

17-Nov-04 19:31

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.

Re: Multiple INSERTs in a Stored Procedure

Luis Alonso Ramos18-Nov-04 6:59

WHERE X IN QueryY - Parameters

18-Nov-04 6:59

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!

Tristan Rhodes17-Nov-04 3:11

Tristan Rhodes

17-Nov-04 3:11

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 Poke tongue | ;-P

)

Mike Ellison17-Nov-04 6:30

Mike Ellison

17-Nov-04 6:30

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.

Tristan Rhodes17-Nov-04 22:31

Tristan Rhodes

17-Nov-04 22:31

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

Mike Ellison18-Nov-04 8:41

Mike Ellison

18-Nov-04 8:41

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.

cannot execute dts packages with asp.net

Tristan Rhodes18-Nov-04 23:39

Tristan Rhodes

18-Nov-04 23:39

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.

steven_wong16-Nov-04 16:38

steven_wong

16-Nov-04 16:38

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.

minhpc_bk16-Nov-04 18:56

16-Nov-04 18:56

Have a look at these links that might help you figure out your problem.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q252987&[^]
http://support.microsoft.com/kb/240221/EN-US/[^]

steven_wong17-Nov-04 22:16

steven_wong

17-Nov-04 22:16

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 Confused | :confused:

transformation task.i don't know why.

minhpc_bk18-Nov-04 22:54

18-Nov-04 22:54

There are a number of reasons that can cause the transformation failed. There are anyway some considerations that you might check:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_usage_3ayb.asp[^]

I happen to know an article on CP about transforming data from Sql to Foxpro that you can also read:
http://www.thecodeproject.com/database/dts.asp[^]

There are also some useful links that you can check out, hopefully that may give you some hints:
http://www.mvps.org/faq/#SQLServer[^]

steven_wong20-Nov-04 16:27

steven_wong

20-Nov-04 16:27

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]

minhpc_bk21-Nov-04 23:16

21-Nov-04 23:16

Hi Steven,
You can read a number of great articles written by Marcin Policht
http://www.databasejournal.com/article.php/1503191[^], and of course don't miss MSDN, CP.

minhpc_bk25-Nov-04 13:44

SQLDataReader for image type

25-Nov-04 13:44

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.

CRecordset Edit

Shotgun16-Nov-04 14:45

Shotgun

16-Nov-04 14:45

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

mysorian16-Nov-04 12:09

mysorian

16-Nov-04 12:09

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.

Luis Alonso Ramos21-Nov-04 15:25

21-Nov-04 15:25

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!

mysorian21-Nov-04 18:18

mysorian

21-Nov-04 18:18

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 Unsure | :~

Luis Alonso Ramos21-Nov-04 18:44

21-Nov-04 18:44

In your code, you're setting the response type to "image/bmp" and that's why if you output text into the response, you can't see it.

When the response type is "image/bmp", the browser treats everything it gets as a single BMP file. If you send some text (or more images) they're treated as part of the first BMP file. Because of the format of BMP files, you can add data at the end of the file and the image won't be corrupted. So, no matter what you output to the browser, you only see the first image you send.

If you want to see multiple files, you would have to build on the fly an HTML file full of IMG tags.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

mysorian22-Nov-04 0:33

mysorian

22-Nov-04 0:33

Thanks. I did try that, may be something else was not correct. Back to the board I think. Eek! | :eek:

mysorian

Luis Alonso Ramos22-Nov-04 5:42