Solved

Updating a Record in a SQL DB from a C# Console

Posted on 2009-04-15
11
194 Views
Last Modified: 2013-12-17
Ok, I was just running some tests and the same record was added again.
I'm trying to figure out how the C# Code is supposed to know what it needs to do to check the ID on the database, look for that record, and if the record exists, then Update. So, it looks like my C# Code is trying to add an ID Value when all it should do is check for existance on DB.
Below is the code in C# and my Stored Procedure. I appreciate any help to remedy the problem code below.
Thanks Experts!
Wally
------------------ C# Code -------------------------

foreach (ManagementObject csObjQueried in csObjSearcher.Get())

                            {

                                try

                                {

                                    sqlCmd2.Parameters.Add("ID", System.Data.SqlDbType.Int).Value = "ID" ;

                                    sqlCmd2.Parameters.AddWithValue("WorkstationName", csObjQueried["Name"]);

                                    sqlCmd2.Parameters.AddWithValue("UserName", csObjQueried["UserName"]);

                                    sqlCmd2.Parameters.AddWithValue("Domain", csObjQueried["Domain"]);

                                    sqlCmd2.Parameters.AddWithValue("WkstnStatus", csObjQueried["Status"]);

                                    //sqlCmd2.Parameters.AddWithValue("WakeUpType", csObjQueried["WakeUpType"]);

                                }

                                catch (Exception ex)

                                {

                                    sw.WriteLine("No Win32_ComputerSystem WMI Data returned for " + client.Wkstn + ".\nError is " +

                                            ex.Message);

                                    sw.Flush();

                                }

                            }

-------------------------------- STORED PROCEDURE --------------------------------

USE WMIDATA

GO
 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		Wally Davis

-- Create date: April 13, 2009

-- Description:	

-- =============================================
 

ALTER PROCEDURE dbo.usp_InsertToWMIDATA

	-- Add the parameters for the stored procedure here

	@ID int,

	@WorkstationName varchar(15),

	@UserName varchar(15),

	@Domain varchar(50),

	@WkstnStatus varchar(10) = NULL,

	@MacAddress varchar(17)

AS

If @WorkstationName is not null

BEGIN

	INSERT INTO Main(WorkstationName, UserName, Domain, WkstnStatus, MacAddress)

	Values (@WorkstationName, @UserName, @Domain, @WkstnStatus, @MacAddress)

	SET @ID = SCOPE_IDENTITY()

END

ELSE

BEGIN

	UPDATE Main 

		SET WorkstationName = @WorkstationName, 

			UserName = @UserName,

			Domain = @Domain, 

			WkstnStatus = @WkstnStatus,

			MacAddress = @MacAddress

	WHERE ID = @ID

END

GO

Open in new window

0
Comment
Question by:wally_davis
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24150333
Why not modify your stored procedure to do the checking?
-- STORED PROCEDURE
 

alter procedure dbo.usp_InsertToWMIDATA

		@ID int,

		@WorkstationName varchar(15),

		@UserName varchar(15),

		@Domain varchar(50),

		@WkstnStatus varchar(10) = NULL,

		@MacAddress varchar(17)

AS

BEGIN

	IF EXISTS (SELECT COUNT(1) FROM Main WHERE ID = @ID)

		UPDATE Main

		SET WorkstationName = @WorkstationName,

		    UserName = @UserName,

		    Domain = @Domain,

		    WkstnStatus = @WkstnStatus,

		    MacAddress = @MacAddress

		WHERE ID = @ID

	ELSE

		INSERT INTO Main (WorkstationName, UserName, Domain, WkstnStatus, MacAddress)

		VALUES (@WorkstationName, @UserName, @Domain, @WkstnStatus, @MacAddress)

END
 
 

// C#
 

foreach (ManagementObject csObjQueried in csObjSearcher.Get())

{

    try

    {

        sqlCmd2.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = "ID" ;

        sqlCmd2.Parameters.AddWithValue("@WorkstationName", csObjQueried["Name"]);

        sqlCmd2.Parameters.AddWithValue("@UserName", csObjQueried["UserName"]);

        sqlCmd2.Parameters.AddWithValue("@Domain", csObjQueried["Domain"]);

        sqlCmd2.Parameters.AddWithValue("@WkstnStatus", csObjQueried["Status"]);

        

        sqlCmd2.CommandType = CommandType.StoredProcedure;

        sqlCmd2.ExecuteNonQuery("dbo.usp_InsertToWMIDATA");

    }

    catch (Exception ex)

    {

        sw.WriteLine("No Win32_ComputerSystem WMI Data returned for " + client.Wkstn + ".\nError is " +

                ex.Message);

        sw.Flush();

    }

}

