Filtering and Paging - Database Discussion Boards

I solved my own problem... In the argument, you have to use "exec DBname.dbo.SP name". It is working for me!

Hello All!

I have some problem when i try Update record in opened Recordset in VB Script. Windows 2000Pro SP4,MDAC2.7SP1,VBS5.6,SQL2000SP3.
Table:
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) NULL ,
) ON [PRIMARY]

Table have an auto-incriment ID and 1 varchar field.
After execute Update method record must have filled ID field.
But in following example this did't appear:
....

Set Conn2 = CreateObject ("ADODB.Connection")<br />
Conn2.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=127.0.0.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=W2000;Use Encryption for Data=False;Tag with column collation when possible=False", "","" <br />
Set rs2 =  CreateObject ("ADODB.Recordset")<br />
rs2.Open "Table1", Conn2,adOpenKeyset, 3, adCmdTable<br />
   Rs2.AddNew<br />
    Rs2("Title") = "Some text"<br />
   Rs2.Update<br />
WScript.StdOut.WriteLine "Added New Record: "  &Rs2("ID")&    " "   &Rs2("Title") ' ID = NULL!!!

....
Somebody can me explain why this did not work in VBS and CORRECTLY executed and UPDATED in MCVC 6.0 SP4.
Thank for all who can help me understand this situation.

IFriendly

SELECT * INTO T2 FROM T1

fills T2 with T1 records.

How can I use an stored procedure that returns records , instead T1
?
thanks.

INSERT table EXECUTE stored_procedure

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

I want to create the table as a temporary table in a stored procedure.
What should i do ?

That was not the question you orginally asked. You asked how to replace T1 with a stored procedure.

To create a temporary table in a stored procedure do something like this

CREATE TABLE #MyTempTable(myKey int, someValue varchar(12))
INSERT INTO #MyTempTable ....

At the end of the stored procedure, SQL Server will drop the temp table for you, however you must remember to prefix the table name with '#' which marks it as temporary.

Does this help?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

use a UDF (user defined funcation) which can act just like a table. This is the MS recommended method.

select * from my_udf

I have a stored procedure to filtering data as :

CREATE PROCEDURE sp_getFilteredAdvertisement
@advertisementTypeID varchar(20),
@visible varchar(2),
@minDate varchar(40),
@maxDate varchar(40)

AS
declare @SQL as nvarchar(1000)

SET @SQL = 'SELECT [Advertisement].[advertisementID] , [Advertisement].[owner] , [Advertisement].[fileName] , [Advertisement].[link] , [Advertisement].[altText] , ABS([Advertisement].[visible]) AS visible , [Advertisement].[viewCounter] , [Advertisement].[clickCounter] , [AdvertisementType].[name] , [Advertisement].[startDate] FROM [Advertisement] INNER JOIN [AdvertisementType] ON [Advertisement].[AdvertisementTypeID]=[AdvertisementType].[AdvertisementTypeID] WHERE 1=1 '

