Solved

C# Console app error --> The parameter data type of UInt16 is invalid

Posted on 2009-04-14
10
1,097 Views
Last Modified: 2013-12-17
I'm getting the error "The parameter data type of UInt16 is invalid" when VS 2008 Runtime reaches the statement "sqlCmd2.ExecuteNonQuery();" in my C# code below. When I go to look in my Locals at the bottom of VS, a Property named "ParamName" is equal to NULL. So, that didn't help. I've looked high and low, changed the SP up some and still I get this error. I just need another set of eyes to help. I've pasted the C# and SP Code below.
Thanks Experts,
Wally
------------------------- C# CODE -------------------------------------------

using System;

using System.Collections.Generic;

using System.Linq;

using System.IO;

using System.Management;

using System.Data.SqlClient;
 

namespace CollectEPData

{

    class Program

    {

        public string wkstn;

        public string Wkstn

        {

            get { return wkstn; }

            set { wkstn = value; }

        }
 

        static void Main()

        {

            string filename = @"C:\CollectEPData.txt";

            StreamWriter sw = new StreamWriter(filename, false);

 

            // Create WrkstnList collection using List Class

            List<Program> WrkstnList = new List<Program>();
 

            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString2);

            try

            {

                conn.Open();
 

                string sqlStr = "SELECT Name FROM dbo.Nodes";

                SqlCommand sqlCmd = new SqlCommand(sqlStr, conn);

                sqlCmd.CommandType = System.Data.CommandType.Text;
 

                SqlDataReader rdr = null;

                rdr = sqlCmd.ExecuteReader();
 

                // Let's check to make sure data

                // is in the database "main" table

                if (rdr.HasRows)

                {

                    while (rdr.Read())

                    {

                        Program client = new Program();

                        client.Wkstn = rdr.GetString(0);

                        WrkstnList.Add(client);                       

                    }
 

                    rdr.Close();

                }
 

                conn.Close();
 

                if (WrkstnList.Count > 0 )

                {
 

                    // For each client in the WrkstnList collection,

                    // lets connect to it and retrieve WMI data, and then

                    // store in parameters list to send and update DB.

                    foreach (Program client in WrkstnList)

                    {

                        // authentication settings needed for WMI Connection

                        // Impersonate - uses the credentials of the caller.

                        // Packet - packet level COM authentication

                        ConnectionOptions mgmtOptions = new ConnectionOptions();

                        mgmtOptions.Impersonation = ImpersonationLevel.Impersonate;

                        mgmtOptions.Authentication = AuthenticationLevel.Packet;

                        mgmtOptions.EnablePrivileges = true;

                        mgmtOptions.Username = "Domain\Username";

                        mgmtOptions.Password = "password";
 

                        /* computer path and connection authentication

                         * options passed to th ManagementScope object

                         * named "scope" which then uses the connect

                         * method to pass in parameters and connect to

                         * the computers WMI Scope.

                         */

                        ManagementScope scope = new ManagementScope("\\\\" + client.Wkstn + "\\root\\cimV2", mgmtOptions);

                        scope.Connect();
 

                        // Management WMI Query to return the specific Hardware data 

                        // using Win32 API Calls.

                      ObjectQuery queryCS = new ObjectQuery("SELECT * FROM Win32_ComputerSystem");

                      ObjectQuery queryNA = new ObjectQuery("SELECT * FROM Win32_NetworkAdapter WHERE DeviceID = 1");
 

                        // ManagementObjectSearcher - retrieves a collection of management objects

                        // based on the specified query.

                        ManagementObjectSearcher csObjSearcher = new ManagementObjectSearcher(scope, queryCS);

                        ManagementObjectSearcher naObjSearcher = new ManagementObjectSearcher(scope, queryNA);
 

                        SqlConnection conn2 = new SqlConnection(SettingsManager.ConnectionString1);

                        SqlCommand sqlCmd2 = new SqlCommand("usp_InsertToPCDATA", conn2);

                        sqlCmd2.CommandType = System.Data.CommandType.StoredProcedure;
 

                            // Invokes the specificed WMI Query and returns the resulting

                            // collection. ManagementObject - represents a WMI 

                            // "Win32_ComputerSystem" Instance

                            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("WakeUpType", csObjQueried["WakeUpType"]);

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

                                }

                                catch (Exception ex)

                                {

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

                                            ex.Message);

                                    sw.Flush();

                                }

                            }
 

                            // Invokes the specificed WMI Query and returns the resulting

                            // collection. ManagementObject - represents a WMI 

                            // "Win32_NetworkAdapter" Instance

                            foreach (ManagementObject naObjQueried in naObjSearcher.Get())

                            {

                                try

                                {

                                    sqlCmd2.Parameters.AddWithValue("MAC Address: {0}", naObjQueried["MACAddress"]);

                                }

                                catch (Exception ex)

                                {

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

                                            ex.Message);

                                    sw.Flush();

                                }

                            }
 

                            //Open a DB connection...send the data and close it

                            try

                            {

                                conn2.Open();

                                sqlCmd2.ExecuteNonQuery();

                            }

                            catch (Exception ex)

                            {

                                sw.WriteLine("Couldn't open connection to update\n WMI information to the Database.\nError message: "

                                                + ex.Message);

                                sw.Flush();

                            }

                            finally

                            {

                                conn.Close();

                            }

                    }

                }

            }

            catch

            {

                Console.WriteLine("Database connection failed.");

            }

            sw.Close();

            Console.ReadLine();

        }

    }

}

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

