Adding New Records in Database????

[sohaib]

Hello All VB.Netians!!!!

I hope u all r enjoying ur life very well but i have getting few problems in maintaining a database which can add records... The navigation process is working fine but when i want to add into the database the screen shows it has been added but unfortunately it was not added in the database as in my case it's a SQL Server....
so plz provide me a simple but working example of adding new records. I hope for the positive response from u people very soon....n plz help me in this regard...i will be thankful to u....

Have a nice day!!!!
Sohaib Ahmed

 

[Arg81]

You need to submit those changes to the database - ADO.Net "caches" changed information into DataTables within your DataSet, so you need to have a "Submit" button that then makes any changes to the DataTables back to the database.

The easiest way to do this is to use the DataForm Wizard, as this adds the necessary button and code to a form to submit the changes. You can then use this code on your form and create a button or event in which to update those changes.

Hope that helps
Luke

 

[sohaib]

Hello!!!

Can u give me a working example of Adding new records so that i will be easily manage it afterwards i hope u dont mind it...

take care,

Sohaib

 

[Arg81]

just recreate your form using the DataForm Wizard - takes less than 5 minutes....

...sorry, my advice is advice, not answers.

 

[sohaib]

Adding New record Problem!!!!

Hello Mr.Luke!!!

You r right it will takes hardly 5 minutes to manage with dataForm Wizard but even after doing the record is not entering in my databases it was looking as "Record has been updated" or "Successfully Inserted" u can understand in this way but the new record is not entering into the database. I m using SQL Data Adpater....Is there any mistake made by me so plz help me in this regard...paste me or send me a sample code related to SQL Command/Adapter but it should enter new record and updated in databases as welll...

Hope u people look forward to help me out from this problem

With Regards,

Sohaib

 

[jeva39]

This is a code you can use to add your record:

