insert statement 1

[Ju1c3]

ok i am trying to take info typed in a text box and have it inserted as a new field in a database. so like i put in physician for a company type, i want a new comapny type to be physician in the company types table in the database. this is some of the code i am using:


the connection / insert:

<%

Set rsCompanyTypes = Server.CreateObject("ADODB.Recordset")
rsCompanyTypes.ActiveConnection = MM_WoosterChamberData_STRING
rsCompanyTypes.Source = "SELECT * FROM COMPANYTYPES"
rsCompanyTypes.CursorType = 0
rsCompanyTypes.CursorLocation = 2
rsCompanyTypes.LockType = 2
rsCompanyTypes.Open()
rsCompanyTypes("CompanyType") = CompanyType
%>

the html text box with update button:

        <table align="center">


            <tr valign="baseline">
              <td width="130" align="right" nowrap><font size="2" face="MS Sans Serif">Enter new company type:</font></td>
              <td width="284"> <input name="CompanyType" type="text" size="32" maxlength="255">
              </td>
            </tr>

            <tr valign="baseline">
              <td nowrap align="right"><font size="2" face="MS Sans Serif">&nbsp;</font></td>
              <td> <input type="submit" value="Update Company Types"> </td>
            </tr>
          </table>

--------------------------------------------------------




if anyone could help with this i would appreciate it

 

[johnpayback]

Okay, first I apologize if I've misunderstood your problem but hopefully it will give you some ideas.

Here is an example of adding data into a field in your SQL table using VBScript. I think it would be less confusing if you changed the name of the user input value to not match the name of the table. I've used strCompanyType for the user's input value. I've also madeup the name of "Type" for the field in the database for my example.

rsCompanyTypes.AddNew 'add new record to destination DB; this would be like your insert query
'assign your values to the corresponding fields; just like the values section of your query
rsCompanyTypes.Fields.Item("Type").Value = strCompanyType 'sets the field "Type" in the CompanyType table to the value typed by the user which I've renamed strCompanyType
rsCompanyTypes.Update 'update the recordset of the destination DB

I've assumed a lot in my example but I hope it is understandable. Hopefully it will at least get the gears turning for you. In any case maybe we can get some help from others in the forum and get a lot better example than mine.

JP

 

[jfdabiri]

why not just use update & sql statement instead of recordset, etc.?
here's some code:

strSQL = "UPDATE my_table SET some_field = 'some value' WHERE ID_FIELD = '123456';"
objConnection.Execute strSQL

 

[tsuji]

[tt]<%
[green]CompanyType=request("CompanyType")[/green]
'etc etc
Set rsCompanyTypes = Server.CreateObject("ADODB.Recordset")
'etc etc
[red]'[/red]rsCompanyTypes.CursorLocation = 2
rsCompanyTypes.CursorLocation = [blue]3[/blue]
'etc etc
rsCompanyTypes.Open()
'if you just want to change the first record returned then
'no need the addnew here, if you want a new record then you need.
[blue]rs.addnew[/blue]
rsCompanyTypes("CompanyType") = CompanyType
[blue]rsCompanyTypes.update[/blue]
'etc etc
[/tt]

 

[Ju1c3]

i was using recordset because thats what the page started with in the first place. basically i am doing a favor for the local chamber of commerce and i am editing someone elses work. they had no clue what the heck they were doing because this is like the 7th page, alon with the database i have had to modify. it is crazy plus the fact they wrote it in dreamweaver so its hard to decipher the code. anyways, with your code tsuji, it trys to do the update before i even load the page fully and click the submit button. it complains about the database not accepting zero length strings, which is a good thing because a zero length string in this table would throw off some of the other drop down boxes i have running on other pages to search by company type or when they add a new company i use this table to allow them to select the company type. basically, how do i make it wait to try the update till i click the submit button?

 

[tsuji]

If both section of what you've shown are on the same page, you have to submit the page to itself to do the update. In that case, either you have some session variable to control the state of the page (whether it has been served blank awaiting submitting back or whether it is now submitting back). If you don't have that you can use the conditional on the CompanyType to make sure the "state" of it as mentioned above and serve as a bonus to validate the non-emptyness of the submitted value. Like this.
[tt]
<%
'etc etc
CompanyType=trim(request("CompanyType"))
if CompanyType<>"" then
'do the stuff addnew or purely changing the 1st record
'etc etc (script above)
'after that redirect to some other page
else
'serve the page blank, ie, doing nothing
end if
%>
<html>
<!-- etc etc, make sure the form action is to submit to itself -->
</html>
[/tt]

 

[Ju1c3]

thanks very much tsuji, i had started to use the conditional before you posted, i just didnt go that route. i was trying to use len and it didnt like it at all. i was trying to do it normal java or c# style and i am not real familiar with the vbscripting. other then that, it works now and the customer will be happy.