Solved

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

Posted on 2006-06-20
18
223 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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