Database

Re: Create a virtual table for form RecordSource

19-Jul-10 9:48

Thanks, i understood what you were getting at and arrived at my solution - following post.

Bernhard Hiller22-Jul-10 20:16

Bernhard Hiller

22-Jul-10 20:16

The ExecuteReader() function of a command can receive a CommandBehavior parameter. Why not use CommandBehavior.SchemaOnly or CommandBehavior.KeyInfo? E.g.

SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable");
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);

Call Sp inside a SP and count the number of rows returned by the SP

Kit Fisto15-Jul-10 23:28

Kit Fisto

15-Jul-10 23:28

Hi guys.

I have a problem. I need to call a Stored Procedure inside a Stored Procedure and count the number of rows returned by the second SP. Something like this:

COUNT(EXEC My_SP 1,1,1,1).

That second SP returns a query with a x number or rows.

Thanks in advance guys.

Re: Call Sp inside a SP and count the number of rows returned by the SP

Goutam Patra16-Jul-10 3:15

Goutam Patra

16-Jul-10 3:15

Declare your second SP like

CREATE PROCEDURE My_SP
 @Para1 AS INTEGER,
 @Para2 AS INTEGER,
 @Para3 AS INTEGER,
 @RecordCount AS INTEGER OUTPUT
AS
 SET @RecordCount = (SELECT COUNT(*) FROM MYTABLE)
GO

In your first SP
Call this like

DECLARE @RecordCount AS INTEGER
EXEC My_SP 1, 1, 1, RecordCount OUTPUT

Thats it

Re: Call Sp inside a SP and count the number of rows returned by the SP

Kit Fisto16-Jul-10 4:45

Kit Fisto

16-Jul-10 4:45

Thanks man. Thats exactly what i was looking for.

vulnerability

sowjanya315-Jul-10 22:07

sowjanya3

15-Jul-10 22:07

how is vulnerability explained in database? Am completely blank about vulnerability. I want to no what does vulnerability mean how and where do we use.I want to no the meaning in terms of database or any other concepts.

Re: vulnerability

Scubapro16-Jul-10 0:40

Scubapro

16-Jul-10 0:40

You mean like this?

Re: vulnerability

Mycroft Holmes16-Jul-10 12:02

Mycroft Holmes

16-Jul-10 12:02

So buy a book on database security, check out some of the database centric sites (SQLServerCentral.com), do some reseach.

You have asked this question in a forum where we help developers not teach wannabes

Never underestimate the power of human stupidity
RAH

Re: vulnerability

sowjanya320-Jul-10 18:44

sowjanya3

20-Jul-10 18:44

Actually i was not asking you people to teach me. i searched in net couldnt get any thing so thought u people may no any ref so that i learn.

Any way thank you for the information.

Very Tough CSV Data Import to SQL 2005 DB Table Challenge

dsabjp14-Jul-10 16:54

dsabjp

14-Jul-10 16:54

I have a comma delimited text file with array type columns (number of columns may vary) to import into SQL 2005 DB Table. Need help please!

I was hoping to attach a safe (no vb or macro) Excel workbook that does a good displaying background data (picture), CSV file format and final SQL 2005 DB Table format as well. I can e-mail if someone has the time to review and provide some useful advice on how to handle my "array" type problem.

Thanks...

Dean Pugh

Re: Very Tough CSV Data Import to SQL 2005 DB Table Challenge

Mycroft Holmes14-Jul-10 19:33

Mycroft Holmes

14-Jul-10 19:33

Does the column count vary per CSV file or are the number of columns different per row.

1
Read the title row, create a table based on the titles

2
Read the entire file create a table with the max columns

This article [^]may help with the techniques

Never underestimate the power of human stupidity
RAH

Re: Very Tough CSV Data Import to SQL 2005 DB Table Challenge

dsabjp17-Jul-10 16:37

dsabjp

17-Jul-10 16:37

Good ideas. I seem to have found an option that works pretty good. Using OPENROWSET my import works okay as long as the table has equal to or more columns than the csv file. In answer to your question, it is possible that different rows have different number of fields. So far in the examples I've played with Openrowset allows records to be added and any missing columns are taken off from the last columns. I can live with that since I can delete the rows later if needed. Now for the few records that were possibly causing the whole import to crash, I now get the whole import and can do a search and delete rows afterwards. Sorry for the delayed response. I've just got back to my computer. Many thanks for your ideas Mycroft, I would try them next.

Smile | :)

Dean

IN Clause Best Practices? [solved]

Dan Mos14-Jul-10 6:41

Dan Mos

14-Jul-10 6:41

Hy,

I have a problem in that I don't know what to choose.

