none
a little help please RRS feed

  • Question

  •  

    can anyone please give me some codes for connecting database(sql) manualy in vb.net

    and how to update its content like add edit delete or something... i just want 2 know how am i going t edit some specific columns on my data base. i like the manual type than using the wizard so please help me

    Sunday, November 18, 2007 6:15 AM

Answers

  • Hi,

    I didnt understand what you want exactly but here a general help

    if you have a table students with columns id(int), name(string) and course(string).

     

    int sID;

    string sName;

    string sCourse;

     // where you get the above 3 values by any way textbox or combobox whatever

     

    to select a record by student id:

    Code Block

    string sqlStatment = " SELECT  * FROM Students WHERE id = " + sID ;

    SqlCommand command = new SqlCommand(sqlStatment, );

    SqlDataReader reader = command.ExecuteReader();

     

     

     

    to select a record by student name:

    Code Block

    string sqlStatment = " SELECT  * FROM Students WHERE name =' " + sName +" ' " ;

    SqlCommand command = new SqlCommand(sqlStatment, );

    SqlDataReader reader = command.ExecuteReader();

     

     

     

    now , if you already executed a sql SELECT statment and have one record or more that have student data (id, name and course)

    Code Block

    while (reader.Read()) // this line will advance reader pointer to point to next retreived record

    {

    int retrievedID = reader.GetInt32(0);

    string retrievedName = reader.GetString(1);

    string retrievedCourse = reader.GetString(2);

    /*

     the passed argument (0,1,2) in the column index in the reader object

    and you cant specify that order in SELECT statment

     

    now you successfully retrieved student data from database to variables,

     do what you want with it.

    */

    }

     

     

     

    Friday, November 23, 2007 11:09 AM

All replies

  •  

    You must specify the database type exactly to know its 'provider' .

    to establis a connection with a database use:

    Code Block

    OleDbConnection con = new OleDbConnection("Provider=; Data Source=yourDataBase.mdb;");
    con.Open();

     

     

    to add or edit or delete:

    we will have to create an istance of class OleDbCommnad which has the sql statment that will be pass to the database.

     

    Code Block

    OleDbCommand com=new OleDbCommand ( <sql statment here> , con) ;

    com.ExecuteNonQuery();

     

     

     

    Sunday, November 18, 2007 1:55 PM
  • will i declare it first please if you wont mind i want to request a complete code for the connection or a complete code as an example.... sorry for demanding im just new in data manipulation

     

    Monday, November 19, 2007 2:38 AM
  • the above code is complete !, you have just to add the 'provider string' and this depends on the database type. and also to add the 'sql statment string' and this depends on the operation you want to do (INSERT, UPDATE, DELETE).

     

    if you want to get data from database the code will be different abit.

    Code Block

    OleDbCommand com = new OleDbCommand (<SELECT sql statment here> , con); // con is instance of OleDbConnection

     

    // this line get the needed data from database to reader obect

    OleDbDataReader reader = com.ExecuteReader( );

     

    while (reader.Read()) // move the reader pointer to point to next retreived record

    {

    /*

    here do what you want with the retreived data by using

    reader.GetInt32(1) , reader.GetString(5) , .... the integer argument specify the column index to get the data from the current record

    */

    }

     

     

     

    read OleDbConnection, OleDbCommand, OleDbReader will help you much

    Monday, November 19, 2007 11:50 AM
  • string sConnection=@ "Data Source=ComputerName;Inetial CataLog=DataBaseName;Persist Security Info=True;User Id=userName;Password=If any password";

    and use Mathods for Operation

    SqlConnection conn=new SqlConnection(sConnection);

    conn.open();

    SqlCommand cmd=new SqlCommand("Command Text",conn);

    cm.ExecuteNonQuery()//use fron executing command which is for insert ,delete or update this will return no of row affected 

    cm.ExecuteScaler()//use fron executing command which returns scaler value like select count(empname) from emp

    cm.ExecuteReader()//return an datareader object by which you can retrive data

     

    Friday, November 23, 2007 7:05 AM
  • thanks for the help!!!

     

    i need to access a data on the database and store it in a variable what will be the code....

     

    this is the scenario in my assignment i have a table named students and elements of id name and course

     

    i need to search a specific id and put the other element on a textbox and the id i choose it in a combobox

     

    i also want to know how to store the data i get on a variable for future manipulation

     

     

    please give me some example code!!! thanks!!!

    Friday, November 23, 2007 9:13 AM
  • Hi,

    I didnt understand what you want exactly but here a general help

    if you have a table students with columns id(int), name(string) and course(string).

     

    int sID;

    string sName;

    string sCourse;

     // where you get the above 3 values by any way textbox or combobox whatever

     

    to select a record by student id:

    Code Block

    string sqlStatment = " SELECT  * FROM Students WHERE id = " + sID ;

    SqlCommand command = new SqlCommand(sqlStatment, );

    SqlDataReader reader = command.ExecuteReader();

     

     

     

    to select a record by student name:

    Code Block

    string sqlStatment = " SELECT  * FROM Students WHERE name =' " + sName +" ' " ;

    SqlCommand command = new SqlCommand(sqlStatment, );

    SqlDataReader reader = command.ExecuteReader();

     

     

     

    now , if you already executed a sql SELECT statment and have one record or more that have student data (id, name and course)

    Code Block

    while (reader.Read()) // this line will advance reader pointer to point to next retreived record

    {

    int retrievedID = reader.GetInt32(0);

    string retrievedName = reader.GetString(1);

    string retrievedCourse = reader.GetString(2);

    /*

     the passed argument (0,1,2) in the column index in the reader object

    and you cant specify that order in SELECT statment

     

    now you successfully retrieved student data from database to variables,

     do what you want with it.

    */

    }

     

     

     

    Friday, November 23, 2007 11:09 AM
  • thanks for the codes but if you have a complete source code can i have a copy.... im confused in declarations of sql commands like that on the sqlcimmand command= new sqlcommand(sqlstatement, ) when i enter that code it is highlighted green and it says needs to be declared.... could you give me a copy of example codes complete code which i can run already so that i can review and analyze how it is used.... in my book theres a different way to declare and it has an example which is very confusing anyway its copyrighted 2000 so i dont know if its applicable  on vs2005

     

    Friday, November 23, 2007 11:45 AM
  • I dont have a complete code , if you search for documentation about this topic will be much helpful.

     

    ohhh , i think you miss "using System.Data.SqlClient;"

    and you have also to create a SqlConnection object to connect with the sql database

    Friday, November 23, 2007 11:56 AM
  • thanks for the help im going to try programming it now... ill ask you again if i encounter another problem... by the way what is the use of sqldatabinding

    Friday, November 23, 2007 12:46 PM
  •     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim conn As New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=C:\Documents and Settings\zybonje\My Documents\test.mdb")
            Dim adapt As New OleDb.OleDbDataAdapter
            Dim data As New System.Data.DataSet()
            Dim val As String
            Dim table As New System.Data.DataTable
            Dim reader As DataTableReader
            Dim i As Integer


            conn.Open()
            adapt.Fill(table)
            reader = table.CreateDataReader
            i = reader.FieldCount
            TextBox1.Text = i
        End Sub




    can u please help me this is my code and i cant make it run. i want to access the databse and count the number of fields in the table of student table(id#, name, grade) in my databse then i would like to put the first element of the table or the value in my id# column on the textbox or in a combo box how will i do it and i would like to know how to initialize an oledb reader?

    Thursday, January 17, 2008 3:32 AM
  • hi,

    first i didnt see you specify the operation of the data adapter object, you should do it like that :

    Dim command As New OleDbCommand("SELECT * FROM Student",conn) // or any other sql select statment

    Dim adapt As New OleDb.OleDbDataAdapter(command)

     

    then , just you get the data in DataTable object , no more need to use OleDbDataReader.

    to get the number of records ( i think you meant records not fields) use:

    adapt.Fill( table)

    Dim count As Integer = table.Rows.Count

     

    finally, to put a value from the table to a textbox, combox .. whatever , you will act with the table as a two dimensinal array , you will any needed data like that:

    Dim studentID As Integer = table[0][0].Value

    Dim studentName As String = table[0][1].Value

    Dim studentgrade As String = table[0][2].Value

     

    Good luck,

    Thursday, January 17, 2008 9:25 PM
  • i have another question... what is the use of databinding source and how will i use it
    Friday, January 18, 2008 12:59 AM
  • guys i need some advice here i have problem that i cant debug heres my code

     


     

    Code Snippet

       Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click

     


            Dim ctr As Integer
            Dim dr1 As DataRow
            Dim cb1 As OleDbCommandBuilder


            adapt.Fill(dtable)
            dr1 = dtable.Rows(1)
            dr1("member_id") = Str(11)

     

            cb1 = New OleDbCommandBuilder(adapt)

            Try

                adapt.Update(dtable)
            Catch ex As Exception
                MsgBox("Yikes, Can't update the database" & vbCr & ex.Message, _
                 MsgBoxStyle.Critical, "Error!")
                Exit Sub
            End Try

            GoTo out

     

    out:

        End Sub

     

     

    the problem is when i edit the values it catches some error

     

    heres the error i get from the msgbox:

     

    Syntax error (missing operator) in query expression '((member_id = ?) AND ((? = 1 AND lastname IS NULL) OR (lastname = ?)) AND ((? = 1 AND firstname IS NULL) OR (firstname = ?)) AND ((? = 1 AND middlename IS NULL) OR (middlename = ?)) AND ((? = 1 AND membership_status IS NULL) OR (membership_status = ?)) AN'.

     

    can you please help me????

    Friday, February 1, 2008 4:51 AM
  • my database is an access and i have already connected to database that code is just a part only

     

    Friday, February 1, 2008 4:55 AM