Solved

Problem saving Data to database using SQL Server and VB.Net

Posted on 2006-06-20
18
229 Views
Last Modified: 2010-04-23
Can you tell me what is wrong with this and correct it??

It tells me somthing about an update command missing on the update command....

    Public Function Save()

        Dim oRow As Data.DataRow
        Dim oDb As New clsDBAccess(mvarServer, mvarDatabase)
        Dim strSQL As String = "Select * from tblReports Where ReportID = " & mvarRecId
        Dim DB_CONN As String = oDb.GetConnectionString
        Dim objDA As New SqlClient.SqlDataAdapter(strSQL, DB_CONN)
        Dim oDS As New Data.DataSet

        objDA.Fill(oDS)
        oRow = oDS.Tables(0).Rows(0)
        If oRow Is Nothing Then oRow = oDS.Tables(0).NewRow

        PopulateRowFromObject(oRow)
        Try
            objDA.Update(oDS)
        Catch ex As Exception

        End Try



    End Function
0
Comment
Question by:ExtremeFitness
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
18 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 16943109
First, since your function does not return a value, make it a Sub:

 Public Sub Save()

        Dim oRow As Data.DataRow
        Dim oDb As New clsDBAccess(mvarServer, mvarDatabase)
        Dim strSQL As String = "Select * from tblReports Where ReportID = " & mvarRecId
        Dim DB_CONN As String = oDb.GetConnectionString
        Dim objDA As New SqlClient.SqlDataAdapter(strSQL, DB_CONN)
        Dim oDS As New Data.DataSet

        objDA.Fill(oDS)
        oRow = oDS.Tables(0).Rows(0)
        If oRow Is Nothing Then oRow = oDS.Tables(0).NewRow

        PopulateRowFromObject(oRow)
        Try
            objDA.Update(oDS)
        Catch ex As Exception

        End Try



    End Sub
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16943140
also what does PopulateRowFromObject do?
0
 
LVL 2

Author Comment

by:ExtremeFitness
ID: 16943144
Error is :       "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."      
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 2

Author Comment

by:ExtremeFitness
ID: 16943156
PopulateRowFromObject  Populates the data row with data from this object...
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16943223
Public Sub Save()

        Dim oRow As Data.DataRow
        Dim oDb As New clsDBAccess(mvarServer, mvarDatabase)
        Dim strSQL As String = "Select * from tblReports Where ReportID = " & mvarRecId
        Dim DB_CONN As String = oDb.GetConnectionString
objDA.SelectCommand=strSQL
        Dim objDA As New SqlClient.SqlDataAdapter(strSQL, DB_CONN)
        Dim oDS As New Data.DataSet

dim updateCmd as SqlCommand
                  
                  
                  ''Create update command for SqlDataAdapter
                  updateCmd = new SqlCommand("UPDATE tblReports SET field1=@value1, field2=@value2.... WHERE ....", objDA.SelectCommand.Connection)
                  
                  //Add parameters for update command
                  updateCmd.Parameters.Add(new SqlParameter("@value1", SqlDbType.VarChar))
                  updateCmd.Parameters("@value1") = .....


                  updateCmd.Parameters.Add(new SqlParameter("@value2", SqlDbType.VarChar))
                  updateCmd.Parameters("@value2")=...
            

                  ''Initialize the UpdateCommand of SqlDataAdapter
                  da.UpdateCommand = updateCmd


        objDA.Fill(oDS)
        oRow = oDS.Tables(0).Rows(0)
        If oRow Is Nothing Then oRow = oDS.Tables(0).NewRow

        PopulateRowFromObject(oRow)
        Try
            objDA.Update(oDS)
        Catch ex As Exception

        End Try

    End Sub

0
 
LVL 2

Author Comment

by:ExtremeFitness
ID: 16943398
Not was i was going for....
I was missing " Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(objDA)"
Seems the right answer is....

    Public Function Save()

        Dim oRow As Data.DataRow
        Dim oDb As New clsDBAccess(mvarServer, mvarDatabase)
        Dim strSQL As String = "Select * from tblReports Where ReportID = " & mvarRecId
        Dim DB_CONN As String = oDb.GetConnectionString
        Dim objDA As New SqlClient.SqlDataAdapter(strSQL, DB_CONN)
        Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(objDA)

        Dim oDS As New Data.DataSet

        objDA.Fill(oDS)
        oRow = oDS.Tables(0).Rows(0)
        If oRow Is Nothing Then oRow = oDS.Tables(0).NewRow

        PopulateRowFromObject(oRow)

        Try
            objDA.Update(oDS)

        Catch ex As Exception
            Console.WriteLine(ex)
        End Try

    End Function
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16943524
well, does it work?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16943529
also as I've said, change Function to Sub
0
 
LVL 2

Author Comment

by:ExtremeFitness
ID: 16943571
Ya it works now....  I need it as a function as it will return a boolean success or fail flag...
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16954485
then change it to

 Public Function Save() As Boolean
dim bResult as boolean
        Dim oRow As Data.DataRow
        Dim oDb As New clsDBAccess(mvarServer, mvarDatabase)
        Dim strSQL As String = "Select * from tblReports Where ReportID = " & mvarRecId
        Dim DB_CONN As String = oDb.GetConnectionString
        Dim objDA As New SqlClient.SqlDataAdapter(strSQL, DB_CONN)
        Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(objDA)

        Dim oDS As New Data.DataSet

        objDA.Fill(oDS)
        oRow = oDS.Tables(0).Rows(0)
        If oRow Is Nothing Then oRow = oDS.Tables(0).NewRow

        PopulateRowFromObject(oRow)

        Try
            objDA.Update(oDS)
    bResult=True
        Catch ex As Exception
            Console.WriteLine(ex)
         bResult=False
        End Try
    Save=bResult
    End Function

otherwise function will not return anything
0
 
LVL 2

Author Comment

by:ExtremeFitness
ID: 17098982
While Im all for giving credit where credit is due...  YZlat didnt solve this problem At all...  He offered a solution that was not at all in the style required....

0
 
LVL 35

Expert Comment

by:YZlat
ID: 17100340
first of all, the function should return a value and yours did not return anything, so either you should change it to Sub or return a boolean value from it
0
 
LVL 2

Author Comment

by:ExtremeFitness
ID: 17100459
Dude that is not an issue even worth thinking about :)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17100580
0
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 17100619
take a look at this argument as well

http://www.pcreview.co.uk/forums/thread-1393077.php
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question