how to update database?

[dyc]

What the step should I take for if i wanna to update my database?The situation is something like this..I have a "User-Interface" form which to let user to insert data on it,for example is like a textbox.when i insert the data on the textbox,i wanna the data to store into the database if that is a new data while if that is a data that ald stored inside the database,then just need to update it...can give some examples code for me as references?

Thanks!! ;-)

[nairda]

What the step should I take for if i wanna to update my database?The situation is something like this..I have a "User-Interface" form which to let user to insert data on it,for example is like a textbox.when i insert the data on the textbox,i wanna the data to store into the database if that is a new data while if that is a data that ald stored inside the database,then just need to update it...can give some examples code for me as references?

Thanks!! ;-)

Hi dyc,

You'll need 1 Command Button (CmdSave) so you can trigger the UPDATE or ADDNEW action from your UI form to your database.
Once you clicked this CmdSave, VB will search for the matching data. If there is no matching data, then VB will save the data as a new data in your database (ADDNEW action). In the contrary, if there's a matching data between your input and your database, VB will be done an UPDATE action to the data.

Private Sub CmdSave_Click()
    Dim conec As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "mydatabase.mdb'"
    conec.CursorLocation = adUseClient
    conec.Open
    
    rs.Open "select * from STUDENT where STUDENTCODE='" & Text1.Text & "'", conec, adOpenKeyset, adLockOptimistic
    
    If Text1.text = "" Then
        MsgBox "Please fill the Student Code Number", vbCritical,
        Else
          With rs
	         ' Search for identical/matching STUDENTCODE
	         If rs.RecordCount = 0 then    '---> If there's no identical/matching STUDENTCODE, then save as new data
		      .AddNew
	         End If
                !STUDENTCODE = Text1.Text
	            !STUDENTNAME = Text2.Text
	            !STUDENTADDRESS = Text3.Text
                .Update
          End With
     End If
End Sub

I hope these code will help you get the picture.

NAIRDA

[dyc]

how about if I'm using SQL database in the visual studio 2005?How do I do the connection string and those related command?and do I also need to use tableadapter?
Thanks...

[debasisdas]

The process is same .

Only thing is that u need to change the connection string.

To do this best way is

1.open notepad.
2.save the file with .UDL extension.
3.then open the file and just follow certain steps (which is so easy).
4. close the file.
5.right click and open with notepad.
6.copy after the first line

u get the connection string for sql-server.

next the process is so simple which u can get from any good material/book

[dyc]

Dim objConnection As SqlConnection = New _
SqlConnection(" server=(128-10);database=YY ")
Dim objCommand As SqlCommand = New SqlCommand()
Dim objDataAdapter As New SqlDataAdapter( )
objDataAdapter. SelectCommand = New SqlCommand()
objConnection.O pen()
objCommand.Conn ection = objConnection
objDataAdapter. SelectCommand.C onnection = objConnection
objDataAdapter. SelectCommand.C ommandText = "InsertYY"
objDataAdapter. SelectCommand.C ommandType = CommandType.Sto redProcedure
' automatically create update/delete/insert commands
Dim objCommandBuild er As SqlCommandBuild er = New SqlCommandBuild er(objDataAdapt er)


when i run this program,it ends up wif an error msg :

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have no idea what have happened ...Is there something wrong with my code??

[dyc]

Can we do something like when we click a update button ,then the data we wanna to update is extracted from a long string and I have chopped it into individual string with substring method.
How do I do if i wanna to update the database?
thanks!!