Link Aspnet_User table with another .

Hi, is correct add a relation to Asp.net_User to one another table(example orders)

With column relactioned the table users with the table Orders , UserId , UserName ,

I am a little confused I need help!. Thank you

Are you seeking for PRIMARY/FOREIGN KEY between database tables?

Foreign Key Constraints:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvovrforeignkeyconstraints.asp

Primary Key Constraints:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvovrprimarykeyconstraints.asp

link

can anybody tell me how to connect asp.net with sql server? any coding can do?Here is a link to a tutorial on data access from ASP.NET.

Line 1: Incorrect Syntax Near.......(is this a bug?)

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect Syntax Near.......

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Limited user

I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?

(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)

Thanks in advance for any advice! :-)

I usually create a SQL Server login that only has Execute permission on the stored procedures that are needed to run the site. That way, they have no other permissions as far as SQL Server is concerned, which means that you are largely protected against SQL Injection through the stored procedures, and also by the fact that the user has no permissions to do anything but fire the stored procedures you allow them to execute.

There is no SQL in the app itself. It's restricted purely to stored procs.

|||

Thanks for the reply! Are there specific stored procedures that a typical user must have execute permission for or do they need to have permission to execute all of them? I'm really concerned about security so I'd like to keep things as limited as possible without removing functionality or causing errors.

|||

Since you are only likely to create stored procs in response to the needs of the site, the user will need execute permission on all of them. If you create a stored proc that returns data to the page but don't give the user permssions on it, your site will fail.

DO NOT grant the user blanket EXECUTE. Grant execute on each proc individually eg

CREATE PROCEDURE MyProc

AS
BEGIN
SELECT field 1 FROM table1
END
GO
Grant Execute On MyProc to MyUser
GO

You can almost think of it as the Page is the user. The page needs to be able to call certain procs in order to display/insert/edit data. So you give it permission to run the procs.

Limitation on Conn.Execute in classic .ASP & ADO with SQL Server

Hello,

I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:

conn.execute(strSQL)

I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.

My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.

Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?

Please let me know. Thanks in advance.It's probably your data provider that is limiting you. Which one are you using, OleDb for SQL Server (SQLOLEDB)? The batch separator for that provider is a semicolon (;) as far as I know. Maybe you'd have better luck with that? I am doubting it but it would be worth a try.

Terri|||I am using ADO thru an ASP page to connect to SQL Server. I have tried numerous separator's (comma,vertibar) with still no luck. That's why I ended up going with the chr(30).

Doing the loop and execute.conn every 50 times through the loop has seemed to definately fix the bug. I am still just curious as to why I couldn't execute the entire command immediately.

What is strange is that I can use ADO and write a general Visual Basic 6.0 application and execute the sql string just fine. Considering ADO is used in both the VB6.0 and .ASP applications you would think it would work?

Oh well, thanks for the thought. Have a good one.|||But which PROVIDER are you using? Are you using the same provider in your VB program that you are in your ASP application (ie, what does your connection string look like for both)? Is the same MDAC version on both machines?

Terri|||Terri,

Yes, MDAC 2.8 is on the web server using the .ASP & ADO connection. However, the VB Project I've created to test out this large sql string is only on my local machine. It does not use a web server or anything like that.|||Sorry forgot to mention that the execute statement is the same in both.

Execute.Conn sqlstr

Where "sqlstr" is representing the hundreds and hundreds of insert statements I need to execute.|||But which PROVIDER are you using? What does the Conn.ConnectionString look like?

Terri|||the connection string looks like:

"DRIVER=SQL Server;SERVER=ServerName;User ID=UserID;PASSWORD=PASSWORD;DATABASE=Database"

I hope this is what you are referring too. Sorry for the confusion.|||You're using the ODBC provider. Use the OleDB provider instead and see if it makes a difference, with semicolons (;) separating your commands.

Your connection string would look like this:

"Provider=sqloledb;Data Source=ServerName;Initial Catalog=Database;User Id=UserID;Password=PASSWORD;"

Seehttp://www.connectionstrings.com for more help on connection strings.

Terri

Limitation of 1023 Characters

