Make a sub in vb2010

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

I am making a project in vb2010 and use sql2005 as my database, in this project i have to do lots of data opretion like Insert ,Update , Delete , Select and all other sql opration on every form

i know how to code for it and make sql queries . bt my proj will gona bi so lenthy if i code like this.

i want to make a sub in my module for all this queries , so by just call them and give the sub the rquire queries i want to do that task

smthing like

Opration(select,"THE SQL QUERIES HERE", sqlc)
textbox1.text = Data
so i can make all this sql opration in one line

Dim cammad As New SqlCommand(" SELECT [fldOcationName] FROM [FoodManagementSystem].[dbo].[OcationMaster] ", sqlc)
       rd = cammad.ExecuteReader
       While rd.Read
         Data = rd("fldOcationName")
       End While
       Try
           rd.Close()
       Catch ex As Exception
           MsgBox(ex.Message)
       End Try

any ideas ? plz help all ........

Posted 9-Aug-12 19:49pm

Vipin Sharma

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-08-09T20:12:00

Solution 1

For example to have a small function to fill a datatable based on a SQL string passed as a parameter, you can try something like the following:

VB

Module SqlModule
    Public Function ExecuteSelect(sql As String, connection As System.Data.SqlClient.SqlConnection) As System.Data.DataTable
        Dim command As New System.Data.SqlClient.SqlCommand(sql, connection)
        Dim table As New System.Data.DataTable
        Dim adapter As New System.Data.SqlClient.SqlDataAdapter(command)

        Try
            adapter.Fill(table)
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        End Try

        Return table
    End Function
End Module

Posted 9-Aug-12 20:12pm

Wendelius

Comments

Vipin Sharma 10-Aug-12 2:18am

thanx for this , but i cant totally understand it, coz i want to use this select command on many form and many time
how ,i give my query in this ?
coz for different form or opration every time the table and query change.. thank u for ur help ,

Wendelius 10-Aug-12 2:23am

The SQL query is sent as a parameter (the first string parameter) to the function. Then that statement is executed against the specified connection.

As a result the datatable is filled. The structure of the datatable will correlate to the SQL query you have provided meaning that the structure of the datatable is created based on the result set.

Try embedding that to a test application and give it a try with the debugger. This way you see how it actually works.

A sample call could be like:

Dim resultTable As System.Data.DataTable = ExecuteSelect("SELECT * FROM MyTable", myConnection)

Vipin Sharma 10-Aug-12 2:26am

thanx , its really helpfull ... thank you .

Wendelius 10-Aug-12 2:33am

You're welcome :)

Vipin Sharma 10-Aug-12 4:20am

are u still here , i want to ask something

Vipin Sharma · Accepted Answer · 2012-08-09T22:31:00

i am done with the insert query but have a problem in select query whenever i try to run i get the error that inveliad attempt to call reader when rd is close

i use module ,and put my sql opration code there and call it to another form

my code for module is :

Imports System.Data.SqlClient
Module Database_Module
    Public sqlc As New SqlClient.SqlConnection("Data Source=AIS-QUARDCORE\SQLEXPRESS;Initial Catalog= Student Record App ;Integrated Security=True;multipleactiveresultsets = true ")
    Dim sqlad As New SqlClient.SqlDataAdapter
    Public rd As SqlDataReader

    Public Function ExecuteSelect(ByVal Query As String, ByVal sqlc As System.Data.SqlClient.SqlConnection) As System.Data.DataTable

        Dim cammad As New SqlCommand(Query, sqlc)
        rd = cammad.ExecuteReader

        Try
            rd.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
            Return Nothing
    End Function
End Module

and the code where i want to call is on form load event .

ExecuteSelect("SELECT  fldName FROM    dbo.Test", sqlc)
      While rd.Read
          ListBox1.Items.Add("fldName")
      End While

want to add data to listbox on load
plz help ......