Member Avatar for coder91

Hi I have the following code to insert data into a db

 int svcTypeID = Convert.ToInt32(comboBox1.SelectedValue);

                String svcGuid = label2.Text;

                int heartbeat = Convert.ToInt32(textBox1.Text);

                String hostname = System.Net.Dns.GetHostName();

                Guid gu = new Guid(svcGuid);
                SqlConnection conn = new SqlConnection(@"Data Source=x\x;Initial Catalog=task_system;Integrated Security=True");

                SqlCommand cmd = new SqlCommand("storedProc");
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@guid", gu);
                cmd.Parameters.AddWithValue("@id", svcTypeID);
                cmd.Parameters.AddWithValue("@heartbeat", heartbeat);
                cmd.Parameters.AddWithValue("@hostname", "'" + hostname + "'");
                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = "RETURN_VALUE",
                    Direction = ParameterDirection.ReturnValue
                });
                conn.Open();
                cmd.ExecuteNonQuery();

However the data will not insert, the stored procedure that I am using works in SQL when I execute it, so not sure what the issue is. The parts of the stored procedure relevant are:

@guid uniqueidentifier,
 @id int,
 @heartbeat int,
 @hostname nvarchar(MAX)

 IF @@ROWCOUNT < 1
BEGIN
  INSERT INTO [services]
             (guid,
              id,
              started_ts,
              last_contact_ts,
              heartbeat,
              hostname)
  VALUES     (@guid,
              @id,
              GETDATE(),
              GETDATE(),
              @heartbeat,
              @hostname)
END

When I run my code in visual studio it tells me the procedure or function expects parameter @guid which was not supplied, any ideas why it isnt getting the values i'm passing?

  • 2 Contributors
  • 2 Replies
  • 228 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by coder91

Dani AI

Generated

Good catch by — the root cause was that the command was being sent as plain text instead of a stored-procedure call, so the provider never bound your procedure parameters. Setting the command type to StoredProcedure makes ADO.NET call the proc and pass the parameters you added; that is why the error complained that @guid was not supplied.

A few extra notes to avoid future surprises:

  • Do not add manual single quotes around hostname. Parameters are passed as values, so "'"+hostname+"'" will store the literal quotes in the column and can mask problems.
  • Avoid relying on AddWithValue for everything; it infers types and sizes and can cause type-mismatch or performance issues. Prefer explicit parameters with the right SqlDbType and size (for example, use UniqueIdentifier for GUIDs and an appropriate NVARCHAR length for hostname).

For more robust, maintainable code use explicit parameters and dispose objects properly. Example parameter patterns (illustrative only — not the original code posted above):

var p = new SqlParameter("@hostname", SqlDbType.NVarChar, 255) { Value = hostname };
cmd.Parameters.Add(p);

var ret = new SqlParameter("@ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
cmd.Parameters.Add(ret);

Other practical tips: schema-qualify the proc name (e.g., dbo.YourProc) to avoid resolution surprises; validate the GUID with TryParse before creating one; wrap connection/command in using blocks; log SqlException details if something still fails. If you need to inspect what actually reaches SQL Server, use SQL Profiler or Extended Events to see the call and parameter values.

Recommended Answers

All 2 Replies

Member Avatar for djjeavons

Hi

You need to specify the type of command you are running. I believe the default is Text which is why you get the exception regarding the missing parameter. Add the following:

cmd.CommandType = CommandType.StoredProcedure;

HTH

Member Avatar for coder91

Thank you, this worked :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.