USE [PCDATA]

GO
 

/****** Object:  StoredProcedure [dbo].[usp_InsertToPCDATA]    Script Date: 04/14/2009 09:25:46 ******/

SET ANSI_NULLS ON

GO
 

SET QUOTED_IDENTIFIER ON

GO
 

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

-- Author:		Wally Davis

-- Create date: April 13, 2009

-- Description:	

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

CREATE PROCEDURE [dbo].[usp_InsertToPCDATA]

	-- Add the parameters for the stored procedure here

	--@ID int OUTPUT,

	@WorkstationName varchar(15),

	@UserName varchar(15),

	@Domain varchar(50),

	@WakeUpType tinyint = NULL,

	@MacAddress varchar(17),

	@WkstnStatus varchar(10) = NULL

AS

If @WorkstationName is not null

BEGIN

	INSERT INTO Main(WorkstationName, UserName, Domain, WakeUpType, MacAdress, WkstnStatus)

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

	--SET @ID = SCOPE_IDENTITY()

END

ELSE

BEGIN

	UPDATE Main 

		SET WorkstationName = @WorkstationName, 

			UserName = @UserName,

			Domain = @Domain, 

			WakeUpType = @WakeUpType,

			MacAdress = @MacAddress, 

			WkstnStatus = @WkstnStatus

	--WHERE PcDataID = @ID

END
 

GO

Open in new window

0
Comment
Question by:wally_davis
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 24140064
first change your lines
       sqlCmd2.Parameters.AddWithValue("WorkstationName", csObjQueried["Name"]);
                                    sqlCmd2.Parameters.AddWithValue("UserName", csObjQueried["UserName"]);
                                    sqlCmd2.Parameters.AddWithValue("Domain", csObjQueried["Domain"]);
                                    sqlCmd2.Parameters.AddWithValue("WakeUpType", csObjQueried["WakeUpType"]);
                                    sqlCmd2.Parameters.AddWithValue("Status", csObjQueried["Status"]);

to :


       sqlCmd2.Parameters.AddWithValue("@WorkstationName", csObjQueried["Name"]);
                                    sqlCmd2.Parameters.AddWithValue("@UserName", csObjQueried["UserName"]);
                                    sqlCmd2.Parameters.AddWithValue("@Domain", csObjQueried["Domain"]);
                                    sqlCmd2.Parameters.AddWithValue("@WakeUpType", csObjQueried["WakeUpType"]);
                                    sqlCmd2.Parameters.AddWithValue("@Status", csObjQueried["Status"]);


and try again....
0
 

Author Comment

by:wally_davis
ID: 24140126
Same exact error.
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 24140194
have you tested the value of csObjQueried["Name"]  before adding as a parameter to the procedure?
0
 

Author Comment

by:wally_davis
ID: 24140273
I've actually added values using the "Execute Stored Procedure" in SSMS and it worked just fine.
I step through the code and can see the value of "csObjQueried["Name"]" in the Locals at the bottom of VS and there is an actual workstation value passed in.
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 24140365
Use sql profiler to test what batch really executes. It will help you a bit
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 6

Expert Comment

by:SaedSalman
ID: 24143063
I would do it this way:

SqlParameter x = new SqlParameter();
                    x.DbType = DbType.String;  //set the right dbType
                    x.ParameterName = "Par";   // parameter name
                    x.Value = "value";          // set the right value

                    sqlCmd2.Parameters.Add(x);
                    sqlCmd2.ExecuteNonQuery();


hope this helps
0
 
LVL 6

Expert Comment

by:SaedSalman
ID: 24143097
Sorry, CommandText missed, here is it:

SqlParameter x = new SqlParameter();
                    x.DbType = DbType.String;  //set the right dbType
                    x.ParameterName = "Par";   // parameter name
                    x.Value = "value";          // set the right value

                    sqlCmd2.CommandText = "SELECT * FROM table WHERE something=:Par"; // precede parameterName with callin ':' as shown
                    sqlCmd2.Parameters.Add(x);
                    sqlCmd2.ExecuteNonQuery();


0
 
LVL 18

Accepted Solution

by:
philipjonathan earned 500 total points
ID: 24144251
Line #125, shouldn't it be:
sqlCmd2.Parameters.AddWithValue("MacAddress", naObjQueried["MACAddress"]);

And shouldn't line #108 be added after line #125, following the order of the parameters in SP?
0
 

Author Closing Comment

by:wally_davis
ID: 31569996
Thanks fo rthe correction philipjonathan. There was also an issue with the WMI Datatype for the Property "WakeUpType". I mean, the DbType when the Property was added as a parameter was the error itself --> "The parameter data type of UInt16 is invalid". This is the strangest thing I've come across in a while.
0
 
LVL 18

Expert Comment

by:philipjonathan
ID: 24153797
Thanks for the accept wally_davis, I hope you don't mind my pasting your comment here, for reference for anyone who might read this q in the future. I'm not sure about the DbType problem, but I suppose you have solved that, and let the matter rest.

>Thanks fo rthe correction philipjonathan. There was also an issue with the WMI Datatype for the Property "WakeUpType". I mean, the DbType when the Property was added as a parameter was the error itself --> "The parameter data type of UInt16 is invalid". This is the strangest thing I've come across in a while.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

14 Experts available now in Live!

Get 1:1 Help Now