actually dont worry i worked it out for myself cheers lots:)

thanks that helped a lot. can you also help me with this similar query too. i need to do the same query but search only for entries where in one of the columns there is the letter f as appose to m. but i cant use a text box to enter the f. this is what i got but gives me the same answer as 'select count(*) from tablename "Select count(*) from Pupils where Sex LIKE '%" & F & "'" cheers

thank you

can anyone tell me the sql command for counting number of entries in a table and displaying as integer in textbox

can anyone tell me how to correct this SQL Query "SELECT DISTINCT * from Pupils WHERE Surname LIKE% '" & tbSearch.Text & "' " Syntax error in query expression 'Surname LIKE% 'Lowndes''. trying to search for data using tbSearch as the search criteria

hi, i need to input a number into a text box, check that number in a access database table, then display another number from the same entry in another text box on the same vb form. i have the connection code i just need the command code, if anyone knows the code to do this can you tell me what it is. it would be appreciated :)thanks

i'm trying to update a checkbox state from vb to an ms access database. can anyone fix my code for me pleae? it says "no value given for one or more parameters(what does this mean?). Imports System.Data.OleDb Public Class Block Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Private Sub Btnblock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlock.Click 'when the logon button is pressed Dim ManagerLogin As String 'declares the variable Try conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCBank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text ManagerLogin = "SELECT DISTINCT Password FROM Accounts WHERE ManagerID='" & Me.tbID.Text & "' " 'the variables value is an SQL command, selects value that is equal to the id AND password. ''MessageBox.Show(ManagerLogin) 'shows the sql command (only needed for testing) comm.CommandText = ManagerLogin 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCBank.mdb") conn.Open() comm = New OleDbCommand("update into Accounts(Blocked) values('" & (chBlock.CheckState) & "')", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") chBlock.Checked = False Catch ex As Exception MsgBox(ex.Message) If dr.Read() Then (it doesn't like this line either)'if the entry is correct (readable) Me.Close():confused: Form2.Show() 'show the index varibale (form) 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid ID, please try again") 'displays error message tbID.Clear() 'clears the managerid

basically i need to check a checkbox on an access database to therefore block the user from using that account. my code is wrong(below) plus the catch exception is brining up this message "No value given for one or more required parametres" i think this line of code could be the problem because i keep on getting warningsm on it :- If dr.Read() Then 'if the entry is correct (readable) can anyone recterfy my code please? Imports System.Data.OleDb Public Class Block Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Private Sub Btnblock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlock.Click 'when the logon button is pressed Dim ManagerLogin As String 'declares the variable Try conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCBank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text ManagerLogin = "SELECT DISTINCT Password FROM Accounts WHERE ManagerID='" & Me.tbID.Text & "' " 'the variables value is an SQL command, selects value that is equal to the id AND password. ''MessageBox.Show(ManagerLogin) 'shows the sql command (only needed for testing) comm.CommandText = ManagerLogin 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCBank.mdb") conn.Open() comm = New OleDbCommand("insert into Accounts(Blocked) values('" & (chBlock.CheckState) & "')", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") chBlock.Checked = False Catch ex As Exception MsgBox(ex.Message) If dr.Read() Then 'if the entry is correct (readable) Me.Close() Form2.Show() 'show the index varibale (form) 'close the current form

i am trying to read a number from an access table check it and use it to control access to another page. this is the code snippet where i think the problem is and the feedback i'm getting is saying this "Data type mismatch in criteria expression" - referring to the highlighted line of code below. Dim PinNumber As String PinNumber = "SELECT DISTINCT Pin FROM Accounts WHERE Pin='" & Me.tbPin.Text & "'AND AccountID = '" & Me.tbID.Text & "'" comm.CommandText = PinNumber 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value If dr.Read() Then 'if the entry is correct (readable) Form2.Show() 'show the index varibale (form) Me.Close() 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid Pin, please try again") 'displays error message tbPin.Clear() 'clears the managerid tbID.Clear() End If

don't worry about it. finally managed to figure it out. it was something so simple its stupid. CustomerForename should of been CustomerForenames PCBank needed .mdb at the end. i feel like i've been wasting my entire life(or a couple of weeks) thanks for all your help. i'll probably be bugging you again soon

found out the problem with the code...i think. when i click to add it to the database the msgbox comes up and basically says it cant find the path to the database... i don't know why, the database file is in the debug in the same project. message:- 'could not find file 'U:\visual studio 2005\projects\manager login\manager login\bin\debug\PCBank' Imports System.Data.OleDb Public Class cust Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Dim str As String Private Sub cust_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCBank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCBank") conn.Open() comm = New OleDbCommand("insert into customers(CustomerID,CustomerSurname,CustomerForename) values('" & (tbID.Text) & "','" & (tbSur.Text) & "','" & (tbFor.Text) & "')", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") Catch ex As Exception MsgBox(ex.Message) End Try conn.Close() End Sub End Class

having problems with adding to the database. the code i have should work, but nothing is getting added to my database. below is the code i have. can anyone pinpoint my broblem and rectify it for me?, thanks Imports System.Data.OleDb Public Class cust Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Dim str As String Private Sub cust_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCbank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Try conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCbank") conn.Open() comm = New OleDbCommand("insert into customers(CustomerID,CustomerSurname,CustomerForename) values('" & (tbID.Text) & "','" & (tbSur.Text) & "','" & (tbFor.Text) & ")", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") Catch ex As Exception End Try conn.Close() End Sub End Class:sigh:

i think i have made the connection to the database. i need to know how to write the code to add and ID, surname and forename to the database. don't have a clue how to do this. can you help me? (this is the connection) Imports System.Data.OleDb Public Class cust Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Private Sub cust_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCbank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text End Sub

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

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:

right now i'm trying to add data to a database. i think i'm almost their but get this error message "Property access must assign to the property or use its value" this is what code i got 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=C:\PCBank.mdb;") cn.Open() str = "insert into customer values(" & CInt(tbID.Text) & ",'" & tbSur.Text & "','" & tbFor.Text & "')" '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 can anybody enliten me?

can anyone please tell me how i can input data into 3 seperate textboxes on a form, then press a button and for that data to be placed in 3 fields on a database. thanks

i'm want to connect to a MS Access database which is stored in the same location as the application. i need to read feilds from the database and display in a listbox on the form. does anyone know how i would go about doing this?

im creating a form where users can login to a seperate form using a login name and password. I'm trying to compare the users imput for the login to a feild in MS Access and the same with password input in a seperate feild in the same table(PCBank.mdb). if input is correct go to form2. if anyone can help it would be greatly appreciated. code below is my attempt Imports System.Data.OleDb Public Class Form1 Dim conn As OleDbConnection Dim comm As OleDbCommand Dim dr As OleDbDataReader Dim da As OleDbDataAdapter Private Sub connect() conn = New OleDbConnection conn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=|datadirectory|\PCBank.mdb" conn.Open() comm = New OleDbCommand comm.Connection = conn comm.CommandType = CommandType.Text End Sub Private Sub DisplayRecord() Me.txtid.Text = dr.GetString(0) Me.txtpass.Text = dr.GetString(1) End Sub Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click connect() comm.CommandText = "select * from Managers where id like '" & txtid.Text & txtpass.Text & "%'" If txtid.Text = "Managers" = dr.GetString(0) And txtpass.Text = "Managers" = dr.GetString(1) Then My.Forms.Form2.Show() Me.Close() End If End Sub End Class