hi,
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
Russ
With MBS Plugin you can always use ? Or : followed by name or ID.
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?
Russ
[code]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©.Name + "], "
next
ins = ins + “[” + rsdata.IdxField(rsdata.FieldCount).Name + “]) VALUES (”
for c as Integer = 1 to rsdata.FieldCount
ins = ins + “:f” +str© + “,”
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©, SQLPreparedStatementMBS.kTypeLong)
case 5,10
p.BindType( “f” + str©, 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©, rsdata.IdxField©.Value )
next
p.SQLExecute
if db2.Error then msgbox("Error : " + db2.ErrorMessage)
rsdata.movenext
wend
end if
[/code]
did you log something?
Like what is final SQL statement?
do you get errors?
you can turn on exceptions to not miss an error.
well, no errors. I step through in the debugger. the sql just shows the :f1 :f2, not the actual sql.
i will try with exceptions rather than checking for errors.
Ah!
i get this exception on the execute
Commands out of sync; you can’t run this command now
is it because I’m trying to set identity_insert as a first statement in the SQL do you think?
We have three ways to talk to Microsoft SQL server and not all of them allow multiple record sets.
Did you turn scrollable on? That helps for some.
What are you expecting rsdata.FieldCount to represent here?
.[quote=420483:@Tanner Lee]What are you expecting rsdata.FieldCount to represent here?[/quote]
@Tanner Lee the index of the last field in the recordset
YAY! Fixed.
I was creating a preparedstatment on the source db not the destination.
I do enjoy making myself look stupid in public 
Is this method of executing stored procedures via SQLPreparedStatementMBS expected to work on Macintosh platforms?
yes, of course.
Why?
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:
[code]
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[/code]For output functions, the recordset will not help.
Please go with SQLCommandMBS class for that. Sorry
Okay, thank you