December 6, 2012 at 3:17 pm
Hi guys I am trying to update my Database column using the below, if the column is not present it get created fine, but wont set all the entries to false?
what am I doing wrong
command.CommandText = "ALTER TABLE MachinesTB ADD TouchScreen bit";
command.ExecuteNonQuery();
command.CommandText = @"UPDATE MachinesTB SET TouchScren ='False'";
command.ExecuteNonQuery();
December 6, 2012 at 3:52 pm
jerome.morris (12/6/2012)
Hi guys I am trying to update my Database column using the below, if the column is not present it get created fine, but wont set all the entries to false?what am I doing wrong
command.CommandText = "ALTER TABLE MachinesTB ADD TouchScreen bit";
command.ExecuteNonQuery();
command.CommandText = @"UPDATE MachinesTB SET TouchScren ='False'";
command.ExecuteNonQuery();
The column name is spelt wrong in the update, is that a typo or is it like that in the code?
btw, you should have a check to see if the column exists before creating it, otherwise you'll get an error if you run that twice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 4:14 am
That is totally embarrassing 🙁 sorry.
I do have a check like this:
try
{
command.CommandText = "ALTER TABLE MachinesTB ADD TouchScreen bit";
command.ExecuteNonQuery();
command.CommandText = @"UPDATE MachinesTB SET TouchScreen ='False'";
command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show(@"This column already exist, patch will continue");
}
Whenever I try to do create a stored procedure in the same was it fails with no errors but doesn't create the procedure. So from Sql Studio I create to clipboard then paste into my command text.
Procedure
USE [SLADB]
GO
/****** Object: StoredProcedure [dbo].[GetMachine] Script Date: 12/07/2012 11:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMachine]
--@mc1 nchar
@startd datetime,
@endd datetime
AS
; WITH OrderedData AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Dockets Desc),
Docket_Machine,
Dockets,
Pct_To_Total = CAST((Dockets*100.0)/SUM(Dockets) OVER(PARTITION BY 1) AS NUMERIC(5,2))
FROM (
SELECT
a1.Docket_Machine,
Dockets = COUNT(*)
FROM SLADB.dbo.DocketTB a1
Where Docket_Category IS NOT NULL AND (Docket_Status = 'CL') AND (Contract = '1')
AND (Docket_Date between @startd and @endd) --AND (Docket_Machine = @mc1)--(Docket_Machine ='APS_01')
GROUP BY a1.Docket_Machine
) d
), Calculator AS (
SELECT
rn, Docket_Machine, Dockets, Pct_To_Total,
RunningTotal = Pct_To_Total
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT
tr.rn, tr.Docket_Machine, tr.Dockets, tr.Pct_To_Total,
RunningTotal = CAST(lr.RunningTotal+tr.Pct_To_Total AS NUMERIC(5,2))
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.rn = lr.rn+1
) SELECT * FROM Calculator ORDER BY rn
GO
December 7, 2012 at 4:36 am
jerome.morris (12/7/2012)
That is totally embarrassing 🙁 sorry.I do have a check like this:
try
{
command.CommandText = "ALTER TABLE MachinesTB ADD TouchScreen bit";
command.ExecuteNonQuery();
command.CommandText = @"UPDATE MachinesTB SET TouchScreen ='False'";
command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show(@"This column already exist, patch will continue");
}
That's not a check, that's handling an error (badly). By check I mean checking to see if the column exists and if it does, not trying to create it.
Not sure what you're asking about that procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 5:20 am
Its the only way I know how at present, and it works. What is a better way
Thanks Gail
December 7, 2012 at 5:32 am
Firstly go and do some reading on .Net error handling and proper ways to handle errors. Your update could fail because the DB's unavailable and still you'd tell the user that 'nothing to worry about, the column's already there'. That's not handling errors, that's ignoring them.
As for checking a column's existence, the sys.columns view can be used, filter on the table and see if there's a column of the name that you're going to create.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 5:36 am
That was a snippet of code just around that method, I have error checking/ignoring method further up the code for connection. Although I really appreciate your help telling me to go read I have already done lots of this and I dislike having to ask for help when I hit a wall to be told Go read.
Thanks all the same.
Jay
December 7, 2012 at 6:11 am
Well I can't figure out what problem you have with the procedure and I gave you a suggestion on how to check that a column exists. If you can give more information about the problems you're having with the procedure, I'll try to help. If you want further guidance on checks for column existence, same.
If you don't want to handle errors better, your choice. As it is, that code is brittle, if the update deadlocks, or fails for any reason whatsoever, if the alter fails because the table's not there, because the database disappeared between the connection open check and now, etc, all the only message that the app gets back is 'This column already exist', which will make debugging any such problem incredibly difficult. I'm suggesting reading because I'm not a .net programmer myself and I don't have any references available on error handling
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 6:24 am
I understand debugging would be hard, but seeing as I am the only person doing the changes and ensure backups and such are carried out first before I deploy this to any user (which all DB are the same) I should be ok.
I get incorrect syntax near go or 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch
from error handling exception
try
{
command.CommandText =
@"
USE [SLADB]
CREATE PROCEDURE [dbo].[GetMachine]
--@mc1 nchar
@startd datetime,
@endd datetime
AS
; WITH OrderedData AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Dockets Desc),
Docket_Machine,
Dockets,
Pct_To_Total = CAST((Dockets*100.0)/SUM(Dockets) OVER(PARTITION BY 1) AS NUMERIC(5,2))
FROM (
SELECT
a1.Docket_Machine,
Dockets = COUNT(*)
FROM SLADB.dbo.DocketTB a1
Where Docket_Category IS NOT NULL AND (Docket_Status = 'CL') AND (Contract = '1')
AND (Docket_Date between @startd and @endd) --AND (Docket_Machine = @mc1)--(Docket_Machine ='APS_01')
GROUP BY a1.Docket_Machine
) d
), Calculator AS (
SELECT
rn, Docket_Machine, Dockets, Pct_To_Total,
RunningTotal = Pct_To_Total
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT
tr.rn, tr.Docket_Machine, tr.Dockets, tr.Pct_To_Total,
RunningTotal = CAST(lr.RunningTotal+tr.Pct_To_Total AS NUMERIC(5,2))
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.rn = lr.rn+1
) SELECT * FROM Calculator ORDER BY rn
";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Thank you for the advise
Jay
December 7, 2012 at 6:34 am
Remove the USE statement, as the error said, the CREATE must be the first statement in the batch.
When you set up the connection string and open the connection you can specify what database to connect to.
p.s. The WITH on a CTE does not start with a ;. The ; is a statement terminator (ends statements)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 6:41 am
Gail thank you, I feel like I move forward in leaps and bounds after mastering certain .net code that I once struggled with to realize that maybe I didn't move that far. My connection was already saying to use this table as you stated.
Another silly overlook on my part.
Have a great weekend and again thank you.
Jay
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply