How to run a SQL statement?

[onechuck]

In VB.NET 2005 and I'm developing a website. First, is there a way to import a .sql file into the database using VB.NET? If that is not possible, is there a way to execute Create and Insert SQL statement? I right click on the Tables and choose New Query and then put in my Create query and try to execute but it gave me a bunch of errors. So what I did is right click and choose Add New Table...I end up and manually adding the table again when I already have the SQL statement to create them. Anyway, after I created the table, I right click on the table and choose New Query and again when I put in an Insert SQL statement, I couldn't execute the statement. It kept giving me errors. The Insert error is, "Unable to parse query text". The error for creating a table is, "The CREATE TABLE SQL construct or statement is not supported.".

 

[djj55]

Hello, there are a couple of ways to do this. I have had great luck with the ADODB (you need to add a reference). This allows me to execute the SQL string I want. Also as I come from VB I like recordsets better than the datasets.

As an example:

    Public DBCONN As New ADODB.Connection
    Public CONNSTRING As String = "DSN=TL_PCQT;"
    DBCONN.Open(CONNSTRING)
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tbl_TitleOrder"
    rs.Open(strSQL, DBCONN,_
        ADODB.CursorTypeEnum.adOpenStatic)
' Or
    rs = DBCONN.Execute(strSQL)

Note that both the rs.Open and rs = handle the data somewhat differently.

You can execute any valid SQL command this way. For and UPDATE or INSERT I use the rs = way.

Good Luck
djj

 

[onechuck]

Thanks! I got it working using another application. I was referring to when adding and creating new tables. I'm not talking from the user's point of view.

 

[jebenson]

You reall should avoid using the older COM objects (that is, ADODB) when doing database operations in .NET. Use the .NET data objects (ADO .NET). Here's some sample code to create a table in an existing database on SQLServer:

Dim cmd As SqlCommand
Dim conn As SqlConnection

Try

conn = New SqlConnection("Data Source=KONGO;Initial Catalog=Timekeeping;Trusted_Connection=Yes")

conn.Open()

cmd = New SqlCommand

With cmd
.CommandType = CommandType.Text
.Connection = conn
.CommandText = "CREATE TABLE Test (Field1 varchar(20), Field2 Int)"
End With

cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
Return
End Try

MsgBox("Table 'Test' Created")

conn.Close()

conn.Dispose()
conn = Nothing

cmd.Dispose()
cmd = Nothing

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira