Updating records from database to textbox

[sonyeo]

When i run my program there's an error in

conn.open()

and tells "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Please help me to solve this problem...
Here's my code:

Dim ds As New DataSet
        Dim dbSource As String
        Dim dbProvider As String
        Dim conn As New OleDb.OleDbConnection
        Dim dbcursor As Integer = 0
        Dim da As New OleDb.OleDbDataAdapter
        Dim cmdUpdate As New OleDb.OleDbCommand
        Dim msg1 As String
        Dim style As MsgBoxStyle
        Dim result As MsgBoxResult

        If conn.State = ConnectionState.Open Then
            GoTo cont
        Else
            If conn.State = ConnectionState.Closed Then
                dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
                dbSource = "data source=.\sqlexpress; integrated security = true; attachdbfilename=|datadirectory|\database.mdf; user instance = true;"
                conn.ConnectionString = dbProvider & dbSource
                conn.Open()
                da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM table1 WHERE id like '%" + "%'")
                da.SelectCommand.Connection = conn
                Dim mycomand As New OleDb.OleDbCommandBuilder(da)
                da.Fill(ds, "table1")
cont:           cmdUpdate.CommandText = "UPDATE table1 " & _
                "SET Processor = '" & TextBox2.Text & "'" & _
                ", Memory = '" & TextBox3.Text & "'" & _
                ", VideoCard = '" & TextBox4.Text & "'" & _
                ", HardDrive = '" & TextBox5.Text & "'" & _
                ", AntiVirus = '" & TextBox6.Text & "'" & _
                cmdUpdate.CommandType = Data.CommandType.Text
                cmdUpdate.Connection = conn
                cmdUpdate.ExecuteNonQuery()
                cmdUpdate.Dispose()
                conn.Close()
                msg1 = "updated successfully."
                result = MsgBox(msg1, style)
                'Catch
                MessageBox.Show("An error occured while updating!")
                'End Try
            End If
        End If

[ss125]

dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "data source=.\sqlexpress; integrated security = true; attachdbfilename=|datadirectory|\database.mdf; user instance = true;"
conn.ConnectionString = dbProvider & dbSource

I hope the problem is with your connection string.

Try the below link

Check it out

Hope it will help you...

Have a happy coding... :-D

[sonyeo]

the problem in this program is when i edit a records everything will gonna change..
because i think the reason is i didn't put the primary key.. my primary key is "id"...
could you help me to fix my program??

Here's my code snippet:

Module1.connect()

        Dim ValueToUpdate1 As String
        Dim ValueToUpdate2 As String
        Dim ValueToUpdate3 As String
        Dim ValueToUpdate4 As String
        Dim ValueToUpdate5 As String

        ValueToUpdate1 = TextBox2.Text.ToString()
        ValueToUpdate2 = TextBox3.Text.ToString()
        ValueToUpdate3 = TextBox4.Text.ToString()
        ValueToUpdate4 = TextBox5.Text.ToString()
        ValueToUpdate5 = TextBox6.Text.ToString()


        sqlcmd = New SqlCommand("UPDATE table1 SET [Processor]=@Processor, [Memory]=@Memory, [VideoCard]=@VideoCard, [HardDrive]=@HardDrive, [AntiVirus]=@AntiVirus", sqlconn)


        sqlcmd.Parameters.AddWithValue("@Processor", ValueToUpdate1)
        sqlcmd.Parameters.AddWithValue("@Memory", ValueToUpdate2)
        sqlcmd.Parameters.AddWithValue("@VideoCard", ValueToUpdate3)
        sqlcmd.Parameters.AddWithValue("@HardDrive", ValueToUpdate4)
        sqlcmd.Parameters.AddWithValue("@AntiVirus", ValueToUpdate5)


        sqlcmd.ExecuteNonQuery()
        MsgBox("Record Updated Successfully")
        sqlconn.Close()

[ss125]

sqlcmd = New SqlCommand("UPDATE table1 SET [Processor]=@Processor, [Memory]=@Memory, [VideoCard]=@VideoCard, [HardDrive]=@HardDrive, [AntiVirus]=@AntiVirus", sqlconn)

Here is the problem. Use a where clause in the update query, else it will update all the records in the table.So the modifies query string may look like

sqlcmd = New SqlCommand("UPDATE table1 SET [Processor]=@Processor, [Memory]=@Memory, [VideoCard]=@VideoCard, [HardDrive]=@HardDrive, [AntiVirus]=@AntiVirus where [id]=@id", sqlconn)

Hope this solves your problem...

Have a happie coding...:-D

[sonyeo]

sir, i already try that code but it cause me an error...
am i going to elaborate the "id" just like the other items that i made?
Just like this:

Dim ValueToUpdate5 As String
ValueToUpdate5 = TextBox6.Text.ToString()
sqlcmd.Parameters.AddWithValue("@AntiVirus", ValueToUpdate5)

[ss125]

Man...U need to keep an unique field for an update statement.

So do it as follows.

1) Add a hidden textbox which holds the id of the item.

2) declare a string variable prod_id.

3) Then use it as I suggested in the above reply.

It will surely work.

Probably, your mistake will be executing the query without the value to the id field.So do as I said above.

Hope this solves your problem...

Have a happie coding...:-D

[sonyeo]

Thanks a lot sir..
i have encountered another problem from my other program. When im gonna refresh the database explorer
it says that:

Server Explorer Error

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Please help me to solve this kind of hindrance~~

[ss125]

Anyway I am not a pro at this.

The reasons for throwing this issue could be wrong server name, disabled remote connection
and firewall blocking.

Could you please follow the steps below to solve this issue?

· Check the server on which SQL Server is running can be accessible. You can use ping command to test that. For instance, ping <computer_name> or ping <IP_address>. The ping command may be block by the firewall, make sure ICMP is enabled in the firewall. More info, check: http://technet.microsoft.com/en-us/library/cc739791%28v=ws.10%29.aspx#BKMK_4.

· Choose appropriate protocol

· Configure Windows firewall accordingly based on what protocol you have chosen to use. For detailed information about how to configure Windows Firewall to allow SQL Server, please check http://msdn.microsoft.com/en-us/library/cc646023.aspx.

· Enable SQL Server Browser Services

You need to enable SQL Server Browser Services if the following are both true:

1.  SQL Server is not listening on default 1433 port or not use default pipe name \\.\pipe\sql\query;
   

2.  The corresponding TCP port or pipe name is not specified in the connection string (such as Srv1\SQL2008, 1500).
   

If you have enabled SQL Server Browser Services, you still need to open UDP 1434 port which is used by Browser Services in the Windows firewall.

[sonyeo]

thanks sir for the suggestion but i've encountered again some problem after i follow the possibilities to resolve my past problem...
the error same as the past problem but the difference is:

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

[ss125]

IF thats the case, Check whether there are any instance conflicts in your SQL Server.

This is the problem which i faced during my college days.
The solutions were
1) Remove the current instance to he database and create the instance with the same name, so that you dont want to change the connection string in all your application.

2) Create a instance with other name and use it all over your application.

Hope this helps u my friend...