Add SQL statements in VB .NET

[CharlieChau]

Hi,

I have just learned from the link
http://support.microsoft.com/kb/821765/EN-US/ to use the
database with VB .NET

From the example of the above link, if I want to use the
table Student as reference table, I need to verify if the
SNo existing in the database, then the SName is matching
with that SNo is existed in the database. The Error to
handle the case will be:

IDERROR=1 IF SNo does not have that ID
IDERROR=2 IF SName does not match with the existing ID.

The Wizard from the "OleDbDataAdapter" automatically
generate the four SQL update, delete, insert... for me. I
want to add a SQL statements as the following:

SELECT *
FROM student
WHERE student.SNo="Input from the textbox of VB .NET".
If the return is null, the IDERROR=1

SELECT *
FROM student
WHERE student.SNo="Input from the textbox of VB .NET"
AND student.SName="Input from the textbox of VB .NET"
If the return is null, the IDERROR=2

Could someone help me to accomplish it, please.

Thanks,
/CC.

 

[Cor Ligthert]

Charlie,

I think that you can use for your question the SQLcommand.executescalar

http://msdn.microsoft.com/library/d...qlclientsqlcommandclassexecutescalartopic.asp

Go up in the links in the page how to use this.

I hope this helps?

Cor

 

[CharlieChau]

thanks you very much Cor Lightert. I did try your
suggestion with the following code:

Dim SqlConnection1 As SqlConnection
SqlConnection1 = New SqlConnection("server =
localhost;uid=;password=;database=testdb")
Dim SQLStatement As String = "SELECT * FROM
student"
Dim RecordCount As Integer
Dim SelectCommand As New SqlClient.SqlCommand
(SQLStatement, SqlConnection1)
Try
SqlConnection1.Open()
RecordCount = CInt(SelectCommand.ExecuteScalar
())
Catch ex As Exception
MessageBox.Show("Failed to execute command")
Finally
SqlConnection1.Close()
End Try
MessageBox.Show("Customers table contains " &
RecordCount & " records")

But I can not well establish the connection. It seems to
me that the parameters SqlConnection1 does not work well,
but I do not know which parameters. Since I always get
the catch error message: ("Failed to execute command").
Could you give me some more hints.

Thanks,
/CC.

 

[Cor Ligthert]

Charlie,

Change the the lines without the > and see what it does (if there are no
security prolbems, I think it would work, however I can not see everything
in a message)

Dim SqlConnection1 As SqlConnection

SqlConnection1 = New SqlConnection("Server =
localhost;Database=YourDataBaseName;Integrated Security = SSPI")

'You had an OleDb connectionstring

Dim SQLStatement As String = "SELECT Count(*) FROM student"

Dim RecordCount As Integer
Dim SelectCommand As New SqlClient.SqlCommand
(SQLStatement, SqlConnection1)
Try
SqlConnection1.Open()
RecordCount = CInt(SelectCommand.ExecuteScalar
())
Catch ex As Exception
MessageBox.Show(ex.tostring)


Finally
SqlConnection1.Close()
End Try
MessageBox.Show("Customers table contains " &
RecordCount & " records")

Maybe it will go better, (do not forget to put in the right name of your
database in the connectionstring).

I hope this helps?

Cor