How to show the ##th record.

Hi,
When I qury data form a View, the system say that :
The are smallint overload in RECORD=RECORD=4891467
How can I get the 4891467th record?Hi
Smallint will hold values between -32,768 and 32,767. It is not clear where
your conversion to smallint is occurring as you have not posted DDL, but you
could use CAST to force it to be converted to int or bigint.
John
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:e4DoKsfYGHA.4884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> When I qury data form a View, the system say that :
> The are smallint overload in RECORD=RECORD=4891467
> How can I get the 4891467th record?
>

How to show the ##th record.

Hi,
When I qury data form a View, the system say that :
The are smallint overload in RECORD=RECORD=4891467
How can I get the 4891467th record?Hi
Smallint will hold values between -32,768 and 32,767. It is not clear where
your conversion to smallint is occurring as you have not posted DDL, but you
could use CAST to force it to be converted to int or bigint.
John
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:e4DoKsfYGHA.4884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> When I qury data form a View, the system say that :
> The are smallint overload in RECORD=RECORD=4891467
> How can I get the 4891467th record?
>

how to show one record per one page

I am using reporting services to build a report to show customer's balance statements. I need each page only show one customer and related transactions and balance etc.

Now it shows several customers' balance in one page, how can i change it to show only one record per one page, and also one customer per one page when print it out.

how can i achieve that?

cheers

place all your controls in a list control

in the properties for the list control

group by customer

check the "page brake before" option

How to Share Subreport Data with the Main Report

I'm having trouble passing a record from a subreport to the main report is CR X any help would be greatly appreciated.This can be done using shared variables.Same shared variable has to be declared in main as well as sub report and in sub report the variable can be assigned the value to be passed to main report which will be accessible in the variable after the sub report.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the database to always use next increment number?

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

I think its good way if ID is incrementing by this you can come to know which record inserted when and so on ....

A good article on identity

http://msdn2.microsoft.com/en-us/library/ms971502.aspx

|||

it is possible but you have to do it yourself by switching identity insert ON and OFF in your code and taking care about inserted value. It is not recommended way because it slow down insert process and also it can destroy your related record information if you have any, If you would like to have records order information for some purposes you can always do select statement like:

select identity(int,1,1) new_ID, old_id
into #test
from yourtablename
select * from #test
order by new_ID

and next select by new_ID your result.
You can also use ROW_NUMBER:

select ROW_NUMBER() OVER(ORDER BY old_id ASC) [OrderNo] from from yourtablename

|||

Many thanks for all your responses. The reason I was thinking about doing that is because this table is for linking pages. These links will be added and deleted often..and if there is no way to keep the ID in order, I'm afraid the ID may get too big but with less than 40 or 50 actual records in the table.

|||

There is no inbuilt solution for your problem.. you need to use alternative methods..

2 of those methods are discusses here.

http://www.sqlteam.com/item.asp?ItemID=765

|||

Many thanks for the link. It looks like I could use this:

declare @.intCounter int
set @.intCounter = 0
update Yaks
SET @.intCounter = YakSequenceNumber = @.intCounter + 1

The question I have is, do I run the above code everytime when I want to update and insert? Or do I run this code at the begingin where there is no record yet in the table?

|||

I think you need to use this while you are inserting. Is this make sense

|||

Okay, so can you give me a sample code of how I would use this while inserting in C# code behind? I'm still unclear as to how I would use it. By the way, if I'm using this, do I turn of the column Identity?

|||

I will create one example for you can give it to you

|||

This one solution has one big disadvantage that it will slow down your insert so maybe you should run this one time a day(or hour) for your table instead of running it on every insert?

But solution is very nice

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Lenght for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to SET multiple variables from one table record?

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.

Currently I'll do something like this:

DECLARE @.var1 int
SET @.var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @.somekey)
DECLARE @.var2 nvarchar(20)
SET @.var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @.somekey)

Of course, I'd rather just have to query "table1" just once to assign my variables.

What obvious bit of T-SQL am I missing?

Thank you in advance.

Select @.var1 = field1, @.var2 = field2, @.var3 = field3 from table1 where recordid = @.recid

|||

Thank you PDraigh!

I knew it was something obvious - I think I need a holiday

How to set fill factor?

Hellow, everyone"
I have a web online table that is inserted about 1500 record one day. Each night, a DST is running to pull all data to anther database. How to set fill factor on a one column index to get the best performance? Current fill factor is 80%.
Thanks
ZYTYou are having performance issues with just 1500 records per day?|||You are having performance issues with just 1500 records per day?

