how to update database when dataset is updated

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
hi
i am newbie in knowledge and also beginner in vb.net and it is my second question here

i am trying to make a software where users will register and then log on using their id
on the sign up form i plced some textboxes to get the user data
and through code i successfully create a new user but when i chek table in server explorer there is no new entry of user
plz tell me how can i update the database tables as the dataset is updated successfully. my code for creating a new user is

"Private Sub btnSignUp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSignUp.Click
' use sql connection to make a new transaction into database
Try
SqlConUp1.Open()



Dim dt As New DataTable
'dt = DsSecretsUp1.Tables("Users", "Secrets")
Dim newrow As DSSecrets.SecretsRow
newrow = DsSecretsUp1.Secrets.NewSecretsRow
newrow.Item("ID") = txtUserName.Text.Trim
newrow.Item("Name") = txtUserName.Text.Trim
newrow.Item("Address") = txtAddress.Text.Trim
newrow.Item("Email") = txtEmail.Text.Trim
newrow.Item("PhoneNo") = txtPhoneNo.Text.Trim
newrow.Item("password") = txtpassword.Text.Trim
DsSecretsUp1.Secrets.AddSecretsRow(newrow)

' the given two lines create run time error so its going to disabled
'now enable the binding data adapter to insert new record into database
' Me.BindingContext(DsSecretsUp1, "Secrets, Users").ResumeBinding()

If Not DsSecretsUp1.GetChanges(DataRowState.Added) Is Nothing Then
SqlDAUp.Update(DsSecretsUp1.GetChanges(DataRowState.Added))
End If

MsgBox("Sign up completed, you can now log on to your account ", vbOKOnly, "Congratulation")
Catch ex As Exception
MsgBox(ex.Message)
End Try
' show the login form and let user to logon to his account
frmLogin.Show()
Me.Close()
end sub"

i created the dataset and dataadapter by placing the objects on the form and the names of dataset are DsSecretsUp1 and the dataadapter is named sqldaUp

when i clicked on sign up button the msg showed to me that the registeration complete but in database the user data is not entered
the new data row is not added successfuully in hte database
i cant understand how to solv it
plz help
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Did you add a SQL Server Express database directly to your project? If so then you wouldn't expect to see any changes in the Server Explorer because that's not the database your application is connecting to. In that case, follow the first link in my signature to learn how local data files are managed.
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
i create my database in Microsoft Visual studio 2010 ultimate using server explorer menu
and i dont know either it is sql server express or something else
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
i am using compact sql server (come to know by a friend about it )
i place a sqldataadapter on the form and then create a connection (the address is given below with sqlcon1) and also create the dataset (dssecretsUp)which i used in coding of sign up form.
and the connection string is

the default string in sqlcon1 object is "Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Vu Data\CS\Miscall\DBMSMissxcall.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"

and i was prevously trying to use this also
"
Dim strcn As String = "Data Source=localhost;Initial Catalog=DBMSMissxcall;" & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strcn)
"

it didnot generate any exception or error
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
i am using compact sql server (come to know by a friend about it )
SQL Server Compact Edition (CE) is not what you're using. You're using SQL Server Express, and you're not using it very well. You should be adding the data file to your project, creating the typed DataSet and then using table adapters to retrieve and save the data. I suggest that you follow this walk-through:

Walkthrough: Connecting to Data in a SQL Server Express LocalDB Database (Windows Forms)
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
thanks for correction for naming that i am using SQL Server Express.
bro i am new in programming so plz i request to neglect my mistakes

here i study your shared link and the same i study from a dot net book
i successfully using this technology and retrieving data on the DataGridView on the profile tab
I again go through and find nothing.

after taking a deep look on my project i think i am mistaking in the SQL Query that i added in the DataAdapter for sign up form
the Query is
"SELECT Users.*, Secrets.Password, Secrets.PhoneNo
FROM Secrets INNER JOIN
Users ON Secrets.ID = Users.ID"
there are two tables in database
USERS[ID,Name,Address, Email] and SECRETS[PhoneNo,Password]

and
on immediate window when i run the program following msg is shown which i do not understand
"A first chance exception of type 'System.InvalidOperationException' occurred in System.Windows.Forms.dll"

i can share the screenshots to you for this problem.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
If you're saving data then you're calling Update on a table adapter or UpdateAll on a TableAdapterManager. If that call succeeds and the number it returns is not zero then the data is being saved. So, does the call succeed and what number does it return?
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
how to check the succeded call of tableadaptermanager

on the following lines i found this

If Not DsSecretsUp1.GetChanges(DataRowState.Added) Is Nothing Then
SqlDAUp.Update(DsSecretsUp1.GetChanges(DataRowState.Added))
End If


I drag the mouse on the datarowstate.added it provide the value "4"
it displays " Added data as system.data.datrowstate = 4 "
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
Sir

I only place the SQLDataAdapter on the form and Create a DataSet for my formlogin
and i think these functions built in the DataAdapter (SqlDAUp in the my code)

i did not wrote seperate functions for update and updateall

you are asking to check the function returning value of these functions
tell me where do i find the code of these functions if it is compiler generated
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
You don't have to find any code. You simply call the function and get the value it returns. You're trying to make this difficult when it's one of the most basic things in programming:
VB.NET:
Dim returnValue = SomeFunction()
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
Code>DsSecretsUp1.Secrets.AddSecretsRow(newrow)</Code>
this function is taking a parameter newrow and functon creating a new record in dataset
by using this line i m calling the function to execute and make changes in dataset

after this
in if statement command i am checking the change by sqldataadapter and then inserting new record into database using the Update method of the sqldatadapter
If Not DsSecretsUp1.GetChanges(DataRowState.Added) Is Nothing Then
<code>SqlDAUp.Update(DsSecretsUp1.GetChanges(DataRowStat e.Added))
End If</code>

plz tell me how to get the function value
i am new in knowledge and practice
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
sir
i have studied your link thoroughly and undertant it
now plz tell me that
in my project i didnot allow to create a copy of database file .
is that making error and not working
i f yes
then tell me how to remove the database securely so that again i can create a link to my application .
i have very less time left.
 

mkkb917

Member
Joined
Nov 24, 2012
Messages
18
Location
Pakistan
Programming Experience
Beginner
sir
i change my code and use the paramerized method in my new code
the code is working fine
and i was searching in wrong place in database file .
at last i found on internet that the copy of database file is updated each time when the application runs because application copy the origional database file into its directory
now i checked it and its work fine
i am very happy that by using your signature help i come to solve this problem
thanks sir
 
Top Bottom