Inserting Data into the database using vb.net

[George_E]

Hey guys,

I want to be able to insert data into text boxes and when i press the button store to be able to store the data into the database.

Does anyone know the code to do this?
Below is the code I have used so far, I know its close to the solution but Im not sure:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

"
Dim myOleDbConnection As OleDb.OleDbConnection

Dim insertcommand As String

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;Persist Security Info=False"

insertcommand = "INSERT INTO Reservations (Transaction_Id, Item_Id, User_Id,Current_Date, Reserved_Until, Contact_Name, Contact_Tel)VALUES ('202-MV174', '8596586', '90803', '20/30/2003', '28/30/2003', 'Peter', '07719072955')"

myOleDbConnection = New OleDb.OleDbConnection(connString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertcommand, myOleDbConnection)

myOleDbConnection.Open()

Dim temp_num As Integer

Try

temp_num = myOleDbCommand.ExecuteNonQuery

Catch ex As Exception
Trace.WriteLine(ex.ToString)
End Try

myOleDbConnection.Close()

End Sub"

Thankyou all for your time

[Jx_Man]

I want to be able to insert data into text boxes and when i press the button store to be able to store the data into the database.

hi george,

so, what the effort from your code?
i think there is nothing wrong with your code..
if u want to insert data with text box, u just add the textbox and change your code a little bit more.

its your code :

insertcommand = "INSERT INTO Reservations (Transaction_Id, Item_Id, User_Id,Current_Date, Reserved_Until, Contact_Name, Contact_Tel)VALUES ('202-MV174', '8596586', '90803', '20/30/2003', '28/30/2003', 'Peter', '07719072955')"

you can change like this :

insertcommand = "INSERT INTO Reservations (Transaction_Id, Item_Id, User_Id,Current_Date, Reserved_Until, Contact_Name, Contact_Tel)VALUES (trim(txtTransactionId.text), trim(txtItemId.text),trim(userid.text),dtpicker1.value.ToShortDateString, dtpicker2.value.ToShortDateString, trim(txtContactName.text),trim(txtContactTelp.text))"

i m sorry if this post not give a current help, cause i didn't find the effort and you didn't show the effort.
Ok. all for the best

[George_E]

Hey Jx_man, thank you for your reply. I have adapted my code to what you suggested.

I have also put the code in a button_click section so the code acts when the button is clicked.

The only problem now is that when I compile the code I enter values into the text boxes and click the save button, nothing happens!!

Does anybody have an idea as to why this happens??

Thanks again for your time

[Jx_Man]

actually i used module to connect with access database :

Module ModUmum

    Public Function Koneksi() As OleDb.OleDbConnection
        Dim con As OleDb.OleDbConnection
        con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath.ToString & "\yourdatabasename.mdb")
        Return con
        con = Nothing
    End Function

    Public bool As Boolean
    Public da As OleDb.OleDbDataAdapter
    Public dt As New DataTable
    Public i As Int16
    Public cmd As OleDb.OleDbCommand
    Public da1 As OleDb.OleDbDataAdapter
    Public strSQL As String
    Public CB As OleDb.OleDbCommandBuilder
    Public con = ModUmum.Koneksi

End Module

and i write my code like this

Try

        Dim connString As String
        Dim cmd As OleDb.OleDbCommand
        Dim KOneksi = ModUmum.Koneksi

       connString = "INSERT INTO Reservations (Transaction_Id, Item_Id, User_Id,Current_Date, Reserved_Until, Contact_Name, Contact_Tel)VALUES (trim(txtTransactionId.text), trim(txtItemId.text),trim(userid.text),dtpicker1.value.ToShortDateString, dtpicker2.value.ToShortDateString, trim(txtContactName.text),trim(txtContactTelp.text))"
               
                cmd = New OleDb.OleDbCommand(strSQL)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()

                 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

ok. hope this help

[George_E]

Hey Jx_Man once again thanks for your response. I have implemented the code you have given and the code debugs perfectly.

The only issue is that a problem arises when a I enter text into the textboxes and click on the button to save the text.

A message appears:

"Command text was not set for the command object"

and it highlights the code "cmd.ExecuteNonQuery()"

any clue what this may be???

Thanks again for your time, i feel as though I am getting closer and closer to the solution

[williamrojas78]

hi

have you tried binding the textboxes to the dataset? Then all you have to do is update the database when the dataset where you are storing all your data has changed and you dont have to manually write the commands.

just another idea

Regards

[amitpathak]

Hi I have given the solution for insert in database using vb.net. please check the solution hope this can help you at http://aspnetans.blogspot.com/2009/06/no-value-given-for-one-or-more-required.html

[suresh bangaru]

try
con.Open()
insertcommand = new OleDb.OleDbCommand ("INSERT INTO Reservations (Transaction_Id, Item_Id, User_Id,Current_Date, Reserved_Until, Contact_Name, Contact_Tel)VALUES (trim(txtTransactionId.text), trim(txtItemId.text),trim(userid.text),dtpicker1.value.ToShortDateString, dtpicker2.value.ToShortDateString, trim(txtContactName.text),trim(txtContactTelp.text))",con)
cmd.ExecuteNonQuery()
msgbox("data Inserted")
con.close()
Catch ex As Exception
MsgBox(ex.Message)
End Try