Yes, about 1500 records everyday. This table has not been maintained about five years. Someone created it that time. I take over and want to imporve performance.

ZYT|||I'm not really into the fill factor thing. In fact I'm still learing my way in SQL Server.

Also, I really don't know if setting a fill factor would benefit your particular case. However, as far as I know, you should be able to determine the fill factor (0-100) of a particular index by using the ALTER INDEX command.

Not really sure if this was the answer you were looking for. There's the chance that I didn't understand your question.

Best regards.|||Well, lets have the whole story then. Post the DDL for the table, along with any indexes on it.
Also, how many records are in it?|||Keep in mind fillfactor only comes into play at the time you create the index, or rebuild the index. It has absolutely nothing to do with how the data is maintained over time.|||Do you defrag/rebuild indexes regularly, let's say based on that volume, every couple of weeks ?|||You are having performance issues with just 1500 records per day?If I may - the nature of the records inserted bares some scrutiny. We had a guy on recently (I forget who) who's records averaged 4KB per record. As such I agree - we need to at least see the DDL to get an idea of what might be inserted.

We could also do with knowing if rows are likely to be updated regularly (and if those updates are likely to dramatically change the size of the rows).

...and finally - we need to know (as MCrowley says) what the reindexing processes are for this table.

@.OP - there is a lot of factors that determine the optimum fillfactor. Please provide the information requested :)

how to set default value for datetime column in sql mobile?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

I can repro the failure you are seeing. On Searching BOL for sql mobile, It says the following about the default definitions for columns.

DEFAULT Definitions

A column can have only one DEFAULT definition. This can contain constant values or constant functions.

You should post this to the SQL Server Everywhere/Mobile/CE Edition Technical Discussion forum.

How to set date as default

Hi, I probably do something stupid, but I cannot figure out how to set the date as deault when

inserting a record in a table. I have created a Default in the database, giving it a name and the Value = GetDate() and bind it to the desired column.

When I insert a record without filling in a value in this datefield, the table shows me "01/01/1900" in stead of Today.

What am I doing wrong ?

Help is appreciated, Ger.

How are you doing the insert?|||

Here is part of the code :

