INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

VB.NET AND MYSQL

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

31 Replies - 19298 Views - Last Post: 30 October 2009 - 09:34 PM Rate Topic: -----

#1 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 06:57 AM

this is problem i had encountered. i am a beginner and i want to have some help from anyone here. please give me an idea or help on how i will formulate the source code of my program i am creating using vb.net and mysql. the problem is on how to INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS... having a primary key and a foreign key.. thanks guys i hope and need your help..
Is This A Good Question/Topic? 0
  • +

Replies To: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

#2 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 07:37 AM

I suggest you read up on Datasets, Databinding, TableAdapters and BindingSources and Database Concurrency.

Otherwise you can do it all with code. (This means you need to get the PK for your first table and insert it into the related table with the rest of the data and YOU will need to manage it all with your code.)

This is quite a inticate story and a lot can be told about it.
Was This Post Helpful? 0
  • +
  • -

#3 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 07:43 AM

View Postmotcom, on 22 Sep, 2009 - 06:37 AM, said:

I suggest you read up on Datasets, Databinding, TableAdapters and BindingSources and Database Concurrency.

Otherwise you can do it all with code. (This means you need to get the PK for your first table and insert it into the related table with the rest of the data and YOU will need to manage it all with your code.)

This is quite a inticate story and a lot can be told about it.


thank you so much for your idea.. it's a pleasure to have such shared idea from you.. thank you very much.. regarding datasets, data binding, table adapters and binding sources and database concurrency.. in connection with those can you share me a tutorial regarding it so i can extend my knowledge.. thank you..
Was This Post Helpful? 0
  • +
  • -

#4 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 07:49 AM

Hey Method,

First off make sure you have the .Net Connector for MySQL, will make doing your Data Set class (XSD) very easy to work with. I generally design my DataSets visually and then create layers to the data through my BLL and DAL.

For info on Dataset and family goto ASP.Net and do a search for "DAL" or "BLL". Although it maybe a little more high lvl than what you are might be looking for, the first couple of a video tut's will get you the idea of using the visual DataSet designer (VDD) for profiling.

Also, With the VDD you can developing queries visually to see how they would work and if you have data in the tables, then you can see what the result of the query would be.
Was This Post Helpful? 0
  • +
  • -

#5 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 07:54 AM

View Postwoodjom, on 22 Sep, 2009 - 06:49 AM, said:

Hey Method,

First off make sure you have the .Net Connector for MySQL, will make doing your Data Set class (XSD) very easy to work with. I generally design my DataSets visually and then create layers to the data through my BLL and DAL.

For info on Dataset and family goto ASP.Net and do a search for "DAL" or "BLL". Although it maybe a little more high lvl than what you are might be looking for, the first couple of a video tut's will get you the idea of using the visual DataSet designer (VDD) for profiling.

Also, With the VDD you can developing queries visually to see how they would work and if you have data in the tables, then you can see what the result of the query would be.


whiw.. that was a nice idea that came from you.. well i'll try to do so for what you had said.. thank you very much..
Was This Post Helpful? 0
  • +
  • -

#6 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 08:00 AM

Hi,

Here is the reference to good tutorials and examples using vb.net 2008. It assumes MSSQL but the MySQL Connector will do the trick as well.

http://msdn.microsof...y/h0y4a0f6.aspx

Hope this helps.......
Was This Post Helpful? 0
  • +
  • -

#7 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 08:02 AM

hello.. ammm.. thanks again.. i'll try to read on it..
Was This Post Helpful? 0
  • +
  • -

#8 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 05:49 PM

whew.. this makes me crazy..

consider this situation:

1. i created one table
2. i created another table
3. i related this two tables with PK ID_no in table1 and ID_no as a FK in table2
4. now i tried to insert date into both tables but it did not insert? why?
5. i used visual basic 2008 and mysql
6. can you help me guys.. inserting into two tables having PK and FK
Was This Post Helpful? 0
  • +
  • -

#9 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 22 September 2009 - 08:07 PM

i usually dont explicitely define foreign keys (FK) cause they can cause some problems, if your not careful and can remember all the FK references ;). I usually make the referential annotation in the table into a column for integrity for queries.

Ill setup a table like
id, int, autonumber, identity
name, varchar(50)
description, varchar(1000)



id, bigint, autonumber, identity
itm_1, int
itm_1_qty, int
itm_2, int
itm_2_qty, int



