Creating a SQL Server database programmatically using VB.NET

Sign in to leave a comment.

• AS

  AbdulRhman Siayari15y agoPosted Jul 10, 2011, 1:45 AM

  Dear Mr. Chand can you please give me a step by step tutorial on how to connect SQL Server 2008 with VB6. thank you for your time Regards,
• RM

  Rajani Mehar15y agoPosted May 12, 2011, 4:56 AM

  It's useful for everyone. Thanks.
• CB

  charles bacalla15y agoPosted Feb 4, 2011, 1:57 AM

  sir mahesh can you send me a sample program and step by step procedure using vb.net with database sql server 2008 that can save, edit, delete and update data or records, if it is ok for you sir... this is my email address [email protected] best regards charles
• JP

  jakapong promsana16y agoPosted Nov 8, 2010, 7:16 AM

  thankthankthankthank
• EE

  Elangovan Elangovan16y agoPosted Oct 26, 2010, 10:21 AM

  Hi Guys.... Can anyone help me to take backup of database(MSSQL Server) everyday by programatically in vb.net.... Thanks in advance, Elangovan A
• LH

  Lezly Harrison16y agoPosted Sep 9, 2010, 3:47 PM

  Thanks for the great code!
• TP

  takura pupi16y agoPosted Apr 28, 2010, 3:27 AM

  hie read your article and benefited greatly how do i connect the forms that i created using vb.net 3.5 to my database using ms sql server
• ES

  eliza sahoo16y agoeditedPosted Apr 15, 2010, 2:59 AMEdited May 25, 2010, 2:29 PM

  In SQL Server Database we can check the record before insert to the database and insert the record to the database by creating a store procedure as follows: CREATE PROCEDURE usp_AddTestData_UsingExists ( @Name varchar(50), @Qualification varchar(50) ) AS DECLARE @Result int BEGIN TRANSACTION IF EXISTS ( SELECT NULL FROM TestData WITH (UPDLOCK) WHERE [Name] = @Name AND Qualification = @Qualification ) BEGIN SELECT @Result = -1 END ELSE BEGIN INSERT INTO TestData ( [Name], Qualification ) VALUES ( @Name, @Qualification ) SELECT @Result = @@ERROR END IF @Result <> 0 BEGIN ROLLBACK END ELSE BEGIN COMMIT END RETURN @Result Hope this would add value.