......Create Connection.....
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
	Dim dtr As DataRow
	If txtTema.Text = "" Or txtTopico.Text = "" Or txtClase.Text = "" Then
	  MessageBox.Show("Tema, Topico o Clase no pueden estar en blanco", vbExclamation)
	  txtTema.Focus()
	  Exit Sub
	End If
	dtr = Me.dst.Tables("Ayuda").NewRow
	dtr("Clase") = Me.txtClase.Text
	dtr("Tema") = Me.txtTema.Text
	dtr("Topico") = Me.txtTopico.Text
	dtr("Info") = Me.txtInfo.Text
	Me.dst.Tables("Ayuda").Rows.Add(dtr)
	Messagebox.Show("Registro creado OK!!)
	Try
	  Me.dta.Update(Me.dst, "Ayuda")
	Catch eUpdate As System.Exception
	  System.Windows.Forms.MessageBox.Show(eUpdate.ToString())
	End Try
	Reset()
  End Sub

Remember: in your Connection code must include:

Dim oCB As SqlCommandBuilder = New SqlCommandBuilder(dta)

The other way, with CommandText:

......Cretate Connection......
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
cmd = con.CreateCommand
	cmd.Connection = con
	Dim Trans As OleDbTransaction
	Trans = con.BeginTransaction
	cmd.Transaction = Trans
	Try
	  Dim vAnulado As String = "N/A"
	  Dim vCausa As String = "N/A"
	 If txtPrestamo.Text = "" Or txtCedula.Text = "" Or txtCom.Text = "" Or txtNombre.Text = "" Then
	 MessageBox.Show("Préstamo,Cédula, Comunidad, Nombre no pueden estar en blanco", "Error en Grabación", MessageBoxButtons.OK, MessageBoxIcon.Warning)
		txtPrestamo.Focus()
		Exit Sub
	  End If
	 cmd.CommandText = "INSERT into Prestamo (idPrestamo,Idcliente,Idcomunidad,nombre) values ('" & txtPrestamo.Text & "', '" & txtCedula.Text & "', '" & txtCom.Text & "', '" & txtNombre.Text & "')"
	  cmd.ExecuteNonQuery()
	 Trans.Commit()
	 MessageBox.Show("Registro grabado correctamente", "Nuevo Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Information)
	  Reset()
	Catch Err As System.Exception
	  Trans.Rollback()
	  System.Windows.Forms.MessageBox.Show(Err.ToString())
	 MessageBox.Show("Error: " & Err.GetType().ToString() & " encontrado. No se actualizaron las tablas.", "Error en grabación", MessageBoxButtons.OK, MessageBoxIcon.Error)
	  Exit Try
	  Exit Sub
	End Try

I hope this code help you...

 

[sohaib]

Query should be updated at runtime....(Requery)

Hello
Mr.Jorge Villamizar and others!!!

Now wht u ve given to me there was one problem in tht it is not completely in English anyway thanx to reply and help but from tht point i know few things and added new record easily in the database now
But it has been added in the end....

I want to add it and then make it requery as we can do in VB6 i mean at runtime when i press button of "Add Record" it should be automatically i mean added into the database according to the query in my case it should be order by any column.... I think so u people understand tht problem very clearly so plz help me as u ve done last time....

C u then till the answer i will get....

ok
Bye

 

[sohaib]

Refreshing Recordset.....

Hi guys!!!

I have already present u a very simple problem but unfortunately i ve not any reply any more...so plz help in my case...

The problem is very clear, I have to add/delete record in/from database when the operation has been done it should be refreshed according to the query which we have written at the start.... I mean if we have a database of "Students" then when i have added a new record it is going to the end of the list i know it's not a problem but it should be refreshed after entering or in the same way after deleting..or updating.....let suppose according to the student's name alphabetically....so in that case refreshed by the student's name column...

so plz forward ur useful advices....
I hope for the positive response from u soon

ok
Bye

 

[jeva39]

I think really you dont´t forget VB 6... My opinion: why, after you Add,Update or Delete a record don't write a Sub or Function with a query than include Order By.... and simply, after each above operation you call this routine...

Really I don't know if I understand very well your problem. This solution is agree like I see you situation....And another thing, my english is not the best of the world....

Regards

 

[kulrom]

Hi Sohaib,

Take a look at the attached project ... and let me know is that what you need? ... cheers

 

[sohaib]

Hi Jorge and Kulrom!!!

Nice to hear from u people. First of all Jorge sorry, if u heart from one of my wording tht i have written about ur english...i dont want to say like that i mean u have written few things in columbian if i m not wrong...anyway sorry again..

Now Kulrom, You r right at ur point of view but the difference is tht u r using simple oledb with Sql Commands directly right and i m using Datarow...anyway there is no difference in any sort of things but refreshing means when i add a record in ur case as well it has been added in the last right;
After order by desc or asc it would be adjustable....i want to say when we have added record then after adding it should be refreshed and im using datarow....so tell me in this regard if u understand wht i want to ask??

ok
thnx & Regards

 

[kulrom]

then, while you add new record change strSQL string to: strSQL = "SELECT * FROM Table ORDER by FirstName ASC" ... 'or DESC whatever you like
That's it ... Cheers

note: i'm on machine which has not VS.NET and i'm only assuming ... about table name and fields ...

 

[kulrom]

Ok here i am ... i just back to my PC
Check this out:

Kind regards

 

[sohaib]

Hello Mr.Kulrom!!

It's really great as u ve given me u know it's very simple and ez to understand now it's working fine but the problem is related to the navigation buttons i mean Moving Next,Previous,First and last button in my last case i m easily using dataset with me.bindingcontext().position method to change the position but now in ur code no dataset is declarable i mean i ve to declare it or use someother thing in this regard......i hope u will give good advice after all...

ok
Bye

Again Thx & Regards.....

 

[jeva39]

This code is for your Navigate Buttons:

[color=green]' First[/color]
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] btnPri_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnPri.Click[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].IposAct = 0 [/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].CargaDatos()[/size]
[size=2]posChange()regInfo()[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
 
[size=2][color=seagreen]' Previous[/color]
[/size][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] btnAnt_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnAnt.Click[/size]
[size=2][color=#0000ff]If[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].IposAct = 0 [/size][size=2][color=#0000ff]Then[/color][/size]
[size=2]MessageBox.Show("First Record")[/size]
[size=2][color=#0000ff]Else[/color][/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].IposAct -= 1[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].CargaDatos()[/size]
[size=2]posChange()[/size]
[size=2]regInfo()[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
 
[size=2][color=#0000ff]'[color=green] Next[/color]
[/color][/size][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] btnSig_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnSig.Click[/size]
[size=2][color=#0000ff]If[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].IposAct = ([/size][size=2][color=#0000ff]Me[/color][/size][size=2].dst.Tables("Clientes").Rows.Count - 1) [/size][size=2][color=#0000ff]Then[/color][/size]
[size=2]MessageBox.Show("Last Record")[/size]
[size=2][color=#0000ff]Else[/color][/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].IposAct += 1[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].CargaDatos()[/size]
[size=2]posChange()[/size]
[size=2]regInfo()[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
 
[size=2][color=green]' Last[/color]
[/size][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] btnUlt_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnUlt.Click[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].IposAct = ([/size][size=2][color=#0000ff]Me[/color][/size][size=2].dst.Tables("Clientes").Rows.Count - 1)[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].CargaDatos()[/size]
[size=2]posChange()[/size]
[size=2]regInfo()
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
 
[size=2][color=#0000ff][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2][color=#000000] regInfo()[/color][/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].lblReg.Text = "Reg : " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].IposAct + 1 & " de " & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].dst.Tables("Clientes").Rows.Count[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]
 
[size=2][color=#0000ff]
[/color][/size][size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] posChange()[/size]
[size=2][color=#0000ff]Me[/color][/size][size=2].lblReg.Text = ((([/size][size=2][color=#0000ff]Me[/color][/size][size=2].BindingContext(dst, "Clientes").Position + 1).ToString + " of ") + [/size][size=2][color=#0000ff]Me[/color][/size][size=2].BindingContext(dst, "Clientes").Count.ToString)[/size]
[size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
[/color][/size][/color][/size]