MBR IT/.NET 247 : Help with Data accress on ASPFriends.com 'ngfx-sqlclient' list

Help with Data accress

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

hassan.hussein@zurich.com

-- Moved from [aspngdata] to [ngfx-sqlclient] by devin <Click here to reveal e-mail address> --

Hi

I am trying to write to a database. Luck of knowledge is letting me down.

I have three tables T_PERSON, T_SKILLS and T_PERSON_SKILLS. I have a page
with a two textboxes for person name and person ID and a listbox containing
skills user to select from. so far my code looks like this

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSubmit.Click
Dim DS As New DataSet()
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand
Dim MyReader As SqlDataAdapter

MyConnection = New SqlConnection
("server=HHU7848;database=FirstDotNet;Trusted_Connection=yes")
MyCommand.Connection.Open()
MyCommand = New SqlCommand("INSERT INTO T_PERSON
(PERSON_NAME,LOGONID) VALUES ('" & Me.TextBox1.Text & "','" & Me
.TextBox2.Text & "')", MyConnection)
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()

'MyReader = New SqlDataAdapter(MyCommand)
'MyReader.Fill(DS)
'MyReader.Update(DS)
'MyConnection.Close()
End Sub

I would like to insert skills into T_SKILLS table but I don't want to
create another command object. I would like one call to DB. Is this
possible. But I know that datasets are disconnected so how do I insert
stuff into the dataset and the update the source db.

Help well appreciated.

Thanks
Hassan

Reply to this message...

Williams Kyle M Contr OC-ALC/LIP

Try creating a stored procedure that passes in all values and inserts into
all of the tables. Plus stored procedures execute quicker than hard coding
your SQL syntax into your code. Your only hurdle would be if users selected
multiple skills at the same time. If this is the case you would probably
need a For Each statement in your click event so each skill would be added
to the T_Person_Skills DB and still be tied to a particular person.

Kyle

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, June 14, 2002 5:15 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Help with Data accress

-- Moved from [aspngdata] to [ngfx-sqlclient] by devin
<Click here to reveal e-mail address> --

Hi

I am trying to write to a database. Luck of knowledge is letting me down.

I have three tables T_PERSON, T_SKILLS and T_PERSON_SKILLS. I have a page
with a two textboxes for person name and person ID and a listbox containing
skills user to select from. so far my code looks like this

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSubmit.Click
Dim DS As New DataSet()
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand
Dim MyReader As SqlDataAdapter

MyConnection = New SqlConnection
("server=HHU7848;database=FirstDotNet;Trusted_Connection=yes")
MyCommand.Connection.Open()
MyCommand = New SqlCommand("INSERT INTO T_PERSON
(PERSON_NAME,LOGONID) VALUES ('" & Me.TextBox1.Text & "','" & Me
.TextBox2.Text & "')", MyConnection)
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()

'MyReader = New SqlDataAdapter(MyCommand)
'MyReader.Fill(DS)
'MyReader.Update(DS)
'MyConnection.Close()
End Sub

I would like to insert skills into T_SKILLS table but I don't want to
create another command object. I would like one call to DB. Is this
possible. But I know that datasets are disconnected so how do I insert
stuff into the dataset and the update the source db.

Help well appreciated.

Thanks
Hassan

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

hassan.hussein@zurich.com

Cheers Kyle

I have never actually written a stored procedure but I will give it a go
with the BOL help

Hassan

Williams Kyle M
Contr OC-ALC/LIP To: "ngfx-sqlclient" <Click here to reveal e-mail address>
<Kyle.Williams@tink cc:
er.af.mil> Subject: [ngfx-sqlclient] RE: Help with Data accress

14/06/2002 04:52 PM
Please respond to
"ngfx-sqlclient"

Try creating a stored procedure that passes in all values and inserts into
all of the tables. Plus stored procedures execute quicker than hard coding
your SQL syntax into your code. Your only hurdle would be if users selected
multiple skills at the same time. If this is the case you would probably
need a For Each statement in your click event so each skill would be added
to the T_Person_Skills DB and still be tied to a particular person.

Kyle

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Friday, June 14, 2002 5:15 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] Help with Data accress

-- Moved from [aspngdata] to [ngfx-sqlclient] by devin
<Click here to reveal e-mail address> --

Hi

I am trying to write to a database. Luck of knowledge is letting me down.

I have three tables T_PERSON, T_SKILLS and T_PERSON_SKILLS. I have a page
with a two textboxes for person name and person ID and a listbox containing
skills user to select from. so far my code looks like this

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSubmit.Click
Dim DS As New DataSet()
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand
Dim MyReader As SqlDataAdapter

MyConnection = New SqlConnection
("server=HHU7848;database=FirstDotNet;Trusted_Connection=yes")
MyCommand.Connection.Open()
MyCommand = New SqlCommand("INSERT INTO T_PERSON
(PERSON_NAME,LOGONID) VALUES ('" & Me.TextBox1.Text & "','" & Me
.TextBox2.Text & "')", MyConnection)
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()

'MyReader = New SqlDataAdapter(MyCommand)
'MyReader.Fill(DS)
'MyReader.Update(DS)
'MyConnection.Close()
End Sub

I would like to insert skills into T_SKILLS table but I don't want to
create another command object. I would like one call to DB. Is this
possible. But I know that datasets are disconnected so how do I insert
stuff into the dataset and the update the source db.

Help well appreciated.

Thanks
Hassan

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

	System.Data.DataSet
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.EventArgs
	System.Object

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com