Solved

Having Data Fill Problem

Posted on 2006-11-05
21
419 Views
Last Modified: 2007-11-27
In a VB.NET project I have set up a SQL adapter and can successfully Preview the data but when I try to do it in my form with:

SqlDataAdapter1.Fill(DataSet11)

It immediately returns a system error.  Why would this happen?

0
Comment
Question by:John Gates
  • 7
  • 7
  • 4
  • +2
21 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Hi dimante,

which error? can you show your code?

Cheers!
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
it's that simple just this fill command is in a button event.  The error returned is "System Error"  I can preview data no problem from the adapter...
0
 
LVL 18

Expert Comment

by:vbturbo
Comment Utility


SqlDataAdapter1.Fill(DataSet11, "your_tblName_here")
0
 
LVL 8

Expert Comment

by:doobdave
Comment Utility
As emoreau mentions, you need to give us a bit more information, and perhaps your code.
Without that, we can only guess what going wrong.

Is the complete error message "System Error" or is there further information there?

I am guessing thhat you are using the Data Access components from the toolbox, given that you say you can 'preview' the data ok.
Is it that those controols have already called the .Fill method, which then causes the error when you call it again?

Please post your code, including the Designere-generated code pertaining to the data access.

Best Regards,

David
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
Remember you asked for it LOL:

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DataSet11 As ORStudentCreate.DataSet1
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DataSet11 = New ORStudentCreate.DataSet1
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=ROCKET;packet size=4096;user id=sa;data source=""3dserv\SQL20053D"";" & _
        "persist security info=False;initial catalog=DI"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "ADST", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("STATUS", "STATUS"), New System.Data.Common.DataColumnMapping("SCHOOLNUM", "SCHOOLNUM"), New System.Data.Common.DataColumnMapping("LASTNAME", "LASTNAME"), New System.Data.Common.DataColumnMapping("FIRSTNAME", "FIRSTNAME"), New System.Data.Common.DataColumnMapping("MIDDLENAME", "MIDDLENAME"), New System.Data.Common.DataColumnMapping("PERMNUM", "PERMNUM"), New System.Data.Common.DataColumnMapping("BIRTHDATE", "BIRTHDATE"), New System.Data.Common.DataColumnMapping("PRNTGUARD", "PRNTGUARD"), New System.Data.Common.DataColumnMapping("MAILADDR", "MAILADDR"), New System.Data.Common.DataColumnMapping("CITY", "CITY"), New System.Data.Common.DataColumnMapping("ZIPCODE", "ZIPCODE"), New System.Data.Common.DataColumnMapping("STATE", "STATE"), New System.Data.Common.DataColumnMapping("TELEPHONE", "TELEPHONE"), New System.Data.Common.DataColumnMapping("BIRTHPLACE", "BIRTHPLACE"), New System.Data.Common.DataColumnMapping("HOMELNG", "HOMELNG"), New System.Data.Common.DataColumnMapping("PRIMARYLNG", "PRIMARYLNG"), New System.Data.Common.DataColumnMapping("DSTLINK", "DSTLINK")})})
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(256, 400)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PR" & _
        "NTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARY" & _
        "LNG, DSTLINK FROM SASI.ADST WHERE (STATUS = '')"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO SASI.ADST(STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM" & _
        ", BIRTHDATE, PRNTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, H" & _
        "OMELNG, PRIMARYLNG, DSTLINK) VALUES (@STATUS, @SCHOOLNUM, @LASTNAME, @FIRSTNAME," & _
        " @MIDDLENAME, @PERMNUM, @BIRTHDATE, @PRNTGUARD, @MAILADDR, @CITY, @ZIPCODE, @STA" & _
        "TE, @TELEPHONE, @BIRTHPLACE, @HOMELNG, @PRIMARYLNG, @DSTLINK); SELECT STATUS, SC" & _
        "HOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PRNTGUARD, MAILADD" & _
        "R, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARYLNG, DSTLINK FRO" & _
        "M SASI.ADST"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATUS", System.Data.SqlDbType.VarChar, 1, "STATUS"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SCHOOLNUM", System.Data.SqlDbType.VarChar, 3, "SCHOOLNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LASTNAME", System.Data.SqlDbType.VarChar, 40, "LASTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FIRSTNAME", System.Data.SqlDbType.VarChar, 20, "FIRSTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MIDDLENAME", System.Data.SqlDbType.VarChar, 13, "MIDDLENAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PERMNUM", System.Data.SqlDbType.VarChar, 12, "PERMNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHDATE", System.Data.SqlDbType.DateTime, 8, "BIRTHDATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRNTGUARD", System.Data.SqlDbType.VarChar, 35, "PRNTGUARD"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MAILADDR", System.Data.SqlDbType.VarChar, 35, "MAILADDR"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 24, "CITY"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZIPCODE", System.Data.SqlDbType.VarChar, 10, "ZIPCODE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATE", System.Data.SqlDbType.VarChar, 2, "STATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TELEPHONE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(15, Byte), CType(0, Byte), "TELEPHONE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHPLACE", System.Data.SqlDbType.VarChar, 20, "BIRTHPLACE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@HOMELNG", System.Data.SqlDbType.VarChar, 3, "HOMELNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRIMARYLNG", System.Data.SqlDbType.VarChar, 3, "PRIMARYLNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DSTLINK", System.Data.SqlDbType.Decimal, 5, System.Data.ParameterDirection.Input, False, CType(7, Byte), CType(0, Byte), "DSTLINK", System.Data.DataRowVersion.Current, Nothing))
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(576, 459)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click





    End Sub


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(DataSet11)
    End Sub
End Class


This results in the system error as soon as the page loads.  The SQL server is SQL 2005.  The version of VB.NET is 1.1 sp1
Please advise.
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
try a simpler query like this:
Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME FROM SASI.ADST WHERE (STATUS = '')"
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
That was actually not the problem.  The problem involved saving the password for the connection.  Do you know how to save it so that it is not readable in compiled sourcecode?
0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
Here's a hint, go grab yourself a copy of vs2005 express and run it with .net 2.0 all your problems will magically dissapear.  Then you can encrypt your web.config which stores the passwords for connection strings which have saved me alot of time :)

0
 
LVL 4

Assisted Solution

by:darkpriest
darkpriest earned 200 total points
Comment Utility
On a more serious note,

Me.SqlConnection1.ConnectionString = "workstation id=ROCKET;packet size=4096;user id=sa;data source=""3dserv\SQL20053D"";" & _
        "persist security info=False;initial catalog=DI"

Wont be viewable on deployment, so you dont to hide it unless you have other developers that you dont want seeing it.
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
It will if you view the source code in a hex editor...  Unfortunately for this app I have to do it in 1.1 so that's the way it goes 8(  

I am still having problems as my dataset will not create an update statement which I am confused about also...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Accepted Solution

by:
vbturbo earned 300 total points
Comment Utility
Checkout this tutorial on working with dataset's. Gives you multiple posibilyties with a minimum of code effort and code lines.

http://www.homeandlearn.co.uk/NET/nets12p9.html

And here from the begining

http://www.homeandlearn.co.uk/NET/nets12p4.html

vbturbo
0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
Hex Editor thats true, i guess never really thought of that since i encrypt all my connection strings anyway > :)

