KeithMcElroy
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. CreatePara meter("uni que_key", adChar, adParamInput, Len(uniquekey), uniquekey))
set rs = Server.Createobject("ADODB .Recordset ")
rs.open cmd, , 0,2
for each field in oContentX.selectNodes("//u pdates/ele ment")
rs.Fields(field.getAttribu te("id")) = request(field.getAttribute ("id"))
next
rs.update
response.write "Record Accepted"
end if
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"))
set cmd = Server.createObject("ADODB
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.
set rs = Server.Createobject("ADODB
rs.open cmd, , 0,2
for each field in oContentX.selectNodes("//u
rs.Fields(field.getAttribu
next
rs.update
response.write "Record Accepted"
end if
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();
}
ASKER
Thank you. This looks maybe like C#?
Can I use this in classic ASP?
Can I use this in classic ASP?
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...
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.
Trying this in the code
for each cmd in cmd.Parameters
response.write cmd.???
next
Any tips appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. CreatePara meter("uni que_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. CreatePara meter("hr_ foreign.un ique_key", adChar, adParamInput, Len(uniquekey), uniquekey))
case else
cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
cmd.Parameters.Append(cmd. CreatePara meter("uni que_key", adChar, adParamInput, Len(uniquekey), uniquekey))
end select
rs.open cmd, , 0, 2
rs.movefirst
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.
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.
case else
cmd.CommandText = "SELECT * FROM pe_name_mstr WHERE unique_key=?"
cmd.Parameters.Append(cmd.
end select
rs.open cmd, , 0, 2
rs.movefirst
You don't seem to be setting uniquekey in your code?
Try replacing:
With
Try replacing:
cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), uniquekey))
With
cmd.Parameters.Append(cmd.CreateParameter("unique_key", adChar, adParamInput, Len(uniquekey), request("unique_key")))
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!
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!
ASKER
I did try the following to be sure...
cmd.Parameters.Append(cmd. CreatePara meter("uni que_key", adChar, adParamInput, Len(request("unique_key")) , Request("unique_key")))
same neg results
cmd.Parameters.Append(cmd.
same neg results
Yes, you did mention it, missed that... Hmm running out of ideas here, perhaps try:
After you open the rs?
rs.movefirst
After you open the rs?
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
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
Strange indeed, what happens when you run the "SELECT * FROM hr_foreign WHERE unique_key='VALIDUNIQUEKEY VAL'" from within SQL?
I have verfieid that there is a field unique_key in hr_foreign and even have aWhat is the data type of unique_key column?
similar page with a select from hr_foreign working
Also, try trimming the value in datapackage:
...
datapackage=Trim(datapackage)
select case datapackage
...
ASKER
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.