CRecordset.Close() causes exception

Hi,
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why?
Many thanks,
Jeremy
Recordset is not connected to database? Recordset is completely empty?
etc?
Free software - Baxter Codeworks www.baxcode.com
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why?
> Many thanks,
> Jeremy
>
|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy
|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy

Creating user

Hi,
I've just installed MSDE, and I'm wondering how to create user so I
don't have to login as 'sa' user.
Thanks in advance
ukasz
Try:
sp_addlogin '<userName','<password>'
Look for sp_addlogin / sp_grantlogin in the BOL.
HTH, Jens Suessmeyer.
|||Hi,
To add on Jens:-
MSDE will not come with GUI tools; so you need to login using OSQL to sql
server and issue TSQL commands to create Logins and users.
For eg; if you need to create a user HARI with read only rights in Finance
database then:-
From command line:-
OSQL -S <servername> -Usa -P<sapassword> (enter)
SP_addlogin 'hari','haripass'
go <enter>
Use Finance
go <enter>
sp_adduser 'hari'
go <enter>
sp_addrolemember 'db_datareader','hari'
go <enter>
he above commands will create user Hari with read rights in Finance
database.
Thanks
Hari
SQL Server MVP
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
> Try:
> sp_addlogin '<userName','<password>'
> Look for sp_addlogin / sp_grantlogin in the BOL.
> HTH, Jens Suessmeyer.
>
|||Hari Prasad wrote:
> Hi,
> To add on Jens:-
> MSDE will not come with GUI tools; so you need to login using OSQL to sql
> server and issue TSQL commands to create Logins and users.
> For eg; if you need to create a user HARI with read only rights in Finance
> database then:-
> From command line:-
> OSQL -S <servername> -Usa -P<sapassword> (enter)
> SP_addlogin 'hari','haripass'
> go <enter>
> Use Finance
> go <enter>
> sp_adduser 'hari'
> go <enter>
> sp_addrolemember 'db_datareader','hari'
> go <enter>
> he above commands will create user Hari with read rights in Finance
> database.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
>
>
Thanks.
|||Hari Prasad wrote:
> Hi,
> To add on Jens:-
> MSDE will not come with GUI tools; so you need to login using OSQL to sql
> server and issue TSQL commands to create Logins and users.
> For eg; if you need to create a user HARI with read only rights in Finance
> database then:-
> From command line:-
> OSQL -S <servername> -Usa -P<sapassword> (enter)
> SP_addlogin 'hari','haripass'
> go <enter>
> Use Finance
> go <enter>
> sp_adduser 'hari'
> go <enter>
> sp_addrolemember 'db_datareader','hari'
> go <enter>
> he above commands will create user Hari with read rights in Finance
> database.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
>
>
Thanks
ukasz

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
Thanks
Post the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating stored procedures in sql server

I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it.

However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster".

Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didnt

Dim comm As New SqlCommand
Dim strsql As String
Dim strconn As String
strsql = "sp_Roster"
strconn = "server=Home; user=sa; pwd=juwar74; database=Book;"
With comm
.Connection = New SqlConnection(strconn)
.CommandText = strsql
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("TeacherID", SqlDbType.Char)
.Value = "DawsMark@.aol.com"
End With
With .Parameters.Add("ClassID", SqlDbType.Int)
.Value = CInt(classid)
End With
With .Parameters.Add("sID", SqlDbType.Int)
.Value = ssID
End With
With .Parameters.Add("sLastName", SqlDbType.Char)
.Value = lastname
End With
With .Parameters.Add("sFirstName", SqlDbType.Char)
.Value = firstname
End With
With .Parameters.Add("sMiddleName", SqlDbType.Char)
.Value = middlename
End With
With .Parameters.Add("Student", SqlDbType.Char)
.Value = fullname
End With
With .Parameters.Add("Password", SqlDbType.Char)
.Value = password
End With
.Connection.Open()
.ExecuteNonQuery()
With comm.Connection
If .State = ConnectionState.Open Then
.Close()
End If
End With
End With

Here is my procedure that I created and that was saved as sp_Roster.sql in sql server

CREATE PROCEDURE sp_Roster

AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)
END
GO

Why isn't ado.net finding it in sql server. Is the sqlstr correct?

In order to save a stored procedure to the DB in SQL 2005 you need to execute this code instead of saving the text as a file. Just click the "Execute" button and you should be ok. Another thing however, is that you have not declared your variables. You will not be able to execute/save your stored procedure without this.

CREATEPROCEDURE sp_Roster

@.TeacherIDint,

@.ClassIDint,

@.sIDint,

@.sLastNamenvarchar(100),

@.sFirstNamenvarchar(100),

@.sMiddleNamenvarchar(50),

@.Studentnvarchar(100),

@.Passwordnvarchar(100)

AS

BEGIN

SETNOCOUNTON;

-- Insert statements for procedure here

INSERTINTO Roster(TeacherID, ClassID,sID, sLastName, sFirstName, sMiddleName, Student,Password)VALUES(@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)

END

GO

|||

Unfortunately, Sql Server doesn't work the same as Access in this way. Wehn you save a query in Sql Server, you are saving the query, but no the query to the database. The script has to be run to actually create your stored procedure. This can be done by pressing F5. However, your query itself needs some work as you have no input parameters to your stored procedure.

sql

Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

Creating Grand Totals

Ive got a basic tabular report using a dataset built from the following
query, Was wondering how do I create Grand Totals for the 'Total Revenue'and
Ã?nits Sold columns on my report, thanks
Charlie
SELECT tblProduct.Name, Count(tblProduct.Name) AS 'Units Sold',
Sum(tblProduct.VOSValue) AS 'Total Revenue',tblProduct.PCMSCode,
tblProductCategory.Name As 'Product Category'
FROM (tblProduct INNER JOIN tblCustomerProduct ON tblProduct.idProduct = tblCustomerProduct.idProduct) LEFT JOIN tblProductCategory ON
tblProduct.idProductCategory = tblProductCategory.idProductCategory
GROUP BY tblProduct.Name, tblProduct.PCMSCode, tblCustomerProduct.idProduct,
tblCustomerProduct.idProductActivity,
tblCustomerProduct.idProductRemovalReason, tblProduct.NonSaleProduct,
tblCustomerProduct.idProductDesignation, tblProductCategory.Name
HAVING (((tblCustomerProduct.idProductActivity)=1) AND
((tblCustomerProduct.idProductRemovalReason) Is Null) AND
((tblProduct.NonSaleProduct)=0) AND
((tblCustomerProduct.idProductDesignation)=1))
ORDER BY tblProduct.NameCharlie:
Just drag and drop the appropriate fields from the Fields (SSRS2000)
or Datasets pane (SSRS2005) into the table footer. SSRS will add the
SUM aggregate for you.
HTH
toolman
Charlie wrote:
> Ive got a basic tabular report using a dataset built from the following
> query, Was wondering how do I create Grand Totals for the 'Total Revenue'=and
> =DAnits Sold columns on my report, thanks
> Charlie
> SELECT tblProduct.Name, Count(tblProduct.Name) AS 'Units Sold',
> Sum(tblProduct.VOSValue) AS 'Total Revenue',tblProduct.PCMSCode,
> tblProductCategory.Name As 'Product Category'
> FROM (tblProduct INNER JOIN tblCustomerProduct ON tblProduct.idProduct ==3D
> tblCustomerProduct.idProduct) LEFT JOIN tblProductCategory ON
> tblProduct.idProductCategory =3D tblProductCategory.idProductCategory
> GROUP BY tblProduct.Name, tblProduct.PCMSCode, tblCustomerProduct.idProdu=ct,
> tblCustomerProduct.idProductActivity,
> tblCustomerProduct.idProductRemovalReason, tblProduct.NonSaleProduct,
> tblCustomerProduct.idProductDesignation, tblProductCategory.Name
> HAVING (((tblCustomerProduct.idProductActivity)=3D1) AND
> ((tblCustomerProduct.idProductRemovalReason) Is Null) AND
> ((tblProduct.NonSaleProduct)=3D0) AND
> ((tblCustomerProduct.idProductDesignation)=3D1))
> ORDER BY tblProduct.Name