|
Instead of null , use System.DBNull.Value . This is because in certain cases you will need to distinguish between a C# null and a database null. For example, in the case of ExecuteScalar a null result means that there was no result (i.e. the answer did not exist) whereas if ExecuteScalar returns DBNull.Value then the answer from the database is null (i.e. there is an answer, and the answer is null).
See MSDN: System.DBNull[^]
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I tip my hat to you Colin, thanks!!!
I have another issue with changing the field to DBNull...
but I think this is a C# issue with binding textbox with DataTable
and so I'll post it there as well.
I'm not able to:
if (txb_Number.Text == "")
{
//this never occurs because the textbox reverts to the original bound Long Integer value
}
I can only change the bound data if a new number is used. A blank textbox (or letters entered) only reverts back to the origninal value. I want to update the field to DBNull if the textbox is empty.
Ron
|
|
|
|
|
Hi Ron,
Please try the following
myCommand.Parameters.Add("@Number", OleDbType.BigInt).Value = DBNull.Value
or
myCommand.Parameters.Add("@Number", DBNull.Value)
|
|
|
|
|
i need to import inside a sql server 2005 table a file exel
with a column with some strings.
column
-------
sfsdfsfd
dsfsfgg
54432fd
dss43r
gdgdgdfgd43
gdfgdfg43
etc...
how can i do?
|
|
|
|
|
Use DTS
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
DTS : Data Transformance Services
Used to import export information by SQL server in Packages that can be run as timed or scheduled events.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Frank Kerrigan wrote: DTS : Data Transformance Services
DTS was renamed SSIS (SQL Server Integration Services) in SQL Server 2005 - It didn't happen until the after the first beta went out so the development team decided not to rename all the classes - So the namespace for programatically accessing SSIS still refers to DTS or DataTransformationServices.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Trying to create a report with tabular format. Run into a few problems...
I have the following entities:
a. Project (Parent of Budget and Contract)
b. Budgets
c. Contracts
So, I created two dataset:
<br />
SELECT <br />
PRJ.[ID] AS PROJID, <br />
PRJ.[Name] AS PROJNAME, <br />
PRJ.[CreateDate] AS PROJCREATEDATE, <br />
CTR.[ID] AS CONTRACTID, <br />
CTR.[Code] AS CONTRACTCODE, <br />
CTR.[Name] AS CONTRACTNAME<br />
FROM <br />
Project AS PRJ INNER JOIN<br />
Contract CTR <br />
ON PRJ.[ID] = CTR.[ProjectID]<br />
<br />
-- DataSet - ProjectBudget: <br />
SELECT <br />
PRJ.[ID] AS PROJID, PRJ.[Name] AS PROJNAME, <br />
PRJ.[CreateDate] AS PROJCREATEDATE, <br />
BDG.[BudgetType] AS BUDTYPE,<br />
BDG.[BudgetDescMM] AS BUDDESCMM,<br />
BDG.[BudgetDescYYYY] AS BUDDESCYYYY,<br />
BDG.[Remarks] AS BDGREMARKS<br />
FROM <br />
Project AS PRJ INNER JOIN<br />
Budget BDG<br />
ON PRJ.[ID] = BDG.[ProjectID]
Now, in my report, I have this ONE table - I wish to have "Project ID" as first column of this table, with TWO drilldown buttons (the "+" sign/button) buttons to respectively show/hide child "Budget" and child "Contract". The problem is, Reporting Service allow one to specify only ONE dataset for each "Table" on the report (right click table, then: "Properties>General>Dataset Name" - only one dataset can be selected.).
I tried to get around this by using "hyperlink" navigation instead of drilldown buttons. But, anyone can tell me if any security issue will get into the way when user navigate from one report to another through embedded hyperlink? Will user be prompted to enter credential twice?
Thanks in advance!
|
|
|
|
|
Hi All
I have a table I would like to sort by name but found that Upper Case and Lower Case of the same character is not treated the same.
SELECT *
FROM MyTABLE
ORDER BY [NAME]
Are the characters being treated as ASCII Code please help....
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
japel wrote: Are the characters being treated as ASCII Code
If the database Collation sequence is set to Latin1_General_BIN then yes, the characters are ordered in the binary sequence of character set.
If it isn't then I would guess that the collation sequence in use is case sensitive.
The default option on installation is Latin1_General_CI_AS (Case insensitive, Accent sensitive) so I would imagine that who ever installed SQL Server had a good reason for changing the default setting.
You can, however, use ORDER BY UPPER([NAME])
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 10:20 Sunday 23rd April, 2006
|
|
|
|
|
Hi
Can any bodu tell me what is the Difference between Equi join, self Join and innner join?
Any help is appreciated
Regards
Rajesh
|
|
|
|
|
Equi-join is where two tables are joined on a column (or columns) with matching values.
A self-join is where a table joins onto itself.
An inner-join is where the join condition must match on both sides - The most common sort of inner join is an equi-join.
To complete the list:
An outer-join will match rows for both sides of the join where it can, and substitute nulls where it can't.
A cross-join will join every row one side to every row on the other side of the join (For a large table this is a very expensive operation).
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I was creating the stored procedure in SQL Server and durind that I stuck into an error
The Query is
Create Procedure Procedurename
@names varchar;
@roll int;
as
Insert into tablename values(@names , @roll)
pankaj garg
|
|
|
|
|
You should post your actual code because this may not be representative.
The error in your example code is that you need to use commas when separating parameters into the stored procedure
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
hey
Create Procedure Procedurename
(
@names varchar,
@roll int
)
as
Insert into tablename values(@names , @roll)
dadax_85@hotmail.com
|
|
|
|
|
what is the difference between these in performance considerations?
1:
SqlParameter paramArticleID = new SqlParameter ( "@articleID" , SqlDbType.Int );
paramArticleID.Value = articleID;
sqlCommand.Parameters.Add ( paramArticleID );
2:
sqlCommand.Parameters.Add ( "@articleID", articleID );
|
|
|
|
|
The former is better because it avoids an extra roundtrip to the server to find the data type of the parameter.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I want to update a particular row from the table in the Dataset. Then I want to update Database through the use of Data Adapter. How can I do that?
Thanks,
Sandy
|
|
|
|
|
Hi,
I have a form on which there is a textbox and a datagrid. Both the text box and datagrid are bound to some data sources. There are only two columns in the datagrid. The text box basically displays a column of the datagrid. When the user clicks on the New or Modify button, he is able to edit the value in the text box or add a new one which is added to the bound grid. But the problem is that the Text property of the text box becomes equal to empty string before it is added to the database or the grid. I have checked the source code many time but i dont know why it happens. I have faced the same problem previously but at that time, it became equal to empty string when AcceptChanges() method of the datatable was called. Any kind of help or suggestion please...
Regards,
Wasif Ehsan.
|
|
|
|
|
Recently, I 'inherited' an application using SQLServer... I was asked to document what tables have indexes, etc. and what they are...
Like most programmers, I HATE repetitive work, so, after much digging, the following statement has been created to list table index attributes.
Hopefully, it will save someone else the pain I endured...
select O.name, I.name, c.name, case isnull(c.autoval,0) when 0 then 'No' else 'Yes' end AutoVal,
Nullable = case isnullable when 0 then 'No' else 'Yes' end,
CASE
WHEN I.status & 2 = 2 THEN 'Yes'
ELSE 'No'
END AS is_unique,
CASE
WHEN I.status & 16 = 16 THEN 'Yes'
ELSE 'No'
END AS is_clustered,
CASE
WHEN I.status & 2048 = 2048 THEN 'Yes'
ELSE 'No'
END AS is_PK_CNS,
CASE
WHEN I.status & 4096 = 4096 THEN 'Yes'
ELSE 'No'
END AS is_UNQ_CNS
from sysindexes I
inner join sysobjects O ON I.id = O.ID
inner join sysindexkeys K ON I.id = K.ID AND I.INDID = K.INDID
inner join syscolumns C ON K.colid = C.colid and c.id = o.id
inner join sysobjects IO on i.name = io.name
WHERE O.Type = 'U'
and i.name not like '_wa%'
order by 1, 2 desc, 3
|
|
|
|
|
this will list ALL column constraints, but if you name them right, you could easily get your index list this way as well:
select * from information_schema.constraint_column_usage
|
|
|
|
|
I am not asking any kind of programming question. MY question "what are the searching capabilities of databases". Just a couple of lines answer.
Thanks
Be FaithFull To Your Work.
|
|
|
|
|
It depends on the database - However, each should manage some funamental filtering such as equality, greater than, less than, not equal to, is null, and so on.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
We are using SAP business one as CRM.
We work with SQL 2000 SP4 and Reporting Services for SQL 2000
Situation
Table 1: OCRD
This table contains most of the data; cardcode /name / phonenumbers /email / biling addres / qrygroup1 tm qrygroup64 in our application you can put a checkmark on a properties tab and we use these properties to define which company or Formula these company's belong to.. in our case if qrygroup1 has a checkmark it means: the customer is part of the formula "profile" if qrygroup4 is checked he is part of the "Bonusbikes" formula
Table 2: CRD1
This table is a "sub table of OCRD" and it contains : cardcode / street /zip /city /country. The field cardcode is the same as in table OCRD but can appear more than one time in the list when customer has more than one adress: for example billing address and Supply address / some customers have more than one branch, the customers cardcode will be the same only an extra line is added in CRD1 table .they are both located in this table, the billing address is also in OCRD))
Table 3:
This table contains the qrygroup columns from OCRD
Groupcode / Groupname (groupcode= 1 refers to qrygroup1 from OCRD the groupname will be Profile, groupcode 4 is qrygroup4 from OCRD and that groupname is BONUSBIKES)
In my report I want to filter on a couple criteria but the only one giving problems is this one!
I want to generate the report so, that I can filter on "Profile / bonusbikes / alle "
-------------------------------------------------------------
In SQL enterprise admin I created two views which contain the following queries:
View1: LEDEN
SELECT TOP 100 PERCENT dbo.OCRD.CardCode AS Lidnummer,
dbo.OCQG.GroupName AS Formule,
dbo.OCRD.CardName AS Naam,
dbo.OCRD.Phone1 AS Telefoon,
dbo.OCRD.frozenFor AS Geblokkeerd,
dbo.OCRD.E_Mail AS [E-mail],
dbo.OCRD.SlpCode AS Accountbegeleider,
dbo.CRD1.ZipCode, dbo.CRD1.City,
dbo.CRD1.Country, dbo.CRD1.Street
FROM dbo.OCRD RIGHT OUTER JOIN
dbo.CRD1 ON dbo.OCRD.CardCode = dbo.CRD1.CardCode CROSS JOIN dbo.OCQG
WHERE (dbo.OCQG.GroupName = 'Profile') AND (dbo.OCRD.QryGroup1 = 'Y') AND
(dbo.OCRD.CardType = 'c') AND (dbo.CRD1.AdresType = 's') OR
(dbo.OCQG.GroupName = 'Fietswereld') AND (dbo.CRD1.AdresType = 's') AND
(dbo.OCRD.QryGroup2 = 'Y') OR
(dbo.OCQG.GroupName = 'BBG') AND (dbo.CRD1.AdresType = 's') AND
(dbo.OCRD.QryGroup3 = 'Y') OR
(dbo.OCQG.GroupName = 'bonusbikes') AND
(dbo.CRD1.AdresType = 's') AND (dbo.OCRD.QryGroup4 = 'Y')
This returns : (I left out some fields)
Lidnummer Formule Naam Telefoon geblokkeerd e-mail
8479 profile jansen 00000 n jansen@profile.nl
8442 bonusbikes klaasen 11111 n klaasen@bonusbikes.nl
8888 BBG smith 22222 n smith@bireto.com
View2: FORMULES
SELECT GroupName, GroupCode
FROM dbo.OCQG
WHERE (GroupCode = 1) OR
(GroupCode = 2) OR
(GroupCode = 3) OR
(GroupCode = 4)
This returns
Profile
Fietswereld
BBG
Bonusbikes
-----------
DOTNET
In visual studio dot net I created a new report project added the datasource and created two datasets:
Dataset 1: LEDEN
Dataset 2: FORMULES
In dataset 1, I place all columns from VIEW 1: LEDEN
Than I set a parameter on FORMULE in dataset LEDEN -> @Formule
In dataset 2: FORMULES, I place the results of VIEW 2: FORMULES
I create the report parameter with values from query and select Dataset: FORMULES
To have the “select all” option I place the following in dataset LEDEN
--------------------------------
UNION
Select ‘-1’, ‘Alle’
--------------------------------
The entire query in dataset 2 now looks like:
-------------------------------------
SELECT Groupname
FROM formulas
UNION
SELECT ‘-1’, ‘Alle’
-------------------------------------
When I run my report I get an error:
The graphical designer doesn’t support UNION etc… this is normal, I know!
But then I get another error : could not generate a list of fields for the query , union must have an equal number of expressions! ?????
What is it that I do wrong or what can I do to resolve this!
Thanks in advance!
Hans Vergouwen
|
|
|
|
|