Tag Archives: Microsoft

Private Sub btnCreateDim_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDim.Click
Try
Dim tblerror, unqerror, pkerror, valerror As String
tblerror = “”
unqerror = “”
pkerror = “”
valerror = “”

Dim sqlcmd As System.Data.SqlClient.SqlCommand
Dim sqladd As System.Data.SqlClient.SqlCommand

If (cbTableName.SelectedIndex = -1) Then
tblerror = “Please select a Table Name”
End If

If (txtUnique.Text = “”) Then
unqerror = “Please complete the Unique field”
End If

If (txtPK.Text = “”) Then
pkerror = “Please complete the Primary Key field”
End If
If (txtValUnique.Text = “”) Then
valerror = “Please supply a val for the unique field”
End If
If ((tblerror.Length > 0) Or (unqerror.Length > 0) Or (pkerror.Length > 0) Or (valerror.Length > 0)) Then
MessageBox.Show(tblerror + vbCrLf + unqerror + vbCrLf + pkerror + vbCrLf + valerror, “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If

If (cbTypeUnique.SelectedItem.ToString = “varchar” Or cbTypeUnique.SelectedItem.ToString = “float” Or cbTypeUnique.SelectedItem.ToString = “decimal”) Then
sqltestUniquePK = “Select syscolumns.name from syscolumns where((syscolumns.name = ‘” & txtUnique.Text & “‘) or (syscolumns.name = ‘” & txtPK.Text & “‘))”
sqlcmd = New System.Data.SqlClient.SqlCommand(sqltestUniquePK, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqlcmd.Connection.Open()
objreader = sqlcmd.ExecuteReader

While objreader.Read
MessageBox.Show(“Please rename the following field before proceeding: ” + objreader.GetString(0), “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End While
sqlcmd.Connection.Close()

sql = “CREATE TABLE ” + cbTableName.SelectedItem.ToString + “(” + txtPK.Text + ” INT IDENTITY (0, 1) NOT NULL PRIMARY KEY,” + txtUnique.Text + ” ” + cbTypeUnique.SelectedItem.ToString + ” (” + txtValUnique.Text + “) ” + ” unique ” + “, ” + sqlstrdim + “)”
sqladd = New System.Data.SqlClient.SqlCommand(sql, New System.Data.SqlClient.SqlConnection(ConnectionString))

sqladd.Connection.Open()
sqladd.ExecuteNonQuery()
sqladd.Connection.Close()

Else

sqltestUniquePK = “Select syscolumns.name from syscolumns where((syscolumns.name = ‘” & txtUnique.Text & “‘) or (syscolumns.name = ‘” & txtPK.Text & “‘))”
sqlcmd = New System.Data.SqlClient.SqlCommand(sqltestUniquePK, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqlcmd.Connection.Open()
objreader = sqlcmd.ExecuteReader

While objreader.Read
MessageBox.Show(“Please rename the following field: ” + objreader.GetString(0), “Information”, MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End While

sqlcmd.Connection.Close()

sql = “CREATE TABLE ” + cbTableName.SelectedItem.ToString + “(” + txtPK.Text + ” INT IDENTITY (0, 1) NOT NULL PRIMARY KEY,” + txtUnique.Text + ” ” + cbTypeUnique.SelectedItem.ToString + txtValUnique.Text + ” unique ” + ” , ” + sqlstrdim + “)”
sqladd = New System.Data.SqlClient.SqlCommand(sql, New System.Data.SqlClient.SqlConnection(ConnectionString))
sqladd.Connection.Open()
sqladd.ExecuteNonQuery()
sqladd.Connection.Close()

End If

MessageBox.Show(“The table ” + cbTableName.SelectedItem.ToString + ” was successfully created”, “Created Table”, MessageBoxButtons.OK, MessageBoxIcon.Information)

Dim cmdFTableload As New System.Data.SqlClient.SqlCommand

cmdFTableload.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
cmdFTableload.Connection.Open()
sqltesttable = “Select Sysobjects.name from SysObjects where((Sysobjects.name like ‘%Fact%’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)”
cmdFTableload = New System.Data.SqlClient.SqlCommand(sqltesttable, cmdFTableload.Connection)

objreader = cmdFTableload.ExecuteReader

While objreader.Read
cbFTableName.Items.Add(objreader.GetString(0))

End While

cmdFTableload.Connection.Close()

‘Add a new row for the newly created table
‘Dim sqlcmdaddtotable As New System.Data.SqlClient.SqlCommand
‘sqlcmdaddtotable.CommandText = “INSERT INTO ” + cbTableName.SelectedItem.ToString + ” (” + txtPK.Text + ” , ” + txtUnique.Text + ” , ” + fieldname + “) ” + “Values()”

‘sqlcmdaddtotable.Connection = New System.Data.SqlClient.SqlConnection(ConnectionString)
‘sqlcmdaddtotable.Connection.Open()
‘sqlcmdaddtotable.ExecuteNonQuery()
‘sqlcmdaddtotable.Connection.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub