[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

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
0
fosiul01
Asked:
fosiul01
  • 14
  • 10
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now