SQL parameters asp.net(vb.net)

Question

Sorry for my bad English.I have a problem in my code:

Dim sq As String = "SELECT username FROM standing WHERE username = @user"
            Dim con As New SqlConnection(Sql.ConnectionString)
            Dim cmd As New SqlCommand(sq, con)
            cmd.Parameters.Add("@user", SqlDbType.VarChar)
            cmd.Parameters("@user").Value = "contesttest"
            con.Open()
            Dim index As Integer = cmd.ExecuteNonQuery
            con.Close()
            If (index > 0) Then
                'Something..
            Else
                'Something else..
            End If

in my code,"contesttest" is exists in Database and returnedrows(index) should be greater than 0.But index is -1 !What's the problem? my connectionstring is right.
It does not matter if C# or VB.Net

You don't need c# tag for this question. Deleted.

Soner Gönül
– Soner Gönül

2013-06-07 08:45:03 +00:00
Commented Jun 7, 2013 at 8:45 — Soner Gönül
– Soner Gönül, Commented Jun 7, 2013 at 8:45

Steve · Accepted Answer · 2013-06-07 08:59:03Z

2

If your username field is an unique index (meaning that you don't have two username with the same value) then your query could be rewritten without using a SqlDataReader

Dim sq As String = "SELECT username FROM standing WHERE username = @user"
Using con SqlConnection(Sql.ConnectionString)
Using cmd As New SqlCommand(sq, con)
    cmd.Parameters.Add("@user", SqlDbType.VarChar)
    cmd.Parameters("@user").Value = "contesttest"
    con.Open()
    Dim username = cmd.ExecuteScalar
    If userName IsNot Nothing Then
        'Something..
    Else
       'Something else..
    End If
End Using
End Using

ExecuteScalar return the first column of the first row retrieved by your command. In the case you column is a unique index/primary key then you have just one row and you return just the username. So if there is something returned then you have found your user

edited Jun 7, 2013 at 8:59

answered Jun 7, 2013 at 8:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Soner Gönül · Accepted Answer · 2013-06-07 08:52:30Z

1

From MSDN;

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

This is not a problem. It is a definition of ExecuteNonQuery method.

edited Jun 7, 2013 at 8:52

answered Jun 7, 2013 at 8:46

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Comments

Clarice Bouwer · Accepted Answer · 2013-06-07 10:11:43Z

1

Use a Reader of some sort (like a SqlDataReader) to get the number of rows returned from a SELECT statement or ExecuteScalar to get a single returned value. Using ExecuteNonQuery will only return the number of rows affected when used with a SELECT statement.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Read about it on MSDN.

edited Jun 7, 2013 at 10:11

answered Jun 7, 2013 at 8:47

Clarice Bouwer

3,8214 gold badges38 silver badges58 bronze badges

Comments

Pranav · Accepted Answer · 2013-06-07 08:46:57Z

0

i guess SqlDbType.VarChar will allow only one character ..you need to pass length also for varchar.

for Example:-

  cmd.Parameters.Add("@user", SqlDbType.VarChar,80)

answered Jun 7, 2013 at 8:46

Pranav

8,9214 gold badges28 silver badges42 bronze badges

1 Comment

Pranav Over a year ago

I am not sure about VB but in C#, you need to pass length also for varchar parameter, otherwise it will take only first character..

Collectives™ on Stack Overflow

SQL parameters asp.net(vb.net)

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related