| |

VerySource

 Forgot password?
 Register
Search
View: 6|Reply: 6

Please help to see if this is using .net to access the stored procedure, but don't know what's wrong there

[Copy link]

1

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

Post time: 2020-3-22 14:30:01
| Show all posts |Read mode
SqlConnection dbConn = Utility.OpenConnection ();
            SqlDataReader dbReader;
            SqlCommand dbComm = new SqlCommand ();
            SqlParameter dbParameter;

            dbComm.CommandText = "List";
            dbComm.Connection = dbConn;
            dbComm.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter ("@ RecordCount", SqlDbType.Int, 4);
            dbParameter.Direction = ParameterDirection.Output;
            dbComm.Parameters.Add (dbParameter);

            dbParameter = new SqlParameter ("@ PageCount", SqlDbType.Int, 4);
            dbParameter.Direction = ParameterDirection.Output;
            dbComm.Parameters.Add (dbParameter);

            dbParameter = new SqlParameter ("@ PageIndex", SqlDbType.Int, 4);
            dbParameter.Direction = ParameterDirection.Input;
            dbParameter.Value = Pageindex;
            dbComm.Parameters.Add (dbParameter);

            dbParameter = new SqlParameter ("@ PageSize", SqlDbType.Int, 4);
            dbParameter.Direction = ParameterDirection.Input;
            dbParameter.Value = Pagesize;
            dbComm.Parameters.Add (dbParameter);

            dbReader = dbComm.ExecuteReader ();
// The above sentence made an error and said [SqlException (0x80131904): The object name 'LOG_User' is invalid. ]
Reply

Use magic Report

0

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

Post time: 2020-6-30 14:45:01
| Show all posts
Not a C# statement error
Check your stored procedure
Instead of ExecuteReader()
Did you write "LOG_User" or something in the stored procedure?
Reply

Use magic Report

1

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

 Author| Post time: 2020-6-30 17:00:02
| Show all posts
CREATE PROCEDURE List
@PageIndex INT, --Enter the current page number
@PageSize INT, --Number of records per page
@RecordCount INT OUT, --Number of records
@PageCount INT OUT - number of pages

AS

SELECT @RecordCount = COUNT(*) FROM BLOG_Info INNER JOIN LOG_User ON BLOG_Info.uid = BLOG_User.uid
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1 - first page

set @SQLSTR = "select top "+STR( @PageSize )+" BLOG_Info.title, BLOG_Info.detail, BLOG_User.uname, BLOG_Info.dtime, BLOG_User.url, BLOG_Info.iid FROM BLOG_Info INNER JOIN BLOG_User ON BLOG_Info.uid = BLOG_User .uid order by BLOG_Info.dtime desc"

else if @PageIndex = @PageCount-1 - last page

set @SQLSTR = "select top "+STR( @PageSize )+"BLOG_Info.title, BLOG_Info.detail, BLOG_User.uname, BLOG_Info.dtime, BLOG_User.url, BLOG_Info.iid FROM BLOG_Info INNER JOIN BLOG_User ON BLOG_Info.uid = BLOG_User .uid where BLOG_Info.iid>(select max(BLOG_Info.iid) from (select top "+STR(@PageSize * @PageIndex )+" BLOG_Info.title, BLOG_Info.detail, BLOG_User.uname, BLOG_Info.dtime, BLOG_User.url , BLOG_Info.iid FROM BLOG_Info INNER JOIN BLOG_User ON BLOG_Info.uid = BLOG_User.uid order by BLOG_Info.dtime desc )) order by BLOG_Info.dtime desc"

else
set @SQLSTR = "select top "+STR( @PageSize )+"BLOG_Info.title, BLOG_Info.detail, BLOG_User.uname, BLOG_Info.dtime, BLOG_User.url, BLOG_Info.iid FROM BLOG_Info INNER JOIN BLOG_User ON BLOG_Info.uid = BLOG_User .uid where BLOG_Info.iid>(select max(BLOG_Info.iid) from (select top "+STR(@PageSize * @PageIndex )+" BLOG_Info.title, BLOG_Info.detail, BLOG_User.uname, BLOG_Info.dtime, BLOG_User.url , BLOG_Info.iid FROM BLOG_Info INNER JOIN BLOG_User ON BLOG_Info.uid = BLOG_User.uid order by BLOG_Info.dtime desc )) order by BLOG_Info.dtime desc"

EXEC (@SQLSTR)
GO

This is my stored procedure to see if it is wrong.
Reply

Use magic Report

1

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

 Author| Post time: 2020-7-1 00:00:01
| Show all posts
Found the problem just now, mainly because of missing a letter, but now it becomes the error below
SqlException (0x80131904): Line 1: There is a syntax error near')'. ]
Reply

Use magic Report

1

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

 Author| Post time: 2020-7-1 12:15:02
| Show all posts
Strange, I changed the value of @PageSize to 10.
Reply

Use magic Report

0

Threads

6

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 China

Post time: 2020-7-1 20:45:02
| Show all posts
There is an error in your stored procedure.
"+STR( @PageSize )+"

Not so written.
Reply

Use magic Report

1

Threads

5

Posts

5

Credits

Newbie

Rank: 1

Credits
5

 China

 Author| Post time: 2020-7-2 16:15:01
| Show all posts
How should I write it, but at present I change the input value @PageSize to 1, 2, it won't work, oh forget to say that the total number of records is three
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list