Please convert my pseudo code to VB

[ping]

Hi,
newbie here, please excuse my basic knowledge. Also, if I have posted this in the wrong location, please advise. Thanks.

I have a membership website using ASP.net and VB code behind
I have an SQL database called “rcm” which has a table called “teaminfo” and is structured as follows.
UniqueID
Username
Teamname

I have a page with a textbox and a button.
I want to the user to type their preferred teamname in the textbox and click the button.

Psuedo code for the button click event.
check if teamname exists in table
if teamname exists then Msgbox “team name taken”; end
else
create new table entry with UniqueID (sequence number), Username (taken from ASP.net given that user is logged in, the page will not be available to non-members) and Teamname from the textbox.
End

Any help would be greatly appreciated. Cheers.

James

 

[Kyle.Allbright]

            Dim cn As System.Data.SqlClient.SqlConnection    
            Dim cmd As System.Data.SqlClient.SqlDataAdapter 
            Dim ds As New System.Data.DataSet
            Dim dt As DataTable
            Dim dr As DataRow



          cn = New  System.Data.SqlClient.SqlConnection(CONNECTION DETAILS HERE)
            'sqltext = "select Teamname='" & _teamName & "' From db.rcm.teaminfo

            cmd = New System.Data.SqlClient.SqlDataAdapter(sqltext, cn)

            cn.Open()
            cmd.Fill(ds)
            cn.Close()
            For Each dt In ds.Tables
                If dt.Rows.Count > 0 Then
                    For Each dr In dt.Rows
                        If InStr("teamname") <> 0 Then 
                             MsgBox("team name taken")
                            Exit For
                         else
                              'Create New Table
                        End If
                    Next
                End If
            Next
            ds.Clear()

There you go

 

[ping]

Thanks Kyle, I will mess with that as soon as I get a chance. Cheers.

 

[ping]

Hi, I guess my basic knowledge may be more basic than I thought.

I get Type DataTable and DataRow not defined?
I change them to Data.DataTable and Data.DataRow and I get
Overload resolution failed because no accessible 'InStr' accepts this number of arguments.

Can I get some examples of what should be in (CONNECTION DETAILS HERE), All I have put is ("database=rtm") 'by the way I got the databse name wrong, it's not rcm, it's rtm

should sqltext not have ' in front? and if so, could you please explain the syntax, it says _teamName is not declared. does this look right...
sqltext = "select Teamname='" & TextBox1.Text & "' From db.rtm.teaminfo"

last but not least how do i actually create a new entry, ie.'create new table
psudo code...
db.rtm.teamname new entry (hope to create a new id with sequential automatically)
db.rtm.teamname.Username = currently logged in user
db.rtm.teamname.Teamname = textbox1.text

further help would be massively appreciated. Cheers.

James

 

[cjard]

Kyle's code is rather broken I'm afraid. I'd advocate posting this in the ASP.NET area of the forum, as it is little to do with SQL Server

Ask for some walkthroughs to show connecting to a database and running queries. The query you'd need to add to your dataset tableadapter (the DW3 link in my signature has tutorials for creating datasets in Windows Forms. The same theory applies to ASP, but you may prefer to follow an ASP dedicated tutorial, and I don't know the address of them I'm afraid) is a "query that returns a single value"
SELECT Count(*) FROM teams WHERE teamname = @teamname

Your VB code would look more like:

If ta.CountTeamName(THE_TEAM_NAME) > 0 Then
  'respond to the browser with an "already taken" message
  'note you CANNOT use MsgBox in ASP.NET!!!
Else 
  ta.InsertTeamName(...blah...)
End If

ta is a TableAdapter; you'll know what one of those is by the time you've finished following a few data walkthroughs

 

[MattP]

Ask for some walkthroughs to show connecting to a database and running queries. The query you'd need to add to your dataset tableadapter (the DW3 link in my signature has tutorials for creating datasets in Windows Forms. The same theory applies to ASP, but you may prefer to follow an ASP dedicated tutorial, and I don't know the address of them I'm afraid)