then code something like this so the user doesnt see all the key values but only the item information
SELECT itm1.name as item1, itm1.description as descript1, purch.itm_1_qty as item1qty, itm2.name as item2, itm2.description as descript2, purch.itm_2_qty as item2qty
FROM purchases as purch
  LEFT OUTER JOIN items AS itm1 ON itm.id = purch.itm_1
  LEFT OUTER JOIN items AS itm2 ON itm.id = purch.itm_2
WHERE [email protected]



Mind you this is just an example of what i usually do in this instance. There are various ways of doing this same example with various SQL commands. I generally like using LEFT OUTER JOIN as it goes from the secondary tables and obtains information basedon the primary tables key data item.

To help us, can you give us your table designs? so we can make sure its not your queries? As well show us your SQL statement that is not working.

As far as Inserts, ill have to do some research but i believe you can use the Join statements similarly but with some different SQL commands. Again, we will need to be given an example of your tables and the pertinent fields in question to help you design your INSERT statement.
Was This Post Helpful? 0
  • +
  • -

#10 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 04:14 AM

oh thanks for the information.. inner join rings the bell whew.. ammm how could i send my database? can i send the mysql dump here or the database relational schema here?
Was This Post Helpful? 0
  • +
  • -

#11 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 04:54 AM

i would stay away from dumping you data into the thread. Just give us the pertinent field in the tables you are having problems with and the query that isnt giving you the result you want.
Was This Post Helpful? 0
  • +
  • -

#12 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 06:31 AM

the tables are:


studentinfo

attributes of studentinfo[/b]

StudentId PK
DepartmentId
Lastname
Firstname
Middlename
CodeNo
SenesterId
YearLevelId
SchoolYearId
Dateofregistration
Address
Campus


academicrecords

asstributes of academicrecords[/b]

PrimaryEduc
IntermediateEduc
1stYear
2ndYear
3rdYear
4thYear
Vocational
College
StudentId FK


personalinfo

attributes of personalinfo[/b]

StudentId FK
CivilStatus
Gender
Nationality
DateofBirth
PlaceofBirth


studentguardian

attributes of studentguardian[/b]

StudentId FK
PersonalSupporting
Relation
Address
Occupation
Contac_No


parents

attibutes of parents[/b]

Father
FatherOccupation
Mother
MoterOccupation
Address
StudentId FK


1. if i input a StudentId in the studentinfo table, the Id number i just had input within it will also append or input
in the other tables, because they are all related to each other. so than if i will try to seacrh for records in the other table i will just use their ID number right away.
2. so when i will delete and update records on my primary table, other tables will follow.
3. it looks so newbie. well i am a newbie trying to extend my knowledge on it. i hope to build up more on my knowledge with database with vb.net.
4. here are my source code.

THE REGISTER FORM wherein the tables i just mentioned are within this FORM

Imports System
Imports System.Data
Imports System.Windows.Forms
Imports MySql.Data.MySqlClient
Public Class Registrar
Private myConn As MySqlConnection
Private myConn2 As MySqlConnection
Private myConn3 As MySqlConnection
Private myConn4 As MySqlConnection
Private myConn5 As MySqlConnection
Private myCmd As New MySqlCommand
Private myCmd2 As New MySqlCommand
Private myCmd3 As New MySqlCommand
Private myCmd4 As New MySqlCommand
Private myCmd5 As New MySqlCommand
Private constr As String

'Connection Function for Openning Database
Private Sub Connectstr()

Dim strConn As String
strConn = "server=localhost;database=asirs_dbase;uid=root;pwd=;"
myConn = New MySqlConnection
myConn.ConnectionString = strConn

myConn2 = New MySqlConnection
myConn2.ConnectionString = strConn

myConn3 = New MySqlConnection
myConn3.ConnectionString = strConn

myConn4 = New MySqlConnection
myConn4.ConnectionString = strConn

myConn5 = New MySqlConnection
myConn5.ConnectionString = strConn

Try
myConn.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

