Adhoc,prepared,dynamic,what else...

I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?
Hi Hassan
"Hassan" wrote:

> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
sql

Adhoc,prepared,dynamic,what else...

I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?Hi Hassan
"Hassan" wrote:

> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John

Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

Adding the sum of column to use as alias

Chumley,
I've double-checked the syntax of the statements I posted, and they're OK.
Have you altered it them any way? Post the exact statement you're executing
and I'll have a look.
Also, which version of SQL Server are you working in?
Thanks
Damien
"Chumley Walrus" wrote:

> Damien, I now get an "Invalid syntax near SUM " error from the sql
> string you have outlined (pointing to the HAVING SUM line). I don't
> understand,a s I know there's data in there meeting this criteria.
>Right,
I'll spare you the 'dynamic sql is bad' stuff, because if you read this
group regularly, you already know.
You are missing a plus sign after your GROUP BY clause, and you cannot use
an alias in your HAVING. In the SQL I posted for you, I put:
SELECT salesperson, SUM( saleamount ) AS allsales
FROM #transactions
--WHERE thedate In ('20050106', '20050206')
GROUP BY salesperson
HAVING SUM( saleamount ) > 0
So you can see, you don't use 'allsales', you use SUM ( saleamount ).
Being rude to Joe is not going to help you. He's earned his right to make
comments like that be being one of the leading authorities in SQL in the
entire world. Even if you don't agree with his point of view, you've at
least got to respect it.
Let me know how you get on with that SQL.
Damien
"Chumley Walrus" wrote:

> I have inner joins (they all work, as the various IDs are related, and
> do fine in my main sql string), I would post the DDL, but I know the
> datatypes are absolutely accurate (saleamount is a money datatype)
> SELECT ticket.Salesperson_ID, employ.LName + ', ' + tblSalesRep.FName
> AS Salesperson, " +
> "ticket.ID, employ.ID, " +
> "transaction.thedate, " +
> "SUM(transactions.saleamount) AS allsales,
> transactions.ticket_ID,transactions.thedate " +
> "FROM ticket " +
> "INNER JOIN employ ON ticket.Salesperson_ID = employ.ID " +
> "INNER JOIN transactions ON ticket.ID=transactions.ticket_ID " +
> "WHERE transactions.theddate IN ('6/1/2005', '6/2/2005')" +
> "GROUP BY Salesperson"
> "HAVING SUM(allsales) > 0 ";
> once again, I get an "Invalid syntax error by SUM" on HAVING
> SUM(allsales) line.
> '
> chumley
>

Adding Roles/Users using SQL Procedures...

Hi everyone out there in ASP.NET land.

Have a quick question...

How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?

Thanks ahead of time,
DenvasCheck in this link a couple of PageDowns in the sectionCreate a New Login

The Sql syntax for adding a role member:

EXEC sp_addrolemember N'db_owner', N'SomeUserName'

|||Thank you so much for the info. Going to apply it tomorrow.

-Denvas

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1