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
I have a stored procedure on an M$SQL server called "sp_AssignFields" that accepts 6 parameters, the last two of which are outputs. For example:
ALTER PROCEDURE [dbo].[sp_AssignFields] -- Parameters @Ble_id VARCHAR(30), @Password VARCHAR(16), @SerialNumber VARCHAR(16), @Trace VARCHAR(MAX), @IsNew BIT OUT, @ErrorMessage VARCHAR(MAX) = '' OUT AS -- Setup ... etc...
I have tried multiple ways to create an SQLPreparedStatementMBS, but so far have only been able to "brute-force" it with a complete sql statement and
db.Select( sql )
The example using "Param1" isn't clear to me...
Any help is much appreciated.
// Using the example code, this is how I started:
Dim strBLE_ID As String = "MyBleId" Dim strPassword As String = "MyPassword" Dim strSerial As String = "0123456789ABCDEF" Dim strTraceInfo As String = "My App created this." Dim boIsNewReturned As Boolean = False Dim strErrorReturned As String Dim sql as string = "dbo.sp_AssignFields" Dim ps As SQLPreparedStatementMBS = db.Prepare(sql) // @Ble_id VARCHAR(30), // @Password VARCHAR(16), // @SerialNumber VARCHAR(16), // @Trace VARCHAR(255), // @IsNew BIT OUT, // @ErrorMessage VARCHAR(MAX) = '' OUT ps.Bind( "Ble_id", strBLE_ID ) ps.BindType( "Ble_id", ps.kTypeString ) ps.Bind( "Password", strPassword ) ps.BindType( "Password", ps.kTypeString ) ps.Bind( "SerialNumber", strSerial ) ps.BindType( "Password", ps.kTypeString ) ps.Bind( "Trace", strTraceInfo ) ps.BindType( "Trace", ps.kTypeString ) ps.Bind( "IsNew", boIsNewReturned ) ps.BindType( "IsNew", ps.kTypeBool ) ps.Bind( "ErrorMessage", strErrorReturned ) ps.BindType( "ErrorMessage", ps.kTypeString ) dim r As RecordSet = ps.SQLSelect