add/Remove/Next/delete(with parameter-stored procedure)

Hi,
I attached 2 pictures.
What i want is :

if any one can Wittie some code for Simple Add/delete/next/previous/update - command by using parameter and stored procedure.

Data connection, function, stored procedure will be in different class( in my picture - under  allclass project)

i want to get idea about how to write 3 tire application - (robust code)- i am following a book, it seems to too complex , so i want a simple robust 3 tire application code.
I have attached another 2 picture- example1 and example2, ( where the writer , he put all functional code, stored procedure in different class)
and he called all those class in main programme.

if you need any explanation, i will try to give more explanation.

bottom line : i want basic robust code for 3 tire application( the example i provided)

project.GIF
pic2.GIF
example1.GIF
example2.GIF
LVL 29
fosiul01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DhaestCommented:
In a multitier-application you'll have the following:
1. GUI (your screens)
2. Business-layer (where is some logic without database and the calls to the database layer)
3. Database-layer (this is where all the communication with the database will be done)

So far, I see no problem. Now if you want to update, show data your GUI must call a function on your business-layer (little example below)

To add parameters to your update, ...
http://msdn2.microsoft.com/en-us/library/yy6y35y8.aspx

If you still have any questions, please fee free to ask them (but a little bit more specific what you really want to know)
GUI
---
dim businessLayer as new bLayer()
datagrid.datasource = bLayer.ShowData()
 
BusinessLayer
-------------
function ShowData() as recordset
   dim dataLayer as new dLayer()
   return dLayer.showData
end function
 
DataLayer
---------
function ShowData() as recordset
   ' retreive data on database and return it as recordset
end function
 
function UpdateData(byval ID as integer, byval value as string) as boolean
   ' update data on database and return a boolean to tell us if it was successful or not
end function

Open in new window

0
fosiul01Author Commented:
Hi Thanks for the code  and good morning

the code you sent me its little bit far  away from my knowledge

project.gif and pic2 - is the basic project i want to do ,
what i want is : just sent me some code where i will be able to add/delete/next by using passing parameter and stored procedure from "class to form" ( Everything would be declare in Class then i want to call those function and procedure from Form)

and example1 and expample2 is just an example picture i got from book. but its too complex in book , so i want  same style code but in a basic way( 2 field table)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

fosiul01Author Commented:
Hi,
thanks for those link, First one i had from before
2nd one is good tutorial
but the problem is , every tutorial , they are inputting data manually, like 2nd tutoiral :

Public Sub InsertCustomer()
    Dim connection As SqlConnection = _
        New SqlConnection(connectionString)
    connection.Open()
    Try
        Dim command As SqlCommand = _
            New SqlCommand("InsertCustomer", connection)
        command.CommandType = CommandType.StoredProcedure

        command.Parameters.Add("@CustomerID", "PAULK")-------------------------------- manualy intering( but what to do with txt box?)
        command.Parameters.Add("@CompanyName", "Pauly's Bar") ---------------------
        command.Parameters.Add("@ContactName", "Paul Kimmel")
        command.Parameters.Add("@ContactTitle", "The Fat Man")
        command.Parameters.Add("@Address", "31025 La Jolla")
        command.Parameters.Add("@City", "Inglewood")
        command.Parameters.Add("@Region", "CA")
        command.Parameters.Add("@Country", "USA")
        command.Parameters.Add("@PostalCode", "90425")
        command.Parameters.Add("@Phone", "(415) 555-1234")
        command.Parameters.Add("@Fax", "(415) 555-1235")

        Console.WriteLine("Rows inserted: " + _
                          command.ExecuteNonQuery().ToString)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Throw
        Finally
            connection.Close()
        End Try
End Sub




