VB Array to Database?

[shelzmike]

To start with, let me lay out the situation. I am a student at the very end of my last assignment of a beginner - intermediate VB class (we have been using VB 2005 Express + MSSQL Express 2005). Anyhow, I really like coding with VB and understand most of what we have learned thus far. However, we have a final project due that is a complete build-from-scratch idea that is useful and contains 10 of 20 requirements as listed on the Rubric.

Without explaining the whole project, the final thing that I am having an issue with (mainly because we delved only once into using Databases - ADO.NET, and that was a VERY basic applicaiton and the database was already built.

Here is the basics of what I am trying to do:

This is a program that generates a sequential list of numbers, based on user input (min and max, plus total amount of numbers) to be used for a raffle type of fundraiser.

So user generates a list of numbers, they enter the lowest number and the total number of ticket/numbers. I can use a Do...While...Lo op to create the list of numbers and then save those into a declared one dimensional array. All good and well, I know how to do that.

The next part is where I am getting hung up. I will have a Next or Save button that when the user clicks it, I want the array to then be saved in a database, with the generated numbers used as the Primary Key of the DB. The database will also have other fields (First Name, Last Name, Phone, Address, Email) that will be Null until a later portion of the application (where I will then enter peoples names who bought the tickets, and will then use a random number generator to pick the winner). It sounds simple enough but cannot figure out how to do it.

Also, if possible, I would love to be able to have each generation of numbers be in a different table (i.e., for more than one raffle, if the user so chooses). So I need help on creating a database (the ones we have used so far have already been created, we just manipulated them) and saving the array as listed above - the other fields will be aded from another form, and I know how to do that.

I am looking for the simplest way to achieve this as it is a simple application.

Thanks!

Mike

[debasisdas]

try to fetch tha value from the arrray and insert to the database till the end of the array.

[rpicilli]

Hi there.

There is no easy way to create a database and table from scratch. In fact this will take from you a lot of efforts and line of code.

The best way to do that is create the database and table by hand and after that ask you database software (Access or Sql) to generate the SQL Script.

As exemple here you find a sql script to create a Database named Test and a Table named myTable.

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Test')
	DROP DATABASE [Test]
GO

CREATE DATABASE [Test]  ON (NAME = N'Test_Data', FILENAME = N'D:\Arquivos de programas\Microsoft SQL Server\MSSQL\data\Test_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'Test_Log', FILENAME = N'D:\Arquivos de programas\Microsoft SQL Server\MSSQL\data\Test_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Latin1_General_CI_AS
GO

use [Test]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO

CREATE TABLE [dbo].[MyTable] (
	[id] [int] NULL ,
	[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[LastName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[BornDt] [smalldatetime] NULL ,
	[Gender] [char] (1) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

As you can see, in your project you'll a lot to type.

By the description of project you are working, I think you will have more issues in near future. As you mencioned you'll save in a data base a certain number of "number" and in the future complete this table with other data. If those numbers has any connection with the names, how you'll know which number is attached to which name?

Good luck

Rpicilli

[shelzmike]

Thanks for the info. I came across this code on MSDN that seems to be doing what I need it to. All I want to do intially is set up a blank database that has two tables - one that has a column for the Raffle Numbers (generated and saved from Array) and the date of the generation. The Raffle number will be the PK, then on the second table, I will have fields that contain the following: Ticket ID (which is the PK also and is the same raffle numbers as the other table), First Name, Last Name, Contact Info. Then I will use another form that is is binded to the second database so that I can use the Navigator (or search) to sort through the numbers and enter peoples names as they are sold.

So essentially, why couldn't I just use the code (below) to create the database? And if I did, what code do I add to manipulate it so I can create the different tables, columns, and keys. By the way, I do have Access and could use that - I have a lot more knowledge with Access, would that be easier at all? I really wanted to use SQL though since I want to learn better how to use it. Thanks!

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnCreateDatabase.Click
    Dim str As String

    Dim myConn As SqlConnection = New SqlConnection("Server=(local)\netsdk;" & _
                                                    "uid=sa;pwd=;database=master")

    str = "CREATE DATABASE MyDatabase ON PRIMARY " & _
          "(NAME = MyDatabase_Data, " & _
          " FILENAME = 'D:\MyFolder\MyDatabaseData.mdf', " & _
          " SIZE = 2MB, " & _
          " MAXSIZE = 10MB, " & _
          " FILEGROWTH = 10%) " & _
          " LOG ON " & _
          "(NAME = MyDatabase_Log, " & _
          " FILENAME = 'D:\MyFolder\MyDatabaseLog.ldf', " & _
          " SIZE = 1MB, " & _
          " MAXSIZE = 5MB, " & _
          " FILEGROWTH = 10%) "

    Dim myCommand As SqlCommand = New SqlCommand(str, myConn)

    Try
        myConn.Open()
        myCommand.ExecuteNonQuery()
        MessageBox.Show("Database is created successfully", _
                        "MyProgram", MessageBoxButtons.OK, _
                         MessageBoxIcon.Information)
       Catch ex As Exception
           MessageBox.Show(ex.ToString())
       Finally
           If (myConn.State = ConnectionState.Open) Then
               myConn.Close()
           End If
       End Try

End Sub

[debasisdas]

So essentially, why couldn't I just use the code (below) to create the database?

Simply because database is not created at run time, even though you can do that.