Member Avatar for zamm65

Hello to everyone i have a databse made up of 5 table, where 4 of the tables are linked to the fifth one by foreign keys.
the tables are as follows.

  1. Client - ClientID, ClientName
  2. Brand - BrandID, BrandName
  3. Currency - CurrencyID, CurrencyType
  4. RegisterShipments - RegShipmentID, ShipmentNo, ConsignmentNo,BDM,HUDINT,PAN

The following table has the above tables linked to it using Foreign Keys:
5. RegisterShipmentNoDetails - ShipmentDetailsID (PK), RegShipmentID(FK),CLinetID(FK),BrandID(FK),CurrencyID(FK), Season

What i would like to do is to be able to retrieve the data depending on shipment number chosen by user, as well as all the data of all the shipments that are present from the corresponding tables and display it in a datagrid.

I have come up with the following Select Statement, but it seem that it is not working as when i run the query in SQL it is giving me an error that Invalid object name 'RegisterShipmentNoDetails'. .

SELECT  Client.*,Brand.*, RegisterShipments.*, Currency.* from RegisterShipmentNoDetails
inner join RegisterShipments on RegisterShipmentNoDetails.RegShipmentID = RegisterShipments.ShipmentNo
inner join Client on RegisterShipmentNoDetails.ClientID = Client.ClientName
inner join Brand on RegisterShipmentNoDetails.BrandID = Brand.BrandName
inner join Currency on RegisterShipmentNoDetails.CurrencyID = Currency.CurrencyType

Could someone please help me as i think that i might be doing something wrong in the select statement or else using the wrong Join. Thanks for any help.

  • 2 Contributors
  • 2 Replies
  • 252 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by zamm65

Dani AI

Generated

As already pointed out, two separate issues are likely at play: the JOINs in the original query are comparing ID columns to name/text columns (wrong), and the "Invalid object name 'RegisterShipmentNoDetails'" message usually means SQL Server can’t find that table in the current database/schema (wrong database, wrong schema or a typo). For a correct result set filtered by shipment number, join FK-to-PK (ID-to-ID), qualify the schema (for example dbo.), and use a parameter for the shipment number before binding the result to the DataGridView.

Corrected SQL example (select only needed columns, join IDs to IDs, filter by ShipmentNo):

SELECT
  c.ClientID, c.ClientName,
  b.BrandID, b.BrandName,
  cu.CurrencyID, cu.CurrencyType,
  rs.RegShipmentID, rs.ShipmentNo, rs.ConsignmentNo, rs.BDM, rs.HUDINT, rs.PAN,
  rsd.ShipmentDetailsID, rsd.Season
FROM dbo.RegisterShipmentNoDetails AS rsd
INNER JOIN dbo.RegisterShipments     AS rs ON rsd.RegShipmentID = rs.RegShipmentID
INNER JOIN dbo.Client                AS c  ON rsd.ClientID      = c.ClientID
INNER JOIN dbo.Brand                 AS b  ON rsd.BrandID       = b.BrandID
INNER JOIN dbo.Currency              AS cu ON rsd.CurrencyID    = cu.CurrencyID
WHERE rs.ShipmentNo = @ShipmentNo;

Example VB.NET pattern to fill a DataTable and bind to a DataGridView (use parameterized command to avoid injection):

Using conn As New SqlClient.SqlConnection(connString)
  Using cmd As New SqlClient.SqlCommand(sqlText, conn)
    cmd.Parameters.AddWithValue("@ShipmentNo", shipmentNo)
    Dim da As New SqlClient.SqlDataAdapter(cmd)
    Dim dt As New DataTable()
    da.Fill(dt)
    dataGridView1.DataSource = dt
  End Using
End Using

Troubleshooting notes: confirm the exact table name and schema in the target database (use sys.tables or INFORMATION_SCHEMA.TABLES), ensure the query window/connection is set to the correct database, and check column datatypes so FK and PK really match. If the schema is different from dbo or the table name has a typo/pluralization difference, fully-qualify the name (e.g., MyDatabase.dbo.RegisterShipmentNoDetails) when testing in SSMS.

Recommended Answers

All 2 Replies

Member Avatar for djjeavons

Hi

Your query is joining ID columns against char columns which is incorrect (although I would have expected a different error than the one you mention). For example, INNER JOIN Client on RegisterShipmentNoDetails.ClientID = Client.ClientName is wrong, it should be RegisterShipmentNoDetails.ClientID = Client.ClientID and so on.

HTH

Member Avatar for zamm65

Hi dijeavons will check it out and see what will happen, will let you know if ok or not, thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.