13 Replies - 1161 Views - Last Post: 03 December 2011 - 01:17 PM Rate Topic: -----

#1 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Error in retrieving data.

Posted 01 December 2011 - 03:46 PM

hi friends .


I have MSaccess database.I have 2 tables
1) CUSTOMER TABLE=----CUSTOMER ID-Primary --AUTO NUMBER

2) ORDER TABLE =----(CUSTOMER ID--Foreign KEY)


Now From the front end using VB.net I have an page in which if i enter Customer details and press button add .I have to INSERT into values into CUSTOMER TABLE and I Also at the same time INSERT into ORDER TABLE with this CUSTOMER ID which i generated now.How is it possible ..I am a little but confused.

Please explain me .thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Error in retrieving data.

#2 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Error in retrieving data.

Posted 01 December 2011 - 04:25 PM

You are right, first add to the Customer because it is the one holding primary key before adding to the Order. This is because Order references the Customer, so when adding the value must be there in Customer.
What query? the same as the one you use to add to each of them, means you will have two different queries for customer and then for order.
Was This Post Helpful? 0
  • +
  • -

#3 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 01 December 2011 - 04:53 PM

View Postsmohd, on 01 December 2011 - 04:25 PM, said:

You are right, first add to the Customer because it is the one holding primary key before adding to the Order. This is because Order references the Customer, so when adding the value must be there in Customer.
What query? the same as the one you use to add to each of them, means you will have two different queries for customer and then for order.



Hi smodh i have got the code.I am getting a new error.can u look into it once.
 Dim cnString As String
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=AirLine.mdb"
        ' Create(connection)
        Dim conn As OleDbConnection = New OleDbConnection(cnString)
        Try

            conn.Open()

            'create query for customer table.
            Dim sqlQRY As String = "SELECT * FROM CustomerDetails"
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            Dim ds As DataSet = New DataSet
            da.FillSchema(ds, SchemaType.Source)
            Dim dt As DataTable = ds.Tables("CustomerDetails")
            'dt.TableName = "CustomerDetails"

            'create query for Purchase table.
            Dim sqlQRY1 As String = "SELECT * FROM PurchaseDetails"
            Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY1, conn)
            Dim cb1 As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
            'fill dataset
            da.FillSchema(ds, SchemaType.Source)
            'get data table
            Dim dt1 As DataTable = ds.Tables("PurchaseDetails")
            'dt1.TableName = "PurchaseDetails"


            ' Create the relationship between the two tables


            [size="5"][b]Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), --------------------- :helpsmilie:/> ds.Tables("PurchaseDetails").Columns("Customerid"))[/b][/size]
            ds.Relations.Add(dam)
            ' Adding new row to  PARENT  table

            Dim newRow As DataRow = dt.NewRow()
            newRow("Firstname") = txtFname.Text
            newRow("Lastname") = txtLname.Text
            newRow("Phonenumber") = txtPhnumbr.Text
            newRow("Adress") = txtAdrs.Text
            dt.Rows.Add(newRow)

            ' Adding new row to  PURCHASE  table
            Dim newRow1 As DataRow = dt1.NewRow()
            newRow1("flightid") = "1212"
            newRow1("CityofDeparture") = "asda"
            newRow1("CityodArrival") = "wereone"
            newRow1("Price") = "1000"
            newRow1("NoofSeats") = "0"
            dt1.Rows.Add(newRow1)

            'UPDATE THE DATABASE
            da.Update(ds, "CustomerDetails")
            da1.Update(ds, "PurchaseDetails")
            


        Finally
            ' Close connection
            conn.Close()
        End Try




I am getting an error at the point where i highlighted..

The ERROR is like as follows....Object reference not set to an instance of an object.


Help please..

sorry in the above post here is where i m gettig error..please check it

Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), --------------------- :helpsmilie:/> ds.Tables("PurchaseDetails").Columns("Customerid"))


Was This Post Helpful? 0
  • +
  • -

#4 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Error in retrieving data.

Posted 01 December 2011 - 05:04 PM

I don't think that ds has any tables in it... you have declared an empty instance of the DataSet object and unless I am mistaken (I hope not) you aren't filling it with data, henceforth causing the problem :)
Was This Post Helpful? 0
  • +
  • -

#5 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 01 December 2011 - 06:28 PM

View PostjimmyBo, on 01 December 2011 - 05:04 PM, said:

I don't think that ds has any tables in it... you have declared an empty instance of the DataSet object and unless I am mistaken (I hope not) you aren't filling it with data, henceforth causing the problem :)



I am by doing this
Dim dt As DataTable = ds.Tables("CustomerDetails")



Arnt me filling the dataset with the above statement??.DO i have to fill my dataset
Was This Post Helpful? 0
  • +
  • -

#6 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Error in retrieving data.

Posted 01 December 2011 - 06:41 PM

Data set is filled using the data adapter method Fill(), so if you have to fill the table retrieved by the data adapter:
da.Fill(ds) 

Or other overloads of the method
Was This Post Helpful? 0
  • +
  • -

#7 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 02 December 2011 - 07:02 PM

Hi friends when i run this code



Dim cnString As String
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=AirLine.mdb"
        ' Create(connection)
        Dim conn As OleDbConnection = New OleDbConnection(cnString)
        Try

            conn.Open()

            'create query for customer table.
            Dim sqlQRY As String = "SELECT * FROM CustomerDetails"
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            Dim ds As DataSet = New DataSet
            'da.Fill(ds, "CustomerDetails")
            da.FillSchema(ds, SchemaType.Source)
            Dim dt As DataTable = New DataTable()
            dt.TableName = "CustomerDetails"

            'create query for Purchase table.
            Dim sqlQRY1 As String = "SELECT * FROM PurchaseDetails"
            Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY1, conn)
            Dim cb1 As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
            'fill dataset
            ' da.Fill(ds, "PurchaseDetails")
            da1.FillSchema(ds, SchemaType.Source)------------------------------ERROR

            'get data table
            Dim dt1 As DataTable = New DataTable()
            dt1 = ds.Tables("PurchaseDetails")
            'dt1.TableName = "PurchaseDetails"


            ' Create the relationship between the two tables


            Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), ds.Tables("PurchaseDetails").Columns("Customerid"))
            ds.Relations.Add(dam)
            ' Adding new row to  PARENT  table

            Dim newRow As DataRow = dt.NewRow()
            newRow("Firstname") = txtFname.Text
            newRow("Lastname") = txtLname.Text
            newRow("Phonenumber") = txtPhnumbr.Text
            newRow("Adress") = txtAdrs.Text
            dt.Rows.Add(newRow)

            ' Adding new row to  PURCHASE  table
            Dim newRow1 As DataRow = dt1.NewRow()
            newRow1("flightid") = "1212"
            newRow1("CityofDeparture") = "asda"
            newRow1("CityodArrival") = "wereone"
            newRow1("Price") = "1000"
            newRow1("NoofSeats") = "0"
            dt1.Rows.Add(newRow1)

            'UPDATE THE DATABASE
            da.Update(ds, "CustomerDetails")
            da1.Update(ds, "PurchaseDetails")
            


        Finally
            ' Close connection
            conn.Close()
        End Try






I am getting following error

MaxLength applies to string data type only. You cannot set Column 'Customerid' property MaxLength to be non-negative number.

at mentioned in the code.can any one tell why am i getting this error? :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

#8 Recoil  Icon User is offline

  • D.I.C Regular

Reputation: 24
  • View blog
  • Posts: 292
  • Joined: 28-June 08

Re: Error in retrieving data.

Posted 02 December 2011 - 07:49 PM

Your problem is this:

newRow1("flightid") = "1212"


You cannot programatically set an auto number to your ID field. When you create a new record it should automatically take care of this, depending on how you are adding the new record. That is why you are getting that error.

Mentor Edit:<Removed +rep asking in post, please put it in your signature if you need to>

This post has been edited by smohd: 02 December 2011 - 07:58 PM

Was This Post Helpful? 0
  • +
  • -

#9 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Error in retrieving data.

Posted 02 December 2011 - 08:11 PM

The FillSchema() methods among its works is to set some columns properties configurations, among those properties is the MaxLength, but this property can only be set to String(text-related) columns, and if there is any non -string clumn an exception will be thrown:
From doc:

Quote

The maximum length of the column in characters. If the column has no maximum length, the value is 1 (default).
A ArgumentException exception is raised if you assign MaxLength to a non-string column.


For your case, you can use Fill() method, this method can do most of the work there and add schema if necessary.
Also you can try Mapped schema as they said:

Quote

The SchemaType usually should be set to Mapped, because any established table and column mappings are used.

Was This Post Helpful? 1
  • +
  • -

#10 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 02 December 2011 - 09:52 PM

View Postsmohd, on 02 December 2011 - 08:11 PM, said:

The FillSchema() methods among its works is to set some columns properties configurations, among those properties is the MaxLength, but this property can only be set to String(text-related) columns, and if there is any non -string clumn an exception will be thrown:
From doc:

Quote

The maximum length of the column in characters. If the column has no maximum length, the value is 1 (default).
A ArgumentException exception is raised if you assign MaxLength to a non-string column.


For your case, you can use Fill() method, this method can do most of the work there and add schema if necessary.
Also you can try Mapped schema as they said:

Quote

The SchemaType usually should be set to Mapped, because any established table and column mappings are used.



