Apply Your Thought On Char To Int
Feb 7, 2004HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
ADVERTISEMENT
CROSS APPLY Vs OUTER APPLY Example Messed Up?
Nov 27, 2007Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
Not Returning What I Thought It Should
Nov 21, 2005SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table.
View 4 Replies View RelatedWell I Thought This Might Work...
Apr 8, 2008So I've been working on this project and I've finally gotten into the program stages. However, I've realised some things that I need to change to some of my stored procedures so that they work the way I want them to inside the program. Anyways I have this stored procedure called "Delete_Minors" and what it originally did was delete any class found in this table called MinorRequiredClasses that matched that minor and was not considered complete. I realised that this would be a bad decision to delete classes in a minor and not consider any other majors/minors a user might have that also require those classes...SO I thought I might be able to come up with a query but it doesn't seem to work. Below are the tables the query/stored procedure deals with as well as the old procedure and the new one (that I attempted to work the way i wanted it to).
Code Snippet
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
IF (SELECT COUNT(*) FROM Student_Minors sMinors WHERE sMinors.MinorID = @minorid AND sMinors.StudentID = @studid) > 0
BEGIN
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
DELETE FROM Student_Minors
WHERE StudentID = @studid AND MinorID = @minorid
END
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 04/07/2008 22:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 04/07/2008 22:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorRequiredClasses](
[MajorClassID] [int] IDENTITY(0,1) NOT NULL,
[MajorDisciplineID] [int] NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MajorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDisciplines] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
OLD QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = "Computer Science"
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
NEW QUERY
DECLARE @studid int
DECLARE @minorid varchar(50)
SET @studid = 0
SET @minorid = 'Computer Science'
DELETE FROM Student_Classes
WHERE StudentID = @studid AND Completed = 0
AND ClassID IN (SELECT minReqC.ClassID
FROM MinorRequiredClasses minReqC
WHERE minReqC.MinorID = @minorid)
AND ClassID NOT IN (SELECT majReqC.ClassID
FROM MajorRequiredClasses majReqC
WHERE majRecC.MajorDisciplineID IN (SELECT sMajors.MajorDisciplineID
FROM Student_Majors sMajors
WHERE sMajors.StudentID = @studid))
Let me know if you need more information...Hope someone can help!
I Thought This Would Be Simple...
Jul 14, 2006I had originally thought a full outer join would solve this, but alas I'm a roadblock. Perhaps it's because it's a Friday!
The goal is to create the most efficient query that will display results of the daily calls and sales as
this:
Date EmpID Calls Sales
7/1/2006 1 20 5
7/1/2006 2 25 7
7/1/2006 3 NULL 1
7/1/2006 4 10 NULL
The problem is a simple full outer join ends up ignoring EmpID 3 who has no Calls in t1, but I still want that row displayed in the results. Any ideas? TIA
create table t1 (Date smalldatetime, EmpID int, Calls int)
create table t2 (Date smalldatetime, EmpID int, Sales int)
insert into t1
values ('7/1/2006', 1, 20)
insert into t1
values ('7/1/2006', 2, 25)
insert into t1
values ('7/1/2006', 4, 10)
insert into t2
values ('7/1/2006', 1, 5)
insert into t2
values ('7/1/2006', 2, 7)
insert into t2
values ('7/1/2006', 3, 1)
This Is More Of An Access Question, But I Thought I'd Try..
Feb 3, 2004I have a hunch this one is going to be a resounding "no", but I thought I'd try anyways.
I have a report that uses a user defined date range many, many times throughout the datasource. Ideally, I would like to pass a query declaring and setting variables and let sql server (2000) sort out the dirty work. Essentially I'm working on something that would look like this:
DECLARE @sDate AS DATETIME
DECLARE @eDate AS DATETIME
SET @sDate = 'this string gets constructed during the On Open event of a report
SET @eDate = 'same thing here
SELECT lotsOfStuff, (SELECT oneOfManySubSelects FROM t2 WHERE t2.field BETWEEN @sDate AND @eDate)
FROM somewhere
WHERE somefield BETWEEN @sDate AND @eDate
I have some five subselects that are dependent on this daterange. I can construct the entire string purely in VB, but it's messy and rather tedious. Ideally I'd like to set the variable ONCE at runtime and be done with it. This way, I keep a full record source that calls @sDate and @eDate. Then I simply set the variables and insert them before the query.
The problem is Access doesn't seem to know how to pass the query without trying to parse the variables itself. So it gets mad that @sDate and @eDate haven't been defined for each occurance. I'm looking for a way to make access ignore the fact that there are variables in the query, and pass it as-is to the sql server.
Thoughts?
Forward Looking Thought: Raw Files
Oct 6, 2006I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
Phil
Okay, I Thought I Had The Return Value From A Statement Figured Out...
Nov 15, 2007 So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>)
SqlParameter param = new SqlParameter();
param.ParameterName = "@return";
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
command.ExecuteNonQuery();
//get the return value
int val = int.Parse(command.Parameters[0].ToString());
Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following
Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs) dim returnValue as integer 'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable? InsertIssue.Insert() Response.Redirect("/addarticletoissue") End Sub
again, thank you for your help and patience with such a beginner =)
Food For Thought (ReIndex And Log Shipping)
Dec 29, 2003I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .
During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.
I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.
AnyIdea
Simple OUTER JOIN (I Thought)
Sep 11, 2007Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
View 2 Replies View RelatedI Thought I Posted This One But Don't See It. Problem With Querying A Query
Jul 20, 2005It is my understanding that Views cannot have parameters. Also thatstored procedures can not be queried. My problem is this:I want to select the rows that match a certain parameter.From that I want to select the most current 20 rows (there is a datefield).From that I want to select the lowest 10 rows based on a numericfield.Finally I want that to be input to a report and some calculations.What this basically is the selection for USGA Golf Handicap Index. Itis the most current 20 rounds of golf by a golfer, then the best 10 ofthose 20 and then finally the calculation.Any help would be appreciated.
View 3 Replies View RelatedNoob Alert! What I Thought Was A Real Simple Query...
Oct 22, 2007Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
How To Substring From 12 Char To 8 Char Itemid
Jun 19, 2008Hi,
alter PROCEDURE [dbo].[PPUpdateIWDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
Char(1) And Char(2) Take Same Space?
Aug 21, 2007 I create two tables:
create table table1
(
col1 char(1)
)
go
create table table2
(
col2 char(2)
)
go
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array
Any suggestions?
How To Apply Hot Fix?
Mar 26, 2007I have a sql server problem for which the solution is at,
http://support.microsoft.com/kb/889521
The article talks about a hot fix, but there is no link for download the hot fix.
Can anyone have a look and guide me.
Thanks
------------------------
I think, therefore I am - Rene Descartes
Does SETROWCOUNT 10 Apply To Only The Sp In Which It Used Or Outside It Also?
May 23, 2007I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
Using Like And Apply To 3 Columns
Sep 7, 2007I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.
View 6 Replies View RelatedDTS Globalvariable Not Apply ......
Jun 24, 2004I am new to DTS.
In ExecuteSQLTask:
want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be
backup database ? to 'backup_' + ? with init
But it looks the parameter could not place a globalVariable to it and parse it with error.
One alternate way probably need to create a proc to pass a parm to it....
Also, how could pass a globalVariable to a FTP task?
thanks
-D
Best Way To Apply SP3 In Replication
Aug 27, 2004We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
Thanks
Apply Lock On A Row?
Sep 12, 2013I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row
View 3 Replies View RelatedSp3a Does Not Seem To Apply
Feb 26, 2004I am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:
8.00.194
Now, according to the sp3a readme:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
the following versions should show:
SQL Server 2000 Original Release 8.00.194 RTM
Database Components SP1 or Desktop Engine SP1 8.00.384 SP1
Database Components SP2 or Desktop Engine SP2 8.00.534 SP2
Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3
This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help.
Also, if it is really installing, is there another way to tell?
Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.
Tony F
Cross Apply
May 22, 2008What is Cross Apply, when it will be used ?
View 2 Replies View RelatedWhere To Apply My SQL Knowledge...
Jul 22, 2007Hello everyone...
i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
don't worry be happy
Which Hotfix To Apply?
Oct 14, 2007We would like to apply a hotfix to solve the issue that brought our server down. The hotfix we are trying to apply is in Build 3175. But the latest Cumulative Update 3 Package is Build 3186. Should we apply 3175? Or we can go for the latest build 3186? Which is recommended?
View 6 Replies View RelatedApply Top && Group By
Nov 20, 2007I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
Thanks
Apply SP2 After Applying CTP?
Feb 20, 2007Should I apply SP2 even though I have already installed the community preview?
View 1 Replies View RelatedHow Sql Connection Apply With Class
Apr 17, 2008HI,
I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb
Class1.vb(see the below code)
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Class1
Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand()
Private da As New SqlDataAdapter(cmd)
End Class
default.aspx(here is asp.net page i want to use the sql connection from that class.)
but this below page saying that Name "cmd" is not declared and connection
Try
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password")
Next drNext dt
If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx")
Else
Label3.Text = "Invalid Username/password"
End IfCatch ex As Exception
' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
End Try
End Sub
please let me know where im doing mistake.....
Thank in Advance
Permissions / Apply Button
Aug 20, 1999Is anyone else having trouble with the Apply button under the permissions tab?
I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
Cross Apply And Newid
Apr 28, 2008Hi,
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
select locations.*, T.c.value('.','nvarchar(max)') from
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from
(
select newid() as Id
union
select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);
Thanks,
Victor
CROSS APPLY Equivalent
Apr 29, 2008I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
How I Apply Unique Constraint
Mar 2, 2007how can i apply unique cosntraint in an existing table's column
i want to do it like this as below
sql server enterprise manager ==> DB==>table (R.click)==>design table
How To Apply Complex Constraints
Jul 23, 2005Hi all!I want to create a constraint that uses data from other tables,specifically i want to make sure that a varchar has exactly the lengthspecified in an integer-column in a table that I pointed out with aforeign key.I would like this to be solved something like this:create table string_size_limits(row_id INTEGER PRIMARY KEYstring_size INTEGER)create table strings(string_size_limit_row_id INTEGERREFERENCES string_size_limits(row_id)string varcher(50)CONSTRAINT check_string_size CHECK ???)Is it possible to solve this problem without SP using above model?Is it possible to solve this problem with SP using above model?Must the above problem be solved using triggers?Any help appreciated
View 4 Replies View RelatedCannot Apply SP4 On Msde 2000 SP3
Apr 2, 2007I try applying the SP 4 on the default instance of the msde 2000 and when it stops after a minute generates the log and says it was successful but no upgrade is done. I use the following command setup /upgradesp sqlrun SECURITYMODE=SQL BLANKSAPWD=1 DISABLENETWORKPROTOCOLS=0 /L*v C:MSDELog.log
On the same machine I has installed also SQLExpress.
I stop the sql services before issuing the command.
Any Ideas?