-
Jan 3rd, 2018, 07:24 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Creating database table
I wrote this code to create database table and when I run it, it returns true which means the table has been created but when I check the database, I don't see the table
Code:
public functionCreateTable() as boolean
dim con as new sqlconnection(cstrimg)
dim sqlcmd as string= "CREATE TABLE [MASTER TABLE]" &_
"(" & _
"[ID KEY] SMALLINT NOT NULL," & _
"[NAME] TEXT NOT NULL"
Dim Creator as new sql command (sqlcmd, con)
con.open
if creator.executeNonQuery() then
return true
else
return false
con.close
End function
Last edited by si_the_geek; Jan 4th, 2018 at 03:24 AM.
Reason: fixed typo in tags
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 3rd, 2018, 08:56 PM
#2
Re: Creating database table
The very first thing you need to do is to turn Option Strict On for this project and any other project you're working on, plus turn it On in the IDE options too so that it will be On for every future project. That code will not compile with Option Strict On, so it is bad code. You need to start thinking about your data types more, which will help you write more robust code.
As for this issue, if the table gets created but you don't see it then you're looking in the wrong place. Are you using a local data file, i.e. an MDF file that is part of your project and gets attached to a SQL Server Express instance at run time?
-
Jan 3rd, 2018, 09:15 PM
#3
Re: Creating database table
in my signature is a link "I swear I saved my data, where'd it run off to?" ... check it out. It probably applies in this case.
-tg
-
Jan 4th, 2018, 03:28 AM
#4
Re: Creating database table
I have a feeling that we may not be seeing the real code, as there is no ")" put in sqlcmd, and there is an extra space in new sql command
If so, there is a good chance that the cause of the problem is being hidden from us.
-
Jan 4th, 2018, 03:42 AM
#5
Re: Creating database table
Hi,
could also be a Typo..
Code:
dim con as new sqlconnection(cstrimg)
'should probably be cstring
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jan 9th, 2018, 03:11 PM
#6
Thread Starter
Hyperactive Member
Re: Creating database table
ok this is the actual code I wrote
Code:
Public Function CreateArmTable(ByVal TableName As String) As Boolean
Dim connection As New SqlConnection(CString)
Dim sqlCmd As String = "CREATE TABLE" & " " & "[" & TableName & "]" & _
"(" & _
"[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
"[STUDENT NAME] TEXT NOT NULL, " & _
"[C.A.1] SMALLINT NULL, " & _
"[C.A.2] SMALLINT NULL, " & _
"[C.A.3] SMALLINT NULL, " & _
"[EXAM] SMALLINT NULL, " & _
"[TOTAL] SMALLINT NULL, " & _
"[POSITION] SMALLINT NULL, " & _
"[GRADE] SMALLINT NULL" & _
")"
Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
connection.Open()
If CreatorCommand.ExecuteNonQuery() Then
Return True
Else
Return False
End If
connection.Close()
End Function
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 9th, 2018, 03:12 PM
#7
Thread Starter
Hyperactive Member
Re: Creating database table
All my codes option strict are always on
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 9th, 2018, 03:38 PM
#8
Re: Creating database table
Hi,
this is how I create a Table with Access...
change the OLE to SQL
Code:
Public Sub CreateTable()
Dim sSQL As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
'here you create your Table in the Database
sSQL = sSQL & " Create Table tbl_Zoo"
sSQL = sSQL & "( [ZO_ID] Integer Identity"
sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
sSQL = sSQL & ", [ZO_Surname] varChar(50) "
sSQL = sSQL & ")"
con.Open()
ExecuteSQL(con, sSQL)
con.Close()
con = Nothing
End Sub
Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
ByVal sSQL As String, _
Optional ByRef ErrMessage As String = Nothing, _
Optional ByVal TransAction As _
OleDb.OleDbTransaction = Nothing) As Integer
ErrMessage = Nothing
Try
Dim Result As Integer = 0
Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
Result = Cmd.ExecuteNonQuery
End Using
Return Result
Catch ex As Exception
ErrMessage = ex.Message
Return 0
End Try
End Function
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jan 9th, 2018, 04:49 PM
#9
Re: Creating database table
Other than field names I don't care for... I don't see anything wrong with the SQL...
If you're not getting an error, but not seeing the table... then I suspect the table is being created but not in the database file you're looking in.
What does your connection string look like? Are you using a connected database (to a SQL Server instance of some kind) or are you using a "LocalFile" ?
-tg
-
Jan 9th, 2018, 06:11 PM
#10
Thread Starter
Hyperactive Member
Re: Creating database table
Originally Posted by techgnome
Other than field names I don't care for... I don't see anything wrong with the SQL...
If you're not getting an error, but not seeing the table... then I suspect the table is being created but not in the database file you're looking in.
What does your connection string look like? Are you using a connected database (to a SQL Server instance of some kind) or are you using a "LocalFile" ?
-tg
This is my connection string
Code:
Private CString As String = "Data Source=(LocalDB)\v11.0 ;AttachDbFilename=|DataDirectory|\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 9th, 2018, 06:55 PM
#11
Thread Starter
Hyperactive Member
Re: Creating database table
Originally Posted by ChrisE
Hi,
this is how I create a Table with Access...
change the OLE to SQL
Code:
Public Sub CreateTable()
Dim sSQL As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
'here you create your Table in the Database
sSQL = sSQL & " Create Table tbl_Zoo"
sSQL = sSQL & "( [ZO_ID] Integer Identity"
sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
sSQL = sSQL & ", [ZO_Surname] varChar(50) "
sSQL = sSQL & ")"
con.Open()
ExecuteSQL(con, sSQL)
con.Close()
con = Nothing
End Sub
Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
ByVal sSQL As String, _
Optional ByRef ErrMessage As String = Nothing, _
Optional ByVal TransAction As _
OleDb.OleDbTransaction = Nothing) As Integer
ErrMessage = Nothing
Try
Dim Result As Integer = 0
Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
Result = Cmd.ExecuteNonQuery
End Using
Return Result
Catch ex As Exception
ErrMessage = ex.Message
Return 0
End Try
End Function
regards
Chris
I'm not writing an Sql database file
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 9th, 2018, 07:12 PM
#12
Re: Creating database table
Originally Posted by TATARPRO
This is my connection string
Code:
Private CString As String = "Data Source=(LocalDB)\v11.0 ;AttachDbFilename=|DataDirectory|\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"
And which database are you looking in to see the table? Is it the one in the project folder? If so then that is the wrong database. Post #3 points you to a documentation page that explains how local data files work. The first link in my signature below leads to that same page.
-
Jan 9th, 2018, 07:13 PM
#13
Re: Creating database table
Originally Posted by TATARPRO
I'm not writing an Sql database file
You are using a SQL Server database. That's what MDF files are. SqlConnection and SqlCommand are members of the SqlClient namespace, which is specifically for SQL Server.
-
Jan 9th, 2018, 07:15 PM
#14
Re: Creating database table
Originally Posted by TATARPRO
All my codes option strict are always on
Except that that's simply not possible. This line would not compile with Option Strict On:
Code:
If CreatorCommand.ExecuteNonQuery() Then
-
Jan 16th, 2018, 06:11 PM
#15
Thread Starter
Hyperactive Member
Re: Creating database table
Originally Posted by jmcilhinney
You are using a SQL Server database. That's what MDF files are. SqlConnection and SqlCommand are members of the SqlClient namespace, which is specifically for SQL Server.
I meant i'm not writing an Access database
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 16th, 2018, 06:12 PM
#16
Thread Starter
Hyperactive Member
Re: Creating database table
I've continuosly checked the compiler options and my option strict is on and i've compiled the code but it still gives me the same result
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 16th, 2018, 06:24 PM
#17
Re: Creating database table
You didn't answer my questions in post #12. If you have read the page you were directed to in post #3 then you should understand that you have a source data file in your project folder and a working data file in your output folder and that any changes you make at run time are made to the working copy, which have no effect on the source file and thus can be lost if and when the source file is next copied over the working copy. If you are making changes to the working copy and then looking at the source file for those changes then of course you won't see them.
Maybe that's not what you're doing but you've been asked to provide information will help us determine whether you are and you've failed to provide it. If we ask you for information, we are doing so because we think we need it in order to help you. Do you want that help or not? If you do then provide the information you're asked for when you're asked for it.
-
Jan 16th, 2018, 06:30 PM
#18
Re: Creating database table
Originally Posted by TATARPRO
I've continuosly checked the compiler options and my option strict is on and i've compiled the code but it still gives me the same result
You say that now but you also said it earlier when it clearly wasn't true, so it's hard to know whether you're correct now or still mistaken. More than one person has claimed that they had Option Strict On when it was actually Off. As you can see below, you would not be able to make a call to ExecuteNonQuery the subject of an If statement with Option Strict On.
-
Jan 16th, 2018, 06:33 PM
#19
Re: Creating database table
Originally Posted by TATARPRO
ok this is the actual code I wrote
Code:
Public Function CreateArmTable(ByVal TableName As String) As Boolean
Dim connection As New SqlConnection(CString)
Dim sqlCmd As String = "CREATE TABLE" & " " & "[" & TableName & "]" & _
"(" & _
"[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
"[STUDENT NAME] TEXT NOT NULL, " & _
"[C.A.1] SMALLINT NULL, " & _
"[C.A.2] SMALLINT NULL, " & _
"[C.A.3] SMALLINT NULL, " & _
"[EXAM] SMALLINT NULL, " & _
"[TOTAL] SMALLINT NULL, " & _
"[POSITION] SMALLINT NULL, " & _
"[GRADE] SMALLINT NULL" & _
")"
Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
connection.Open()
If CreatorCommand.ExecuteNonQuery() Then
Return True
Else
Return False
End If
connection.Close()
End Function
In this code, you are never closing the connection because the call to Close is after the Return statements. That's (one reason) why you use Using blocks. If you create the connection with a Using statement then it will be automatically closed when execution leaves the block, regardless of how that happens. You can hit the End Using statement directly, hit a Return statement or even throw an exception and the object created by the Using statement will still be disposed. Disposing a database connection means closing it.
-
Jan 16th, 2018, 07:15 PM
#20
Thread Starter
Hyperactive Member
Re: Creating database table
excuse me which version of Vs are you using cause mine is Vs 2012 and it shows no errors
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 16th, 2018, 07:32 PM
#21
Re: Creating database table
I don't have VS 2012 installed anywhere to test but the screenshot I provided was from VS 2017 and I just tested VS 2010 and it behaved the same way. I very much doubt that things changed after VS 2010 and then changed back again before VS 2017. If you really do have Option Strict On and you're not seeing a compilation error then your IDE is broken and you should repair or reinstall.
-
Jan 19th, 2018, 08:14 PM
#22
Thread Starter
Hyperactive Member
Re: Creating database table
Originally Posted by jmcilhinney
I don't have VS 2012 installed anywhere to test but the screenshot I provided was from VS 2017 and I just tested VS 2010 and it behaved the same way. I very much doubt that things changed after VS 2010 and then changed back again before VS 2017. If you really do have Option Strict On and you're not seeing a compilation error then your IDE is broken and you should repair or reinstall.
Ok thanks. I created a new project and I copied the code into the new project and it showed me the errors as you predicted and I've solved it.
Nothing I post is Self Reliable. Use it at your own risk
-
Jan 19th, 2018, 08:17 PM
#23
Thread Starter
Hyperactive Member
Re: Creating database table
Originally Posted by ChrisE
Hi,
this is how I create a Table with Access...
change the OLE to SQL
Code:
Public Sub CreateTable()
Dim sSQL As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
'here you create your Table in the Database
sSQL = sSQL & " Create Table tbl_Zoo"
sSQL = sSQL & "( [ZO_ID] Integer Identity"
sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
sSQL = sSQL & ", [ZO_Surname] varChar(50) "
sSQL = sSQL & ")"
con.Open()
ExecuteSQL(con, sSQL)
con.Close()
con = Nothing
End Sub
Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
ByVal sSQL As String, _
Optional ByRef ErrMessage As String = Nothing, _
Optional ByVal TransAction As _
OleDb.OleDbTransaction = Nothing) As Integer
ErrMessage = Nothing
Try
Dim Result As Integer = 0
Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
Result = Cmd.ExecuteNonQuery
End Using
Return Result
Catch ex As Exception
ErrMessage = ex.Message
Return 0
End Try
End Function
regards
Chris
Chris, thanks to you also. I learned from the code you presented and then I modified it to Sql Server and it worked.
Code:
Public Function CreateArmTable(ByVal Tablename As String) As Integer
Dim connection As New SqlConnection(CSString)
Dim sqlCmd As String = "CREATE TABLE " & " " & Tablename.ToUpper & _
"(" & _
"[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
"[STUDENT NAME] TEXT NOT NULL, " & _
"[C.A.1] SMALLINT NULL, " & _
"[C.A.2] SMALLINT NULL, " & _
"[C.A.3] SMALLINT NULL, " & _
"[EXAM] SMALLINT NULL, " & _
"[TOTAL] SMALLINT NULL, " & _
"[POSITION] SMALLINT NULL, " & _
"[GRADE] SMALLINT NULL" & _
")"
Dim result As Integer = 0
Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
Try
connection.Open()
result = CreatorCommand.ExecuteNonQuery()
connection.Close()
Catch ex As System.Data.SqlClient.SqlException
MsgBox("There is already a table in the database with that" & vbCrLf & "tablename you are trying to use.")
End Try
Return result
End Function
Nothing I post is Self Reliable. Use it at your own risk
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|