Thank you smodh i got it now .Now i have an new problem. in the above code

Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), ds.Tables("PurchaseDetails").Columns("Customerid"))



Here i am getting an error named
"Object referebce not set to an instance of an Object ".. :helpsmilie: .

What is that i am not initiliazing here?? :whatsthat:
Was This Post Helpful? 0
  • +
  • -

#11 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Error in retrieving data.

Posted 02 December 2011 - 10:24 PM

*Topic with the same issue merged*
Please post your updated code after the first fixes
Was This Post Helpful? 0
  • +
  • -

#12 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Error in retrieving data.

Posted 02 December 2011 - 11:22 PM

View Postbabbu25, on 03 December 2011 - 02:52 PM, said:

View Postsmohd, on 02 December 2011 - 08:11 PM, said:

The FillSchema() methods among its works is to set some columns properties configurations, among those properties is the MaxLength, but this property can only be set to String(text-related) columns, and if there is any non -string clumn an exception will be thrown:
From doc:

Quote

The maximum length of the column in characters. If the column has no maximum length, the value is 1 (default).
A ArgumentException exception is raised if you assign MaxLength to a non-string column.


For your case, you can use Fill() method, this method can do most of the work there and add schema if necessary.
Also you can try Mapped schema as they said:

Quote

The SchemaType usually should be set to Mapped, because any established table and column mappings are used.



Thank you smodh i got it now .Now i have an new problem. in the above code

Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), ds.Tables("PurchaseDetails").Columns("Customerid"))



Here i am getting an error named
"Object referebce not set to an instance of an Object ".. :helpsmilie: .

What is that i am not initiliazing here?? :whatsthat:

All I can think of now is that there must not be tables or columns by the names you have given. However, I am not sure an error of that type would throw that exception. Hmmmmmm.
Was This Post Helpful? 0
  • +
  • -

#13 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 03 December 2011 - 10:09 AM

View Postsmohd, on 02 December 2011 - 10:24 PM, said:

*Topic with the same issue merged*
Please post your updated code after the first fixes







   Dim cnString As String
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data    Source=AirLine.mdb"
        ' Create(connection)
        Dim conn As OleDbConnection = New OleDbConnection(cnString)
        Try

            conn.Open()

Dim sqlQRY As String = "SELECT * FROM CustomerDetails"
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            Dim ds As DataSet = New DataSet
            'da.Fill(ds, "CustomerDetails")
            da.FillSchema(ds, Int(SchemaType.Source))
            Dim dt As DataTable = New DataTable()
            dt.TableName = "CustomerDetails"

            'create query for Purchase table.
            Dim sqlQRY1 As String = "SELECT * FROM PurchaseDetails"
            Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY1, conn)
            Dim cb1 As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
            'fill dataset
            da.Fill(ds, SchemaType.Mapped)
            Dim dt1 As DataTable = New DataTable()
            'dt1 = ds.Tables("PurchaseDetails")
            'da1.FillSchema(ds, (SchemaType.Mapped))

            'get data table
           
            dt1.TableName = "PurchaseDetails"


            ' Create the relationship between the two tables


            Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), ds.Tables("PurchaseDetails").Columns("Customerid"))
            ds.Relations.Add(dam)
            ' Adding new row to  PARENT  table

            Dim newRow As DataRow = dt.NewRow()
            newRow("Firstname") = txtFname.Text
            newRow("Lastname") = txtLname.Text
            newRow("Phonenumber") = txtPhnumbr.Text
            newRow("Adress") = txtAdrs.Text
            dt.Rows.Add(newRow)

            ' Adding new row to  PURCHASE  table
            Dim newRow1 As DataRow = dt1.NewRow()
            newRow1("flightid") = "1212"
            newRow1("CityofDeparture") = "asda"
            newRow1("CityodArrival") = "wereone"
            newRow1("Price") = "1000"
            newRow1("NoofSeats") = "0"
            dt1.Rows.Add(newRow1)

            'UPDATE THE DATABASE
            da.Update(ds, "CustomerDetails")
            da1.Update(ds, "PurchaseDetails")
            


        Finally
            ' Close connection
            conn.Close()
        End Try




this is the whole code smodh..thank you
Was This Post Helpful? 0
  • +
  • -

#14 babbu25  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 68
  • Joined: 26-November 11

Re: Error in retrieving data.

Posted 03 December 2011 - 01:17 PM

please friends .i have to submit my project in 2 days.Please help me solve this problem ...

Dim dam As New DataRelation("ParentChild", ds.Tables("CustomerDetails").Columns("Customerid"), ds.Tables("PurchaseDetails").Columns("Customerid"))



Here i am getting an error named
"Object referebce not set to an instance of an Object ".. .

What is that i am not initiliazing here??
pleass help me solve it ..

ABove is the whole code
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1