Try
myConn2.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Try
myConn3.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Try
myConn4.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Try
myConn5.Open()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
'Connection Function for Closing Database
Private Sub CloseConnection()
myConn.Close()
myConn = Nothing
End Sub
Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
'if the student is old studnt then only few boxes/textboxes,comboboxes letting the user to input his ID Number for homing features
'(when ID Number is inputed the information of the student will automatically pop up to the remaining textboxes).
If CheckBox1.Checked = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = False
btnupdate.Enabled = True
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox4.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
TextBox10.Enabled = False
TextBox11.Enabled = False
TextBox12.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
TextBox15.Enabled = False
TextBox16.Enabled = False
TextBox17.Enabled = False
TextBox18.Enabled = False
TextBox19.Enabled = False
TextBox20.Enabled = False
TextBox21.Enabled = False
TextBox22.Enabled = False
TextBox23.Enabled = False
TextBox24.Enabled = False
TextBox25.Enabled = False
TextBox26.Enabled = False
TextBox27.Enabled = False
TextBox28.Enabled = True
TextBox29.Enabled = False
ComboBox1.Enabled = True
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = True
ComboBox5.Enabled = True
TextBox1.Enabled = True
ElseIf CheckBox1.Checked = False Then
CheckBox2.Enabled = True
CheckBox3.Enabled = True
btnupdate.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox4.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
TextBox10.Enabled = False
TextBox11.Enabled = False
TextBox12.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
TextBox15.Enabled = False
TextBox16.Enabled = False
TextBox17.Enabled = False
TextBox18.Enabled = False
TextBox19.Enabled = False
TextBox20.Enabled = False
TextBox21.Enabled = False
TextBox22.Enabled = False
TextBox23.Enabled = False
TextBox24.Enabled = False
TextBox25.Enabled = False
TextBox26.Enabled = False
TextBox27.Enabled = False
TextBox28.Enabled = False
TextBox29.Enabled = False
TextBox1.Enabled = False
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
End If
End Sub
Private Sub CheckBox2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox2.CheckedChanged
'if the student is New all the boxes are enabled giving the user to input data in them for storages
If CheckBox2.Checked = True Then
CheckBox1.Enabled = False
CheckBox3.Enabled = False
btnreg.Enabled = True
TextBox2.Enabled = True
TextBox3.Enabled = True
TextBox4.Enabled = True
TextBox5.Enabled = True
TextBox6.Enabled = True
TextBox7.Enabled = True
TextBox8.Enabled = True
TextBox9.Enabled = True
TextBox10.Enabled = True
TextBox11.Enabled = True
TextBox12.Enabled = True
TextBox13.Enabled = True
TextBox14.Enabled = True
TextBox15.Enabled = True
TextBox16.Enabled = True
TextBox17.Enabled = True
TextBox18.Enabled = True
TextBox19.Enabled = True
TextBox20.Enabled = True
TextBox21.Enabled = True
TextBox22.Enabled = True
TextBox23.Enabled = True
TextBox24.Enabled = True
TextBox25.Enabled = True
TextBox26.Enabled = True
TextBox27.Enabled = True
TextBox28.Enabled = True
TextBox29.Enabled = True
TextBox1.Enabled = True
ComboBox1.Enabled = True
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = True
ComboBox5.Enabled = True
ElseIf CheckBox2.Checked = False Then
CheckBox1.Enabled = True
CheckBox3.Enabled = True
btnreg.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox4.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
TextBox10.Enabled = False
TextBox11.Enabled = False
TextBox12.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
TextBox15.Enabled = False
TextBox16.Enabled = False
TextBox17.Enabled = False
TextBox18.Enabled = False
TextBox19.Enabled = False
TextBox20.Enabled = False
TextBox21.Enabled = False
TextBox22.Enabled = False
TextBox23.Enabled = False
TextBox24.Enabled = False
TextBox25.Enabled = False
TextBox26.Enabled = False
TextBox27.Enabled = False
TextBox28.Enabled = False
TextBox29.Enabled = False
TextBox1.Enabled = False
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
End If
End Sub
Private Sub CheckBox3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox3.CheckedChanged
If CheckBox3.Checked = True Then
CheckBox1.Enabled = False
CheckBox2.Enabled = False
btnupdate.Enabled = True
TextBox2.Enabled = True
TextBox3.Enabled = True
TextBox4.Enabled = True
TextBox5.Enabled = True
TextBox6.Enabled = True
TextBox7.Enabled = True
TextBox8.Enabled = True
TextBox9.Enabled = True
TextBox10.Enabled = True
TextBox11.Enabled = True
TextBox12.Enabled = True
TextBox13.Enabled = True
TextBox14.Enabled = True
TextBox15.Enabled = True
TextBox16.Enabled = True
TextBox17.Enabled = True
TextBox18.Enabled = True
TextBox19.Enabled = True
TextBox20.Enabled = True
TextBox21.Enabled = True
TextBox22.Enabled = True
TextBox23.Enabled = True
TextBox24.Enabled = True
TextBox25.Enabled = True
TextBox26.Enabled = True
TextBox27.Enabled = True
TextBox28.Enabled = True
TextBox29.Enabled = True
TextBox1.Enabled = True
ComboBox1.Enabled = True
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = True
ComboBox5.Enabled = True
ElseIf CheckBox3.Checked = False Then
CheckBox1.Enabled = True
CheckBox2.Enabled = True
btnupdate.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox4.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
TextBox10.Enabled = False
TextBox11.Enabled = False
TextBox12.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
TextBox15.Enabled = False
TextBox16.Enabled = False
TextBox17.Enabled = False
TextBox18.Enabled = False
TextBox19.Enabled = False
TextBox20.Enabled = False
TextBox21.Enabled = False
TextBox22.Enabled = False
TextBox23.Enabled = False
TextBox24.Enabled = False
TextBox25.Enabled = False
TextBox26.Enabled = False
TextBox27.Enabled = False
TextBox28.Enabled = False
TextBox29.Enabled = False
TextBox1.Enabled = False
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
End
End Sub
Private Sub Registrar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'connects to the dbase as soon as the form loads up(not yet complete due to some luck of codes and source codes).
Connectstr()
Dim searchSTR As String
searchSTR = "SELECT * FROM studentinfo"
Dim myCmd As New MySqlCommand(searchSTR, myConn)
Dim myReader As MySqlDataReader
myReader = myCmd.ExecuteReader
CloseConnection()
TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox4.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
TextBox10.Enabled = False
TextBox11.Enabled = False
TextBox12.Enabled = False
TextBox13.Enabled = False
TextBox14.Enabled = False
TextBox15.Enabled = False
TextBox16.Enabled = False
TextBox17.Enabled = False
TextBox18.Enabled = False
TextBox19.Enabled = False
TextBox20.Enabled = False
TextBox21.Enabled = False
TextBox22.Enabled = False
TextBox23.Enabled = False
TextBox24.Enabled = False
TextBox25.Enabled = False
TextBox26.Enabled = False
TextBox27.Enabled = False
TextBox28.Enabled = False
TextBox29.Enabled = False
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
End Sub
Private Sub btnreg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreg.Click
Dim mycmdSave As MySqlCommand
Dim saveStr As String
Dim searchSTR As String
Dim Found As Boolean
Try
Connectstr()
searchSTR = "SELECT studentinfo.*, academicrecords.*,personalinfo.*,studentguardian.*,parents.* from studentinfo,inner join StudentId on academicrecords.StudentId = studentinfo.StudentId,inner join StudentId on personalinfo.StudentId = studentinfo.StudentId,inner join StudentId on studentguardian.StudentId=studentinfo.StudentI,inner join StudentId on parents.StudentId=studentinfo.StudentId"
Dim myCmd As New MySqlCommand(searchSTR, myConn)
Dim myReader As MySqlDataReader
myReader = myCmd.ExecuteReader