ASP.NET Data Access (Visual Studio)

Web Forms Data: The Official Microsoft ASP.NET Site

The Data Access Tutorials in the 2nd link will take some time to get through but have a wealth of information.

 

[ping]

Thanks cjard and MattP. I have tried following walkthroughs and found most of the ones I find are simply creating a table on a page rather than single values.

I guess I have a lot to learn, being from Excel (VBA) background, I am used to x = sheet2.range("A1") and sheet2.range("A1") = x. And that's what I am trying to acheive with this.

I will check your walkthroughs posted and hopefully learn a lot and move forward.

Thanks heaps for your responses, hopefully one day I can be as helpfull to others as you are to me.

Cheers.
James

 

[cjard]

I have tried following walkthroughs and found most of the ones I find are simply creating a table on a page rather than single values.

Tables on a page are more or less the same thing as single values, it's jsut that a table shows multiple sets of single values

In windows forms I could change from showing a table to showing a load of text boxes using just a single mouse click. I'd hope the same thing is true of asp.net pages but suffice to say:
you have a container that it loaded with data
you have a grid conencted to it
the grid shows the data

if the container only showed one row of data, and you had a load of textboxes connected to it instead, things would work out - hopefully thus going from grid -> textboxes is quite easy

 

[ping]

hey cjard,
it's not so much getting a grid or single values in a grid that I am trying to acheive, its assigning the single value to a (handle?)
for example
x = (db.rtm.teamnames WHERE username = @username)
and then the opposite
(db.rtm.teamnames WHERE username = @username) = textbox1.text (thats very broken, but the general idea)

I think I am getting the idea with some of the walkthroughs with TableAdapters and possibly DataAdapters?. i have more to go through and am sure I will figure it out eventually.

 

[ping]

cjard! thank you so much! i think i have got over the largest hurdle.

my code in the end is

        Dim ta As New rtmTableAdapters.teamnameTableAdapter()

        If ta.CountUsername(User.Identity.Name) > 0 Then
            Label2.Text = "You already have a Team Name."
        Else
            If ta.CountTeamname(TextBox1.Text) > 0 Then
                Label2.Text = "That Team Name is taken."
            Else
                Label2.Text = ""
                ta.Insert(User.Identity.Name, TextBox1.Text)
            End If
        End If

so simple really, i just didn't know about how to create tableadapters, i thought it would all be done using code. with this new found piece of knowledge, i should go a long way now with my project.

Cheers and thank you so much once again.
James

 

[cjard]

I think I am getting the idea with some of the walkthroughs with TableAdapters and possibly DataAdapters?..

A tableadapter is a device that shunts data between a database table (a specific one) and your app. It is built by VS based ona query you enter. VS understands what parameters are, so when you enter a query like:

SELECT count(*) FROM tblPeople WHere lastName = @lastName

It will eventually make a method for you that (after you have named it e.g. call it CountPeopleNamed) you can call in code simply like this:

Dim ta As New MyTableAdapter
Dim ctr as Integer = ta.CountPeopleNamed("Smith")


They can do a lot, including downloading and saving back entire rows worth of information. You can either use them in "DBDirect" mode:

ta.NewTeam("TeamName", "john Smith", "1 The Road, Cityville", "01234567890") 'runs an INSERT statement

Or you can (having made a tableadapter with SELECT * FROM teams) put a datarow together and insert that:
Dim ro as New TeamsRow
ro.Name = "John Smith"
ro.Address = "1 the road, cityville"
ro.Phone = "01234567890"
ro.TeamName = "TeamName"
ta.Update(ro) 'note the Update isnt necessarily an UPDATE sql statement.. Update() actually means 'save to db'


Even better, the TeamsRow can be data-bound to controls so you dont have to type the tiresome value assignment code. Anything typed into the control is automatically added to the relevant property(column) of the row, and you just say Update() at the end of it