need help on database connection!
-
can anyone tell me how to connect to an access database and then add a feild to that database. any help with this would be greatly appreciated as i literally don't have a clue how to do it.:confused:
-
can anyone tell me how to connect to an access database and then add a feild to that database. any help with this would be greatly appreciated as i literally don't have a clue how to do it.:confused:
what are you using ? adodb ? adodc ? adding field/column ? why dont you make it directly in access ? or did you mean insert value of your table's fields ? you can use sql - INSERT command to insert value of fields and ALTER for add and drop column... if you're using adodb.. here is the connect function
Function connect() As Boolean Dim strConnect As String connect = False On Error GoTo errHandler strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\dbpakar.mdb;" Set conn = New ADODB.Connection With conn .CursorLocation = adUseClient .Open strConnect End With connect = True Exit Function errHandler: Debug.Print "ERROR! error number: " & Err.Number & ", Description: " & Err.Description End Functiondont forget to declare the conn , e.gPublic connVP As ADODB.Connectionif you're using adodc, then just set the adodc's property. sorry if this isn't the answer that you're looking for- i misunderstood it then.. :)Practicing
-
what are you using ? adodb ? adodc ? adding field/column ? why dont you make it directly in access ? or did you mean insert value of your table's fields ? you can use sql - INSERT command to insert value of fields and ALTER for add and drop column... if you're using adodb.. here is the connect function
Function connect() As Boolean Dim strConnect As String connect = False On Error GoTo errHandler strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\dbpakar.mdb;" Set conn = New ADODB.Connection With conn .CursorLocation = adUseClient .Open strConnect End With connect = True Exit Function errHandler: Debug.Print "ERROR! error number: " & Err.Number & ", Description: " & Err.Description End Functiondont forget to declare the conn , e.gPublic connVP As ADODB.Connectionif you're using adodc, then just set the adodc's property. sorry if this isn't the answer that you're looking for- i misunderstood it then.. :)Practicing
im using oledb. this is how i've tried to do it. it doesn't come up with any problems but it doesn't work. Imports System.Data.OleDb Public Class Cust Inherits System.Windows.Forms.Form Dim cn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader Dim icount As Integer Dim str As String Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCBank.mdb;") cn.Open() str = "insert into customer (CustomerID, CustomerSurname, CustomerForename) values ('tbID', 'tbSur', 'tbFor')" 'string stores the command and CInt is used to convert number to string cmd = New OleDbCommand(str, cn) icount = cmd.ExecuteNonQuery MessageBox.Show(icount) 'displays number of records inserted Catch End Try cn.Close() End Sub End Class
-
im using oledb. this is how i've tried to do it. it doesn't come up with any problems but it doesn't work. Imports System.Data.OleDb Public Class Cust Inherits System.Windows.Forms.Form Dim cn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader Dim icount As Integer Dim str As String Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCBank.mdb;") cn.Open() str = "insert into customer (CustomerID, CustomerSurname, CustomerForename) values ('tbID', 'tbSur', 'tbFor')" 'string stores the command and CInt is used to convert number to string cmd = New OleDbCommand(str, cn) icount = cmd.ExecuteNonQuery MessageBox.Show(icount) 'displays number of records inserted Catch End Try cn.Close() End Sub End Class
-
sorry, did you succesfully connect to your db ? i mean, where is your problem ? adding or connect ? then what was the error message ?
Practicing
i dont know whether i have connected to the database or not, how can i tell? i think i may of, there are no error messages . but when i add new record, no record gets added. i think that adding the record may be the problem?
-
i dont know whether i have connected to the database or not, how can i tell? i think i may of, there are no error messages . but when i add new record, no record gets added. i think that adding the record may be the problem?
-
im using oledb. this is how i've tried to do it. it doesn't come up with any problems but it doesn't work. Imports System.Data.OleDb Public Class Cust Inherits System.Windows.Forms.Form Dim cn As OleDbConnection Dim cmd As OleDbCommand Dim dr As OleDbDataReader Dim icount As Integer Dim str As String Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PCBank.mdb;") cn.Open() str = "insert into customer (CustomerID, CustomerSurname, CustomerForename) values ('tbID', 'tbSur', 'tbFor')" 'string stores the command and CInt is used to convert number to string cmd = New OleDbCommand(str, cn) icount = cmd.ExecuteNonQuery MessageBox.Show(icount) 'displays number of records inserted Catch End Try cn.Close() End Sub End Class
To check state of connection, you can use cn.ConnectionState or something like it. The problem is you are eating up the error by not doing anything in the Catch block. Do something like the other poster suggested to see if you have any error. Also, your problem is this:
peteyshrew wrote:
values ('tbID', 'tbSur', 'tbFor')"
You cannot insert a string for CustomerID (I am assuming). What you need (maybe) is CInt(tbID.text), tbSur.Text, tbFor.Text A better method is to use stored procs to do this to avoid SQL injection attacks. Hope this helps.
Shreekar
-
can anyone tell me how to connect to an access database and then add a feild to that database. any help with this would be greatly appreciated as i literally don't have a clue how to do it.:confused:
Imports System.Data.Oledb Dim con as new Oledb.OledbConnection("Provider=Microsoft.Jet.Oledb.4.0; " & _ "Data Source=dbase.mdb") NOTE: Put this code under the Public Class Form1 CODE OF BUTTON SAVE: Dim objDataAdapter As New OleDb.OleDbDataAdapter Dim objCommand As New OleDb.OleDbCommand Dim objDataTable As New DataTable Dim objDataSet As New DataSet Dim objOleCon As New OleDb.OleDbConnection Dim strInsert As String Dim strSelect As String Try strSelect = "Select * from Table1" strInsert = "Insert into Table1 (name,address) Values('" & tname.text & "','" & taddress.text & "')" objOleCon = New OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; " & _ "Data Source=dbase.mdb") objDataAdapter.SelectCommand = New OleDb.OleDbCommand(strInsert, objOleCon) objDataAdapter.Fill(objDataSet) objDataTable = objDataSet.Tables(0) With objCommand .Connection = objOleCon .Connection.Open() .CommandText = strInsert .CommandType = CommandType.Text .ExecuteNonQuery() .Connection.Close() End With MsgBox("Record Saved") Catch ex As Exception MsgBox(ex.Message) End Try
Don't block the drive way of all the newbies in programming. :))