Dim ConnStr As String = "workstation id=ONTWIKKEL;packet size=4096;integrated security=SSPI;data source='ONTWIKKEL\WEBAPPS';persist security info=False;initial catalog=UPOdyssee"
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim mSQL As String
Dim a(99)
a(1) = Request.Form("HtxtVestiging")
a(2) = Request.Form("HtxtInschrijfdatum")
a(3) = Request.Form("HtxtNaamVoorletters")
a(4) = Request.Form("HtxtRoepnaam")
a(5) = Request.Form("HtxtLoginnaam")
a(6) = Request.Form("HtxtPaswoord")
a(7) = Request.Form("HtxtAdres")
a(8) = Request.Form("HtxtPostcode")
a(9) = Request.Form("HtxtWoonplaats")
a(10) = Request.Form("HtxtTelnr")
a(11) = Request.Form("HtxtEmail")
a(12) = Request.Form("HtxtMobiel")
a(13) = Request.Form("HtxtBereikbaarVia")
a(14) = Request.Form("HtxtGeboortedatum")
a(15) = DDL_Geslacht.SelectedValue
a(16) = Request.Form("HtaWatVoorWerk")
a(17) = DDL_VastTijdelijk.SelectedValue
a(18) = Request.Form("HtxtVanafWelkePeriode")
a(19) = Request.Form("HtxtTotWelkePeriode")
a(20) = DDL_Beschikbaarheid.SelectedValue
a(21) = Request.Form("HtxtVanUren")
a(22) = Request.Form("HtxtTotUren")
a(23) = DDL_Vervoer.SelectedValue
a(24) = DDL_Reisbereidheid.SelectedValue
a(25) = Request.Form("HtxtMaxReistijd")
a(26) = Request.Form("HtxtMaxKM")
a(27) = Request.Form("HtxtInkomenBruto")
a(28) = Request.Form("HtxtInkomenNetto")
a(29) = DDL_Uitkering.SelectedValue
a(30) = Request.Form("HtxtUitkering")
a(31) = Request.Form("HtxtFiscaalnr")
a(32) = DDL_Identificatiebewijs.SelectedValue
a(33) = Request.Form("HtxtSoortLegitimatiebewijs")
a(34) = Request.Form("HtxtLegitimatiebewijsnr")
a(35) = Request.Form("HtxtGeldigTot")
a(36) = DDL_Rijbewijs.SelectedValue
a(37) = Request.Form("HtxtSoortRijbewijs")
a(38) = Request.Form("HtxtRijbewijsnr")
a(39) = DDL_MilitaireDienst.SelectedValue
a(40) = Request.Form("HtxtFunctieMD")
a(41) = Request.Form("HtxtJaarMD")
a(42) = Request.Form("HtxtPaspoortnr")
a(43) = Request.Form("HtxtEinddatumPaspoort")
a(44) = Request.Form("HtxtVergunningnr")
a(45) = Request.Form("HtxtEinddatumVergunning")
a(46) = Request.Form("HtxtBankGiroNr")
a(47) = DDL_Ziektekosten.SelectedValue
a(48) = Request.Form("HtxtPolisnr")
a(49) = DDL_HoeAanOnsGekomen.SelectedValue
a(50) = Request.Form("HtxtOpl1")
a(51) = Request.Form("HtxtOpl2")
a(52) = Request.Form("HtxtOpl3")
a(53) = Request.Form("HtxtOpl4")
a(54) = Request.Form("HtxtOpl5")
a(55) = Request.Form("HtxtOpl6")
a(56) = Request.Form("HtxtOpl7")
a(57) = Request.Form("HtxtRichting1")
a(58) = Request.Form("HtxtRichting2")
a(59) = Request.Form("HtxtRichting3")
a(60) = Request.Form("HtxtRichting4")
a(61) = Request.Form("HtxtRichting5")
a(62) = Request.Form("HtxtRichting6")
a(63) = Request.Form("HtxtRichting7")
a(64) = Request.Form("HtxtDipl1")
a(65) = Request.Form("HtxtDipl2")
a(66) = Request.Form("HtxtDipl3")
a(67) = Request.Form("HtxtDipl4")
a(68) = Request.Form("HtxtDipl5")
a(69) = Request.Form("HtxtDipl6")
a(70) = Request.Form("HtxtDipl7")
a(71) = Request.Form("HtxtJaar1")
a(72) = Request.Form("HtxtJaar2")
a(73) = Request.Form("HtxtJaar3")
a(74) = Request.Form("HtxtJaar4")
a(75) = Request.Form("HtxtJaar5")
a(76) = Request.Form("HtxtJaar6")
a(77) = Request.Form("HtxtJaar7")
a(78) = DDL_NogStudie.SelectedValue
a(79) = Request.Form("HtxtWelkeStudie")
a(80) = Request.Form("HtxtWGVanTot1")
a(81) = Request.Form("HtxtWGVanTot2")
a(82) = Request.Form("HtxtWGVanTot3")
a(83) = Request.Form("HtxtWGVanTot4")
a(84) = Request.Form("HtxtWGVanTot5")
a(85) = Request.Form("HtxtWGVanTot6")
a(86) = Request.Form("HtxtFunctie1")
a(87) = Request.Form("HtxtFunctie2")
a(88) = Request.Form("HtxtFunctie3")
a(89) = Request.Form("HtxtFunctie4")
a(90) = Request.Form("HtxtFunctie5")
a(91) = Request.Form("HtxtFunctie6")
a(92) = Request.Form("HtxtVertrek1")
a(93) = Request.Form("HtxtVertrek2")
a(94) = Request.Form("HtxtVertrek3")
a(95) = Request.Form("HtxtVertrek4")
a(96) = Request.Form("HtxtVertrek5")
a(97) = Request.Form("HtxtVertrek6")
a(98) = Request.Form("HtaBijzOpm")
a(99) = DDL_Functie.SelectedIndex + 1 ' i.v.m. de zerobased van de ddl en de 1-based van de tabel
mSQL = "INSERT into Medewerkers (Vestiging,Inschrijfdatum, Naam, Roepnaam, Loginnaam, Paswoord, "
mSQL = mSQL & "Adres, Postcode, Woonplaats, Telefoonnummer, Email, Mobiel, BereikTel, Geboortedatum, Geslacht, WatVoorWerk, VastTijdelijk, VanafPeriode, TotPeriode, Beschikbaarheid, "
mSQL = mSQL & "VanUren, TotUren, Vervoer, Reisbereidheid, MaxReistijd, MaxKM, InkomenBruto, InkomenNetto, Uitkering, SoortUitkering, Fiscaalnr, "
mSQL = mSQL & "Identificatiebewijs, SoortLegibewijs, Legitimatiebewijs, GeldigTot, Rijbewijs, "
mSQL = mSQL & "Soortrijbewijs, Rijbewijsnr, MilitaireDienst, FunctieMD, JaarMD, Paspoortnr, EinddatumPaspoort, Vergunningnr, EinddatumVergunning, BankGironr, "
mSQL = mSQL & "Ziektekosten, Polisnr, HoeAanOnsGekomen, Opl1, Opl2, Opl3, Opl4, Opl5, Opl6, "
mSQL = mSQL & "Opl7, Richting1, Richting2, Richting3, Richting4, Richting5, Richting6, Richting7, Dipl1, Dipl2, Dipl3, Dipl4, Dipl5, Dipl6, Dipl7, "
mSQL = mSQL & "Jaar1, Jaar2, Jaar3, Jaar4, Jaar5, Jaar6, Jaar7, NogStudie, WelkeStudie, WGVanTot1, WGVanTot2, WGVanTot3, WGVanTot4, WGVanTot5, "
mSQL = mSQL & "WGVanTot6, Functie1, Functie2, Functie3, Functie4, Functie5, Functie6, Vertrek1, Vertrek2, Vertrek3, Vertrek4, "
mSQL = mSQL & "Vertrek5, Vertrek6, BijzOpm, FunctieID) "
mSQL = mSQL & "VALUES ('" & a(1) & "','" &a(2) & "','" & a(3) & "','" & a(4) & "','" & a(5) & "','" & a(6) & "',"
mSQL = mSQL & "'" & a(7) & "','" & a(8) & "','" & a(9) & "','" & a(10) & "','" & a(11) & "','" & a(12) & "',"
mSQL = mSQL & "'" & a(13) & "','" & a(14) & "','" & a(15) & "','" & a(16) & "','" & a(17) & "','" & a(18) & "',"
mSQL = mSQL & "'" & a(19) & "','" & a(20) & "','" & a(21) & "','" & a(22) & "','" & a(23) & "','" & a(24) & "',"
mSQL = mSQL & "'" & a(25) & "','" & a(26) & "','" & a(27) & "','" & a(28) & "','" & a(29) & "','" & a(30) & "',"
mSQL = mSQL & "'" & a(31) & "','" & a(32) & "','" & a(33) & "','" & a(34) & "','" & a(35) & "','" & a(36) & "',"
mSQL = mSQL & "'" & a(37) & "','" & a(38) & "','" & a(39) & "','" & a(40) & "','" & a(41) & "','" & a(42) & "',"
mSQL = mSQL & "'" & a(43) & "','" & a(44) & "','" & a(45) & "','" & a(46) & "','" & a(47) & "','" & a(48) & "',"
mSQL = mSQL & "'" & a(49) & "','" & a(50) & "','" & a(51) & "','" & a(52) & "','" & a(53) & "','" & a(54) & "',"
mSQL = mSQL & "'" & a(55) & "','" & a(56) & "','" & a(57) & "','" & a(58) & "','" & a(59) & "','" & a(60) & "',"
mSQL = mSQL & "'" & a(61) & "','" & a(62) & "','" & a(63) & "','" & a(64) & "','" & a(65) & "','" & a(66) & "',"
mSQL = mSQL & "'" & a(67) & "','" & a(68) & "','" & a(69) & "','" & a(70) & "','" & a(71) & "','" & a(72) & "',"
mSQL = mSQL & "'" & a(73) & "','" & a(74) & "','" & a(75) & "','" & a(76) & "','" & a(77) & "','" & a(78) & "',"
mSQL = mSQL & "'" & a(79) & "','" & a(80) & "','" & a(81) & "','" & a(82) & "','" & a(83) & "','" & a(84) & "',"
mSQL = mSQL & "'" & a(85) & "','" & a(86) & "','" & a(87) & "','" & a(88) & "','" & a(89) & "','" & a(90) & "',"
mSQL = mSQL & "'" & a(91) & "','" & a(92) & "','" & a(93) & "','" & a(94) & "','" & a(95) & "','" & a(96) & "',"
mSQL = mSQL & "'" & a(97) & "','" & a(98) & "','" & a(99) & "')"
Dim Command As SqlClient.SqlCommand = New SqlClient.SqlCommand(mSQL)
Dim x As Integer
Conn.Open()
Command.Connection = Conn
x = Command.ExecuteNonQuery()
Conn.Close()
Command = Nothing