I have some old projects in 1.1, and i mean some really old ones but with some basic adapters i used to specify the parameters like this since your doing a Fill im under the indication that you have already set the parameters.


'----------- Porition of Code
   
        Dim dtScanner As DataTable
        Dim dtScannerRow As DataRow

        dtScanner = Scannerds1.Tables("tblWebOrders")

        SCANDA.SelectCommand.Parameters(0).Value = "True"
        Scannerds1.Tables("tblWebOrders").Clear()
        SCANDA.Fill(Scannerds1, "tblWebOrders")

       
'---------------------------

This code was just something quick and simple it sets a parameter to a value, up in your code you have parameters being used.

You need to set each one, since i haven't seen any code where you have set these values for your select command, so incase you haven't set your parameters hope that helps.
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
What I am not understanding is when I set up a SQLDataAdapter and attempt to configure it it will not generate the update, insert, or delete code it returns an error.  The user for now is sa so permissions on the sql2005 database are not the issue.  Why is .NET refusing to generate the above functions?
0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
Just for testing sake, im about to go into a meeting could you try using a OLE adapter.  I should be back in about 2 hrs or so.

0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
One thought though, is there a primary key in the table?  Usually tables will not allow generation of updates if there is no primary key.
0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
Im back briefly,

Had a chance to try the ole or check the primary key?
0
 
