Web Development

Ask something
Question Locked

sql/asp.NET/vb.net Programing Question

0 helpful
11 replies

by brian

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am currently creating a program in ASP.net/vb.net using an sql db. I am using the id (key) column as the report number for the reports. What i want to do is on page load, create a row using an insert statement (which i have working). Then they fill in the information, and on the submit button, have it update that row that was created earlier (which i have working). My problem is that i have to use auto postback for the other checkbox on the page which will show a text box on click (thus using auto postback). when they choose the check box, it creates a new row in the db which i don't want to have happen for the postback. is there a way to stop this from happening. I am doing it this way so that it will display the report number, and also so the report number can not be taken by any body else creating a new report, it will take the next number in line.

11 replies

by tony hopkinson

If you want a simple identifier for the user and a column, let them give it a nice name. RPTJan, Fred's Daily...

Look at all the obstacles you having to negotiate to do this, there's a reason it's painful, it's not a good idea.
Your key is an internal implementation detail, it's irrelevant to the user

All pain, no gain, don't do it.

by brian

Understandable, however, think of this system as a PO system, all the PO numbers will auto sequence up. If somebody has generated a request for a new PO number, you wouldn't want the next request to generate with the same number. Even if the PO is canceled, there is just a blank line of information for that number, no biggie...what matters most is that each number is auto generated sequentialy, thus the reason i was using the ID column (key) because it genertes as an Integer. Granted the person will be able to create a name for the report, but some names will be the exact same (if you think of report names as vendors of a PO), thus i would not be able to use that approach. I hope this gives a little insight as to what i'm looking at doing...unless i can autonumber more than one column in SQL (and if so, it may be more beneficial to take this approach, however i am unsure of this)...

by tony hopkinson

Still think you are tying yourself in knots for nothing.

Vendor names should n't be on a PO
That should be vendorID linked to Vendors which should have the name.....

So no I can't think of it like that.

So your real problem is that
you want to

Insert into orders to force the next number in the sequence, so you can show it on a form to be filled in with the real detail.

So the second transaction has to be an update, your's is an insert.

You gave giot two queries or are chnage froim insert to update ?

Can't say much more without the code.

by brian

This is for the page where people will fill in information:
on page load...
Dim firstinsert As String = "insert into newRequest (originalcr, createdby, createddate, requestname, ues, cns, urr, crr, uep, cnp, fat, ctp, other, othertext, originalnotes) values ('', '', '1/1/1900', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '', '')"

Dim headinfo As String = "Select TOP 1 id from newRequest ORDER BY id DESC "
Dim mysqlconn2 As New SqlConnection(connstr)
Try
mysqlconn2.Open()
Dim rptinfostuff As New SqlCommand(headinfo, mysqlconn2)
Dim dr1 As SqlDataReader = rptinfostuff.ExecuteReader
While dr1.Read
servicerequestNumber_label.Text = (dr1("id"))
'requestnumber = servicerequestNumber_label.Text
Session("correctrequestnumber") = servicerequestNumber_label.Text()
End While
On Submit Button:
Dim check1 As String
Dim check2 As String
Dim check3 As String
Dim check4 As String
Dim check5 As String
Dim check6 As String
Dim check7 As String
Dim check8 As String
Dim check9 As String

If Me.updatescript_Check.Checked = True Then
check1 = "1"
Else
check1 = "0"
End If

If Me.newscript_Check.Checked = True Then
check2 = "1"
Else
check2 = "0"
End If

If Me.updateresrule_Check.Checked = True Then
check3 = "1"
Else
check3 = "0"
End If

If Me.createresrule_Check.Checked = True Then
check4 = "1"
Else
check4 = "0"
End If

If Me.updateprocedure_Check.Checked = True Then
check5 = "1"
Else
check5 = "0"
End If

If Me.createprocedure_Check.Checked = True Then
check6 = "1"
Else
check6 = "0"
End If

If Me.furtheragenttraining_Check.Checked = True Then
check7 = "1"
Else
check7 = "0"
End If