Inschrijdatum is empty, so I expect that the field in the table will be TODAY and not 1/1/1900.

Ger.

|||

Two problems:

First, Inschrijdatum isn't empty, it's a zero length string.

Secondly, the only way you are going to get SQL Server to insert the default is one of three ways.

a) Don't mention the column in the insert at all.

b) Tell it to use the DEFAULT like INSERT ... VALUES (...,DEFAULT,...), note there is no quotes around DEFAULT, it is not a string.

c) Explicitly tell it the default value like INSERT ... VALUES (...,GetDate(),...)

That said, replace:

"','" &a(2) & "','"

with:

"'," & IIF(a(2)<>"","'" & a(2) & "'","DEFAULT") & ",'"

|||

Oh, I guess I should mention this code is susceptible to SQL injection attacks.

For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

|||

Motley wrote:

Oh, I guess I should mention this code is susceptible to SQL injection attacks.

For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

Thanks Motley for replying I will reconstruct the inserts according your advice.

How can I avoid this SQL injection attack ?

Ger.

How to set a link whitch opens the record in an new Window

Hello NG,
I have to link to a SharePoint Document library out of the Report. But the
Library opens in the Report Window and not in an new Window.
How can I set the Link in Reporting Services that the Library will open in a
new Window?
Thanks
ThomasThis is for a regular report (not SharePoint) but hopefully it will put you
in the right direction. You need RS 2000 SP1 or greater (which hopefully is
everybody at this point).
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"','_blank'))"
Couple of things to notice. First, I use the Globals!ReportServerURL so I do
not hard code the server name. Second, be careful with the single and double
quotes.
Note that the Jump to URL action is used for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Burger" <thomas.burger@.nospam.de> wrote in message
news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
> Hello NG,
> I have to link to a SharePoint Document library out of the Report. But the
> Library opens in the Report Window and not in an new Window.
> How can I set the Link in Reporting Services that the Library will open in
> a new Window?
> Thanks
> Thomas
>|||Hello, thank you for your Answer.
I try it many times, but it wont work. The Report doesn't identify it as a
Link.
I build it like this.
="javascript:void(window.open('http://serveradress&"/"&Fields!new_wss_id.Value.ToString()&"/"default.aspx"+"'))"
But there must be an Mistake in it. Have I forgot something?
Thanks
Thomas
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23AX%23YeueIHA.5160@.TK2MSFTNGP05.phx.gbl...
> This is for a regular report (not SharePoint) but hopefully it will put
> you in the right direction. You need RS 2000 SP1 or greater (which
> hopefully is everybody at this point).
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "','_blank'))"
> Couple of things to notice. First, I use the Globals!ReportServerURL so I
> do not hard code the server name. Second, be careful with the single and
> double quotes.
> Note that the Jump to URL action is used for this.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
> news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
>> Hello NG,
>> I have to link to a SharePoint Document library out of the Report. But
>> the Library opens in the Report Window and not in an new Window.
>> How can I set the Link in Reporting Services that the Library will open
>> in a new Window?
>> Thanks
>> Thomas
>|||You are piecing together a string. Yours is a total mess. Look closely at
mine. You are doing the same thing.
For instance, what is the apersand doing here? Appersand means to append two
string. You do not have two strings to append. You are putting an appersand
in the middle of a string. You have done that same throughout this.
="javascript:void(window.open('http://serveradress&"/
I suggest you first hard code the value (get it working with out the Field
values.
I don't know if this will work but it is a whole lot closer:
="javascript:void(window.open('http://serveradress/" &
Fields!new_wss_id.Value.ToString() & "/default.aspx'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Burger" <thomas.burger@.nospam.de> wrote in message
news:%23T5dHrTfIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hello, thank you for your Answer.
> I try it many times, but it wont work. The Report doesn't identify it as a
> Link.
> I build it like this.
> ="javascript:void(window.open('http://serveradress&"/"&Fields!new_wss_id.Value.ToString()&"/"default.aspx"+"'))"
> But there must be an Mistake in it. Have I forgot something?
> Thanks
> Thomas
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
> news:%23AX%23YeueIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> This is for a regular report (not SharePoint) but hopefully it will put
>> you in the right direction. You need RS 2000 SP1 or greater (which
>> hopefully is everybody at this point).
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "','_blank'))"
>> Couple of things to notice. First, I use the Globals!ReportServerURL so I
>> do not hard code the server name. Second, be careful with the single and
>> double quotes.
>> Note that the Jump to URL action is used for this.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
>> Hello NG,
>> I have to link to a SharePoint Document library out of the Report. But
>> the Library opens in the Report Window and not in an new Window.
>> How can I set the Link in Reporting Services that the Library will open
>> in a new Window?
>> Thanks
>> Thomas
>>
>|||Ok I did it like you. Now the Report identyfies it as a link. But when i
click at the link nothing will happen. If I copy the link into the Internet
Explorer, nothing will happens too.
Thats the generated link.
javascript://void(window.open('http://servername:port/wss_id/default.aspx'))
If I take the middle of this link
'http://servername:port/wss_id/default.aspx than its working.
Maybe its because of the Internet Options?
Thomas
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:uzvWj3TfIHA.4728@.TK2MSFTNGP03.phx.gbl...
> You are piecing together a string. Yours is a total mess. Look closely at
> mine. You are doing the same thing.
> For instance, what is the apersand doing here? Appersand means to append
> two string. You do not have two strings to append. You are putting an
> appersand in the middle of a string. You have done that same throughout
> this.
> ="javascript:void(window.open('http://serveradress&"/
>
> I suggest you first hard code the value (get it working with out the Field
> values.
> I don't know if this will work but it is a whole lot closer:
> ="javascript:void(window.open('http://serveradress/" &
> Fields!new_wss_id.Value.ToString() & "/default.aspx'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
> news:%23T5dHrTfIHA.5164@.TK2MSFTNGP03.phx.gbl...
>> Hello, thank you for your Answer.
>> I try it many times, but it wont work. The Report doesn't identify it as
>> a Link.
>> I build it like this.
>> ="javascript:void(window.open('http://serveradress&"/"&Fields!new_wss_id.Value.ToString()&"/"default.aspx"+"'))"
>> But there must be an Mistake in it. Have I forgot something?
>> Thanks
>> Thomas
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
>> news:%23AX%23YeueIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> This is for a regular report (not SharePoint) but hopefully it will put
>> you in the right direction. You need RS 2000 SP1 or greater (which
>> hopefully is everybody at this point).
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "','_blank'))"
>> Couple of things to notice. First, I use the Globals!ReportServerURL so
>> I do not hard code the server name. Second, be careful with the single
>> and double quotes.
>> Note that the Jump to URL action is used for this.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
>> Hello NG,
>> I have to link to a SharePoint Document library out of the Report. But
>> the Library opens in the Report Window and not in an new Window.
>> How can I set the Link in Reporting Services that the Library will open
>> in a new Window?
>> Thanks
>> Thomas
>>
>>
>|||What is this about? You don't want to have a // after javascript.
javascript://void
As I mentioned previously, I do not know about sharepoint integration.
However, I don't think it will make any difference.
Do the following, put what I have below in the jump to URL action and make
sure this works as advertised. This pulls up the google page in another
window:
="javascript:void(window.open('http://www.google.com/','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Thomas Burger" <thomas.burger@.nospam.de> wrote in message
news:uCiNm7UfIHA.1900@.TK2MSFTNGP02.phx.gbl...
> Ok I did it like you. Now the Report identyfies it as a link. But when i
> click at the link nothing will happen. If I copy the link into the
> Internet Explorer, nothing will happens too.
> Thats the generated link.
> javascript://void(window.open('http://servername:port/wss_id/default.aspx'))
> If I take the middle of this link
> 'http://servername:port/wss_id/default.aspx than its working.
> Maybe its because of the Internet Options?
> Thomas
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
> news:uzvWj3TfIHA.4728@.TK2MSFTNGP03.phx.gbl...
>> You are piecing together a string. Yours is a total mess. Look closely at
>> mine. You are doing the same thing.
>> For instance, what is the apersand doing here? Appersand means to append
>> two string. You do not have two strings to append. You are putting an
>> appersand in the middle of a string. You have done that same throughout
>> this.
>> ="javascript:void(window.open('http://serveradress&"/
>>
>> I suggest you first hard code the value (get it working with out the
>> Field values.
>> I don't know if this will work but it is a whole lot closer:
>> ="javascript:void(window.open('http://serveradress/" &
>> Fields!new_wss_id.Value.ToString() & "/default.aspx'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:%23T5dHrTfIHA.5164@.TK2MSFTNGP03.phx.gbl...
>> Hello, thank you for your Answer.
>> I try it many times, but it wont work. The Report doesn't identify it as
>> a Link.
>> I build it like this.
>> ="javascript:void(window.open('http://serveradress&"/"&Fields!new_wss_id.Value.ToString()&"/"default.aspx"+"'))"
>> But there must be an Mistake in it. Have I forgot something?
>> Thanks
>> Thomas
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
>> news:%23AX%23YeueIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> This is for a regular report (not SharePoint) but hopefully it will put
>> you in the right direction. You need RS 2000 SP1 or greater (which
>> hopefully is everybody at this point).
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "','_blank'))"
>> Couple of things to notice. First, I use the Globals!ReportServerURL so
>> I do not hard code the server name. Second, be careful with the single
>> and double quotes.
>> Note that the Jump to URL action is used for this.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
>> Hello NG,
>> I have to link to a SharePoint Document library out of the Report. But
>> the Library opens in the Report Window and not in an new Window.
>> How can I set the Link in Reporting Services that the Library will
>> open in a new Window?
>> Thanks
>> Thomas
>>
>>
>>
>|||I took your link into the Jump to Url action. But its the same Problem.
When I click at the Link nothing will happen.
Is there any other alternative without javascript to open in a new Window?
Thomas
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OIAzHiVfIHA.5900@.TK2MSFTNGP02.phx.gbl...
> What is this about? You don't want to have a // after javascript.
> javascript://void
> As I mentioned previously, I do not know about sharepoint integration.
> However, I don't think it will make any difference.
> Do the following, put what I have below in the jump to URL action and make
> sure this works as advertised. This pulls up the google page in another
> window:
> ="javascript:void(window.open('http://www.google.com/','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
> news:uCiNm7UfIHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Ok I did it like you. Now the Report identyfies it as a link. But when i
>> click at the link nothing will happen. If I copy the link into the
>> Internet Explorer, nothing will happens too.
>> Thats the generated link.
>> javascript://void(window.open('http://servername:port/wss_id/default.aspx'))
>> If I take the middle of this link
>> 'http://servername:port/wss_id/default.aspx than its working.
>> Maybe its because of the Internet Options?
>> Thomas
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
>> news:uzvWj3TfIHA.4728@.TK2MSFTNGP03.phx.gbl...
>> You are piecing together a string. Yours is a total mess. Look closely
>> at mine. You are doing the same thing.
>> For instance, what is the apersand doing here? Appersand means to append
>> two string. You do not have two strings to append. You are putting an
>> appersand in the middle of a string. You have done that same throughout
>> this.
>> ="javascript:void(window.open('http://serveradress&"/
>>
>> I suggest you first hard code the value (get it working with out the
>> Field values.
>> I don't know if this will work but it is a whole lot closer:
>> ="javascript:void(window.open('http://serveradress/" &
>> Fields!new_wss_id.Value.ToString() & "/default.aspx'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:%23T5dHrTfIHA.5164@.TK2MSFTNGP03.phx.gbl...
>> Hello, thank you for your Answer.
>> I try it many times, but it wont work. The Report doesn't identify it
>> as a Link.
>> I build it like this.
>> ="javascript:void(window.open('http://serveradress&"/"&Fields!new_wss_id.Value.ToString()&"/"default.aspx"+"'))"
>> But there must be an Mistake in it. Have I forgot something?
>> Thanks
>> Thomas
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
>> news:%23AX%23YeueIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> This is for a regular report (not SharePoint) but hopefully it will
>> put you in the right direction. You need RS 2000 SP1 or greater (which
>> hopefully is everybody at this point).
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "','_blank'))"
>> Couple of things to notice. First, I use the Globals!ReportServerURL
>> so I do not hard code the server name. Second, be careful with the
>> single and double quotes.
>> Note that the Jump to URL action is used for this.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Thomas Burger" <thomas.burger@.nospam.de> wrote in message
>> news:upw1MZueIHA.5788@.TK2MSFTNGP02.phx.gbl...
>> Hello NG,
>> I have to link to a SharePoint Document library out of the Report.
>> But the Library opens in the Report Window and not in an new Window.
>> How can I set the Link in Reporting Services that the Library will
>> open in a new Window?
>> Thanks
>> Thomas
>>
>>
>>
>>
>