Simple database adding question

[Bonzol]

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.

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

[mar_zim]

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.

[Bonzol]

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.

[mar_zim]

Here a sample on how to insert a new row to your table.

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()

[Bonzol]

do I have to import SQL for that? if so whats the command for that?

cheers

[mar_zim]

Haven't you tried using sqlcommands? Sqlcommand is under namespace System.Data.Sqlclient

[Bonzol]

humm, im totally lost, if I use that name space nothing works at all now.

[mar_zim]

ok. what database are you using?

[Bonzol]

just a normal access database, connected by an oleDbDataAdapter

[mar_zim]

oh. so just change the sql to oledb.

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()

[Bonzol]

I dont know what im doing wrong here

oledbcommand is not defined

[mar_zim]

Imports System.Data.Oledb

[Bonzol]

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.

[mar_zim]

Could you post the code you're using? and yeah con is your oledbconnection.

[jmcilhinney]

mar_zim has mistakenly put parentheses after the type. This:

dim cmd as Oledbcommand()

is declaring an OleDbCommand array variable. Drop the parentheses and it should be alright.

[Bonzol]

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

[Bonzol]

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

[Bonzol]

ok I think I fixed that problem, I now have

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

[mar_zim]

Yeah sorry for the lacking of () and for your last error you must open a connection before making transaction to the database.

oledbconnection1.open()

[Bonzol]

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?