|
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
|
|
|
|
|
Hi there,
I'm in a bit of a quandary.
I have a VB.net application that uses triggers to generate an audit trail from the tables in my SQL Server 2000 DB. The main DB tables are fairly normalised with some of the tables having a one-to-one relationship. The audit DB structure exactly matches that of the main one with the addition of four columns in each table:
Audit_ID IDENTITY int
Audit_Timestamp smalldatetime getDate()
Audit_Username // who did the audi
Audit_Action //INSERT, UPDATE or DELETE.
My problem is that I need to join records together in the audit DB for display in an Audit Search utility.
For example, I have a Transaction table with the following fields:
TransID [PK] int IDENTITY
TransDate
NumberOfLots
...
and the following child tables
Trans_Future
TransID [FK]
SpotPrice
FuturePrice
...
Trans_Option
TransID [FK]
Premium
...
Trans_CFD
TransID [FK]
CFDPrice
StampdutyApplies
...
So to display the Transaction audit log I need to be able to show all the Transaction table values and selected ones from the realted table.
Inserts and updates of, say, a Future Transaction, write a record to Transaction table and then to the Trans_Future table with the new TransID all in a SQLTransaction in VB.net.
Any suggestions would be great,
Daire
Daire Cunningham
|
|
|
|
|
I am executing a stored procedure from within other procedure with EXEC SPname Command. I have read that we should use sp_ExecuteSql system stored procedure in place of EXEC command because it will catch the execution plan whereas executing a statement or a stored procedure will not catch the execution plan. Now i am trying to execute my stored procedure as
Execute Sp_ExecuteSql @parameterName from within another stored procedure
where @paramter is an integer (but internet says that sp_executesql only accepts nvarchar/ntext datatype).So i am not able to really execute my stored procedure with sp_ExecuteSql. Am i missing out something..is there some prodedure to do this task???
ANY HELP WILL BE GREATLY APPRECIATED.
THANKS
|
|
|
|
|
rohitgraycell wrote: but internet says that sp_executesql only accepts nvarchar/ntext datatype
You may wish to take a closer look at the documentation sp_executesql[^]
For example:
sp_executesql 'SELECT * FROM MyTable WHERE someColumn = @someIntParam',
'@someIntParam INT',
@someIntParam = 12;
"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
|
|
|
|
|
Aloha,
Some genius have upgraded our SQL Server (Server1) to version 2005. This is bad since we need to restore a database from Server1 to another server that only runs version 2000 (Server2).
Is there some way we can backup our database in version 2000 format? I have tried a lot of things, but we always seems to end up with something that Server2 doesn't want to eat 
Thank you for your help,
phi
phimix.com 
|
|
|
|
|
Hi..
I have .NET application that traks projects. I want to be able to create search page that will allow me to search project by title, description, etc.
I was able to create a search page and search functionality as well. The only problem I have is when I search within description user must type exactly word to word as description is in DB (an it can be long one). In my SQL statement I use LIKE to match what user types and datatbase entry. Is there anyway around it? Maybe I should use something else (I'm not sure what because I'm not a big SQL guy). The other thing is I need a way around case sensativity project 1 is not PROJECT 1.
Any help would be very welcome.
Thanks,
|
|
|
|
|
SELECT p.title, p.description <br />
FROM project p<br />
WHERE UPPER(p.description) LIKE '%' + UPPER(@cSearchString) + '%'
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Thansk a lot bro. I got figure it out. 
The funny thing is I used LOWER function.
Thanks again. I really appreciate it.
|
|
|
|
|
alexfromto wrote: The other thing is I need a way around case sensativity project 1 is not PROJECT 1
That could be hard, because the default installation of SQL Server is not case sensitive. You could try converting both strings to varbinary (to preserve case sensitivity), altough I'm not sure how you would compare them.
Luis Alonso Ramos
Intelectix
Chihuahua, Mexico Not much here: My CP Blog!
|
|
|
|
|
Hi All... Currently i am doing my banking application using ASP.Net,C#, SQL Server2000... i want some technical solution realated securities from you guys as follow..
i already logged to my application by using username and password.. after login page my home page Home.aspx coming on screen.. i have features all like Balance Inquiry, Mini Statement, Last Transaction, Private Banking, Net Banking in my Home Page in left menu side. All Features are working very well.. For Security Reason i want some technical help from all of you guys...
Problem is After my successful login page If i not touch the Home page or if i not click on any features available in home page for 10 minutes.. Like if i click on Balance Inquiry Link after 10 minutes that time Page Login.aspx want to open instead of BalanceInquiry.aspx... After my successful login by username and password i want to open page BalanceInquiry.aspx (do not want open Home.aspx Page Again)... For that what i have to do in my Application to make more secure... if u have any articles link then please send me..
Please Please Help me if you people have some technical solution... Bcz right now i am on mid way of that project... if u have any articles or articles link please help me out....
Regards,
Patel...
|
|
|
|
|
|
Toasty0,
Thank you for providing fellow Cpians with the informative link.
Paul
|
|
|
|
|
Hi
I have one GridView and in this gv i have two column one is mark-printer other is model-printer.So i done this column like template but i don't know how to do exchanging of data when i doing edit???I mean if i choos for example "HP" i want to see only models for hp not all how to link both controls pleas help me.
Good bye best regards
|
|
|
|
|
I have a function which is used as a default in a number of tables/columns.
I want to alter that function but when I do an "ALTER FUNCTION..." bit of SQL it tells me it can't be altered because it is in use - does anyone have a handy "drop all the defaults, alter function, reinstate all the defaults" type script I could cadge?
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
<br />
create table tempdb..DEJ_Constraints<br />
(<br />
table_name sysname,<br />
constraint_name sysname,<br />
constraint_text varchar(1000),<br />
column_name sysname<br />
)<br />
go<br />
<br />
<br />
insert into tempdb..DEJ_Constraints<br />
select object_name(sysconstraints.id) as table_name,<br />
object_name(sysconstraints.constid) as constraint_name,<br />
syscomments.text as constraint_text,<br />
syscolumns.name as column_name<br />
from (syscomments inner join sysconstraints on<br />
syscomments.id = sysconstraints.constid) INNER JOIN syscolumns<br />
on syscolumns.id = sysconstraints.id <br />
and syscolumns.colid = sysconstraints.colid<br />
where syscomments.text like '%GetNextkey%'<br />
go<br />
<br />
-- Drop any existing constraints that use the function...<br />
declare @table_name sysname,<br />
@constraint_name sysname,<br />
@command varchar(1000)<br />
<br />
declare constraint_killer cursor for<br />
select table_name, constraint_name <br />
from tempdb..DEJ_Constraints<br />
<br />
open constraint_killer <br />
<br />
<br />
FETCH NEXT FROM constraint_killer INTO @table_name , @constraint_name<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @COMMAND = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @constraint_name<br />
<br />
EXECUTE(@COMMAND)<br />
<br />
print 'Executed : ' + @command <br />
<br />
FETCH NEXT FROM constraint_killer INTO @table_name , @constraint_name<br />
END<br />
<br />
close constraint_killer <br />
<br />
deallocate constraint_killer <br />
go<br />
then alter the function then..
<br />
declare @table_name sysname,<br />
@constraint_name sysname,<br />
@column_name sysname,<br />
@constraint_text varchar(1000),<br />
@command varchar(1000)<br />
<br />
declare constraint_builder cursor for<br />
select table_name, constraint_name , constraint_text, column_name<br />
from tempdb..DEJ_Constraints<br />
<br />
open constraint_builder <br />
<br />
<br />
FETCH NEXT FROM constraint_builder INTO @table_name , @constraint_name, @constraint_text ,@column_name <br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @COMMAND = 'ALTER TABLE ' + @table_name + ' ADD CONSTRAINT ' + @constraint_name + ' DEFAULT ' + @constraint_text + ' FOR ' + @column_name <br />
<br />
EXECUTE(@COMMAND)<br />
<br />
print 'Executed : ' + @command <br />
<br />
FETCH NEXT FROM constraint_builder INTO @table_name , @constraint_name, @constraint_text , @column_name <br />
END<br />
<br />
close constraint_builder <br />
<br />
deallocate constraint_builder <br />
go<br />
<br />
drop table tempdb..DEJ_Constraints<br />
go<br />
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