/**** My problem is ****

when i am trying to input data from text box, i am stuck, I cant do that. so i need help of hand coding or tutorial
( how to add/delete data -between class and form- by using parameter, store procedure and text box)

hope i made it clear?
0
DhaestCommented:
Replace the hardcoded-values by textbox.text !
command.Parameters.Add("@CustomerID", YourTextbox.text)
0
fosiul01Author Commented:
ok I will try with code from codeguru, if any problem i will come back to you
i just want a starting- after that i would be allright.
0
fosiul01Author Commented:
Hi,
See, Same problem -

tell me where i am doing wrong, check the picture please,

its not taking any data , there are some error about

Warning      1      'Public Function Add(parameterName As String, value As Object) As System.Data.SqlClient.SqlParameter' is obsolete: 'Add(String parameterName, Object value) has been deprecated.  Use AddWithValue(String parameterName, Object value).  http://go.microsoft.com/fwlink/?linkid=14202'      d:\accounts\Visual Studio 2005\Projects\storedprocedure\storedprocedure\Form1.vb      22      9      storedprocedure

store.GIF
0
DhaestCommented:
When you move to 2.0, you should instead be using: -

SqlCommand.Parameters.AddWithValue("@Name","Value");
0
fosiul01Author Commented:
I just did , what you said
still same problem, check the picture agian
Imports System.Data.SqlClient
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim connectionString As String = _
          "Data Source=localhost\SqlExpress;Initial Catalog=account;" _
                    & "Integrated Security=True"
 
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        connection.Open()
 
        Dim command As SqlCommand = _
                  New SqlCommand("insertemp", connection)
 
        command.CommandType = CommandType.StoredProcedure
        SqlCommand.Parameters.AddWithValue("@name", txtname)
 
        SqlCommand.Parameters.AddWithValue("@phone", txtphone)
 
        Try
            Console.WriteLine(connection.State.ToString())
        Finally
            connection.Close()
        End Try
    End Sub
End Class

Open in new window

store1.GIF
0
DhaestCommented:
Command.Parameters.AddWithValue("@name", txtname)
Command.Parameters.AddWithValue("@phone", txtphone)
0
fosiul01Author Commented:
ok Thanks
i am not seeing any error now
but when i am click on Button after putting Data in text field

nothing is saving to database, its just blank

i tryed with console mode, Connection has been established but database is not updating.

i atteched code picture and Storedprocedure picture aswell
stored2.GIF
storedpic.GIF
0
fosiul01Author Commented:
Ok done
i had to add   command.ExecuteNonQuery()

Thanks i will accept your answer soon, just give me little bit of time
0
DhaestCommented:
I just wanted to post it... You always need to execute the stored procedure (otherwise it won't update, delete, ...)
0
fosiul01Author Commented:
Can you   please assist me with last question please

i put Connection to database in a seperate class , so that i dont have to write the same code always
but i am having problem

please fix this bug, then no more further question for this topic!!!
class1.GIF
class2.GIF
0
DhaestCommented:
Did you call your class class1 or empclass where you hold the function getConnection ?

dim myClass as Class1  = new class1()
Dim connection as sqlConnection = myClass.getConnection()
0
fosiul01Author Commented:
Ok i just update with some code,
Check the bellow picture please


now problem is , its saying,  its cant find the stored procedure
class3.GIF
class4.GIF
0
DhaestCommented:
Are you sure of your connectionstring.
In the latest I see your initial catalog on "payables" and in previous screenshot i see initial catalog on "account"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fosiul01Author Commented:
hahahahahaha
Thanks

what happended, i was copying code from book example by copy and past !!

forgot that , i didnot change the catalog
its fine now
Thanks

you helped me to start of vb.net!!!
0
DhaestCommented:
Glad I could help you.
Enjoy programming vb.net
0
fosiul01Author Commented:
i will open some other questions as well may be tomorrow , what is the best you to let you know that, " please check my question"?

i mean, how can i get your  attention if  i submit new question ???
because, i will continue with vb.net learning.
0
DhaestCommented:
I usually check every day if there are new questions... So if you post it to this zones I will notice them and if I have time (and can) I'll try to answer your questions !
0
fosiul01Author Commented:
ok , no problem, i will submit a new question soon, its regards to this question as well, but i need some explanation of some authors code
0
fosiul01Author Commented:
0
fosiul01Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.