|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
Well, thats why i said in my post If you want it in a single string.
and then this will not solve your purpose
|
|
|
|
|
Why do you insist on 4 variables, printing Patras solution will achieve the same output
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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);
|
|
|
|
|
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
|
|
|
|
|
|
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.
|
|
|
|
|
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...
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
Thanks, i understood what you were getting at and arrived at my solution - following post.
|
|
|
|
|
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);
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Thanks man. Thats exactly what i was looking for.
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
