does anyone have an example of using prepared statement to insert data into MSSQL?
i looked on the site, but it seems to be a stored procedure example using param1
i know some databases use ? chars but I'm unsure about sql server
yeah, its strange. I'm trying to copy data from a mysql recordset into a mssql table with the same structure
the code below produces no errors, but no data gets written either.
in this case the table contains two fields an Id int and a varchar(50) but i have dozens of tables i want to do his with, so i want it to build itself
I'm sure i must be mssing something obvious, but i cant see what. Can you?
dim rsData as recordset = GetData("select * from " + tname) if rsdata <> nil then //Build up the SQL dim ins as string ="SET IDENTITY_INSERT [" + Tname + "] ON; " + EndOfLine + " INSERT into [" + Tname + "] (" for c as Integer = 1 to rsdata.FieldCount -1 ins = ins + "[" + rsdata.IdxField(c).Name + "], " next ins = ins + "[" + rsdata.IdxField(rsdata.FieldCount).Name + "]) VALUES (" for c as Integer = 1 to rsdata.FieldCount ins = ins + ":f" +str(c) + "," next ins = ins.Left(ins.len-1) + ")" // Now insert the data dim p as SQLPreparedStatementMBS = db.Prepare(ins) for c as Integer = 1 to rsdata.FieldCount dim t as integer = rsdata.ColumnType(c-1) select case t case 3,7 p.BindType("f"+ str(c), SQLPreparedStatementMBS.kTypeLong) case 5,10 p.BindType( "f" + str(c), SQLPreparedStatementMBS.kTypeString) case else msgbox (str(t)) end select next while not rsdata.eof for c as Integer = 1 to rsdata.FieldCount p.bind("f"+str(c), rsdata.IdxField(c).Value ) next p.SQLExecute if db2.Error then msgbox("Error : " + db2.ErrorMessage) rsdata.movenext wend end if