user3187105 user3187105 - 15 days ago 8
Vb.net Question

SQL Server Table is accessible in SSMS but returns error in VB.NET

As the title says, I have a database and a table am trying to connect to using vb.net, if I run the select using vb.net it says invalid object name '[table name]
but the same SQL query copy pasted in SSMS runs OK, am going crazy over it. Any help would be highly appreciated, thanks in advance
Here is the code

Public Sub importVehicles()
Dim con As New SqlConnection()
Dim configs = ConfigurationManager.AppSettings
If configs.Count = 0 Then
MsgBox("No settings configured for connection", MsgBoxStyle.Exclamation, "Error")
Dim dResult As Integer = Settings.ShowDialog
If dResult = DialogResult.OK Then
Dim changedConfigs = ConfigurationManager.AppSettings
con.ConnectionString = "Server=" & changedConfigs("appServer") & ";Database=" &
changedConfigs("appDatabase") & ";User=" & changedConfigs("appUser") & ";Pwd=" & changedConfigs("appPass")
Else
Application.Exit()
End If
Else
con.ConnectionString = "Server=" & configs("appServer") & ";Database=" &
configs("appDatabase") & ";User=" & configs("appUser") & ";Pwd=" & configs("appPass")
End If

Try
con.Open()
Dim sageCmd As New SqlCommand("select * from _btblFAAsset", con)
Dim sageDa As New SqlDataAdapter(sageCmd)
Dim sageDs As New DataSet
sageDa.Fill(sageDs)
con.Close()

If Not sageDs.Tables(0).Columns.Contains("ucFARegistrationNumber") And
Not sageDs.Tables(0).Columns.Contains("ulFAType") And
Not sageDs.Tables(0).Columns.Contains("uiFACapacity") And
Not sageDs.Tables(0).Columns.Contains("ucFAMake") Then

MsgBox("Please add the following user defined fields to Sage before importing, " &
"RegistrationNumber as a String of size 200, Capacity of type Integer, Make of type " &
"String of size 200, Model of type String size 200 and Type of type look up with " &
"options '4x4 and Mini Van'", MsgBoxStyle.Exclamation,
"Missing Fields")

Exit Sub
End If

openConnection(con)
Dim cmd As New SqlCommand("select * from [dbo].[_btblFAAsset]", con)
Dim da As New SqlDataAdapter(sageCmd)
Dim ds As New DataSet
da.Fill(ds)

Dim available As New Collection
For local As Integer = 0 To ds.Tables(0).Rows.Count - 1
available.Add(ds.Tables(0).Rows(local).Item("registration_number"))
Next
Dim imported As Integer = 0
Dim values As New List(Of String)
For sage As Integer = 0 To sageDs.Tables(0).Rows.Count - 1
If available.Contains(sageDs.Tables(0).Rows(sage).Item("ucFARegistrationNumber")) Then
Continue For
End If
values.Add("('" & sageDs.Tables(0).Rows(sage).Item("ucFARegistrationNumber") & "', '" &
sageDs.Tables(0).Rows(sage).Item("ulFAType") & "', '" &
sageDs.Tables(0).Rows(sage).Item("ucFAMake") & "', " &
sageDs.Tables(0).Rows(sage).Item("uiFACapacity") & ")")
imported = imported + 1
Next
If values.Count > 0 Then
cmd.CommandText = "INSERT INTO vehicles(registration_number, type, model, capacity) VALUES " &
String.Join(", ", values)
cmd.ExecuteNonQuery()
MsgBox(imported & " assets successfully imported", MsgBoxStyle.Information, "Success")
Else
MsgBox("No new vehicles fetched, local data is upto date", MsgBoxStyle.Information, "Success")
End If

con.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
End Try
End Sub

Answer

A few things to look at:

  1. Make sure that the Database that you are actually connecting to has the table.

  2. If the table exists in only one Schema, then Schema-qualify the table name in the first query, just like you are doing in the second query (i.e. dbo._btblFAAsset).

  3. Or, if this table exists as the same name in multiple Schemas, then you need to check the SQL Server Login that you are connecting as, including what the Login's default Schema is (since that is where it will look for objects).

  4. Make sure you are executing the correct SqlCommand. I'm not sure what the intention is for the second command, but it does at least seem odd to create a new SqlCommand object and then execute the first one:

    Dim cmd As New SqlCommand("select * from [dbo].[_btblFAAsset]", con)
    Dim da As New SqlDataAdapter(sageCmd)
    

    As you can see, you create cmd yet pass the original sageCmd into the SqlDataAdapter.