Open in new window

0
 

Author Comment

by:wally_davis
ID: 24150546
After updating the code, I get this error "Failed to convert parameter value from a String to a Int32". That must have something to do with passing the ID to @ID as a parameter.
So, I shouldn't pass anything in really.......correct? and if that is correct, how do I adjust the code so that the SP also doesn't expect a Parameter.
0
 

Author Comment

by:wally_davis
ID: 24150567
Thinking about it again. ID should be = to something shouldn't it? So, if I pass in something to @ID, I think a call or some Select statement should be run to find the ID on the specific workstation before an update could be made.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24150754
I took your ID column to be an auto-increment field that would just add 1 every time a record is inserted. Is this correct? If not, what is the ID column for?
0
 

Author Comment

by:wally_davis
ID: 24150814
Correct, the ID Column is an Identity / PK Column. I understand how the Inserts are working, but, how is the actual C# Code going to hunt down the specific workstation client's Identity ID in order to know what record to update?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24150896
Other than ID, which fields are unique to a workstation? Workstation name I presume?
0
 

Author Comment

by:wally_davis
ID: 24151300
Correct, just the Workstation Name.
0
 

Accepted Solution

by:
wally_davis earned 0 total points
ID: 24162966
Expert never returned to answer my issue. If Expert doesn't respond in 48 hours please delete this question. I will ask this question again in another thread if no response.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24163052
My apologies. I was having a rough time figuring out how to set rules to move emails in my email client. I think EE's mail was a victim of this. Back to the issue...


I attached some modifications. Since workstation name is unique, we can identify a record in the database. The id column is auto-generated so we don't need to pass in anything for that.
-- STORED PROCEDURE

 

alter procedure dbo.usp_InsertToWMIDATA

                @WorkstationName varchar(15),

                @UserName varchar(15),

                @Domain varchar(50),

                @WkstnStatus varchar(10) = NULL,

                @MacAddress varchar(17)

AS

BEGIN

        IF EXISTS (SELECT COUNT(1) FROM Main WHERE WorkstationName = @WorkstationName)

                UPDATE Main

                SET UserName = @UserName,

                    Domain = @Domain,

                    WkstnStatus = @WkstnStatus,

                    MacAddress = @MacAddress

                WHERE  WorkstationName = @WorkstationName

        ELSE

                INSERT INTO Main (WorkstationName, UserName, Domain, WkstnStatus, MacAddress)

                VALUES (@WorkstationName, @UserName, @Domain, @WkstnStatus, @MacAddress)

END

 

 

// C#

 

foreach (ManagementObject csObjQueried in csObjSearcher.Get())

{

    try

    {

        sqlCmd2.Parameters.AddWithValue("@WorkstationName", csObjQueried["Name"]);

        sqlCmd2.Parameters.AddWithValue("@UserName", csObjQueried["UserName"]);

        sqlCmd2.Parameters.AddWithValue("@Domain", csObjQueried["Domain"]);

        sqlCmd2.Parameters.AddWithValue("@WkstnStatus", csObjQueried["Status"]);

        

        sqlCmd2.CommandType = CommandType.StoredProcedure;

        sqlCmd2.ExecuteNonQuery("dbo.usp_InsertToWMIDATA");

    }

    catch (Exception ex)

    {

        sw.WriteLine("No Win32_ComputerSystem WMI Data returned for " + client.Wkstn + ".\nError is " +

                ex.Message);

        sw.Flush();

    }

}

Open in new window

0
 

Author Comment

by:wally_davis
ID: 24169399
kaufmed. I tested it and this allowed Updates to work perfectly. I'll accept your solution but I really need you to answer this other question first with respect to the Workstation being unique. I have not added the "WorkstationName" column in my table to the Table Designer/Indexes...Keys/Column Property as a "Unique" column. Should I add that column as a Unique ID? I would imagine adding it would make sense but there may also be a reason not too. Thanks!
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 24171068
Adding a Unique constraint to your database column ensures that if you try to add a record with a particular Workstation Name, and that name already exists in a record, then you will get an error (which you can catch/handle) when the new record is attempted to be added. There's nothing saying you do or do not have to do it this way, but I would imagine having it might be more beneficial than not. Of course, there will be a little overhead in checking the table for uniqueness when you add records, but I would perceive it as negligible compared to database integrity.

I hope that made sense :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now