I am using ASP to query a SQL Server 2000 database.
Whenever I try to insert large pieces of data (column is
declared as varchar), the data allowed is limited to 1023
characters. I have installed SP3, and still the same
error. It is not down to any syntax in the cell as trying
to insert 1024 instances of "b" (bbbbb etc. etc.) will
error on the insertion of the 1024th. I am sure this must
be a configuration conflict somewhere.
Anybody out there able to help me rectify this?
Many thanks
Martin Burford> Whenever I try to insert large pieces of data (column is
> declared as varchar),
VARCHAR(what?)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Sorry. Varchar(4000) in this case.
Martin
>--Original Message--
>> Whenever I try to insert large pieces of data (column
is
>> declared as varchar),
>VARCHAR(what?)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||All right, so can you show the statement that ASP is trying to run (e.g. if
you are using conn.execute sqlStatement, use response.write sqlStatement
instead, and show us the result).
Also, the exact error message might be helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> Sorry. Varchar(4000) in this case.
> Martin
> >--Original Message--
> >> Whenever I try to insert large pieces of data (column
> is
> >> declared as varchar),
> >
> >VARCHAR(what?)
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >.
> >|||I think I have actually determined the error. I was
entering my data through Enterprise Manager, which I'm
guessing can't accept more than a byte of info (1,024
chars a bit of a coincedence). Entering the data through
INSERT statements in Query Analyzer works upto 4000 chars
which is fine. This however causes a slight problem...is
there an easier way rather than creating custom insert
statements for each record? Otherwise, I'll just create a
basic admin area interface, and insert the code through
submitted form values (textareas).
Many thanks
Martin Burford
>--Original Message--
>All right, so can you show the statement that ASP is
trying to run (e.g. if
>you are using conn.execute sqlStatement, use
response.write sqlStatement
>instead, and show us the result).
>Also, the exact error message might be helpful.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
>> Sorry. Varchar(4000) in this case.
>> Martin
>> >--Original Message--
>> >> Whenever I try to insert large pieces of data
(column
>> is
>> >> declared as varchar),
>> >
>> >VARCHAR(what?)
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >.
>> >
>
>.
>|||> I think I have actually determined the error. I was
> entering my data through Enterprise Manager,
Don't do that! Enterprise Manager should be used for management, not data
entry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||http://www.aspfaq.com/search.asp?q=enterprise+manager+1023+characters
:-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Martin Burford" <martin_burford@.hotmail.com> wrote in message
news:065101c3c314$97ee3980$a401280a@.phx.gbl...
> I think I have actually determined the error. I was
> entering my data through Enterprise Manager, which I'm
> guessing can't accept more than a byte of info (1,024
> chars a bit of a coincedence). Entering the data through
> INSERT statements in Query Analyzer works upto 4000 chars
> which is fine. This however causes a slight problem...is
> there an easier way rather than creating custom insert
> statements for each record? Otherwise, I'll just create a
> basic admin area interface, and insert the code through
> submitted form values (textareas).
> Many thanks
> Martin Burford
> >--Original Message--
> >All right, so can you show the statement that ASP is
> trying to run (e.g. if
> >you are using conn.execute sqlStatement, use
> response.write sqlStatement
> >instead, and show us the result).
> >
> >Also, the exact error message might be helpful.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> >> Sorry. Varchar(4000) in this case.
> >>
> >> Martin
> >>
> >> >--Original Message--
> >> >> Whenever I try to insert large pieces of data
> (column
> >> is
> >> >> declared as varchar),
> >> >
> >> >VARCHAR(what?)
> >> >
> >> >--
> >> >Aaron Bertrand
> >> >SQL Server MVP
> >> >http://www.aspfaq.com/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Limit the number of records returned in Stored procedure.

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.

Query

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

Select the top 100 records:

SELECT TOP 100 *

Select the top 10% of the total number of records:

SELECT TOP 10 percent *

|||

Or if you want to retrieve a limited number of records, but not necessarily the top X records, you could use the row_number() function. For example, to retrieve records 250-299 you could try

 
1SELECT*
2FROM3(
4SELECT *, row = row_number()OVER (ORDER BY id)
6FROM yourtable
7) a
8WHERE rowBETWEEN 250AND 299
 

Limit query results

Hello, I'm using MSSql and ASP
I need to create an ASP page with record pagination
I'd like to know which is the best technique to get the best performance
The ideal way I think should be something like the LIMIT clause for Mysql
In MSSql there is the TOP clause to get the first top n records but it's not
the same as LIMIT
Is there any other way?
I'm searching for an asp pagination class also, but I can't find it,
do you guys have one? or can you point me to any website?
ThanksCheck out this site for a number of approaches...
http://www.aspfaq.com/show.asp?id=2120
"Deniz" <dzoddi@.mvmnet.com> wrote in message
news:eXAOsi6QGHA.4608@.tk2msftngp13.phx.gbl...
> Hello, I'm using MSSql and ASP
> I need to create an ASP page with record pagination
> I'd like to know which is the best technique to get the best performance
> The ideal way I think should be something like the LIMIT clause for Mysql
> In MSSql there is the TOP clause to get the first top n records but it's
not
> the same as LIMIT
> Is there any other way?
> I'm searching for an asp pagination class also, but I can't find it,
> do you guys have one? or can you point me to any website?
> Thanks
>
>