how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures
how to insert using stored procedures how to insert using stored procedures
how to insert using stored procedures
Go Back  Xtreme Visual Basic Talk > > > how to insert using stored procedures


Reply
 
Thread Tools Display Modes
  #1  
Old 05-24-2005, 07:37 PM
19d20Scout 19d20Scout is offline
Newcomer
 
Join Date: Mar 2005
Posts: 5
Question how to insert using stored procedures


I have created a stored procedure as follows:

CREATE PROCEDURE AddCreditCard
(@CName varchar(40),
@Accept bit)
AS
BEGIN
/* This is an insert procedure in MSDE */
INSERT INTO CardType (ctypeName, ctypeAccept)
Values (@CName, @Accept)
commit
END

I am trying to insert records with the following code:
Code:
Private Sub AddCreditCard() Dim sqlConn As New SqlClient.SqlConnection sqlConn.ConnectionString = strDBConn sqlConn.Open() Dim command As SqlCommand = _ New SqlCommand("AddCreditCard", sqlConn) command.CommandType = CommandType.StoredProcedure command.Parameters.Add(New _ SqlParameter("@CName", SqlDbType.VarChar, 40)) command.Parameters("@CName").Value = "'" & Trim(txtCCType.Text)& "'" command.Parameters.Add(New _ SqlParameter("@Accept", SqlDbType.Bit)) command.Parameters("@Accept").Value = "" & intCardAct & "" Dim sqlDA As New SqlClient.SqlDataAdapter(command) Dim sqlDS As New DataSet sqlDA.Fill(sqlDS, TABLE_NAME) End Sub

The insert never happens and I keep getting an error: " An unhandled exception of type 'System.FormatException' occurred in system.data.dll
Additional information: String was not recognized as a valid Boolean. "

I have created several select stored procedures with no problem, but I have tried all day and I have not yet inserted a single record. Any help is greatly needed and appreceiated
Reply With Quote
  #2  
Old 05-25-2005, 08:56 AM
19d20Scout 19d20Scout is offline
Newcomer
 
Join Date: Mar 2005
Posts: 5
Default

Hey everyone it would seem that I have got it working. I removed the data types since they are already in my stored procedure and I also removed the single quotes, double quotes, and the &. Now on to the task of UPDATING with stored procedures. I will admit that the theory of Stored Procedures sounds great but the speed difference of a normal SQL connection with a string of field names and variables is just as fast on the first run. After the first run they both are about the same as far as speed,
but I would like to speed up the first run if possible. I understand about just in time compiling, but the delay can be up to 5 or 6 seconds before the screen is refreshed. Does anyone know of any methods of decreasing the delay time on SQL statements?

Code:
Private Sub AddCreditCard() intCardAct = -1 Dim sqlConn As New SqlClient.SqlConnection sqlConn.ConnectionString = strDBConn sqlConn.Open() Dim cmd As New SqlClient.SqlCommand("AddCreditCard", sqlConn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CName", Trim(txtCCType.Text)) cmd.Parameters.Add("@Accept", intCardAct) cmd.ExecuteNonQuery() sqlConn.Close() End Sub
Reply With Quote
  #3  
Old 05-25-2005, 02:47 PM
HardCode's Avatar
HardCodehow to insert using stored procedures HardCode is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Feb 2004
Location: New Jersey
Posts: 3,339
Default

Stored Procedures show their true colors in speed on complex SQL statements. A single-record update would hardly be faster, but selecting a set of records from a ten table join would be much, much faster. Additionally, you can run multiple SQL statements in a stored procedure. Plus, parameters make sure you pass the correct data type to the SQL. Which means you should put back your data types in the Parameters, and make sure you aren't passing a -1 as True to the SP, rather a 1 (Bits store only 1 or 0).

The immediate benefit of SPs regardless of SQL execution is security. Giving users table-level access is not a good security decision, and would be necessary with in-line SQL statements. Using SPs allows tight security by only granting permissions on those SPs to whom you want. The users can never directly access the underlying tables. Think of the SP as a bank teller. They provide you with your cash - you never reach behind the counter and take your money yourself That would not make your money secure.

The 5 to 6 seconds can depend on many factors, such as the physical server the DB is on, the network traffic, the speed of the PC running the program, etc.
__________________
DON'T CLICK HERE

Useful forum tags: [VB][/VB], [CODE][/CODE], [HTML][/HTML]
Reply With Quote
  #4  
Old 05-31-2005, 07:44 AM
19d20Scout 19d20Scout is offline
Newcomer
 
Join Date: Mar 2005
Posts: 5
Default Still have the delay

hey hardcode thanks for the reply, sorry for not returning a response sooner. Since I've figured out my mistake with the single/double quote issue I now have 26 stored procedures in my database. The largest sql statement that I have has 7 or 8 inner joins and there is a noticeable difference between the initial run of that statement and lets say a single table select statement. I have a P-3 800 mhz with 320mb of ram and the database is on my computer. After the first run of the procedure it is very fast, but on the initial run there is still a 3-4 second delay. If that is a common issue then that is ok, I had read about the power of stored procedures and had assumed that my initial delay was no longer an issue. After reading further into VB.net I think my problem is with the just-in-time compiling. I will be using install shield to compile the finished product. Hopefully once the program is fully compiled the delay will be greatly reduced or completely gone. When you mentioned security, do you mean that since I am passing only sql variable names and program values that I can restrict any changes from the user on the database and have 100% security? Again thanks for your all of your help.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures how to insert using stored procedures
how to insert using stored procedures
how to insert using stored procedures
 
how to insert using stored procedures
how to insert using stored procedures
 
-->