Solved

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

Posted on 2009-04-14
10
1,163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

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
 
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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 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