Solved

vb.net connect to sql database and add a row

Posted on 2003-11-13
4
1,819 Views
Last Modified: 2008-09-12
Hi,

This is my first vb .net project, so it's probably something simple.
When my code runs, I get the following error. (code pasted below)
The example was taken from msdn.

Thanks for any help offered.

The Error
-------------------------------------------------------------------------------------------------------------------------------

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.
--------------------------------------------------------------------------------------------------------------------------------
'Start of code
        'ADD TO DB


        Dim sConnectionString As String


        ' Modify the following code to correctly connect to your SQL Server.
        sConnectionString = "Password=cue;User ID=cue;" & _
                            "Initial Catalog=Cue;" & _
                            "Data Source=pd100408"


        Dim objConn As New SqlConnection(sConnectionString)
        objConn.Open()

        ' Create an instance of a DataAdapter.
        Dim da As _
            New SqlDataAdapter("Select * From DH51", objConn)

        ' Create an instance of a DataSet, and retrieve data from the DH51 table.
        Dim ds As New DataSet("DH51")
        da.FillSchema(ds, SchemaType.Source, "DH51")
        da.Fill(ds, "DH51")

        '*****************
        'BEGIN ADD CODE
        ' Create a new instance of a DataTable.
        Dim tblDH51 As DataTable
        tblDH51 = ds.Tables("DH51")

        Dim drCurrent As DataRow
        ' Obtain a new DataRow object from the DataTable.
        drCurrent = tblDH51.NewRow()

        ' Set the DataRow field values as necessary.

        drCurrent("Unit Number") = strUnitNumber
        drCurrent("Enquiry Type") = strEnquiryType
        drCurrent("Record Type") = strRecordType
        'drCurrent("Report Usage") =
        drCurrent("Processing Status") = strProcessingStatus
        drCurrent("Creation Date") = strCreationDate
        drCurrent("Creation Time") = strCreationTime
        drCurrent("Cue Reference Number") = strCueReferenceNumber

        'Pass that new object into the Add method of the DataTable.Rows collection.
        tblDH51.Rows.Add(drCurrent)
        'MsgBox("Add was successful.")

        'END ADD CODE


        '*****************
        'BEGIN SEND CHANGES TO SQL SERVER

        Dim objCommandBuilder As New SqlCommandBuilder(da)
        da.Update(ds, "DH51")

        '07979 906888
        MsgBox("SQL Server updated successfully" & Chr(13) & "Check Server explorer to see changes")

        ' END SEND CHANGES TO SQL SERVER
0
Comment
Question by:andy_booth
[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
  • 2
4 Comments
 
LVL 1

Author Comment

by:andy_booth
ID: 9740110
Oops for to say, the line it falls over on is...

da.Update(ds, "DH51")
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9740455
you may need to make a single 'small' addition:

 Dim da As _
            New SqlDataAdapter("Select * From DH51", objConn)
 da.InsertCommand = new SQLCommand("Insert into DH51 (Unit Numner, Enquiry Type,Record Type,Processing Status,Creation Date,Creation Time,Cue Reference Number) VALUES(@UnitNumber, @EnquiryType,@RecordType,@ProcessingStatus,@CreationDate,@CreationTime,@CueReferenceNumber), objConn)
Dim workParm as sqlParameter

workParm = da.InsertCommand.Parameters.Add(New sqlParmeter("@UnitNumber",sqlDbType.Int))
workParm.SourceColumn = "Unit Number"
workParm.SourceVersion = DataRowVersion.Current

workParm = da.InsertCommand.Parameters.Add(New sqlParmeter("@EnquiryType",sqlDbType.Int)) ' or whatever type is relevant
workParm.SourceColumn = "Enquiry Type"
workParm.SourceVersion = DataRowVersion.Current

workParm = da.InsertCommand.Parameters.Add(New sqlParmeter("@RecordType",sqlDbType.Int)) ' or whatever type is relevant
workParm.SourceColumn = "Record Type"
workParm.SourceVersion = DataRowVersion.Current

workParm = da.InsertCommand.Parameters.Add(New sqlParmeter("@ProcessingStatus",sqlDbType.Int)) ' or whatever type is relevant
workParm.SourceColumn = "Processing Status"
workParm.SourceVersion = DataRowVersion.Current

and so on for each of the fields in the record.

notice that it is a VERY VERY bad idea to have embeddied BLANKS in the names of either Tbales or Fields---  rename the Field "Unit Number' to either "UnitNumber" or "Unit_Number".

AW



0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9740482
you should use the DataAdaper WIZARD for the first couple of applications, as it will build all of the UGLY code that you see above, for you, to build the necessary INSERT,DELETE and UPDATE commands for the DataAdapter.

AW
0
 
LVL 4

Accepted Solution

by:
krznpsk earned 250 total points
ID: 9744424
Andy,

The code looks fine to me; the error is clearly occurring in your SqlConnection.

In case you didn't know, the DataAdapter object will implicitly OPEN your SqlConnection object, then CLOSE it when it's done with the Fill() method.

Anyway, the problem is kind of hard to identify right now because you're not really seeing the error message.  Do you know about Try/Catch blocks?  You should place your code in a Try/Catch so that you can at least see a more detailed error message.

Here is your code rewritten in a Try/Catch block... you'll see the only changes are one line at the top and three at the bottom.  Good luck!:

Try
        Dim sConnectionString As String


        ' Modify the following code to correctly connect to your SQL Server.
        sConnectionString = "Password=cue;User ID=cue;" & _
                            "Initial Catalog=Cue;" & _
                            "Data Source=pd100408"


        Dim objConn As New SqlConnection(sConnectionString)
        objConn.Open()

        ' Create an instance of a DataAdapter.
        Dim da As _
            New SqlDataAdapter("Select * From DH51", objConn)

        ' Create an instance of a DataSet, and retrieve data from the DH51 table.
        Dim ds As New DataSet("DH51")
        da.FillSchema(ds, SchemaType.Source, "DH51")
        da.Fill(ds, "DH51")

        '*****************
        'BEGIN ADD CODE
        ' Create a new instance of a DataTable.
        Dim tblDH51 As DataTable
        tblDH51 = ds.Tables("DH51")

        Dim drCurrent As DataRow
        ' Obtain a new DataRow object from the DataTable.
        drCurrent = tblDH51.NewRow()

        ' Set the DataRow field values as necessary.

        drCurrent("Unit Number") = strUnitNumber
        drCurrent("Enquiry Type") = strEnquiryType
        drCurrent("Record Type") = strRecordType
        'drCurrent("Report Usage") =
        drCurrent("Processing Status") = strProcessingStatus
        drCurrent("Creation Date") = strCreationDate
        drCurrent("Creation Time") = strCreationTime
        drCurrent("Cue Reference Number") = strCueReferenceNumber

        'Pass that new object into the Add method of the DataTable.Rows collection.
        tblDH51.Rows.Add(drCurrent)
        'MsgBox("Add was successful.")

        'END ADD CODE


        '*****************
        'BEGIN SEND CHANGES TO SQL SERVER

        Dim objCommandBuilder As New SqlCommandBuilder(da)
        da.Update(ds, "DH51")

        '07979 906888
        MsgBox("SQL Server updated successfully" & Chr(13) & "Check Server explorer to see changes")

        ' END SEND CHANGES TO SQL SERVER

Catch ex as Exception
        messagebox.show(ex.message)
End Try
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 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