Simple database adding question
Hey there,
(using a windows applicaiton) What I have is a database with engineers on it in a table tited 'Engineer', what I want to do is be able to add a new engineer to the database. Via text boxes and a submit button instead of a data grid.
I have the data connections n such and have fiddled around a bit and tried this code.
VB Code:
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim Name As String
Dim qual As String
Dim mob As String
Dim phone As String
Dim NOK As String
Dim specId As String
Dim newEngineer As DataRow
Name = txtName.Text
qual = txtQual.Text
mob = txtMobile.Text
phone = txtPhone.Text
NOK = txtNOK.Text
specId = txtSpecID.Text
newEngineer = Update1.Engineer.NewRow
newEngineer("Eng-name") = Name
newEngineer("Eng-Qual") = qual
newEngineer("Eng-Mobile") = mob
newEngineer("Eng-Phone") = phone
newEngineer("ENG-NOK") = NOK
newEngineer("Spec-ID") = CInt(specId)
Update1.Engineer.Rows.Add(newEngineer)
OleDbDataAdapter1.Update(Update1)
but to no prevail, anyone know what im doing wrong? If you need more information please ask
Re: Simple database adding question
What I have understand in your question is that you want to add a data to your engineer table using textbox and a submit button. Is that what you mean?
If so..create an sqlcommand and provide an insert statement as parameter to your sqlcommand or better use stored procedures.
I recommend reading some toturials about ADO.NET would help you a lot.
Re: Simple database adding question
yeah, pretty much, but id like to add an entirely new row. so a new Engineer in other words.
I have some trouble with that SQL stuff. what do you mean exactly?
I've been looing through tutorials, and I am finding them difficult to understand to some extent.
Re: Simple database adding question
Here a sample on how to insert a new row to your table.
VB Code:
dim cmd as sqlcommand()
strSQL = "INSERT INTO Engineer(Eng-name,Eng-Qual) VALUES('" & txtName.Text & "','" & txtQual.Text "')"
cmd = New SqlCommand(strSQL, con) 'con is your connection
cmd.executenonquery()
Re: Simple database adding question
do I have to import SQL for that? if so whats the command for that?
cheers
Re: Simple database adding question
Haven't you tried using sqlcommands? Sqlcommand is under namespace System.Data.Sqlclient
Re: Simple database adding question
humm, im totally lost, if I use that name space nothing works at all now.
Re: Simple database adding question
ok. what database are you using?
Re: Simple database adding question
just a normal access database, connected by an oleDbDataAdapter
Re: Simple database adding question
oh. so just change the sql to oledb.
VB Code:
dim cmd as Oledbcommand()
strSQL = "INSERT INTO Engineer(Eng-name,Eng-Qual) VALUES('" & txtName.Text & "','" & txtQual.Text "')"
cmd = New OledbCommand(strSQL, con) 'con is your connection
cmd.executenonquery()
Re: Simple database adding question
I dont know what im doing wrong here
oledbcommand is not defined
Re: Simple database adding question
Imports System.Data.Oledb
Re: Simple database adding question
ok, half way three I suppose
"con" as my connection is that as in my oledbconnection ("OleDbConnection1")?
i tired that and it didnt work.
and it says
cmd.executenonquery()
is not a memeber System.Array
but that may have something to do with my connection not being set yet
Thanx for your help so far btw.
Re: Simple database adding question
Could you post the code you're using? and yeah con is your oledbconnection.
Re: Simple database adding question
mar_zim has mistakenly put parentheses after the type. This:
VB Code:
dim cmd as Oledbcommand()
is declaring an OleDbCommand array variable. Drop the parentheses and it should be alright.
Re: Simple database adding question
VB Code:
Dim strSQL As String
Dim cmd As OleDbCommand()
strSQL = "INSERT INTO Engineer(Eng-name,Eng-Qual,Eng-Mobile,Eng-Phone,Eng-NOK,Spec-ID) VALUES('" & txtName.Text & "','" & txtQual.Text "','" & txtMobile.Text "','" & txtPhone.Text "','" & Eng-NOK "','" & Spec-ID.Text "')"
cmd = New OleDbCommand(strSQL, OleDbConnection1)
cmd.executenonquery()
also at the end of the insert statment, it has an error also, probably just wrong syntax
Re: Simple database adding question
ok yeah jmc that works, thanx for that
but now I have a problem at the end of
strSQL = "INSERT INTO Engineer(Eng-name,Eng-Qual) VALUES('" & txtName.Text & "','" & txtQual.Text "')"
the "') " is underlined 'end of statment expected' hopefully ill be able to udnerstand all this stuff once i get in to it more
Re: Simple database adding question
ok I think I fixed that problem, I now have
VB Code:
Dim cmd As OleDbCommand
strSQL = "INSERT INTO Engineer(Eng-name,Eng-Qual,Eng-Mobile,Eng-Phone,Eng-NOK,Spec-ID) VALUES([txtName.Text] ,[txtQual.Text], [txtMobile.Text], [txtPhone.Text], [txtNOK.text] ,[Spec-ID.Text] )"
cmd = New OleDbCommand(strSQL, OleDbConnection1)
cmd.ExecuteNonQuery()
but now when I run it, I can an error on
cmd.ExecuteNonQuery()
saying
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed
Re: Simple database adding question
Yeah sorry for the lacking of () and for your last error you must open a connection before making transaction to the database.
oledbconnection1.open()
Re: Simple database adding question
ok, now I get
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
on cmd.ExecuteNonQuery()
Nothing ever goes right with me, does it have something to do with me having a dataset?
Im assuming that a dataset a dataset is use with a closed connection, is that correct?