How to insert a row into SQL db with VBScript 1

[ije]

Hope this is simple for the experts out there...

I need to create a VBScript that will insert a row into db on SQL 2000. There is a System ODBC connection available if that is easier pointing the database in question

Details are be:
ODBC Datasource Name: SMS
Type: SQL 2000
Table: Outgoing ( has two columns - PhNumber and TextMsg
Db User: sa
DB Password: blank
Server Name: SQLServer

I have a process that will recreate this file each time it is required, inserting the 2 text values ( for the fields PhNumber and TextMsg ) into the appropriate section of the vbscript file.

many thanks
ije

 

[ralphtrent]

Heres what you can do:
Not sure if you established a connection and just need the sql but Ill show you both

dim conn, insertSQL
set conn = createobject("ADODB.Connection&quot
conn.open "dsn=SMS;uid=sa;pwd=blank;database=databasename"
conn.execute("insert into Outgoing values('PhNumberValue','TextMsgValue')"

conn.close
set conn = nothing

Let me know if that helps

 

[ije]

thanks ralphtrent! I needed both parts - mainly the connection bit though...many thanks for the example!

 

[ije]

Ok this is not a new topic really, but an extension of what I need to achieve the first objective!! As this script is used to insert rows in a table used to send mobile phone sms messages out of, i need to remove dashes of mobile phone entries.

When I try the following script I get "The name 'mob1' is not permitted in this context. Only constants, expressions or variables allowed here. Column names not permitted" ( btw - there is no column called mob1 )....

any ideas on how to do the data checking?

Dim conn, insertSQL
Dim mob1,mob1verified
mob1 = replace("0455-555-555","-","&quot
Set conn = createobject("ADODB.Connection&quot
conn.open "dsn=sms;uid=sa;pwd=;database=sms"
conn.execute("insert into outgoing values('should be no dashes in mobile number here',mob1)&quot
conn.close
Set conn = Nothing

 

[ije]

...an extension of what I need to achieve the first objective!! As this script is used to insert rows in a table used to send mobile phone sms messages out of, i need to remove dashes of mobile phone entries.

When I try the following script I get "The name 'mob1' is not permitted in this context. Only constants, expressions or variables allowed here. Column names not permitted" ( btw - there is no column called mob1 )....

any ideas on how to do the data checking?

Dim conn, insertSQL
Dim mob1,mob1verified
mob1 = replace("0455-555-555","-","&quot
Set conn = createobject("ADODB.Connection&quot
conn.open "dsn=sms;uid=sa;pwd=;database=sms"
conn.execute("insert into outgoing values('should be no dashes in mobile number here',mob1)&quot
conn.close
Set conn = Nothing

 

[ralphtrent]

your insert looks a little confusing. Do you have two columns in the table one for phone number w/dashes and one for phone number w/o dashes? If so then you might want to try this:
mob1 = request.form("phone number&quot 'ie 0455-555-555 (the number is backwards btw)
mob1NoDash = replcase(mob1,"-","&quot
conn.execute("insert into outgoing value ('mob1NoDash','mob1')&quot
You might have been passing a numeric value (mob1) to a field that is not numeric on the database. check that out.

Let me know how it goes.

 

[DANZIG]

What would the connection string be if your not wanting to use a dsn and keep all of the connection in the script?

I'm trying to do something similar but keep getting odbc errors.

 

[ije]

no there is only one mobile phone field....i ended up doing it this way:

Dim conn, insertSQL
Dim mob1
mob1 = replace("0455-555-555","-","&quot
Set conn = createobject("ADODB.Connection&quot
conn.open "dsn=sms;uid=sa;pwd=;database=sms"
conn.execute("insert into outgoing values('this is the text field - there should be no dashes in corresponding mobile number here','" & trim(cstr(mob1)) & "')&quot
conn.close
Set conn = Nothing

many thanks!!