SQL insert from ASP class
-
I'm stumped. I have a function in a class module that can be called from within the project, which will insert an event into a database. I call it ok, but it fails with: Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'localhostname'. I suspect its something to do with the connection string, but I just cant work it out. I dont know how else it could get my local pc host name in the error. Have I taken the wrong approach completely for this?
Public Shared Function CreateEvent( ByVal strMessage, ByVal strCategory, Optional ByVal strEnvironment = "0", Optional ByVal strNodeId = "0") Dim sqlConn = New System.Data.SqlClient.SqlConnection sqlConn.ConnectionString = "Server=;Initial Catalog=eventlog;User Id=user;password=password;Trusted_Connection=False" Dim SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand Dim strEventTime As Date = DateTime.Now Dim strUserName As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name 'Try Dim cmdInsert As New SqlCommand("INSERT INTO eventlog (enviromnent_id, category, node_id, event, username, event_time) VALUES (" _ & "'" & strEnvironment & "', " _ & "'" & strCategory & "', " _ & "'" & strNodeId & "', " _ & "'" & strMessage & "', " _ & "'" & strUserName & "', " _ & "'" & strEventTime & "', ", sqlConn) sqlConn.Open() cmdInsert.ExecuteNonQuery() 'Catch ex As Exception 'Finally sqlConn.Close() 'End Try End Function -
I'm stumped. I have a function in a class module that can be called from within the project, which will insert an event into a database. I call it ok, but it fails with: Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'localhostname'. I suspect its something to do with the connection string, but I just cant work it out. I dont know how else it could get my local pc host name in the error. Have I taken the wrong approach completely for this?
Public Shared Function CreateEvent( ByVal strMessage, ByVal strCategory, Optional ByVal strEnvironment = "0", Optional ByVal strNodeId = "0") Dim sqlConn = New System.Data.SqlClient.SqlConnection sqlConn.ConnectionString = "Server=;Initial Catalog=eventlog;User Id=user;password=password;Trusted_Connection=False" Dim SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand Dim strEventTime As Date = DateTime.Now Dim strUserName As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name 'Try Dim cmdInsert As New SqlCommand("INSERT INTO eventlog (enviromnent_id, category, node_id, event, username, event_time) VALUES (" _ & "'" & strEnvironment & "', " _ & "'" & strCategory & "', " _ & "'" & strNodeId & "', " _ & "'" & strMessage & "', " _ & "'" & strUserName & "', " _ & "'" & strEventTime & "', ", sqlConn) sqlConn.Open() cmdInsert.ExecuteNonQuery() 'Catch ex As Exception 'Finally sqlConn.Close() 'End Try End FunctionIt's more likely that the data you are using is corrupting the sql query. You don't encode the strings properly when you put them in the query, so any string containing an apostrophe would break it. Replace apostrophes in the strings with double apostrophes, or better yet, use a parameterized query. --- b { font-weight: normal; }
-
It's more likely that the data you are using is corrupting the sql query. You don't encode the strings properly when you put them in the query, so any string containing an apostrophe would break it. Replace apostrophes in the strings with double apostrophes, or better yet, use a parameterized query. --- b { font-weight: normal; }
Guffa wrote:
Replace apostrophes in the strings with double apostrophes
Aiiii!! :eek: I would never recommend that. I'd go straight for the paramerised query and not even tell them how to escape apostrophes. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
I'm stumped. I have a function in a class module that can be called from within the project, which will insert an event into a database. I call it ok, but it fails with: Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'localhostname'. I suspect its something to do with the connection string, but I just cant work it out. I dont know how else it could get my local pc host name in the error. Have I taken the wrong approach completely for this?
Public Shared Function CreateEvent( ByVal strMessage, ByVal strCategory, Optional ByVal strEnvironment = "0", Optional ByVal strNodeId = "0") Dim sqlConn = New System.Data.SqlClient.SqlConnection sqlConn.ConnectionString = "Server=;Initial Catalog=eventlog;User Id=user;password=password;Trusted_Connection=False" Dim SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand Dim strEventTime As Date = DateTime.Now Dim strUserName As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name 'Try Dim cmdInsert As New SqlCommand("INSERT INTO eventlog (enviromnent_id, category, node_id, event, username, event_time) VALUES (" _ & "'" & strEnvironment & "', " _ & "'" & strCategory & "', " _ & "'" & strNodeId & "', " _ & "'" & strMessage & "', " _ & "'" & strUserName & "', " _ & "'" & strEventTime & "', ", sqlConn) sqlConn.Open() cmdInsert.ExecuteNonQuery() 'Catch ex As Exception 'Finally sqlConn.Close() 'End Try End FunctionYour connection string does not specify a server. You must specify a server. If you want to use the local machine you can use
(local), if you know you are going to use TCP/IP as the transport you can also specifylocalhost, but I don't recommend that if it is sitting on the same machine. Next, as Guffa already pointed out, the way your SQL Query is being built up is poorly designed. In fact it would allow a SQL Injection Attack to take place quite easily. You may like to read SQL Injection Attacks and Tips on How to Prevent Them[^] in order to defend yourself, and your database from a mallicious attack via your web application. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell -
It's more likely that the data you are using is corrupting the sql query. You don't encode the strings properly when you put them in the query, so any string containing an apostrophe would break it. Replace apostrophes in the strings with double apostrophes, or better yet, use a parameterized query. --- b { font-weight: normal; }
-
Your connection string does not specify a server. You must specify a server. If you want to use the local machine you can use
(local), if you know you are going to use TCP/IP as the transport you can also specifylocalhost, but I don't recommend that if it is sitting on the same machine. Next, as Guffa already pointed out, the way your SQL Query is being built up is poorly designed. In fact it would allow a SQL Injection Attack to take place quite easily. You may like to read SQL Injection Attacks and Tips on How to Prevent Them[^] in order to defend yourself, and your database from a mallicious attack via your web application. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell