Sql Statement


#1

Here is my code:

SQL = "SELECT SUM (Credits)[AmtPaid] from Table1 where date > dateadd(day,-385,getdate()) and customerid = '200'"
if AmtPaid > 100 then
response.Write "greater than 100"
else
response.Write "Amt is less than 100"
end if

This is the table view of the code:
sitepoint

It should say “greater than100”. not less than 100.

So can anybody tell me where I am going wrong?

Thank you.


#2

not a database question – try javascript


#3

Actually it looks like ASP to me…

@javascript7 what language are you writing this in? How are you executing this code?


#4

Yes, you are right. I am executing the code with ASP.


#6

It looks like the code for showing the “AmtPaid 110” table is missing from the code snippet you posted.

My guess is the value the AmtPaid variable is changing somewhere, but without seeing more of the code that’s only a guess.

What do you see with this

if AmtPaid > 100 then
response.Write AmtPaid & " is greater than 100"
else
response.Write AmtPaid & " is less than 100"
end if

* note, as the code stands, when AmtPaid is equal to 100 it will write less than 100


#7

That’s the SQL Statement and then standard ASP code that writes the html table, but it doesn’t affect the outcome:

SQL = "SELECT SUM (Credits)[AmtPaid] from Table1 where date > dateadd(day,-385,getdate()) and customerid = '200'"

Yes, I am aware that 100 exactly would produce that result, thank you though for pointing that out.


#8

Sorry, missed this part in my last reply.

When using your response.write code I only get:

“is less than 100”

So the amount is not showing - therefore, the response would be correct in that it is < 100.

So the sql is right in pulling up the 110, just not sure in how to do the if/then.

Any thoughts would be appreciated.

Thanks


#9

That is VB.Net code. There is no semicolon at the end and no parentheses around the condition (in the if statement) and you have “end if” at the end.

How do you execute the SQL? If that is all the code then you are not executing the SQL.

Are you using Visual Studio? If so then do you know how to use the debugger? Something easy to do is to use Debug.WriteLine to write data to the Output window. You need to learn how to set breakpoints so you can see what is actually happening. Note that to debug you might need to execute VS as an Administrator, but I might be wrong about use of the VS server. I have been using IIS for the server so I might have forgotten what happens when debugging using the VS server (I think it is IIS Express). You can use the debugger to confirm that the SQL does not have any affect on anything.

Also, I don’t know how VB.Net is converted to a link. I did not do that.


#10

It is ASP code. You don’t need a semicolon or quotes around end if.

The code is executed on the server from the database.
Customerid paid 110 less than 90 days ago.

SQL = "SELECT case when SUM (Credits)>=110 then 'freegift' end as [AmtPaid] from Table1 where date > dateadd(day,-385,getdate()) and customerid = '1606'"
if AmtPaid = false then
response.Write "false"
end if

So the SQL Statement should be true but response.writes false regardless of the amount I use. So somewhere I am off on my statement. Just can’t figure it out.


#11

ASP is not a language. I don’t know how to emphasize that except to say you will continue to fail if you think that ASP is a language. See If…Then…Else Statement (Visual Basic); it documents that VB.Net statement. It is VB.Net, not ASP!

I am asking how you execute the code on the server. What line of code are you using? I don’t know how to make that more clear except to say you will never execute SQL simply by putting SQL code in a variable called SQL.

If you can link to a Microsoft documentation page of the library you are using to execute the SQL then someone can help you with that. Otherwise we don’t know what to say. My suggestion is that you use Entity Framework as in Overview - EF6. It takes time to learn but it is the professional solution and will save time after you learn it. Entity Framework Overview appears to be a useful introduction too.

Perhaps if you told us what you are using to learn from then we can help you understand what you need to do.


#13

I understand that VB is the language.

I have 10 websites that run on asp. But I don’t know how to bring the asp code to the editor - very simple. I probably don’t have the expertise that you do. I am just looking for some help to coordinate the database field with the editor.

I don’t need the lectures from you!

Thanks, and I hope that I can gain some assistance. I mean well, not sure if you do!


#14

I could help you but you seem to not want my help. You did not describe the problem very accurately or clearly and when I asked for clarification you replied as if you know more than I do. I had to lecture to get you to say:

That is much more useful. That is a problem that can be solved and needs to be solved.

I am not sure what:

means. I apologize for sounding arrogant but it was my hope you would describe the problem well enough to get help.

There are two problems here. One problem is how to edit the websites using Visual Studio. I assume you want to do that but you have not said so. The other problem is how to execute the SQL and get the results that you need. I think it would be better to create a new question for the first problem. For executing the SQL I think you still have not provided all the relevant code.