MSSQL SQL Server library

[alanyu]

This library, a tidied up version of http://www.b4x.com/forum/additional-libraries/3082-ms-sql-library.html allows access to SQL Server databases.

A rudimentary help file is included that describes the available methods and properties but it is expected that you already know what do do in order to connect to a SQL Server instance because I don't! However you do need System.Data.SqlClient.Dll from post #8 of this thread on your device.

EDIT :- Version 1.7 posted. See post #8 for details.

EDIT :- Version 1.8 posted. See post #10 for details.

EDIT :- Version 1.9 posted. See post #13 for details.

EDIT :- Version 2.0 posted. See post #32 for details.

HI,

I use this dll can be connected server, but sql.ReadField can't read data always # ERROR #
Sub Button1_Click
sql.New1
Dim connected As Boolean

connected = sql.Open("Persist Security Info=false;Integrated Security=false;Server=" & server & ", " & port &"; initial catalog=" & database & "; user id="& login & "; password=" & pass)

If connected = True Then

ss=sql.ExecuteNonQuery("Select * FROM test WHERE barcode='" & name.Text & "'")

Msgbox(sql.ConnectionState)
Msgbox(sql.ReadField("name") & sql.ReadField("name") & ss)

Else
Msgbox("Unable to connect to server")
End If

sql.Close

End Sub

 

[agraham]

I don't do databases but I think you should be using ExecuteQuery for a Select to obtain a table containing the required data.

 

[alanyu]

I don't do databases but I think you should be using ExecuteQuery for a Select to obtain a table containing the required data.

Thank you for your help，I tried to replace , but the result is still the same

 

[alanyu]

I don't do databases but I think you should be using ExecuteQuery for a Select to obtain a table containing the required data.

I'm using 2012mssql sever

 

[alanyu]

I don't do databases but I think you should be using ExecuteQuery for a Select to obtain a table containing the required data.

I'm using 2012mssql sever

 

[mjcoon]

Thank you for your help，I tried to replace , but the result is still the same

I think that this response must have been intended for the other thread about compiling.

What agraham is pointing out is that a "Select" command is a query on the database and thus should be issued using ExecuteQuery() and not ExecuteNonQuery().

This is a rather fundamental distinction in use of SQL so agraham did not think it needed further elaboration. But by the same logic, it would be good if the error message had pointed out what the cause of the problem was!

Mike.

 

[alanyu]

I think that this response must have been intended for the other thread about compiling.

What agraham is pointing out is that a "Select" command is a query on the database and thus should be issued using ExecuteQuery() and not ExecuteNonQuery().

This is a rather fundamental distinction in use of SQL so agraham did not think it needed further elaboration. But by the same logic, it would be good if the error message had pointed out what the cause of the problem was!

Mike.

This is my code, do you can see the error

Sub Globals
End Sub
Sub App_Start
form1.Show

End Sub
Sub Button1_Click
Mssql.New1
Dim connected As Boolean
server="SQL5004.Smarterasp.net"
port="1433"
database="DB_9AACA3_alan"
login="DB_9AACA3_alan_admin"
pass="blq4ever"

connected = Mssql.Open("Persist Security Info=false;Integrated Security=false;Server=" & server & ", " & port &"; initial catalog=" & database & "; user id="& login & "; password=" & pass)

If connected = True Then
ss=Mssql.ExecuteQuery("Select * FROM Warehouse WHERE barcode='" & code.Text & "'")
'ss=Mssql.ExecuteQuery("Select * FROM Warehouse WHERE barcode='1'")
Msgbox(Mssql.ReadField("test1") & ss)
Else
Msgbox("Unable to connect to server")
End If

Mssql.Close

End Sub

 

[alanyu]

This is my code, do you can see the error

Sub Globals
End Sub
Sub App_Start
form1.Show

End Sub
Sub Button1_Click
Mssql.New1
Dim connected As Boolean
server="SQL5004.Smarterasp.net"
port="1433"
database="DB_9AACA3_alan"
login="DB_9AACA3_alan_admin"
pass="blq4ever"

connected = Mssql.Open("Persist Security Info=false;Integrated Security=false;Server=" & server & ", " & port &"; initial catalog=" & database & "; user id="& login & "; password=" & pass)

If connected = True Then
ss=Mssql.ExecuteQuery("Select * FROM Warehouse WHERE barcode='" & code.Text & "'")
'ss=Mssql.ExecuteQuery("Select * FROM Warehouse WHERE barcode='1'")
Msgbox(Mssql.ReadField("test1") & ss)
Else
Msgbox("Unable to connect to server")
End If

Mssql.Close

End Sub

I resolved,

 

[cirollo]

Hi alanyu,

I'm getting the same errore, how do you resolved?
this is my code

Sub BtnRicerca_Click
   StrQry="select * from StoreArticolo where Codice ='"&TxtCodice.Text&"'"
   sql1.ExecuteQuery(StrQry)
   label6.Text=sql1.ReadField(1)
End Sub

 

[cirollo]

tried also

label6.Text=sql1.ReadField("MyField")