Solved

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

Posted on 2009-04-14
10
1,120 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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

831 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