If Me.newtrainingprocedure_Check.Checked = True Then
check8 = "1"
Else
check8 = "0"
End If

If Me.othercheck_Check.Checked = True Then
check9 = "1"
Else
check9 = "0"
End If

Session("check01") = check1
Session("check02") = check2
Session("check03") = check3
Session("check04") = check4
Session("check05") = check5
Session("check06") = check6
Session("check07") = check7
Session("check08") = check8
Session("check09") = check9
Session("crnumber") = originatingcrNumber_label.Text()
Session("createdby") = createdBy_label.Text()
Session("requestname") = requestname_Text.Text()
Session("notes") = notesbox_Text.Text()

On Request Added Successfully Page:
Public Sub addgoodstuff(ByVal crnumber, ByVal createdby, ByVal requestname, ByVal notes, ByVal correctrequestnumber, ByVal check01, ByVal check02, ByVal check03, ByVal check04, ByVal check05, ByVal check06, ByVal check07, ByVal check08, ByVal check09)

by brian

Dim gatherstuff As String = "UPDATE newRequest SET originalcr = '" & crnumber & "', createdby = '" & createdby & "', createddate = '" & Date.Now & "', requestname = '" & requestname & "', ues = '" & check01 & "', cns = '" & check02 & "', urr = '" & check03 & "', crr = '" & check04 & "', uep = '" & check05 & "', cnp = '" & check06 & "', fat = '" & check07 & "', ctp = '" & check08 & "', other = '" & check09 & "', otherText = '""', originalnotes = '" & notes & "' WHERE id = '" & correctrequestnumber & "'"
'Response.Write(gatherstuff)
Dim mysqlconn As New SqlConnection(connstr)
Try
mysqlconn.Open()
Dim updatestuff As New SqlCommand(gatherstuff, mysqlconn)
updatestuff.ExecuteNonQuery()

Catch ex As Exception
Response.Write("" & ex.Message & "
" & ex.StackTrace & "
" & ex.Source & "
")
End Try
mysqlconn.Close()
mysqlconn.Dispose()

Protected Sub Page_PreLoad(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
addgoodstuff(Session("crnumber"), Session("createdby"), Session("requestname"), Session("notes"), Session("correctrequestnumber"), Session("check01"), Session("check02"), Session("check03"), Session("check04"), Session("check05"), Session("check06"), Session("check07"), Session("check08"), Session("check09"))
End Sub

by brian

What it is being used for is service requests...however the way i want it to operate is like a PO system, autogening a number on page load, and not allowing it to be taken if somebody else goes in to create a new request while another is being created. i will eventually modify this code to create our internal PO system as well...this is just step one of a 5 phase project. If you can decipher the code, any pointers/help would be wonderful!

by tony hopkinson

you'll be pleased to know it's not obvious. :(

What does the extra record look like?
the insert or a duplicate?

Ps
look up Select @@Identity...

Also building up sql strings from user input is is major nono, look up parameterised queries.

It's too long since I did ASP, but I'd want to do the insert get the number and then build the update form separately

Separates the functions and you get amend out of it.

by brian

That definatly makes sence, make the insert a function that happens on a seperate page...now why didn't i think of that?!? as for the extra record, it is all blank...however the update does update the correct field (the field number specified as ID). All of this is webpage based so...however i will definatly look up those two suggestions! Thank you! I'll try it out and let you know what happens with the insert on a seperate page redirecting to the user page.

by brian

Creating a start page with the insert statement, and having it redirect on page load to the user input page worked flawlessly!!!!!!!!!!!!!!!!! YOU ARE A GENIUS!!!!!! I will however look into the other ways mentioned to do this, i am semi new to the programming world, so i do things the way that makes sence to me even if it isn't the correct way, still learning! THANK YOU AGAIN!

by tony hopkinson

The don't build sql from user input (use parameterised queries is one to learn early), it's a very bad habit to burn in.

Look up SQLInjection

This discussion is closed to new replies.