While myReader.Read
Found = True
End While

If Found = False Then
'if one of the textboxes are not filled then this message below will inform the user that one of the fileds have no values/datas inputed in it.
If (TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or TextBox6.Text = "" Or TextBox7.Text = "" Or TextBox8.Text = "" Or TextBox9.Text = "" Or TextBox10.Text = "" Or TextBox11.Text = "" Or TextBox12.Text = "" Or TextBox13.Text = "" Or TextBox14.Text = "" Or TextBox15.Text = "" Or TextBox16.Text = "" Or TextBox17.Text = "" Or TextBox18.Text = "" Or TextBox19.Text = "" Or TextBox20.Text = "" Or TextBox21.Text = "" Or TextBox22.Text = "" Or TextBox23.Text = "" Or TextBox24.Text = "" Or TextBox25.Text = "" Or TextBox26.Text = "" Or TextBox27.Text = "" Or TextBox29.Text = "") Then
MsgBox("Please Fill Up All the Neccesary Fields...!", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "WARNING...!")
Else
CloseConnection()
Connectstr()
'data are inputed inside 4 different tables at the same time the tables studentinfo,academicrecords,personalinfo and parents
saveStr = "Insert Into studentinfo Values ('" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & ComboBox1.Text & "','" & ComboBox3.Text & "','" & ComboBox2.Text & "','" & ComboBox4.Text & "')"
saveStr = "insert into academicrecords values('" & TextBox20.Text & "','" & TextBox21.Text & "','" & TextBox22.Text & "','" & TextBox23.Text & "','" & TextBox24.Text & "','" & TextBox25.Text & "','" & TextBox26.Text & "','" & TextBox27.Text & "') "
saveStr = "insert into personalinfo values('" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox29.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "')"
saveStr = "insert into parents values('" & TextBox11.Text & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "','" & TextBox15.Text & "')insert into studentguardian values ('" & TextBox16.Text & "','" & TextBox17.Text & "','" & TextBox18.Text & "','" & TextBox19.Text & "')"
mycmdSave = New MySqlCommand
mycmdSave.Connection = Me.myConn
mycmdSave.CommandType = CommandType.Text
mycmdSave.CommandText = saveStr
mycmdSave.ExecuteNonQuery()
mycmdSave.Dispose()
MsgBox("Records have been Saved...!", MsgBoxStyle.Information, "Saving...")
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
TextBox21.Text = ""
TextBox22.Text = ""
TextBox23.Text = ""
TextBox24.Text = ""
TextBox25.Text = ""
TextBox26.Text = ""
TextBox27.Text = ""
TextBox29.Text = ""
End If
Else
MsgBox("Record Already Exists...!", MsgBoxStyle.Critical, "WARNING...!")
End If
'Closing Connection Strings
myReader.Close()
CloseConnection()
myCmd.Dispose()
myCmd = Nothing
Catch
End Try
End Sub
Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