We have somewhere DataLayer/BussinessLogic something like:

public class MainClass{
.
.
.
protected ObservableCollection<SecondaryClass> lst;//contected to the main class through IDs in the DB
.
.
.
}

So the question is:

What is the best way to get some rows based on a array/list of IDs from the "main" table:
1) Getting all the MainClass rows/objects than generating a string from code(c#) using StringBuilder with all the IDs separated by commas and run a query/storedproc to get all objects from the realted
table(s) with a string/varchar paramter for the IN clause and fill the list of SecondaryClass objects from the MainClass or
2) Generate a temporary table with the IDs and use that in another
query/proc as the IN or EXISTS filter?

I've run some tests and for some reason or another from the speed point of view the first one is
the winner. But both are pretty fast. So speed alone is not that big of an issue.

Because of the Object Model we can not use a join. I mean we could but makes no sence.

Any suggestions will be apreciated.

Thanks

I bug

modified on Wednesday, July 14, 2010 1:47 PM

Re: IN Clause Best Practices?

Eddy Vluggen14-Jul-10 7:10

Re: IN Clause Best Practices?

14-Jul-10 7:10

Hi,

Have you considered Table Valued Parameters[^]?

I are Troll Suspicious | :suss:

Dan Mos14-Jul-10 7:24

Dan Mos

14-Jul-10 7:24

Nope.

Nice solution.
Thanks

I bug

Re: IN Clause Best Practices?

Eddy Vluggen14-Jul-10 7:35

Re: Accessing mdiParent form menu from mdiChild form via code

14-Jul-10 7:35

You're welcome Smile | :)

Message Removed

14-Jul-10 0:00

AussieLew

14-Jul-10 0:00

Message Removed

Richard MacCutchan14-Jul-10 1:28

Richard MacCutchan

14-Jul-10 1:28

I'm not sure what this has to do with "General Database" however ...

In your hormoneTypeToolStripMenuItem_Click you will need to use a reference to your MainForm, either make it global or ensure this method is part of your MainForm. Also note that your HormoneTypeForm f1 will be destroyed as soon as this method returns; I doubt that is what you want.

It's time for a new signature.

Access 2007 - black hole forms

john john mackey13-Jul-10 7:30

13-Jul-10 7:30

I am noticing some freakiness in MS Access 2007.

I had a DB application with 2 forms I use in a utility capacity (to produce a listing of tables within and properties, the other to produce a listing of objects - form names, macro names, query names, ...)

I decided to copy those forms to another application so that DB application #2 can have the same utility features.

I did a Ctrl-A select all on the controls from Form 1, db App#1 into new form, db App#2. Likewise for the other Form 2.

Now in db App#1, i can't display Form 1 or Form 2. I even go directly to the Navigation pane and select Form 1, choose Open.... NOTHING Confused | :confused:

I don't know what happened with Access 2007?!?!?!

Richard MacCutchan13-Jul-10 22:19

Richard MacCutchan

13-Jul-10 22:19

A stupid question but, did you use cut (ctl-X) instead of copy (ctl-c)?

It's time for a new signature.

john john mackey15-Jul-10 12:05

15-Jul-10 12:05

From DB App #1 (source), I selected all controls (Ctrl-A); copied (Ctrl-C); went into DB App #2, on a blank form; pasted (Ctrl-V).

I had a co-worker look it over and he went into DB App #1, did a change to code (then undid it - basically forced a modification to be recognized), saved DB App #1.

DB App #1 then worked as before!

Craziness of MS Office products Unsure | :~

Thanks for the reply

Eddy Vluggen14-Jul-10 4:31

14-Jul-10 4:31

Might be a missing reference. Are you using any ActiveX-controls or other special controls in the first project?

You can check the current references in the Visual Basic editor, first item in the "Tools" menu.

I are Troll Suspicious | :suss:

john john mackey15-Jul-10 12:10

15-Jul-10 12:10

Both Apps (source copied from, DB App #1 and destination copied to, DB App #2) have the same referencing as they use similar Active X controls and reference similar objects (like Excel, etc...)

It is the source, original DB App #1 that is experiencing the problem - only copied the controls from a form in DB App #1 and pasted them into a blank form in DB App #2 (Ctrl-A, select all controls; Ctrl-C, copy from DB App#1; Ctrl-V, paste into DB App #2).

A co-worker took a look and did this: "faked a change" in DB App #1 VBA code (made change/undid change). Saved the VBA code that supports DB App #1.

Then DB App #1 (the source copied from) worked as before!

Craziness that is MS Office Unsure | :~

Thanks,
John John

Eddy Vluggen15-Jul-10 12:34