• MD

  Marty Davis17y agoeditedPosted Oct 16, 2009, 10:17 AMEdited Oct 27, 2009, 6:50 PM

  Very good article.I have learned a lot from it. Can I create databases and tables using textboxes to name them with vb .net 2003 and sql 2000. If so how would I go about it?
  • MD

    Marty Davis17y agoPosted Nov 6, 2009, 5:49 AM

    I have tried to do just that and I keep getting a syntax error.I have tried many different variations of code and still get an error unless I hard code names for the DB`s and tables. I am using .net 2003 and sql 2000,just in case it makes a difference. Here is an example of my code: Dim res As String res = TB1.Text Dim strSQL As String = _ "IF EXISTS " & _ "SELECT * " & _ "FROM master..sysdatabases " & _ "WHERE Name =" + res & vbCrLf & _ "DROP DATABASE" + res & vbCrLf & _ "CREATE DATABASE" + res
  • 

    Mahesh Chand17y agoPosted Nov 4, 2009, 10:27 PM

    You need to build your SQL statement dynamically and take table name, column name etc from TextBox controls. Something like this: "CREATE TABLE " + TableNameTextBox.Text
• C

  Cory17y agoeditedPosted Jun 17, 2009, 2:51 PMEdited Jun 17, 2009, 2:54 PM

  Hi Mahesh, this tutorial has been very helpful, so thanks for your work. I do have one question though. When I run this program through the first time, everything works as expected. However, when I launch the program a second time, and click the "Create DB" button, I get a SQL Exception message saying "Database 'mydb' already exists. Choose a different database name." Similarly, when I click "Create Table", I get a message saying "There is already an object named 'myTable' in the database." This occurs even if I delete the files stored in the C:\\mysql directory. I cannot see the "mydb" database or the "myTable" data connected to my project within Visual Studio, or located in the project folder on my hard drive. However, I know the data resides somewhere, because when I click "View Table", the correct information is still displayed. So, that is my question: in what file location is the "mydb" database (and its associated "myTable" table) created? What is the relationship between this "real" data location, and the data that was saved to the C:\\mysql directory? Any explanation regarding what data is being created by the "Create DB" routine, and where it is being stored on my machine, would be a great help. Thanks again! PS -- In case it helps, I'll also share a little information about my connection string. The only way I could get the program working was to add a blank database (called "Database1.mdf") to my project from within Visual Studio. After doing this I was given a connection string of: Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True The full expanded version of this string is: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\cspicer\My Documents\Visual Studio 2008\Projects\CreateDBTest2\CreateDBTest2\Database1.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True Once I put the first connection string (containing the "Data Directory" shortcut for the full file path), the program worked correctly other than the problems I am asking about above.
  • KP

    Ken Phipps14y agoPosted Jan 31, 2012, 10:09 AM

    I'm having the same problem, after first creation of the database, it can't be created again. I deleted it from the "c:\mysql" folder where it was created, but every time I try to create it again the application throws an exception saying 'mydb' already exists. I searched the entire drive for the file "mydb_data.mdf" and it does not exist, so I can't see what what to do. In my case I'm using SQL Server Express.
• SG

  sai ganesh goli17y agoPosted Jan 20, 2009, 2:02 AM

  Dear sir, Thanks for your article.it is extremely good. I have a problem now , please help me for that. problem details: How to create a polyline feature from pixel coordinates (x & y) extracted from an image. first we have to convert pixel coordinates (x & y) to latitude/longitude coordinates called as geo coordinates.Then convert these latitude/longitude coordinates to polyline feature. please help me for this in VB.Net
  • 

    Mahesh Chand17y agoPosted Apr 6, 2009, 10:26 AM

    You may want to post your question on C# Corner forums. There are many experts there.
• DB

  Desi Bravo18y agoPosted Dec 2, 2008, 12:57 PM

  I like to let you know that your tutorials are great. But there is a problem that I am having with your tutorial on how to create a database programmatically in VB.Net. I created a new application and called is TestDB. Then I also dropped two buttons CreateDB and Create Table both buttons named btnCreateDB and btnTable and a DataGridView to retrieve the database data. When I ran the application and clicked on the CreateDB button nothing happened and then the Create Table nothing happened. I opened sql server to see if the database and the tables were created nothing did. I think this is a good tutorial creating a database programmatically I like to learn how to do that. But is not working for me. Is there something that I am doing wrong. I mean I copy and pasted and changed the naming convention in the button controls. I did emailed you and sorry if I caused any problems it won't happen again. I appreciate for your feedback.
• KR

  kala rathie18y agoPosted Mar 26, 2008, 6:10 AM

  Dear sir help me sir.i get a job in IT field.i have finished m.sc IT,help the .NET Q&A,vb,asp,ado.c# etc send reply my mail address [email protected]
  • 

    Mahesh Chand17y agoPosted Apr 7, 2009, 10:11 PM

    This is a good start. Read more articles and tutorials on this site, participate on forums and blogs and ask any questions you have. Good luck!
• KR

  kala rathie18y agoPosted Mar 26, 2008, 6:01 AM

  Dear sir article is very useful sir.Please publish your article continue sir.any comments send my mail:[email protected],[email protected].
• SD

  saket dwivedi18y agoPosted Mar 13, 2008, 8:24 AM

  hi Dear sir i m facing some problem please help me (1)How can display hierarchical xml file data into datagridview using vb.net/c#.net window based please send me full program (2) how can transfer single or all tables of ms sql server database into ms access database send me full programe my Email [email protected] [email protected]
• RN

  rikita nagar18y agoPosted Mar 11, 2008, 2:58 AM

  I had used the same code to create the database inaccess, But it wasn,t work. Can u tell me what changes i have to make in coding?Thanks in advance
  • 

    Mahesh Chand17y agoPosted Apr 6, 2009, 10:23 AM

    Are you getting any errors? Did you try to debug your code and see where and what the problem is? I would look for permissions also. Make sure you use Admin user id and pwd.
• H

  Hardey18y agoPosted Feb 24, 2008, 12:58 PM

  How do i get SQL server instance(s) running on my network from vb6 environment or VB2005 IDE? [email protected]
• BB

  bradley bumgardner18y agoPosted Jan 30, 2008, 1:50 PM

  In using this code with 2005 .net adn sqlexpress I had to change a few things for it to work: 1. Import system.data.sqlclient 2. Change 'localhost' in all the strings to '.\sqlserver' 3. in the 'ExecuteSQLStmt' routine, delete the two lines below: ConnectionString = "Integrated security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\SqlExpress;" conn.ConnectionString = ConnectionString Also, if you have Management studio express running, the database will not show up until you hit 'refresh'. Hope this helps someone!
  • 

    Mahesh Chand18y agoPosted Feb 27, 2008, 10:21 AM

    Thanks Bradley.
• AK

  asgar khan18y agoPosted Jan 14, 2008, 9:39 AM

  i have just started with asp.net i have created table with field as name roll sub month.. i want to dispaly data in grid as per the roll no selected by drop down list... can u provide me code
• JB

  jason Budas18y agoeditedPosted Dec 19, 2007, 10:29 PMEdited Dec 19, 2007, 10:31 PM

  a great article.....it helps a lot....thank u..... may i just ask if how can i search in a database....sample program pls!!
• LH

  Leuwa Hardik19y agoPosted Aug 20, 2007, 2:33 AM

  I am Nikhil to give how to connaction sql in vb.net i am crating inventary mangmant system but not conaction to sql erroor in fill data
  • DS

    duain sulch19y agoPosted Sep 30, 2007, 3:52 PM

    Hi! I want to create the database only when the database doesn´t exists. How can i verify if the database already exists in the computer? In vb.net Thanks!
  • 

    Mahesh Chand19y agoPosted Aug 23, 2007, 8:23 AM

    Connection string is defined in this article as following: ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;" Replace localhost with your server name and mydb with your database. If you are using user id and password, search this site for connection string.
• M

  Mehdi19y agoPosted Jul 8, 2007, 3:40 AM

  Thank's. Brief & useful it was.
• N

  nsahar19y agoPosted Jun 21, 2007, 5:18 AM

  this article proved to be a good piece of help...need more help about sql n database connectivity do mail me some good piece of advice.. thanks
• BB

  bin bin19y agoPosted Apr 30, 2007, 9:56 AM

  hello Mr, how to be with vb6?
  • 

    Mahesh Chand19y agoPosted Apr 30, 2007, 11:04 AM

    With VB 6.0, you will have to use ADO to execute queries. SQL queries will be same. I do not have any code sample for VB 6.
• GG

  gir gez19y agoPosted Apr 16, 2007, 8:29 AM

  create table mytable(english varchar(100),amharic Nvarchar(100)) insert into mytable values('pretty',N'konjo') insert into mytable values('nice',N'melkam') then select * from mytable; works fine, select * from mytable where english='nice' this works fine ,but select * from mytable where amharic=N'melkam'; don't work; select * from mytable where amharic='melkam'; don't work. know that amharic is a langauge which has its own alphabet, it is very different from latin alphabets, so how is it possible to select non-english langauges from table in sql server 2000? thanks,
• LS

  Leifur Saemundsson19y agoPosted Feb 25, 2007, 11:28 AM

  Dear Mr. Chand. I found your article EXTREMELY helpful and now I finally have some idea about HOW to manage. I tried many books and many articles but they all lost me in the XML parts, which I try to avoid... Thank you for your article! Leifur, Iceland.
  • 

    Mahesh Chand19y agoPosted Feb 26, 2007, 9:39 AM

    Thanks Liefur. I am glad I was any help.
• LS

  Leifur Saemundsson19y agoPosted Feb 25, 2007, 11:28 AM

  Dear Mr. Chand. I found your article EXTREMELY helpful and now I finally have some idea about HOW to manage. I tried many books and many articles but they all lost me in the XML parts, which I try to avoid... Thank you for your article! Leifur, Iceland.
• MZ

  Mehr Zeeshan19y agoPosted Feb 10, 2007, 4:16 AM

  1- we have 2 SQL tables (one contains fields like emp_id, name, designation, section and other emp related info And 2nd table contains emp_id, allowance_name, allowance_amount, deduction_name, deduction_amount) 2- we need a crystal report showing section wise employees 3- single employee data must be in a single row 4- sample report fields can be emp_name, emp-designation, allowance1, allowance2, ..., allowanceN, SumOfAllowances, deduction1, deduction2, ..., deductionN, SumOfDeductions, SumOfAllowances-SumOfDeductions. I deadly need this sum1 plz help
• RD

  Rashad Davis19y agoPosted Feb 2, 2007, 12:56 PM

  Is it possible to have a user enter information into a txtbox, and on the button click event, pull certain information from a database based solely upon the information entered?
  • 

    Mahesh Chand19y agoPosted Feb 5, 2007, 11:19 PM

    Yes it is possible. Please post all non-article related questions on forums by clicking on the Forums link on the header.
• RG

  ramesh goud20y agoPosted Apr 20, 2006, 5:43 AM

  hello sir how do we design a loginpage in asp.net using vb.net.on asp.net webpage I wil place two textboxes and twolabels with name of username and password.And two buttons one is submit and another one is register button. what code we have to write for register button. and we have to do in SQL serverdatabase i e to store the values of username and password. thank you sir.
• RG

  ramesh goud20y agoPosted Apr 20, 2006, 5:43 AM

  hello sir how do we design a loginpage in asp.net using vb.net.on asp.net webpage I wil place two textboxes and twolabels with name of username and password.And two buttons one is submit and another one is register button. what code we have to write for register button. and what I have to do in SQL serverdatabase i e to store the values of username and password. thank you sir.
• AS

  aswathy s20y agoPosted Jan 11, 2006, 10:24 AM

  sir, this article is very useful .please publish the continuation of this article