IF ( LEN( @advertisementTypeID ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [Advertisement].[advertisementTypeID] = ' + @advertisementTypeID
END

IF ( LEN( @visible ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [visible] = ' + @visible
END

IF ( LEN( @minDate ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [startDate] >= ''' + @minDate + ''''
END
IF ( LEN( CAST( @maxDate AS VARCHAR(40) ) ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [startDate] <= ''' + @maxDate + ''''
END
exec (@SQL)

RETURN

I have a stored procedure to paging found from msdn :

CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
AdvID int
)

-- Insert into the temp table
INSERT INTO #PageIndex (AdvID)
SELECT
advertisementID
FROM
Advertisement
ORDER BY
advertisementID DESC

-- Return total count
SELECT COUNT(advertisementID) FROM Advertisement

----
select into

-- Return paged results
SELECT
O.*
FROM
Advertisement O,
#PageIndex PageIndex
WHERE
O.advertisementID = PageIndex.AdvID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID

END

How i can put paging procedure in filtering procedure ?

Here is what you can do,

(1) Create this procedure
CREATE PROCEDURE GetDataByPage(
@SqlStr varchar(8000),
@PageSize int,
@CurrentPage int)
AS
DECLARE @FirstRec int, @LastRec int,@dt varchar(10)
BEGIN
-- Initialize variables.
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)

SELECT @dt= substring(convert(varchar,rand()),3,10)

-- Create a temp table to hold the current page of data
SELECT @SqlStr = stuff(@SqlStr,charindex(' FROM ',upper(@SqlStr)),6,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)

-- Add an ID column to count the records
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD RID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)

--Return the total number of records available as an output parameter
SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
EXECUTE (@SqlStr)

--Select one page of data based on the record numbers above
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging'+@dt+' WHERE RID > '+convert(varchar,@FirstRec)+' and RID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)

-- Drop the temporary table
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)

END

(2) In sp_getFilteredAdvertisement add two more parameters @pageSize, @pageNo
instead of a call to exec(@sql) use

exec GetDataByPage @sql , @pageSize, @pageNo

Hope this helps.

I have a asp.net web page. On this web page I have a OleDb Adaptor and Connection with a DataSet. I have bound a Datagrid yet when I run the page I receive this error...

Could not lock file.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Could not lock file.

Source Error:

Line 151: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 152: 'Put user code to initialize the page here
Line 153: OleDbDataAdapter1.Fill(DataSet11)
Line 154: DataGrid1.DataBind()
Line 155:

Does anyone have any ideas as to what I am doing wrong. I am new to working with Access and ADO so I know it must be something simple yet I am stumped.

Javolin
VB.Net, ASP.Net

Access makes a file to show that the DB has been locked - perhaps your DB is outside of the directory structure of your web app, and so the ASP.NET user does not have permission to create this file ?

Christian Graus - Microsoft MVP - C++

Hi
The problem is the following:
i have an ms access database containing a memo field and i want to insert a new record from within my c# app.
I use an ADO connection and an ADO command.

this is my sql command text:

"INSERT INTO ProductTable (Description) VALUES (' BLA BLA BLA BLA BLA')

All of this seemed to work fine, up until the moment i tried using longer strings.
When adding a string of more than 100 characters in the description field, the command executes normally (no errors), but when looking in the database afterwards shows me the value text is truncated to 100 characters. Editing the value in ms access works flawless, and (and this is the part i dont understand at all), when replacing my ado connection object and command by an ODBC connection object and command, it works just fine, it inserts the complete text without truncation!

Anybody have an idea where this comes from? Should i specify some "long string" property in my connection object? Are there other ways to do this using ADO, because i dont really want to use ODBC, as i read in different places that it is actually deprecated stuff.

thanks

i think it comes from the database restrictions instead...

what database do you use ?
isn't the field [ProductTable].[Description] set to varchar[100] or something like that ?

TOXCCT >>> GEII power
[toxcct][VisualCalc]

it is an access 2000 database, and the field type is "memo".

But even if this were the case, why does it work using the ODBC objects and not using the ADO ones?

Use parameterized queries and make the parameter data type adLongVarChar.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Hi,
I am writing an application, which takes a access table < OLEDB 4.0 > provided by ADO.NET connection. I have used both OleDbDataReader as well as OleDbDataAdapter to fetch records from that table, but is providing me only 65536 rows where that table contatains 13000 rows. Neither I can export to it in a text file , nor I can show it to a DataGrid. Since I have to keep those records in some other Repository, please , some one , provide me such a solution to access all the rows..
[ if code placed , please Code it in C# ]

Thank you.

love2code

No body liked this questions.. How fool am I .. Shall I quit the programming ???

love2code

Hello,

I have a Receipts table with fields ID_receipt, WeekNumber, CustomerID. Then I have a Payments table with ID_payment, CustomerID, WeekShouldHaveBeenPaid, WeekPaid.

When I authorize a credit for a customer, I create all the payments on advance (say, 54) and starting the next week, I generate a receipt. Each week one payment will be marked as should-be-paid that week, but only when it really is paid the week number will be stored in the payment row.

I'm trying to migrate some old data to this new format. My problem is that I have the receipts, but I don't have the payments marked when they should be paid. Since I'll make this migration after a week is closed and before the next week is opened, getting those week numbers is easy: I just have to find the first receipt for a customer, mark the first payment with that week, and mark every payment with a consecutive week number until I run out of receipts.

I'm trying to think of a SQL query that could do this for me. Otherwise I'll have to write a little C# program that counts all the receipts and then marks each payment with the corresponding week number. But this is what SQL was made for, so there should be a way.....

Any ideas? Thanks!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

hi,

What does .net stand for? or What is the .net original name?

Thanks

cross posters are not welcomed...

was it really necessary to ask on C#, General Discussions, SQL, VB, ASP and .NET forums[^] ???

you already have at least 2 answers on 2 different forums. that's enough Suspicious | :suss:

TOXCCT >>> GEII power
[toxcct][VisualCalc]

i am working on a project to automatically generate view if the user select certain number of tables.
my problem is how can i save the query generated as a view

create an execute non query and create a view.

CREATE VIEW dbo.vw_myview
AS
SELECT * from mytable

i understand your anwser but my question is this

the query is autogenerated in a form of string in a text box
so how can i pass the string query and save it as a view
using sqldmo

Try something like this

You will have to fill in the blanks for connections string etc..

Before some pipes up with "injection attacks security risks etc..."
This is a very basic code to give an idea of how it could work.

=========================

string MySQL = TextBox1.Text; // SQL in the view select * from table
string MyViewName = TextBox2.Text; // The view name on DB

SQLstring = "CREATE VIEW " + MyViewName + " AS " + MySQL

SqlConnection myConnection = new SqlConnection(strConnectSQL);
SqlCommand myCommand = new SqlCommand( SQLstring, myConnection );

try
{
myConnection.Open();
myCommand.ExecuteScalar();
myConnection.Close();
}
catch
{
myConnection.Close();
}

I'm working with some people on other countries. I want all people will work
on the same database at certain server. Help me ASAP ! Thanks !

Hung