[RESOLVED] Doing Query and Update for SQL Database

[EntityX]

Here's something I've been stuck on for some time. I used code from the ADO.NET Tutorial and tried to translate it so it applies to SQL and the database on my website. I set the Configuration Manager for Debug and so I can see a detailed error message.

Here's my code:

Option Strict On
Imports System.Web.AspNetHostingPermission
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.SqlDbType
Imports System.Data.Sql
Imports System.IO
Imports System.Text

Public Class Form1
    Dim intCurrentIndex As Integer = 0
    Dim conn As New SqlConnection()
    Dim ds As New DataSet()
    Dim da As New SqlDataAdapter()

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        conn.ConnectionString = "Network Library=DBMSSOCN;" & "Data Source=174.120.212.3,1433;" & "Initial Catalog=Database1;" & "User ID=myuserID;" & "Password=MyPassword"
        Try
            conn.Open()
            da.SelectCommand = New SqlCommand("SELECT ActivationID, EmailAddress FROM Ray Rover Activation")
            da.SelectCommand.Connection = conn
            MessageBox.Show("Connection Opened Successfully")
            DoStuff()
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Dispose()
        End Try
    End Sub

    Sub DoStuff()
        Try
            da.UpdateCommand = New SqlCommand("UPDATE Ray Rover Activation SET EmailAddress = @EmailAddress WHERE ActivationID = @ActivationID")

            '    da.SelectCommand = New SqlCommand("SELECT ActivationID, EmailAddress FROM Ray Rover Activation")
            '    da.SelectCommand.Connection = conn

            '  da.UpdateCommand = New SqlCommand("UPDATE Ray Rover Activation SET EmailAddress = @EmailAddress WHERE ActivationID = @ActivationID")
            '  da.UpdateCommand.Connection = conn
            '  da.UpdateCommand.Parameters.Add("@EmailAddress", NVarChar, 100, "EmailAddress")

            '  da.InsertCommand = New SqlCommand("INSERT INTO Ray Rover Activation(EmailAddress) VALUES(@EmailAddress)")
            '   da.InsertCommand.Connection = conn
            '  da.InsertCommand.Parameters.Add("@EmailAddress", NVarChar, 100, "EmailAddress")

            '  da.DeleteCommand = New SqlCommand("DELETE FROM Ray Rover Activation WHERE ActivationID = @ActivationID")
            '  da.DeleteCommand.Connection = conn
            '  da.DeleteCommand.Parameters.Add("@EmailAddress", NVarChar, 100, "EmailAddress")

            da.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
                FillFields()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "", MessageBoxButtons.OK, MessageBoxIcon.Warning) '
        End Try
    End Sub

    Private Sub FillFields()
        TextBoxEmailAddress.Text = ds.Tables(0).Rows(intCurrentIndex).Item("EmailAddress").ToString()
    End Sub

I have most of the lines in the DoStuff Sub commented out right now but I get the same error message either way so it's responding to the first line below Try.

Here's the error message I see. Line 52 is :
da.Fill(ds)

The table in Database1 that I'm trying to access is called Ray Rover Activation.

[brin351]

The error is saying your query is wrong

"SELECT ActivationID, EmailAddress FROM Ray Rover Activation"

It should be something like

"SELECT ActivationID, EmailAddress FROM name_Of_Table WHERE .....etc.... "

[EntityX]

The name of the table I'm trying to query is Ray Rover Activation. In the ADONet tutorial there's this line :

da.SelectCommand = New OleDbCommand("SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master")

If I try to connect to the table Ray Rover Activation from a winforms app and go through the process of creating the connection string it shows the table as "Ray Rover Activation (dbo)". I was trying various things like adding (dbo) and .dbo to the end but I still got the error message and I also tried putting in WHERE ActivationID = @ActivationID at the end but I still got the same message.

Here's a screenshot which shows Server Explorer for my website which has the SQL database I'm trying to access. There are 2 instances of the table Ray Rover Activation shown there. One is under Database1.mdf and the other under the longer name beginning win-y24. Under Database1.mdf the name of the table is Ray Rover Activation and under the longer name it's Ray Rover Activation (dbo).

Something else I just noticed. I thought the error message where it says "incorrect syntax near Activation" was referring to the line of code :

da.UpdateCommand = New SqlCommand("UPDATE Ray Rover Activation SET EmailAddress = @EmailAddress WHERE ActivationID = @ActivationID")

which is inside the DoStuff Sub but it's actually responding to the line :

da.SelectCommand = New SqlCommand("SELECT ActivationID, EmailAddress FROM Ray Rover Activation")

which is just below

Try
conn.Open()

in the Form1 Load event sub. I could see that by alterring the word Activation to Activatio. It would then say incorrect syntax near Activaio.

[sapator]

First up
Imports System.Data.Sql ? What's that?
second
conn.ConnectionString = "Network Library=DBMSSOCN;" & "Data Source=174.120.212.3,1433;" & "Initial Catalog=Database1;" & "User ID=myuserID;" & "Password=MyPassword"

use it properly and don't add to the string(it may work that way but USE IT PROPERLY)
conn.ConnectionString ="Data Source=174.120.212.3,1433;Network Library=DBMSSOCN;Initial Catalog=Database1;User ID=myuserID;Password=MyPassword;"

Third.I don't believe there is anyone serious enough about databases that will cut the name in 3 parts with spaces.
Ray Rover Activation would be much nicer like this:
Ray_Rover_Activation , hence this is where your problem is ( i wrote it last so you can view how to PROPERLY use the database)
instead of this: Ray Rover Activation you must do
this:[Ray Rover Activation]
so the select is:
"SELECT ActivationID, EmailAddress FROM [Ray Rover Activation]"
As an advice i suggest once more to create everything properly database wise.You can abuse your DAL later

[gep13]

Hello,

The problem here is the name of the Database Table. Using spaces within the table name is a very bad idea, and will cause you nothing but problems in the long run. If you can, go back and edit your database to remove them. If you can't, then you "should" be able to "fix" the issue by doing [Ray Rover Activation].

On another issue, have you tried running the same query directly against the database? I don't think you have, and if you had, you would have found that the query didn't work. You should always test the query directly against the database first, before bringing it into your code, that way you know where to start looking for a solution.

Gary

[EntityX]

Well thank you guys very much. I finally got a successful query just using brackets around Ray Rover Activation. In the future I'll name all my database tables differently.

[gep13]

Well thank you guys very much. I finally got a successful query just using brackets around Ray Rover Activation. In the future I'll name all my database tables differently.

Glad to hear that you got it sorted!

Gary