SQL Programming Basics - VB.NET

SQL Programming Basics - VB.NET

Post by Fred Nelso » Thu, 23 May 2002 03:57:25



Hi:

I'm going through the process of learning VB.NET and SQL Server 2000.
Naturally I have books on both however I don't have one that discusses both
topics together.

What I would really like is a VERY basic tutorial on calling SQL stored
procedures and returning values from them and handling errors.  I have
searched MSDN and five VB.NET books and typically they start talking about
DataGrid and DataAdapter which is well and good and does work very well.

However:  I'm looking for a way to just add a record to a file (nonquery)
and return a result to the calling program.

I have a simple web project that gets name and address info and does update
an SQL database however I can't figure out how to determine if the record
was added successfully (unique key) or not and return this to the calling
VB.NET program.

Any "pointers" to sources would be greatly appreciated.

Thanks,

Fred

 
 
 

SQL Programming Basics - VB.NET

Post by Dave Fashenpou » Thu, 23 May 2002 04:41:21


Fred,

For starters, try calling any Stored Procedure (say XYZ),
by using 'exec XYZ'.  If the SP has parameters, then of
couse you have to handle those -- but start by calling SPs
without params.  Like sp_rename.

Dave.

Quote:>-----Original Message-----
>Hi:

>I'm going through the process of learning VB.NET and SQL
Server 2000.
>Naturally I have books on both however I don't have one
that discusses both
>topics together.

>What I would really like is a VERY basic tutorial on
calling SQL stored
>procedures and returning values from them and handling
errors.  I have
>searched MSDN and five VB.NET books and typically they
start talking about
>DataGrid and DataAdapter which is well and good and does
work very well.

>However:  I'm looking for a way to just add a record to a
file (nonquery)
>and return a result to the calling program.

>I have a simple web project that gets name and address

info and does update
Quote:>an SQL database however I can't figure out how to

determine if the record

- Show quoted text -

Quote:>was added successfully (unique key) or not and return
this to the calling
>VB.NET program.

>Any "pointers" to sources would be greatly appreciated.

>Thanks,

>Fred

>.


 
 
 

SQL Programming Basics - VB.NET

Post by Bob Pfeiff [MS » Thu, 23 May 2002 05:58:48


This paper has some good info on data access in .Net:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbd...
daag.asp

www.gotdotnet.com has some tutorial and sample material too.

There are some books on the way from people like Bill Vaughn on ADO.Net that
would probably be useful to you, but I'm not sure when they'll be available.

There is also info on using stored procedure parameters in the Visual Studio
.Net docuementation under "Input and Output Parameters, and Return Values".

--
Bob Pfeiff
Microsoft Consulting Services

This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

SQL Programming Basics - VB.NET

Post by Fred Nelso » Thu, 23 May 2002 07:01:44


Dave:

Thanks - I am able to call stored procedures.  What I would like to know is
how I can get a return value from a stored procedure that tells me whether
or not the procedure worked.  For example in the attached code I am simply
passing parameters to the stored procedure and would like to return a result
code (text or integer) that tells me if its successful or not.  In the
example the "email" filed is unique so attempts to add a second record with
the same e-mail as another will fail.  (presently the program just crashes).

I have tried everything to get a return value including defining parameters
with:

ParamaterDirection.Output & ParameterDirection.ReturnValue

Nothing has ever worked - the records are added yet I can't get my response
back!

I'm sure that there is a simple solution yet I can't find it in any examples
so if you could tell me what would need to be done to return a value at the
end of this code I would  greatly appreciate it!

Thanks,

Fred

---
ALTER PROCEDURE dbo.MembersAddNew

(








)

AS
 INSERT INTO members
 (
  email,
  password,
  name,
  address,
  state,
  city,
  zip,
  statement
 )
 values
 (








 )

 return <---- SOMETHING??????

---

Quote:> Fred,

> For starters, try calling any Stored Procedure (say XYZ),
> by using 'exec XYZ'.  If the SP has parameters, then of
> couse you have to handle those -- but start by calling SPs
> without params.  Like sp_rename.

> Dave.
> >-----Original Message-----
> >Hi:

> >I'm going through the process of learning VB.NET and SQL
> Server 2000.
> >Naturally I have books on both however I don't have one
> that discusses both
> >topics together.

> >What I would really like is a VERY basic tutorial on
> calling SQL stored
> >procedures and returning values from them and handling
> errors.  I have
> >searched MSDN and five VB.NET books and typically they
> start talking about
> >DataGrid and DataAdapter which is well and good and does
> work very well.

> >However:  I'm looking for a way to just add a record to a
> file (nonquery)
> >and return a result to the calling program.

> >I have a simple web project that gets name and address
> info and does update
> >an SQL database however I can't figure out how to
> determine if the record
> >was added successfully (unique key) or not and return
> this to the calling
> >VB.NET program.

> >Any "pointers" to sources would be greatly appreciated.

> >Thanks,

> >Fred

> >.

 
 
 

SQL Programming Basics - VB.NET

Post by Fred Nelso » Thu, 23 May 2002 10:09:34


Dave:

I was able to get it working!  I spend a lot of time digging through the
demo at www.ibuyspy.com and found the answers. I'm still not certain of
several things yet it will come over time!

Thanks again,

Fred


Quote:> Fred,

> For starters, try calling any Stored Procedure (say XYZ),
> by using 'exec XYZ'.  If the SP has parameters, then of
> couse you have to handle those -- but start by calling SPs
> without params.  Like sp_rename.

> Dave.
> >-----Original Message-----
> >Hi:

> >I'm going through the process of learning VB.NET and SQL
> Server 2000.
> >Naturally I have books on both however I don't have one
> that discusses both
> >topics together.

> >What I would really like is a VERY basic tutorial on
> calling SQL stored
> >procedures and returning values from them and handling
> errors.  I have
> >searched MSDN and five VB.NET books and typically they
> start talking about
> >DataGrid and DataAdapter which is well and good and does
> work very well.

> >However:  I'm looking for a way to just add a record to a
> file (nonquery)
> >and return a result to the calling program.

> >I have a simple web project that gets name and address
> info and does update
> >an SQL database however I can't figure out how to
> determine if the record
> >was added successfully (unique key) or not and return
> this to the calling
> >VB.NET program.

> >Any "pointers" to sources would be greatly appreciated.

> >Thanks,

> >Fred

> >.

 
 
 

SQL Programming Basics - VB.NET

Post by William Chen [M » Fri, 24 May 2002 16:15:58


Use the ExecuteNonQuery method to return only the return parameters and the
   return values. Any returned rows are discarded. This is most useful for
   executing action queries.

For more information, please refer to the following Knowledge Base article:

Q308049 HOW TO: Call Parameterized Stored Procedure Using ADO.NET and VB
http://support.microsoft.com/support/kb/articles/q308/0/49.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks,

William Chen

 
 
 

1. VB basics, Variables, combo boxes, SQL, vb.net

I need to learn how to correctly use a Combo Box with a button that when
pushed, sends the selection to a stored procedure on SQL 2k  that returns
data to the same page and shows it in a Table/grid/whatever you use.

THANK YOU in advance, this is stumping me, I am so close, yet so far away,

2. SQING.DLL questions

3. US-GA-.NET DEVELOPER WITH ORACLE FORMS VB.NET+ASP.NET

4. New FileMaker Pro 6.0v3 Updater Now Available

5. Newbie needs code pages for SQL Server 2000 access from asp.net page using vb.net

6. How can I access a Memo in a database?

7. Programming DTS in VB.NET

8. opportunistic lock ???

9. Programming DTS in VB.Net

10. Install Net 8 Client with VB program?

11. ODBC driver for Visual Basic 6 or VB.NET

12. relationship between VB, SQL and VB.NET

13. Visual C++.NET ADO.NET PROGRAMMING