asked on

Debug asp sql Command

I am a little new to using the Command object. I need help adding response.writes to the code so I can narrow down to the point of failure.

The following code I wrote works flawlessly for case "pe-name-mstr", but fails for case hr_foreign. I have verfieid that there is a field unique_key in hr_foreign and even have a
similar page with a select from hr_foreign working. So, I need help debugging. How can I view the result of the command with the parameter included? I tried response.write cmd and that errs.

Any help appreciated. This is a real mystery!

if Len(request("unique_key")) > 10 and request("modeval") = "View Mode" then

set cmd = Server.createObject("ADODB.Command")
set cmd.ActiveConnection = con
cmd.CommandType = adCmdText

select case datapackage
      case "pe-name-mstr"
      cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
      case "hr_foreign"
      cmd.CommandText = "SELECT * FROM hr_foreign WHERE unique_key=?"
      case else
      cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
end select


cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))

set rs = Server.Createobject("ADODB.Recordset")
rs.open cmd, , 0,2

for each field in oContentX.selectNodes("//updates/element")
rs.Fields(field.getAttribute("id")) = request(field.getAttribute("id"))
next

rs.update

response.write "Record Accepted"



end if

KeithMcElroy

ASKER

I verified that the variable uniquekey is populated using
response.write "Record Accepted" & uniquekey
It is populated above this portion of the script as follows
uniquekey = request("unique_key")
I am going to try trimming it in case spacing is causing an issue.

tlayton

I use a method (follows below) which allows me to get the SQL Command Text from a Command object, I can then take that text and try and run it in SQL Server for a more meaningful answer:

    public string CommandAsSql(SqlCommand cmd)
    {
        StringBuilder _return = new StringBuilder();
        bool bFirstParam = true;

        //_return.AppendLine("use " & Convert.ToString(cmd.Connection.Database) & ";")
        switch (cmd.CommandType)
        {
            case CommandType.StoredProcedure:
                //_return.AppendLine("declare @return_value int;")

                foreach (SqlParameter _param in cmd.Parameters)
                {
                    if ((_param.Direction == ParameterDirection.InputOutput) || (_param.Direction == ParameterDirection.Output))
                    {
                        _return.Append("declare " + _param.ParameterName + " " + _param.SqlDbType.ToString() + " " + "= ");
                        _return.AppendLine(((_param.Direction == ParameterDirection.Output) ? "null" : ParameterValueForSQL(_param)) + ";");
                    }
                }

                _return.AppendLine("exec [" + Convert.ToString(cmd.CommandText) + "]");
                foreach (SqlParameter _param in cmd.Parameters)
                {
                    if (_param.Direction != ParameterDirection.ReturnValue)
                    {
                        _return.Append((bFirstParam) ? "\t" : "\t" + ", ");

                        if (bFirstParam)
                        {
                            bFirstParam = false;
                        }

                        if (_param.Direction == ParameterDirection.Input)
                        {
                            _return.AppendLine(_param.ParameterName + " = " + ParameterValueForSQL(_param));
                        }
                        else
                        {
                            _return.AppendLine(_param.ParameterName + " = " + _param.ParameterName + " output");
                        }
                    }
                }
                //_return.AppendLine(";")
                //_return.AppendLine("select 'Return Value' = convert(varchar, @return_value);")
                foreach (SqlParameter _param in cmd.Parameters)
                {
                    if ((_param.Direction == ParameterDirection.InputOutput) || (_param.Direction == ParameterDirection.Output))
                    {
                        _return.AppendLine(("select '" + _param.ParameterName + "' = convert(varchar, ") + _param.ParameterName + ");");
                    }
                }
                break;
            case CommandType.Text:
                _return.AppendLine(cmd.CommandText);
                break;
        }
        return _return.ToString();
    }

Open in new window

KeithMcElroy

ASKER

Thank you. This looks maybe like C#?
Can I use this in classic ASP?

tlayton

It is c# yes and works for a SqlCommand as opposed to your ADODB.Command, just posted it for reference, you should be able to refactor into something more useful for classic ASP...

KeithMcElroy

ASKER

I think I sort of get it.
Trying this in the code

                  for each cmd in cmd.Parameters
                        response.write cmd.???
                  next
Any tips appreciated.

ASKER CERTIFIED SOLUTION

tlayton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KeithMcElroy

ASKER

The following hr_foreign works in another asp page.
There is a join to another table.

      cmd.CommandType = adCmdText
      select case datapackage
      case "pe-name-mstr"
            cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
            cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))

      case "hr_foreign"
            cmd.CommandText = " SELECT * FROM hr_foreign " & _
                        " JOIN hr_pe_mstr ON hr_foreign.id = hr_pe_mstr.hr_pe_id " & _
                        " WHERE hr_foreign.unique_key=?"
            cmd.Parameters.Append(cmd.CreateParameter("hr_foreign.unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))

      case else
            cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
            cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))
      end select

      rs.open cmd, , 0, 2
      rs.movefirst

tlayton

You don't seem to be setting uniquekey in your code?

Try replacing:

cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))

Open in new window

With

cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), request("unique_key")))

Open in new window

KeithMcElroy

ASKER

1. I do set uniquekey. see second comment above

2. The following

for each param in cmd.Parameters
response.write param.Name & ": " & param.Value & "<br>"
next

successfully returned the unique key. Just had to change spelling of Parameters and it worked
I think that verifies the Parameter is set correct.

So, mystery is why does the rs not get instantiated .
Arg!

KeithMcElroy

ASKER

I did try the following to be sure...
cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(request("unique_key")), Request("unique_key")))

same neg results

tlayton

Yes, you did mention it, missed that... Hmm running out of ideas here, perhaps try:

 rs.movefirst

Open in new window

After you open the rs?

KeithMcElroy

ASKER

Very odd thing, the following works
case "hr_foreign"
cmd.CommandText = "SELECT * FROM hr_foreign WHERE id='TEST00001'"

I am using another field, id, which would work in this case but it does not makes sense that unique_key should not work.
I am going to retrofit the code to use id.
Does not clear up the mystery though

tlayton

Strange indeed, what happens when you run the "SELECT * FROM hr_foreign WHERE unique_key='VALIDUNIQUEKEYVAL'" from within SQL?

hielo

I have verfieid that there is a field unique_key in hr_foreign and even have a
similar page with a select from hr_foreign working

What is the data type of unique_key column?

Also, try trimming the value in datapackage:

...
datapackage=Trim(datapackage)
select case datapackage
...

Open in new window