[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Debug asp sql Command

Posted on 2012-09-20
14
Medium Priority
?
467 Views
Last Modified: 2012-10-04
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
0
Comment
Question by:KeithMcElroy
  • 7
  • 6
14 Comments
 

Author Comment

by:KeithMcElroy
ID: 38417081
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.
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417086
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

0
 

Author Comment

by:KeithMcElroy
ID: 38417102
Thank you.  This looks maybe like C#?
Can I use this in classic ASP?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:tlayton
ID: 38417109
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...
0
 

Author Comment

by:KeithMcElroy
ID: 38417134
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.
0
 
LVL 5

Accepted Solution

by:
tlayton earned 2000 total points
ID: 38417147
Would be more like:

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

Open in new window

0
 

Author Comment

by:KeithMcElroy
ID: 38417160
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
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417169
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

0
 

Author Comment

by:KeithMcElroy
ID: 38417177
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!
0
 

Author Comment

by:KeithMcElroy
ID: 38417191
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
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417193
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?
0
 

Author Comment

by:KeithMcElroy
ID: 38417236
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
0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417301
Strange indeed, what happens when you run the "SELECT *  FROM  hr_foreign  WHERE unique_key='VALIDUNIQUEKEYVAL'" from within SQL?
0
 
LVL 82

Expert Comment

by:hielo
ID: 38427072
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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, 1 hour left to enroll

873 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