LVL 18

Expert Comment

by:vbturbo
Comment Utility
Hi

As darkpriest  states you need to define primary key.

Here is a small simplyfied ole code you can test out ! allthough if implemented in a larger solution then watch the scope for the commandbuilder      


        Dim pk(0) As DataColumn'define a primarykey collum
        Dim  cbSqlDataAdapter1 As OleDb.OleDbCommandBuilder 'define the command builder
        Dim  sId As Integer 'variabel to hold the found primarykey in the dataset
        Dim  sSql As String
        Dim  TmpStr As String
        Dim  oDatarow As DataRow
-----------------------------------------------------

        m_cn.Open() 'open the connection
        DataSet11.Clear()

        'fill the dataset and setup the dataadapter
        sSql = "SELECT Priority,Id,Firmname,Mailaddress,Fname,Lname,Katagori FROM tblname"
        SqlDataAdapter1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
        SqlDataAdapter1.Fill(DataSet11, "tblname")

        DataSet11.Tables("tblname").Columns("Id").AutoIncrement = True
        pk(0) = DataSet11.Tables("tblname").Columns("Id")   '<--- the name collumname of your primary key
        DataSet11.Tables("tblname").PrimaryKey = pk
        m_cn.Close()

 ----------------------------------------------------  
       ' do the UPDATE to the datasource ! all the modifications made to the dataset   "insert , update , delete"

        Public Sub updatetblname()
        cbSqlDataAdapter1 = New OleDb.OleDbCommandBuilder(SqlDataAdapter1)
        SqlDataAdapter1.Update(DataSet11, "tblname")

        End Sub

--------------------------------------------------------

Example ...

       'loop through the dataset collum (6) to see if a match is found to conditon = TmpStr

       TmpStr = "some condition"

        For Each oDatarow In DataSet11.Tables("tblname").Rows

            If oDatarow.Item(6) = TmpStr Then
                sId = oDatarow.Item(1)'the collum with primary
                DataSet11.Tables("tblname").Rows.Find(sId)' find the particular row with that primary
                oDatarow.Delete()
            End If
        Next

        updatetblname()' call the update sub
----------------------------------------------------
vbturbo
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
I guess I should state the entire scope of what I am doing.  I am working against a database that CANNOT be modified in any way.  If there is no primary key, that is how it must remain and in the table in question that is true.  So if I hand code the connection etc is that still using ADO.NET?  I thought the point of .NET was to ease database programming by allowing you to drag components to the designer..  If I have to hand code it all that's fine but it seems to me that .NET is not all it is cracked up to be.  If I hand code this (Even without a primary key) I will successfully be able to add and update records so why won't the designer just warn about it, but still create the code?  
0
 
LVL 18

Expert Comment

by:vbturbo
Comment Utility
Hi

I belive that when using the designer to generate your " update delete " statements it would need a primary key to create such statements/methods for you,
although it can create "select and insert " statements under your mentioned problem description.
But without an unique identifier (pk) you wount be able to acomplish that.
It would be fair to say that , if there is a ambiguos possibility then the designer wount create your desired request. Such thing doesn't exist in any progamming langues.

And yes off course you hand write code to work around/acomplish that, what the designer wount do for you.
Also you seem to know that designers are a bit limited in some regards.

I personally belive there is benefits and disadvantece useing ado.net "most benefits"
The greatest one in my humble opinion is useing dataset's , you can make any manipulation to without makeing/addressing a connection request to the database
until you are done with all your data manipulation and then in the END, do one final request to the database namely ...updateing all the modifications you made
to the dataset


Answer to your question
>>>so why won't the designer just warn about it, but still create the code?  
As mentioned above it creates the insert and select methods for you and you would also have seen 2 warnings (yellow once)
SO that why the designer still creates the code for you.(thogh it is limited)





0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
Well with that said I will just hand code the connections etc and not drag and drop.  Thanks for your input!

-D-
0
 
LVL 4

Expert Comment

by:darkpriest
Comment Utility
what vbturbo said ;)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now