Bobski Bobski - 5 days ago 5
SQL Question

Capturing a SQL INSERT statement with additional values in SQL Server

I have an INSERT statement in my vb.net application. Something along the way of...

SQL = " Insert into tableA (Value1, Value2, Value3) Values (Mark1, City2, State3) "


What I'm trying to do is pass this into a function that will actually insert the SQL statement. I am trying to insert a copy of this sql statement in a LOG table.

Public Function (InsertSQL as String) As Boolean
nSql = "INSERT INTO tblSQLLOG ( InsertSQL, Date, User) VALUES ( "
nSql += "'" & InsertSQL + "', "
nSql += "'" & Now() & "', "
nSql += CStr(userName) + ") "

cmd = New SqlCommand(nSql, conn)
End Function


So now if i check my nSQL it looks something like....

Insert into tblSQLLOG (insert sql, date,user) values ('insert Insert into tableA (Value1, Value2, Value3) Values ('Mark1', 'City2', 'State3')','11/30/2016 8:46:41 AM', 'Bobby')


In this insert statement I'm gettig an error near Value1 - I don't know what i'm doing wrong. Everything looks fine to me.

Answer

Yes you should use parameters. that said the issue is that you have single quotes within the the string. Notice the Replace() for InsertSQL

Public Function (InsertSQL as String) As Boolean
 nSql = "INSERT INTO tblSQLLOG ( InsertSQL, Date, User) VALUES ( "
 nSql += "'" & Replace(InsertSQL,"'","''") + "', "
 nSql += "'" & Now() & "', "
 nSql += CStr(userName) + ") "

 cmd = New SqlCommand(nSql, conn)
 End Function
Comments