I'm using an ODBC db connection to connect to my sql server and tryingto insert data into a table I've created, but I keep getting an errorsaying that the table doesn't exist. This is the first time I'veever used ODBC and DSN to connect to a db so I don't konw if there areqerks associated with this, but I've tried everything I can thinkof. I've changed the name of the table, placed it in brackets,placed "dbo" infront of the name, and still I get the same error aboutthe table not existing. My "Intial Catalog" in my connectionstring is the correct db, I just can't seem to get it going.
Any suggestions?
This is usually due to a table ownership discrepancy. If youqualify your table name with the owner do you have better success? E.g.instead of myTable use huffmaster.myTable (substitute the owner name forhuffmaster).
|||No, the owner is "dbo" and that doesn't seem to help.
|||OK, can you post the DSN (obfuscate the password), the connection string, and the exact error message?
|||Here's my Error:
ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Application'.
And here's my Connection String:
<addkey="LiquorLiabilityConnection_Local" value="Server=macdaddy;InitialCatalog=LiqourLiability;User ID=***;Password=***;" />
|||
It doesn't look like your connection string is incorrect. What about your code? Can you share some?
|||Here's my code that creates the connection and tries to push the insert:******Segment From Web Page Code Behind************
Dim sql As String
sql = "INSERT INTO [dbo].[Application] " & insert & "VALUES " & values
'insert & values are string variables I create based on what was filled out.
Try
ll.InsertApplication(sql)
Catch
pnlIntro.Visible = False
pnlError.Visible = True
divError.Controls.Add(New LiteralControl(Err.Description.ToString &"<br><br>"))
End Try
******Segment From Class File***************
Public Sub InsertApplication(ByVal sql As String)
Dim connection As New OdbcConnection()
connection = New OdbcConnection()
connection.ConnectionString= ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection")
Dim command As New OdbcCommand(sql, connection)
connection.Open()
command.ExecuteNonQuery()
connection.Close()
End Sub
I think if the connection was bad itwould throw an error on Opening the connection, but it waits until thequery executes. And it's not until it looks for my 'Application'table. I'm just confused.
|||
It seems you may have misspelt the name of the database specified inthe Initial Catalog, but I agree that your problem does not lookconnection-related. Also, I would stick with the attributesoutlined for SQL Server - ODBC athttp://www.connectionstrings.com.
Can you supply us the value contained in command.CommandText immediately before your ExecuteNonQuery?
|||My CommandText looks like this:
INSERT INTO [dbo].[Application] ('4_state', '5_state', '9_state','10_state', '11_state', '14_years', '14_months', '14_man_years','14_man_months', '15_years', '15_months', '15_stories', '21_sqft','21_apl_sqft', '21_apt_sqft', '21_units', '21_lro_sqft','22_mon_thurs_o', '22_mon_thurs_c', '22_fri_o', '22_fri_c', '22_sat_o','22_sat_c', '22_sun_o', '22_sun_c', '23a_open', '23b_open', '23c_open','23d_open', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33','34', '35', '36', '37', '38', '39', '40', '41', '42a', '42b', '42e','42f', '42g', '44', '45_food_p', '45_food_n', '45_alch_p', '45_alch_n','45_alch_tr_p', '45_alch_tr_n', '45_cc_p', '45_cc_n', '45_opc_p','45_opc_n', '45_other_p', '45_other_n', '48_gen', '48_fire', '48_prod','48_med', '48_eac', '48_pers') VALUES ('Alabama', 'Georgia', 'Alabama','Alabama', 'Alabama', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0','0', '0', 'Closed', 'Closed', 'Closed', 'Closed', 'Closed', 'Closed','Closed', 'Closed', 'Open', 'Open', 'Open', 'Open', 'No', 'No', 'No','No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No', 'No','No', 'No', 'Average age 21-25', 'No', 'Yes', 'No', 'No', 'No', 'Yes','0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '654','654', '654', '654', '654', '654')
Please ignore the funny values and field names, this is about as short as I could go with it without deleting any of it.
|||FWIW I made a simple page based on your code and it is working OK for me:
I had to enclose the field names beginning with a numeric character insquare brackets, and I used one of the connection string formats fromhttp://www.connectionstrings.com.
<%@. Page Language="VB"%>
<%@. Import Namespace="System.Data.ODBC"%>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim sql As String
sql = "INSERT INTO [dbo].[Application] ([4_state], [5_state]) VALUES ('Alabama','Pennsylvania')"
Try
InsertApplication(sql)
Catch
divError.Controls.Add(New LiteralControl(Err.Description.ToString & "<br><br>"))
End Try
End Sub
Public Sub InsertApplication(ByVal sql As String)
Dim connection As New OdbcConnection()
connection = New OdbcConnection()
connection.ConnectionString = "Driver={SQL Server};Server=(local);Database=test;Trusted_Connection=yes;"Dim command As New OdbcCommand(sql, connection)
connection.Open()
command.ExecuteNonQuery()
connection.Close()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<div id="divError" runat="server">
Any errors will appear here
</div>
</form>
</body>
</html>
|||I put all my column names in "[]" & that seems to have got thingssolved for me now. I'm no longer getting an error concerning thetable, just data conversion errors (ex. varchar to money). Thanksfor all the help.
|||I still don't understand why you were receiving that "invalid objectname dbo.Application" error message. The error I received aboutthe column names was:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '_state
Had that been the message to begin with we would have had this figuredout long ago. In any case, I am glad you got this problem sortedand are moving on to the next. :-)
|||Now that I've moved to my next problem, let me ask a question. Isthere a way to INSERT Null values into a table. For instance ifI'm preparing parameters for a stored procedure:
Dim param as New OdbcParameter("@.name", OdbcType.Varchar)
How would I make the above parameter of value NULL? Do you justnot specify a value (ie, para.Value = ?)? Or it there some otherway of doing this?
|||Dinakar has a nice blog post on how to do this:
Entering NULL values into SQL Server 2000 using Parameterized Queries (including date columns)
|||Now that I've got the DBNull.Value in place, I run my stored proocedure and get a new error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'InsertApplication' expects parameter '@.1', which was not supplied.
This is the first parameter that has DBNull.Value assigned to it. soI'm not sure exactly what is going on. It doesn't have anythingto do with expecting nulls in the stored procedure does it?
