Database

Re: Access Form - datasheet view, which OnEvent

22-Jul-10 10:51

Thanks for the advice.

I added a text control, separate from the record "set" to which the rest of the form is related. I just shoved the other controls to the right so that I can squeeze my text control to look like it occupies it's own column.

My idea was to have my new text control filled with the date, for that group of records with the same date. Only showing that date once.

I have my own function that identifies the date for each record and could do something like MyTextCtrl.value = strDateLabel (when I figure out how to react to the right Event.)

Thanks again, i will give your ideas a try.

JJM

David Mujica23-Jul-10 4:29

David Mujica

23-Jul-10 4:29

Ah ha. I think I see what you are doing.

How about this ...

When you are building your dataset with a query, add a column where you can calculate a value you want displayed.

For example:

SELECT Payroll.WKNum, IIf([wkNum] Mod 2=0,"Even","Odd") AS OddEven
FROM Payroll;

This query displays the Payroll Week Number and a Text value of "Even or Odd"

This would free the DataSheet View from having to do any logic processing on the dataset, it would just display it.

If you need more complex logic, maybe you could extend this by manipulate a temporary table, then display that contents.

Give it a shot.
Cool | :cool:

T-SQL Address line Packing

Pascal Ganaye20-Jul-10 1:24

Pascal Ganaye

20-Jul-10 1:24

I am trying to pack four address lines, to leave no blank between the lines and no null values.

The code below is so horrid, it could qualify quite well to the coding horror forum.
This is also a question so I post it here.
The question is :
- How would you write this more elegantly ?
It would be fun to compare various other ways, I will perhaps post some

DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)

SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'


SET @line1 = RTRIM(LTRIM(COALESCE(@line1,'')))
SET @line2 = RTRIM(LTRIM(COALESCE(@line2,'')))
SET @line3 = RTRIM(LTRIM(COALESCE(@line3,'')))
SET @line4 = RTRIM(LTRIM(COALESCE(@line4,'')))

DECLARE @cpt INTEGER

WHILE @line1 = '' AND @cpt<3
BEGIN
	SET @line1 = @line2
	SET @line2 = @line3
	SET @line3 = @line4
	SET @line4 = ''
	SET @cpt = @cpt + 1
END

SET @cpt = 0
WHILE @line2 = '' AND @cpt<2
BEGIN
	SET @line2 = @line3
	SET @line3 = @line4
	SET @line4 = ''
	SET @cpt = @cpt + 1
END

IF @line3 = '' 
BEGIN
	SET @line3 = @line4
	SET @line4 = ''
END

PRINT 'Line 1:' + @line1
PRINT 'Line 2:' + @line2
PRINT 'Line 3:' + @line3
PRINT 'Line 4:' + @line4

Goutam Patra20-Jul-10 3:16

20-Jul-10 3:16

If you want to just make a single string then there is a solution

DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)
DECLARE @line5 AS VARCHAR(2000)

SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'



SET @line5 = (SELECT RTRIM(LTRIM(COALESCE(@line1,''))) +
       CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line2,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line2,''))) +
       CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line3,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line3,''))) +
       CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line4,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line4,''))))



PRINT @line5

Pascal Ganaye20-Jul-10 3:28

Pascal Ganaye

20-Jul-10 3:28

Then you would need

SET @line1 = SUBSTRING(line5, [something here], [something here])
SET @line2 = SUBSTRING(line5, [something here], [something here])
SET @line3 = SUBSTRING(line5, [something here], [something here])
SET @line4 = SUBSTRING(line5, [something here], [something here])

Wouldn't you?

Goutam Patra20-Jul-10 3:31

20-Jul-10 3:31

Well, thats why i said in my post If you want it in a single string.
and then this will not solve your purpose

Mycroft Holmes20-Jul-10 12:16

Mycroft Holmes

20-Jul-10 12:16

Why do you insist on 4 variables, printing Patras solution will achieve the same output

Never underestimate the power of human stupidity
RAH

How to retrieve binary file from Sql Server database to hard disk using SQL?

Luc Pattyn20-Jul-10 12:43

Luc Pattyn

20-Jul-10 12:43

a bubble-sort approach could solve that; in pseudo-code:

do {
    bool more=false;
    if (line1==empty && line2!=empty) {line1=line2; line2=empty; more=true;}
    if (line2==empty && line3!=empty) {line2=line3; line3=empty; more=true;}
    if (line3==empty && line4!=empty) {line3=line4; line4=empty; more=true;}
while(more);

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Ahmad Safwat18-Jul-10 5:29

Ahmad Safwat

18-Jul-10 5:29

Hi,
I'm Using:
SELECT * FROM OPENROWSET(BULK 'G:\1.jpg', SINGLE_BLOB)

to save binary files from hard disk to sql server database

Now I need an SQL code to retrieve binary file from Sql Server database to hard disk

Re: How to retrieve binary file from Sql Server database to hard disk using SQL?

Ahmad Safwat19-Jul-10 1:25

Ahmad Safwat

19-Jul-10 1:25

heeeeeeeeey

Re: How to retrieve binary file from Sql Server database to hard disk using SQL?

PIEBALDconsult19-Jul-10 3:59

PIEBALDconsult

19-Jul-10 3:59

I just SELECT it and use a FileStream to write it. Here's an example:

byte[] content = (byte[]) ds [ 1 ].Rows [ i ]  [ "FileContent" ] ;

fs.Write ( content , 0 , content.Length ) ; 

fs.Close() ;

ds is a DataSet, and fs is the FileStream.

Re: How to retrieve binary file from Sql Server database to hard disk using SQL?

Luc Pattyn19-Jul-10 4:40

Luc Pattyn

19-Jul-10 4:40

that is not really streaming anything, so I'd recommend a simple File.WriteAllBytes().

BTW: I'm a bit puzzled by the ds[1] part...

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: How to retrieve binary file from Sql Server database to hard disk using SQL?

PIEBALDconsult19-Jul-10 4:58

PIEBALDconsult

19-Jul-10 4:58

Luc Pattyn wrote:
File.WriteAllBytes().

In the case I quoted, I had to check whether or not a file by the chosen name existed first. There's no point creating the file if it already exists.
So I use a FileInfo to check Exists and simply use its Open method to get a FileStream.

Luc Pattyn wrote:
the ds[1] part

refers to the oneth DataTable in the DataSet -- the set of files I want to create.

Re: How to retrieve binary file from Sql Server database to hard disk using SQL?

Luc Pattyn19-Jul-10 5:19

Luc Pattyn

19-Jul-10 5:19

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Create a virtual table for form RecordSource

john john mackey16-Jul-10 7:09

16-Jul-10 7:09

I am using a SQL statement to fill the contents of a subform's RecordSource:

Me.sub_NestedSchedule.Form.RecordSource = strMySqlStatement

How can I create a "virtual table" that has the empty string for all 6 of its fields and force that into my subform's RecordSource?

I want to initialize the subform during OnLoad and have the empty string.

Thanks,
JJM

Mycroft Holmes16-Jul-10 12:07

Mycroft Holmes

16-Jul-10 12:07

Not sure how you are using the sql string but if you query the table with a nonsense where clause you will get back and empty data table.

Select * from MyTable where IDField = 0

Select * from MyTable where 1=1

These will return an empty datatable

Never underestimate the power of human stupidity
RAH

Goutam Patra16-Jul-10 19:57

16-Jul-10 19:57

Select * from MyTable where 1=1

I think the previous SQL statement will return all rows in table

I think this is what you want to say

Select * from MyTable where 1=2

In addition to those solutions we can use

Select top 0 * from MyTable

john john mackey19-Jul-10 9:47

19-Jul-10 9:47

YES! That's the ticket...

To initialize my RecordSource with an empty table that has the EmptyString instead of "#Name?" appearing, here is what I did.

I used a table that had a text data field (Label) and has as default value the empty string. I know that this table (tbl_MyHandyTable) will always be present. So I did this:

strSQL_subform = "SELECT [Lable] AS field1, [Label] AS field2, "
strSQL_subform = strSQL_subform + "[Label] AS field-N, "
...
strSQL_subform = strSQL_subform + "FROM [tbl_MyHandyTable] "
strSQL_subform = strSQL_subform + "WHERE 1=2;"

'initialize the record source with the empty table of EmptyStrings
Me.RecordSource = strSQL_subform

Thanks all!

john john mackey19-Jul-10 9:48

19-Jul-10 9:48

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

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

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);

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