none
MS SQL Relationship With Vb.net RRS feed

  • Question

  • Dear Friends

    i am making an erp for my present company . i worked on many modules but i am stuck on stock in module. i checked many examples of stock in modules  but almost all of them using relationship between sql and vb.net. 

    i tried relationship in ms sql and it worked for me in following way

    i mentioned stockID as integer with identity increment auto

    then on other database it keep same property with same name and relate both and it worked .... but i want to use NVCHAR(50) in relationship which i tried and it not working . 

    if possible can anyone teach me or refer me any easy website of videos or anything in which i can able to learn and understand relationship of MS SQL and which can easily work in VB.NET or any specific code for it. 

    That will be a great help to me from you.

    Many Thanks in Advance 

     

    Thursday, February 14, 2019 4:52 AM

Answers

  • BTW this is how a INSERT works, the INSERT is followed by a SELECT to get the new key when auto incrementing, there is no other way.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Omi4u Friday, February 15, 2019 5:10 AM
    Thursday, February 14, 2019 1:40 PM
    Moderator

All replies

  • Hi,

    learn about relationship:

    https://social.msdn.microsoft.com/Forums/en-US/464d7e68-03a5-4e09-bebe-e81b09fd6a23/using-database-relationships-in-vbnet?forum=adodotnetdataproviders

    example:

    https://code.msdn.microsoft.com/windowsdesktop/Relational-Database-Example-5343d132

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 14, 2019 7:17 AM
  • Thank you for your reply Alex

    Please check below link...i tried this relationship in vb.net but it not working please check and help me with it

    https://1drv.ms/u/s!Aqjx5CNCnnObhVwJMUo60F5j9dTD

    it not doing anything like saving data to linked doctorID

    and when i add new, relationship data in datagrid will show previous data

    please help 

    Thursday, February 14, 2019 7:50 AM
  • Hello,

    I looked at your project and with that.

    Using TableAdapters mixed with NVARCHAR(50) key is not supported, if you try no Update or delete commands will be generated as the data wizard requires auto-incrementing keys for UPDATE and DELETE.

    If you want to learn how to create a master/details setup using Visual Studio, VB.NET and SQL-Server (which does not use TableAdapters) see the following MSDN code sample I wrote. The code was written in VS2015 but will run with VS2017 and VS2019.

    The following is a screenshot

    • In this case there are three relations but you can learn three or learn two as my code is crystal clear how to do the relations.
    • Edits and inserts are done via a pop-up form, for edits press a button or double click a row.
    • Although I used DataGridView controls you could use other controls e.g. ListBox, ListView but a DataGridView is the most commonly used control for master-details.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 14, 2019 10:57 AM
    Moderator
  • thank you so much for your reply Ma'am

    your given link is not working. 

    Thursday, February 14, 2019 11:15 AM
  • working now
    Thursday, February 14, 2019 11:35 AM
  • i saw your application. nice work

    but as i understand you did not draw any relationship diagram and you are using forms to insert \ edit data in grid 

    but i have one form with stock in details and after user enter details of stockin he will press add button and all fields data will goto datagrid which is in relation to stockin data. it will same both stockin data and datagrid with all details. in datagrid id is automatic but relationship field is manual which come from stockin textbox while pressing add button . now here i tried to save it but in database nothing happened nothing is saved . as i send u the example . that is the concept of my erp stock in module. that example also not saving my information. i need exactly same ma'am

    Thursday, February 14, 2019 11:53 AM
  • Hello,

    In regards to drawing, that is ONLY with TableAdapters. For how I showed how to do this you don't draw a relationship in code, instead you setup relations in SSMS (SQL-Server Management Studio) which includes primary and foreign keys as shown below for example.

    In regards to one form, a DataGridView, one DataGridView per table in a database is how it's done. Concerning pressing a button (usually done in a BindingNavigator) to add a record is possible but is the novice way to do this, perhaps less than five percent of developers will use this then later realize this is not the way to go and use methods such as what I showed. 

    Now talking about manual relations with TableAdapters, untrue, TableAdapters support automatically doing relations when setting them up in SSMS as per below. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 14, 2019 12:11 PM
    Moderator
  • agree with your reply 

    i tried in same way that you mentioned above in picture but in my case if i use DATACASE as INT then relation work fine but if i use NVChar or NChar it do not save the data. i am using only one datagrid and others are textboxes. 

    Thursday, February 14, 2019 12:18 PM
  • agree with your reply 

    i tried in same way that you mentioned above in picture but in my case if i use DATACASE as INT then relation work fine but if i use NVChar or NChar it do not save the data. i am using only one datagrid and others are textboxes. 

    In regards to not using INT, that is what I have informed you that not using INT would not work.

    Also consider that the fact that any string or char type value can change and not be unique later down the road, this is why not to use anything but INT. I've been working with relational databases since 1990 and know what works and doesn't work and with that would not give you incorrect advice.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 14, 2019 1:03 PM
    Moderator
  • hmm actually Ma'am i am using ID as Alphabatic and Numaric (STD1000) and i link it with other table ... please advice me what should i do .. i know INT will work in relationships but what if i have to use alphabatic and numaric as ID 

    Thursday, February 14, 2019 1:11 PM
  • hmm actually Ma'am i am using ID as Alphabatic and Numaric (STD1000) and i link it with other table ... please advice me what should i do .. i know INT will work in relationships but what if i have to use alphabatic and numaric as ID 

    Using alphanumeric is still a bad method to go as a TableAdapter will not create update and remove queries in the classes generated.

    If you were to create a trigger to create a key such as STD1000 which is possible even then a TableAdapter will not have generated queries for update and remove.

    The path you are going down has no good outcome using TableAdapters so as mentioned before DO NOT go down this path because in the end all you will be doing is fighting with TableAdapters, going against how they were meant to be used and all that will come out of it is you wasting time.

    So with that what is inherently is the problem is

    • Lack of understanding how relationships and proper database designs work.
    • Not understanding how TableAdapters work.

    The majority of developers who use TableAdapters are looking for an easy way to get non relational or relational system to work. Professional developers avoid TableAdapters altogether.

    So best of luck going down this path, I have nothing else to offer since you are set on going down this path with TableAdapters and not a proper primary key for each table.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 14, 2019 1:37 PM
    Moderator
  • BTW this is how a INSERT works, the INSERT is followed by a SELECT to get the new key when auto incrementing, there is no other way.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Omi4u Friday, February 15, 2019 5:10 AM
    Thursday, February 14, 2019 1:40 PM
    Moderator
  • Thank you Karen Ma'am for guiding me ... really appreciate. i will use INT from now where required 


    Friday, February 15, 2019 4:56 AM