End Sub
End Class


HERE IS MY MODULE

Module Connection
Public Function GetConnectionString() As String
Return "server=localhost;database=asirs_dbase;uid=root;pwd=;"
End Function
Private Function GetDataConnection() As MySql.Data.MySqlClient.MySqlConnection
Return New MySql.Data.MySqlClient.MySqlConnection(Connection.GetConnectionString())
End Function

Public Function ConString() As String
Dim cString As String
Dim con As New MySQL_Registry.Registry

Try
cString = "server=localhost;database=asirs_dbase;uid=root;pwd=;"
'cString = "server=" & con.ServerHost & ";uid=" & con.UserName & ";password=" & con.Password & ";database=" & con.Database
ConString = cString

Catch ex As Exception

End Try
End Function
End Module
Was This Post Helpful? 0
  • +
  • -

#13 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 06:51 AM

Dude,

Why do you post as methodman and redoc???

I replied you on your previous post...

BIG NOTE: give your controls meaning full names -> this will help you or any other programmer understand what
is going on in your code.

e.g. txtFirstName (for a textbox), cboCountry (for a Combobox)

Look at the comments in the code i posted...

'You set saveStr to "Insert...." without actually sending it to the database
saveStr = "Insert Into studentinfo Values ('" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & ComboBox1.Text & "','" & ComboBox3.Text & "','" & ComboBox2.Text & "','" & ComboBox4.Text & "')"

'Here you overwrite the saveStr with another "Insert..." without actually sending it to the database
saveStr = "insert into academicrecords values('" & TextBox20.Text & "','" & TextBox21.Text & "','" & TextBox22.Text & "','" & TextBox23.Text & "','" & TextBox24.Text & "','" & TextBox25.Text & "','" & TextBox26.Text & "','" & TextBox27.Text & "') "

'Again overwrite the saveStr with another "Insert..." without actually sending it to the database
saveStr = "insert into personalinfo values('" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox29.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "')"

'And agin overwrite the saveStr with another "Insert..." without actually sending it to the database
saveStr = "insert into parents values('" & TextBox11.Text & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "','" & TextBox15.Text & "')insert into studentguardian values ('" & TextBox16.Text & "','" & TextBox17.Text & "','" & TextBox18.Text & "','" & TextBox19.Text & "')"

'Now here, you execute the last saveStr to the database. all the previos ones have been overwritten.
mycmdSave = New MySqlCommand
mycmdSave.Connection = Me.myConn
mycmdSave.CommandType = CommandType.Text
mycmdSave.CommandText = saveStr
mycmdSave.ExecuteNonQuery()
mycmdSave.Dispose()




Was This Post Helpful? 1
  • +
  • -

#14 methodman3130   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 22-September 09

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 07:09 AM

ahmmm thanks for the comment or advise.. than will help.. can i ask for a website where i can read more on vb.net and mysql? can you share more ideas on the code i had done.. is my module code right and the other code i've done?

you mean that the control names are just like the naming convention of a certain control? ahmm thank you for the advise again.. i'll append on it.. nice one..

thank you motcom and the others.. i've learned a lot from you and with dreamincode.. it's so nice to be hear wonderful comments from good one's within this forum.. whew..
Was This Post Helpful? 0
  • +
  • -

#15 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: INSERT INTO TWO OR MORE TABLES WITH RELATIONSHIPS

Posted 23 September 2009 - 07:15 AM

Quote

you mean that the control names are just like the naming convention of a certain control? ahmm thank you for the advise again.. i'll append on it.. nice one..


Have a look at

Naming Conventions
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3